5 ms·

What? That seems absurdly apologist even for me and I love postgres. Everyone seems to be downplaying this corruption issue saying it only happens when x or y

What? That seems absurdly apologist even for me and I love postgres. Everyone seems to be downplaying this corruption issue saying it only happens when x or y and it can be avoided by doing complex operation z but… if build anything that isn’t a toy or demo on Postgres you NEED to build indexes concurrently. They shipped optimization to a feature to let indexes be built concurrently yet failed to perform thorough enough testing to ensure concurrent updates don’t disrupt the correctness of the index? Yikes, doesn't exactly make me confident in using any new Postgres features. Further, there’s no workaround that wouldn’t cause a production outage due to requiring a table lock until they release the new build which is somehow not as soon as it’s available. How are we not more alarmed by the impact of this?

mattashii 4y ago
> Everyone seems to be downplaying this corruption issue saying it only happens when x or y and it can be avoided by doing complex operation z but… I don't say corruption is not happening, but I clarify that you might not be affected: this corruption is not as "silent" as the title of the original post would make you think (it only occurs during concurrent reindex/index creation, which is not something that occurs naturally). > if build anything that isn’t a toy or demo on Postgres you NEED to build indexes concurrently I fully disagree. Many databases of customers I've seen are <5GB in size (largest table ~800MB) and used by latency-sensitive workloads only 8 hours each day, while also being mostly read-only. Locking DDL outside that 8-hour window would be perfectly fine, and even within that window non-concurrent reindex generally would have been OK - sorting 800MB does take some 10 seconds, but that is only a small hiccup for data modifying workloads. > Further, there’s no workaround that wouldn’t cause a production outage due to requiring a table lock until they release the new build which is somehow not as soon as it’s available. As my comment describes, that is incorrect. If you carefully maintain snapshots in concurrent connections on that database, you can safely reindex the table concurrently while having as many locks on the table as PG13 had. These connections do not need to hold a lock on the target table, they only need to register and hold their snapshot at the right times.
- tremon 4y ago
  this corruption is not as "silent" Whether corruption is silent or not has no relation to how it can be caused. It just means that the corruption is not automatically detected and/or resolved, and the system will happily use the corrupted data. Which seems to be the case here, as you admit that both detection and fixing require manual intervention.
  - mattashii 4y ago
    The title implies that data is silently being corrupted, which is true, but in well-known and detectable workloads (CIC/RIC). However, contrary to what the title would make you think, once they are created the indexes do not get more corrupt over time; the corruption does not spread. Instead, they get less and less corrupt over time as the rows missing from the index are either updated in a non-HOT manner, or deleted.
claytongulick 4y ago
Software has bugs. Sometimes they're missed in testing. Do you hold the software that you write to the same standard?
- rattray 4y ago
  To be fair, I think most users of Postgres hold it to a higher standard than the software they write themselves, and reasonably so. Postgres has developed a sterling reputation for reliability, and this does tarnish it somewhat. (Not by much in my personal view, but not zero either).
  - snuxoll 4y ago
    Not the first time there’s been a PostgreSQL bug like this, like always it will get a regression test and the PGDG will be better for it. It’s not like other database products haven’t had very similar issues, either [https://support.microsoft.com/en-us/topic/fix-data-corruption-occurs-in-clustered-index-when-you-run-online-index-rebuild-in-sql-server-2012-or-sql-server-2014-075b0c04-1260-1716-6b9d-737ada4f8686 https://support.microsoft.com/en-us/topic/fix-data-corruptio...] Hell, last night I literally had a MSSQL server where Windows Server Failover Clustering randomly decided to delete all of the AlwaysOn Availability Group configurations from the node; along with random NTFS corruption (no unclean shutdown, SAN is fine) resulting in us having to restore master and msdb from backups before we could rejoin it to those AGs. Bugs happen.
    - rattray 4y ago
      Sure. But notice how we're now comparing Postgres to MSSQL? Generally I think of it as head-and-shoulders better than it. I still do, but by a little less. That's okay. Did they act responsibly, fix it quickly, and communicate effectively? Yes, tremendously so – which makes me feel overall better about using Postgres generally. But, I'll feel ever so slightly more hesitant about adopting a major version shortly after it comes out next time. Again, that's okay.
      - 4y ago
eurasiantiger 4y ago
rastignack 4y ago
Those type of issues happened with Oracle database, Microsoft sql server, MySQL. That’s the reason why you should always be at least one major release behind the latest.
deleted 4y ago
[deleted]
merpkz 4y ago
Why do you say that anything but toy and demo NEED concurrent indexes? So far I have built toys and demos and never needed concurrent indexes, are they a must to dramatically speed up production workloads or am I missing something?
- zasdffaa 4y ago
- blep_ 4y ago
  Non-concurrent indexing locks the table until it's done creating the index. If it's a large table, it locks it for a long time. This makes things trying to use that table time out. This is a non-issue on things that don't have real traffic going to them, or on small enough tables that the indexing time is trivial.
- 411111111111111 4y ago
  It's just a question of locking. You'll be fighting tooth and nail against anything larger then a row level lock if you're targeting the consumer internet and allow user generated data/user writes. you'll probably never care if that's not the case, which is arguably the usual situation. Even b2c Websites often don't have user generated content to speak of, so they're pretty much just talking from a bubble (I do get where they're coming from, I couldnt have done them either at some of my previous employers)
baq 4y ago
> if build anything that isn’t a toy or demo on Postgres you NEED to build indexes concurrently. except when you can't, because you have so many writes the reindex doesn't ever finish and the DB grinds down to a halt with 300 load. don't ask me how I know.
atombender 4y ago
There's nothing apologist about the comment you're replying to. The parent is not defending the behaviour; they are explaining which scenarios are affected, and what the appropriate mitigations are. Nobody here is denying that it's a serious issue.
Someone 4y ago
> They shipped optimization to a feature to let indexes be built concurrently yet failed to perform thorough enough testing to ensure concurrent updates don’t disrupt the correctness of the index? From what I can tell, a problem _may_ occur in those circumstances, but there’s no guarantee it _will_ occur. That can make finding it in testing difficult. A performant ACID database is a very complex piece of software. It is a pipe dream to think about any such feature would ship without bugs. See for example https://support.microsoft.com/en-us/topic/fix-data-corruption-occurs-in-clustered-index-when-you-run-online-index-rebuild-in-sql-server-2012-or-sql-server-2014-075b0c04-1260-1716-6b9d-737ada4f8686 https://support.microsoft.com/en-us/topic/fix-data-corruptio..., which shows a similar issue on SQL Server.

mattashii 4y ago

> Everyone seems to be downplaying this corruption issue saying it only happens when x or y and it can be avoided by doing complex operation z but… I don't say corruption is not happening, but I clarify that you might not be affected: this corruption is not as "silent" as the title of the original post would make you think (it only occurs during concurrent reindex/index creation, which is not something that occurs naturally). > if build anything that isn’t a toy or demo on Postgres you NEED to build indexes concurrently I fully disagree. Many databases of customers I've seen are <5GB in size (largest table ~800MB) and used by latency-sensitive workloads only 8 hours each day, while also being mostly read-only. Locking DDL outside that 8-hour window would be perfectly fine, and even within that window non-concurrent reindex generally would have been OK - sorting 800MB does take some 10 seconds, but that is only a small hiccup for data modifying workloads. > Further, there’s no workaround that wouldn’t cause a production outage due to requiring a table lock until they release the new build which is somehow not as soon as it’s available. As my comment describes, that is incorrect. If you carefully maintain snapshots in concurrent connections on that database, you can safely reindex the table concurrently while having as many locks on the table as PG13 had. These connections do not need to hold a lock on the target table, they only need to register and hold their snapshot at the right times.

tremon 4y ago
this corruption is not as "silent" Whether corruption is silent or not has no relation to how it can be caused. It just means that the corruption is not automatically detected and/or resolved, and the system will happily use the corrupted data. Which seems to be the case here, as you admit that both detection and fixing require manual intervention.
- mattashii 4y ago
  The title implies that data is silently being corrupted, which is true, but in well-known and detectable workloads (CIC/RIC). However, contrary to what the title would make you think, once they are created the indexes do not get more corrupt over time; the corruption does not spread. Instead, they get less and less corrupt over time as the rows missing from the index are either updated in a non-HOT manner, or deleted.

claytongulick 4y ago

Software has bugs. Sometimes they're missed in testing. Do you hold the software that you write to the same standard?

rattray 4y ago
To be fair, I think most users of Postgres hold it to a higher standard than the software they write themselves, and reasonably so. Postgres has developed a sterling reputation for reliability, and this does tarnish it somewhat. (Not by much in my personal view, but not zero either).
- snuxoll 4y ago
  Not the first time there’s been a PostgreSQL bug like this, like always it will get a regression test and the PGDG will be better for it. It’s not like other database products haven’t had very similar issues, either [https://support.microsoft.com/en-us/topic/fix-data-corruption-occurs-in-clustered-index-when-you-run-online-index-rebuild-in-sql-server-2012-or-sql-server-2014-075b0c04-1260-1716-6b9d-737ada4f8686 https://support.microsoft.com/en-us/topic/fix-data-corruptio...] Hell, last night I literally had a MSSQL server where Windows Server Failover Clustering randomly decided to delete all of the AlwaysOn Availability Group configurations from the node; along with random NTFS corruption (no unclean shutdown, SAN is fine) resulting in us having to restore master and msdb from backups before we could rejoin it to those AGs. Bugs happen.
  - rattray 4y ago
    Sure. But notice how we're now comparing Postgres to MSSQL? Generally I think of it as head-and-shoulders better than it. I still do, but by a little less. That's okay. Did they act responsibly, fix it quickly, and communicate effectively? Yes, tremendously so – which makes me feel overall better about using Postgres generally. But, I'll feel ever so slightly more hesitant about adopting a major version shortly after it comes out next time. Again, that's okay.
    - 4y ago

eurasiantiger 4y ago

rastignack 4y ago

Those type of issues happened with Oracle database, Microsoft sql server, MySQL. That’s the reason why you should always be at least one major release behind the latest.

deleted 4y ago

[deleted]

merpkz 4y ago

Why do you say that anything but toy and demo NEED concurrent indexes? So far I have built toys and demos and never needed concurrent indexes, are they a must to dramatically speed up production workloads or am I missing something?

zasdffaa 4y ago
blep_ 4y ago
Non-concurrent indexing locks the table until it's done creating the index. If it's a large table, it locks it for a long time. This makes things trying to use that table time out. This is a non-issue on things that don't have real traffic going to them, or on small enough tables that the indexing time is trivial.
411111111111111 4y ago
It's just a question of locking. You'll be fighting tooth and nail against anything larger then a row level lock if you're targeting the consumer internet and allow user generated data/user writes. you'll probably never care if that's not the case, which is arguably the usual situation. Even b2c Websites often don't have user generated content to speak of, so they're pretty much just talking from a bubble (I do get where they're coming from, I couldnt have done them either at some of my previous employers)

baq 4y ago

> if build anything that isn’t a toy or demo on Postgres you NEED to build indexes concurrently. except when you can't, because you have so many writes the reindex doesn't ever finish and the DB grinds down to a halt with 300 load. don't ask me how I know.

atombender 4y ago

There's nothing apologist about the comment you're replying to. The parent is not defending the behaviour; they are explaining which scenarios are affected, and what the appropriate mitigations are. Nobody here is denying that it's a serious issue.

Someone 4y ago

> They shipped optimization to a feature to let indexes be built concurrently yet failed to perform thorough enough testing to ensure concurrent updates don’t disrupt the correctness of the index? From what I can tell, a problem _may_ occur in those circumstances, but there’s no guarantee it _will_ occur. That can make finding it in testing difficult. A performant ACID database is a very complex piece of software. It is a pipe dream to think about any such feature would ship without bugs. See for example https://support.microsoft.com/en-us/topic/fix-data-corruption-occurs-in-clustered-index-when-you-run-online-index-rebuild-in-sql-server-2012-or-sql-server-2014-075b0c04-1260-1716-6b9d-737ada4f8686 https://support.microsoft.com/en-us/topic/fix-data-corruptio..., which shows a similar issue on SQL Server.