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

PostgreSQL's Imperfections

medium.com

Post reply on HN
Print view View original

51–60 of 139 posts

Re: PostgreSQL's Imperfections

#51

Post by bragh »

jaakl wrote: post #26
topspin wrote: post #12

If I could have one thing on that list fixed it would be #9 - no planner hints. I used Oracle (6 through 11) for both bespoke applications and to back large third party systems. I never saw widespread abuse of hints. Yet they were immensely helpful during development and troubleshooting. I put perhaps two queries into production with hints over 10+ years. No one ever had a reason to complain about either. There are n…

Few years back we had nation-wide panic that Parliament election results were unavailable for hours after closing polls due to “IT issue”. Analysis showed that everything was right, load tested, cached etc, except real life situation of added few million rows and few thousands queries a second late. Automatic query planner just failed and all the smartest experts in country could do was to wait until it self-heals. I…

Please stop spreading FUD: https://www.postgresql.org/message-id/flat/201103091459.p29E...

Re: PostgreSQL's Imperfections

#52

Post by rossmohax »

Criticism is valid, but he talks about cases of millions connections to a single db, that is a significant scale many companies will never see. In addition to that, probably no database can serve under significant load without careful tuning, preferably with understanding of DB internals and knowing compromises DB authors took when designin it.

PostgreSQL is constantly improving. At least some of the problems with scaling with number of connections have more to do with locking rather than process-per-connection architecture, it is being worked on with impressive results doubling number of transactions per second for 200 connections: https://www.postgresql.org/message-id/20200301084638.7hfktq4...

Re: PostgreSQL's Imperfections

#53

Post by rossmohax »

phamilton wrote: post #16

Moving to PostgreSQL on Amazon Aurora simplifies all the replication issues listed. We (Remind) use an autoscaled PostgresQL Aurora cluster and have been pretty happy with it.

How much knoweldge is transferable? Isn't Aurora just protocol and SQL dialect compatible, but underneath it has nothing to do with postgres?

Re: PostgreSQL's Imperfections

#55

Post by sho »

Doctor_Fegg wrote: post #36

> The on-disk binary format is incompatible across major versions This is my major bugbear. If Postgres were able to upgrade its datastore on the fly (optionally, of course) that would make a massive difference. Instead I’ve had heart-in-mouth moments when Homebrew has decided that it wants to upgrade Postgres. (Yes, I do now use brew pin, until I transition off Homebrew for good.) #2 for me is inefficient enum stora…

Homebrew added an extremely useful `brew postgresql-upgrade-database` option which will handle that. You do have to remember to run it separately.

By the way, I used to have your encyclopaedia of all world knowledge

Re: PostgreSQL's Imperfections

#56

Post by orthoxerox »

topspin wrote: post #12

If I could have one thing on that list fixed it would be #9 - no planner hints. I used Oracle (6 through 11) for both bespoke applications and to back large third party systems. I never saw widespread abuse of hints. Yet they were immensely helpful during development and troubleshooting. I put perhaps two queries into production with hints over 10+ years. No one ever had a reason to complain about either. There are n…

I once couldn't convince Oracle planner to use the index at all. It was an index on the one-char status column and all the query had to do was to return the count of unauthorized rows. Almost always 100% of the rows were authorized, so no matter how many times you ran the analyzer on the table the planner remained convinced that the index was useless and opted for a full table scan instead, wasting minutes. I had to nail down the index with a hint.

Re: PostgreSQL's Imperfections

#57

Post by karyon »

There's also one inherent data privacy problem in MVCC that I've been running into.

Suppose you have an app that lets people anonymously vote or comment on stuff, but only once. The vote in the DB must not have any connection to the person. So, you give the person a flag whether or not they voted already, and store the vote separately.

Now, you'd want to set both values in the same transaction for obvious reasons. But, since Postgres uses MVCC, the two tuples that are added to the database both contain the same transaction ID (XID), so there's the connection between user and vote again.

There seems to be no way to instruct postgres to "clean" those XIDs in any way. What we're doing now is periodically and manually updating every tuple in each affected table with dummy changes, essentially duplicating all tuples with all new XIDs, and then running VACUUM to delete the tuples with the old, potentially-deanonymizing XIDs. We haven't found anything easier...

Re: PostgreSQL's Imperfections

#58

Post by rossmohax »

karyon wrote: post #57

There's also one inherent data privacy problem in MVCC that I've been running into. Suppose you have an app that lets people anonymously vote or comment on stuff, but only once. The vote in the DB must not have any connection to the person. So, you give the person a flag whether or not they voted already, and store the vote separately. Now, you'd want to set both values in the same transaction for obvious reasons. Bu…

> There seems to be no way to instruct postgres to "clean" those XIDs in any way

There is, VACUUM FREEZE

Re: PostgreSQL's Imperfections

#59

Post by karyon »

rossmohax wrote: post #58
karyon wrote: post #57

There's also one inherent data privacy problem in MVCC that I've been running into. Suppose you have an app that lets people anonymously vote or comment on stuff, but only once. The vote in the DB must not have any connection to the person. So, you give the person a flag whether or not they voted already, and store the vote separately. Now, you'd want to set both values in the same transaction for obvious reasons. Bu…

> There seems to be no way to instruct postgres to "clean" those XIDs in any way There is, VACUUM FREEZE

Sadly, since 9.4, that does not clear the XID anymore, see that blue box here: https://www.postgresql.org/docs/9.4/routine-vacuuming.html#V...

Re: PostgreSQL's Imperfections

#60

Post by dragonwriter »

karyon wrote: post #57

There's also one inherent data privacy problem in MVCC that I've been running into. Suppose you have an app that lets people anonymously vote or comment on stuff, but only once. The vote in the DB must not have any connection to the person. So, you give the person a flag whether or not they voted already, and store the vote separately. Now, you'd want to set both values in the same transaction for obvious reasons. Bu…

> Now, you'd want to set both values in the same transaction for obvious reasons. But, since Postgres uses MVCC, the two tuples that are added to the database both contain the same transaction ID (XID), so there's the connection between user and vote again.

The simple solution is that at each change, you rewrite the whole election, not just the new votes, and clear out all outdated tuples (basically, that you make the "periodic and manual" process you are currently doing automatic and integrated with the "real" transactions rather than additional side process.)

Alternatively, you don't do the changes in the same database transaction but in the same business domain transaction which is managed outside the database, and where any database artifacts related to the management of the business transaction are deleted and vacuumed after the transaction is completed.

Post reply on HN