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

21–30 of 139 posts

Re: PostgreSQL's Imperfections

#21

Post by hobs »

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 see hint abuse constantly and everywhere in enterprise databases, it's really more about the lineage you work with.

Re: PostgreSQL's Imperfections

#22

Post by jeffdavis »

DiabloD3 wrote: post #10
jeffdavis wrote: post #3

I largely agree, but a couple points I'd like to challenge: Is hardware corruption really happening and making it into the WAL stream with checksums on? The next point, on planner hints: it's really just something that hasn't been done. If a few engineers made plans to tackle the problem, a lot could be done in a couple releases' worth of work. In the mean time, people are getting by with various half-measures anyway…

Hardware corruption can happen anywhere to anything. If you care about your data: Use ECC, and use a checksumming filesystem like ZFS, and also on top of this all, export your WALs to a second machine.

Well, I'm asking specifically if the author saw corruption that snuck past the checksumming features that postgres has.

Extra layers are always good, but since I was one of the main authors of checksums in Postgres, I'd like to know if there's room for improvement. (Aside: the page checksum is only 16 bits, so if you have frequent corruption it's entirely believeable that a few sneak past. But I haven't seen it personally.)

Re: PostgreSQL's Imperfections

#23

Post by ptman »

jeffdavis wrote: post #3

I largely agree, but a couple points I'd like to challenge: Is hardware corruption really happening and making it into the WAL stream with checksums on? The next point, on planner hints: it's really just something that hasn't been done. If a few engineers made plans to tackle the problem, a lot could be done in a couple releases' worth of work. In the mean time, people are getting by with various half-measures anyway…

Also, pg has initdb --data-checksums

Re: PostgreSQL's Imperfections

#24

Post by skyde »

rb808 wrote: post #18

Its scary that my company is replacing old Oracle DBs and teams of specialist DBAs with developers running their own PostSQL instances. Sure it saves money but its gonna blow one day soon...

is it one postSQL per app replacing many app on a single Oracle instance ?

moving away from some sharing fix most of the problem DBA are hired to mitigate

Re: PostgreSQL's Imperfections

#26

Post by jaakl »

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. Imagine national TV screening for hours “no results available” and You giving updates to the PM every 5 minutes. And all you know that your most important DB in country cannot figure it out... True story

Re: PostgreSQL's Imperfections

#28

Post by raverbashing »

hibikir wrote: post #17
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…

While I won't say that the Postgres ideosyncrasies are correct here, I can tell you that your experience is not necessarily typical. In my career, hint abuse has always been rampant. From telcos to biotech companies, a high percentage of complex queries I had to interact with had hints in them. In one extremely egregious case, the company decided to purchase an Exadata server, and since its performance characteristic…

Bad hints can always be dropped.

Every system is prone to abuse, I don't think hints are too out there in this aspect

Re: PostgreSQL's Imperfections

#29

Post by KingOfCoders »

The 'best database' article recently linked here, says

"PostgreSQL picks a method of concurrency control that works best for high INSERT and SELECT workloads. [...] tracking overhead for UPDATE and DELETE."

This one says "INSERT and UPDATE operations create new copies (or “row versions”) of any modified rows, leaving the old versions on disk until they can be cleaned up."

I do think this one is wrong, but this is my wild guess as I am no expert in any way. INSERT should be fine. Or how would INSERT "create new copies"?

[Edit] See authors comment.

Re: PostgreSQL's Imperfections

#30

Post by rbranson »

KingOfCoders wrote: post #29

The 'best database' article recently linked here, says "PostgreSQL picks a method of concurrency control that works best for high INSERT and SELECT workloads. [...] tracking overhead for UPDATE and DELETE." This one says "INSERT and UPDATE operations create new copies (or “row versions”) of any modified rows, leaving the old versions on disk until they can be cleaned up." I do think this one is wrong, but this is my…

Author here. This should not have included INSERT. I updated the post to reflect that. Thanks for picking this up.
Post reply on HN