Hacker News Reader

Comment by throwawaydingus | Hacker News Reader

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?

View on HN

> 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.

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.

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.

> 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-corruptio..., which shows a similar issue on SQL Server.

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.

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.

> 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.

Software has bugs.

Sometimes they're missed in testing.

Do you hold the software that you write to the same standard?

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).

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-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.

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.

> Again, that's okay.

Don't need to be reassuring to HN commenters :)

So out of all the bugs you choose this one for tarnishing its reputation? Don't go looking for postgresql and fsync then...

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?

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.

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)

Reply on news.ycombinator.com