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

71–80 of 101 posts

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

#71

Post by obudo »

bigiain wrote: post #39
SonOfLilit wrote: post #17

Earlier quoted context omitted.

Large C codebases _have_ to be exceptionally nice, or they immediately collapse under their own weight. As a dev team, the language teaches you this the hard way. I've never seen a terrible huge C codebase (but have seen many in other languages).

> I've never seen a terrible huge C codebase I have 100% confidence they exist. They just don't get uploaded to Github out of shame or embarrasment.

Can confirm they do. I worked at a company that was producing control systems for electric engines. Great environment and fun job but the code was beyond redemption. 15k line files with 2k+ lines #ifdef statements that ran different code for different customers, some variable names were just curses against pushy clients, not a single abstraction in sight.

Not only they exist, they power massive machines that could crush a person in the blink of an eye.

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

#72

Post by merpkz »

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…

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?

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

#73

Post by baq »

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…

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

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

#74

Post by zasdffaa »

merpkz wrote: post #72
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…

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?

No post body was provided.

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

#75

Post by blep_ »

merpkz wrote: post #72
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…

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.

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

#76

Post by 411111111111111 »

merpkz wrote: post #72
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…

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?

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)

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

#77

Post by Avamander »

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.

Don't look at MariaDB or MySQL changelog or issue tracker then. You'd get a heart attack.

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

#78

Post by doliveira »

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.

Thankfully they don't follow the whole mindset of "self-documenting code"

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

#79

Post by tremon »

mattashii wrote: post #23
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…

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

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.

Post reply on HN