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

31–40 of 139 posts

Re: PostgreSQL's Imperfections

#31

Post by DrJokepu »

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…

A series of smaller queries moving data through temporary tables is what I do when the query is too complicated for the query planner. It’s also easier to maintain than a giant query, with or without CTEs or hints.

Re: PostgreSQL's Imperfections

#32

Post by KingOfCoders »

rbranson wrote: post #30
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.

Thanks for your fast reply, much appreciated.

Re: PostgreSQL's Imperfections

#33

Post by jedberg »

I agree with everything on this list. Been bit by most of these at one point or another. XID wraparound was the worst.

Ironically we avoided a lot of the replication bugs by accidentally deciding to use logical replication from the start, but that of course brought in a whole different set of bugs instead.

I'm surprised there wasn't a complaint about the vacuumer. That was probably my biggest single pain of running a large active cluster. There was never a good time to vacuum, but if you skipped it, it eventually happened automatically, usually at the worst possible time, when the database was most active.

To be fair I haven't managed postgres since 8.3, so maybe that got better?

Re: PostgreSQL's Imperfections

#34

Post by phamilton »

MuffinFlavored wrote: post #25
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 do you pay a month for what size?

Cost is complicated because we autoscale from 2-3 readers up 10+ readers during peak traffic. Instead of running 10 replicas 24/7, we can spin one up and within about 10 minutes it's handling reads. So we have a few instances that only run for about 3 hours a day and others that run maybe 6 hours. That is a big cost savings over running them all 24 hours a day. We couldn't bring up new replicas like that when we were on normal Postgres RDS.

Pricing is also surprising compared to vanilla Postgres RDS because reader nodes double as spare writers. A multi-az deployment of Postgres RDS plus two single-az replicas is more expensive than a 3 node (1 writer and two readers) Aurora cluster. E.g. on 2xlarge instances, this Aurora setup is $3.48/hour vs $4/hr on RDS for similar effective hardware and fault tolerance.

Running directly on EC2 is going to be much cheaper obviously. $1.51/hr for three 2xlarge instances(if you want to failover to an active replica) or $2.01/hr for 4 if you want a dedicated failover instance (like RDS does).

Re: PostgreSQL's Imperfections

#35

Post by p_l »

animalnewbie wrote: post #15

Anyone got postgres (or general db) tips for a CoW-fs? I'm using postgres on zfs.

Ensure that the dataset containing your postgres data is configured with record size equal to postgres page size or close enough (Lots of places use 8kB ZFS records for 4kB pages).

This will reduce write amplification due to excessive read-modify-write cycles.

Re: PostgreSQL's Imperfections

#36

Post by Doctor_Fegg »

> 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 storage. Each value takes up 4 bytes. A single-byte enum would vastly reduce my database size.

Re: PostgreSQL's Imperfections

#38

Post by Tostino »

jedberg wrote: post #33

I agree with everything on this list. Been bit by most of these at one point or another. XID wraparound was the worst. Ironically we avoided a lot of the replication bugs by accidentally deciding to use logical replication from the start, but that of course brought in a whole different set of bugs instead. I'm surprised there wasn't a complaint about the vacuumer. That was probably my biggest single pain of running a…

It's pretty darn different than 8.3 today.

Re: PostgreSQL's Imperfections

#39

Post by asah »

raverbashing wrote: post #28
hibikir wrote: post #17

Earlier quoted context omitted.

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

Came here to say this. We are not (all) children.
Post reply on HN