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

Postgres scaling advice

cybertec-postgresql.com

Post reply on HN
Print view View original

61–70 of 207 posts

Re: Postgres scaling advice

#61

Post by tyingq »

fabian2k wrote: post #20
bmcahren wrote: post #11

Earlier quoted context omitted.

Yes. Memory can reach 768GB on a single instance today and I imagine that to expand. From there you can scale by sharding. In memory provides real-time transactions you can't guarantee when using disk-based storage.

You can get more than that on common servers, see e.g. the recent post by Let's Encrypt on their new database server: https://letsencrypt.org/2021/01/21/next-gen-database-servers... They have 2TB RAM in there, and I suspect that is not the largest possible amount if you're willing to spend more money (though probably the largest possible amount for that particular server). I played around a bit on the Dell website, a…

4TB seems to be the limit for normal-ish servers. 32 slots filled with 128GB DIMMS. You can find servers with more slots (48 is common, 6TB), but you're going past most people's definition of "commodity".

Re: Postgres scaling advice

#62

Post by mumblemumble »

ComodoHacker wrote: post #23

It's not surprising to hear such advice from PostgreSQL consultancy shop: don't bother and relax until the day you desperately need PostgreSQL consultancy! /s Seriously though, the main point stands. PostgreSQL isn't designed with "distributed" in mind, so try vertical scaling tricks first.

The skepticism is founded, but there's some truth.

PostgreSQL (and traditional RDBMSes) aren't built with horizontal scaling in mind. That's both a blessing and a curse. The curse is obvious. The blessing is that it means that they will let you get much further with vertical scaling. Especially if you take the time to learn how to tune them.

Support for horizontal scaling comes at a cost. When I've done performance analyses, I regularly find that parallel and distributed implementations spend most their wall clock time on synchronization and communication. Occasionally more than 90%. That creates a big up-front cost you need to overcome before you see a net benefit. It also leads to a sort of horizontal scaling version of the rocket equation where returns can diminish rapidly.

Re: Postgres scaling advice

#63

Post by isoprophlex »

Pretty solid advice, nice article.

One thing:

> For example, on my (pretty average) workstation, I can do ca. 25k simple read transactions per 1 CPU core on an “in memory” pgbench dataset…with the default config for Postgres v13!

Forget about reaching those numbers on managed DB-as-a-service instances, specifically Azure managed postgres. In my experience these have comparatively poor peak performance, with high variability in latency to boot.

Bare metal all the way, if you can spare the allocation of a dedicated DBA.

Also... if rapid reads and steady performance is what you're after, provision a replica for the occasional analytical workloads.

Re: Postgres scaling advice

#64

Post by jajool »

jandrewrogers wrote: post #42

The assertion that PostgreSQL can handle dozens of TB of data needs to be qualified, as this is definitely not the case in some surprising and unexpected cases that are rarely talked about. PostgreSQL's statistics collection, which is used by the query planner, doesn't scale with storage size . For some ordinary data distributions at scale, the statistical model won't reflect any kind of reality and therefore can pro…

I have seen many PostgreSQL benchmarks having solid performance with TB data but my real world experience is the complete opposite.

Here are some of the main issues that I have encountered so far:

1. Queries on large tables (around 10 GB) are slow even when "index only scan" is used because of MVCC and the way postgreSQL manages concurrency.

2. Hot-standby instances can't be used for anything serious since all queries are dropped regularly (I believe it's not safe to use "hot_standby_feedback" config to overcome this issue).

3. It is not possible to have tables with heavy "update" workflows. (because of simultaneous autovaccum execution)

I would be very happy if anyone could show me that I am wrong.

Re: Postgres scaling advice

#65

Post by atonse »

wiredfool wrote: post #58
ahachete wrote: post #27

Earlier quoted context omitted.

EC2's u-24tb1.metal is 224/448 cores/hyperthreads and 24TB RAM ;) https://aws.amazon.com/sap/instance-types/

And nearly 2 million for a 3 year reserved instance.

This reminded me of a story from 15 years ago.

I once worked for a company that was writing a proposal for a US Homeland Security IT system. This was 2006. I wasn't involved in it but my office-mate was. He randomly turned his chair around and said "hey, can you go on Dell.com and see if you can build the most expensive server imaginable" – so I did and I ended up at around $350k. I don't remember what it was, but at the time the stats just felt absolutely obscene. I do remember that, at that scale we were buying a closet, not just one box.

And I told him the price, and he said, "They have set aside $20 million for server hardware, and what you configured is way way overkill for what they think they need" – so we were both so damn perplexed because, at least from what he told me, this didn't need that much. And it wasn't one of those classified projects where they couldn't tell you what it was for. It was a pretty boring line of business type thing (like HR or building access control or something).

Maybe that's probably more a story of just how much cash was being poured into that agency during the years after 9/11.

Re: Postgres scaling advice

#66

Post by mvanbaak »

kitd wrote: post #33
jjice wrote: post #13

In the opinion of a last semester CS student who has never written an application from scratch that needed more than a SQLite DB (so take me with a half grain of salt), it seems like premature optimization, while always talked about, is very common. I see people talking about using Kubernetes for internal applications and I just can't figure out why. If it's a hobby project and you want to learn Kubernetes, that's a…

I see people talking about using Kubernetes for internal applications and I just can't figure out why. There is benefit in having established platforms for running your code, and this is especially true for large orgs where the people who run the systems are an entirely different group from those that developed or assembled it. And people (+ their skills) are what cost the most money in any business. It's true that m…

> There is benefit in having established platforms for running your code

You do know k8s is very new, there's a constant stream of changes and updates to it, etc etc? it's not established. It's known, but that's it.

Re: Postgres scaling advice

#67

Post by aeyes »

jandrewrogers wrote: post #42

The assertion that PostgreSQL can handle dozens of TB of data needs to be qualified, as this is definitely not the case in some surprising and unexpected cases that are rarely talked about. PostgreSQL's statistics collection, which is used by the query planner, doesn't scale with storage size . For some ordinary data distributions at scale, the statistical model won't reflect any kind of reality and therefore can pro…

Vacuum is a way bigger problem, if you have a few million updates per day on a 5TB table you are going to have a hard time keeping index and page bloat down.

Sure, if your tables are insert only you might be fine but doing any kind of DDL or maintenance (analyze after version upgrades) is going to ruin your day.

Re: Postgres scaling advice

#68

Post by mmacedo_en »

I'm building an app using Postgres for the first time. Naturally I was a bit worried about performance and scaling if the not launched yet app becomes a major success.

I began simulating a heavy use scenario. 100k users creating 10 records daily for three years straight.

100000 x 10 x 365 x 3 ~= 1 billion rows or about 200 GB with a record's length of 200 bytes. This is peanuts for modern databases and hardware.

Seems like a single node can support it for a long way before I have to worry about performance...

Re: Postgres scaling advice

#69

Post by evanelias »

dijit wrote: post #14
ksec wrote: post #7

Earlier quoted context omitted.

In 2016 I was expecting some sane defaults like MySQL would have arrived by 2018 or 2019. Looks like 2021 isn't that much different to 2016. There are work being done, but doesn't seems to be anywhere close to the level of MySQL.

wow, are you kidding? MySQL replication is possibly the worst I've ever seen. There is almost no consideration for the target being up to date, I have personally experienced missed inserts, replication lags, and the replica being set to read/write. PGs integrated replication is far superior, it even has support for bootstrapping a new replica node without rsync and a command to `promote` the replica to write master.

Replication issues in modern MySQL are caused by user error, not inherent bugs. For example if you're configuring replication manually, you must ensure that replicas are set to read_only. Clustering options introduced over the past few years do this automatically though.

> PGs integrated replication is far superior, it even has support for bootstrapping a new replica node without rsync and a command to `promote` the replica to write master

MySQL has this functionality too, e.g. https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html and https://dev.mysql.com/doc/mysql-shell/8.0/en/working-with-re...

To be clear, I am not claiming MySQL is superior to PG. However, I have found that many PG users are unfamiliar with the huge improvements introduced in MySQL over the past ~8 years.

Re: Postgres scaling advice

#70

Post by smoyer »

jjice wrote: post #13

In the opinion of a last semester CS student who has never written an application from scratch that needed more than a SQLite DB (so take me with a half grain of salt), it seems like premature optimization, while always talked about, is very common. I see people talking about using Kubernetes for internal applications and I just can't figure out why. If it's a hobby project and you want to learn Kubernetes, that's a…

> I see people talking about using Kubernetes for internal applications.

I think the important issue when first starting a project is to create a "12 Factor App" so that if and when you create a Docker image and/or run the application in Kubernetes, you don't have to rewrite the entire application. Most of the tools I write run on the CLI but I am in fact a fan of Kubernetes for services, message processing and certain batch jobs simply because I don't have to manage their life-cycles.

Post reply on HN