Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Important PostgreSQL 14 update to avoid silent corruption of indexes

migops.com

Post reply on HN
Print view View original

21–30 of 101 posts

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#21

Post by throwawaylinux »

colordrops wrote: post #10
msk20 wrote: post #6

Looking at the file with changes https://github.com/postgres/postgres/blob/master/src/backend... , I have to say this source code repository is so well documented/commented and structured, I really gives you a huge trust in postgres to be used in your stack.

File could definitely be broken up a bit. Over 5000 lines! Just a nitpick though.

5000 lines looks like half are comments or whitespace, none of the functions look more than a couple hundred lines or a few levels of control flow depth. Pretty harsh nitpick.

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#22

Post by throwawaylinux »

TedShiller wrote: post #20

I love Postgres. But when I look at the bug list of every release it makes me scared. The types of bugs they have are indicative of a poor development process.

What types of bugs are those? Can you explain a bit more?

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#23

Post by mattashii »

throwawaydingus wrote: post #19
mattashii wrote: post #5

It is noted in the post, I'll repeat it to be clear: This corruption can only occur _during_ a (re)index with CONCURRENTLY specified, on rows that are modified during the reindex operation, and only for that index. No other indexes are impacted, and an index can only be impacted when the updates on the table don't update indexed columns. Nevertheless, if you frequently run CIC, you could be having this issue -- right…

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 eno…

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

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#24

Post by jtc331 »

TedShiller wrote: post #20

I love Postgres. But when I look at the bug list of every release it makes me scared. The types of bugs they have are indicative of a poor development process.

No, it's indicative of the complexity of the system.

Please show other similarly complex systems with fewer bugs: basically doesn't happen.

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#25

Post by claytongulick »

throwawaydingus wrote: post #19
mattashii wrote: post #5

It is noted in the post, I'll repeat it to be clear: This corruption can only occur _during_ a (re)index with CONCURRENTLY specified, on rows that are modified during the reindex operation, and only for that index. No other indexes are impacted, and an index can only be impacted when the updates on the table don't update indexed columns. Nevertheless, if you frequently run CIC, you could be having this issue -- right…

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 eno…

Software has bugs.

Sometimes they're missed in testing.

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

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#27

Post by avi_vallarapu »

TedShiller wrote: post #20

I love Postgres. But when I look at the bug list of every release it makes me scared. The types of bugs they have are indicative of a poor development process.

I worked on Oracle and MySQL for ages and seen tons of bugs. As a support company, the number of bugs we encounter with Postgres are none or hardly 1 in an year. I am still curios to see what those bugs are that made one scary !!!

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#28

Post by osigurdson »

gnulinux wrote: post #14
colordrops wrote: post #10

Earlier quoted context omitted.

File could definitely be broken up a bit. Over 5000 lines! Just a nitpick though.

Quite the opposite, I hate it when projects have dozens upon dozens of modules with 1 function. Multiple huge files are the best sweet spot. (Only crazy adn exceptional things like putting everything into a single file damages the readability imho)

300 to 1000 lines per file is best IMO.

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#29

Post by plq »

colordrops wrote: post #10
msk20 wrote: post #6

Looking at the file with changes https://github.com/postgres/postgres/blob/master/src/backend... , I have to say this source code repository is so well documented/commented and structured, I really gives you a huge trust in postgres to be used in your stack.

File could definitely be broken up a bit. Over 5000 lines! Just a nitpick though.

While I'm not sure that was a consideration here, sometimes C compilers produce better machine code when they got access to more function definitions. Eg. Sqlite recommends that embedders use the single ~10mb sqlite.c file[1] for both ease of use and performance reasons.

[1]: https://www.sqlite.org/amalgamation.html

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#30

Post by rattray »

claytongulick wrote: post #25
throwawaydingus wrote: post #19

Earlier quoted context omitted.

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 eno…

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

Post reply on HN