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

41–50 of 207 posts

Re: Postgres scaling advice

#41

Post by wejick »

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 know one of the biggest Ecommerce shop in Asia were using 1 big DB with multiple read only slave in monolithic architecture for more than 5 years.

However not only driven by DB performance, but also on organizing hundreds of engineers they adapted microservice architecture. Then they slowly migrating to per domain specific DB, it is just classic microservice migration story.

While single DB may bring us pretty long way, designing the system into more discipline logical domain level segregation will help when there's need to move to microservice.

*looks like HN reader quite sensitive with microservice migration comment, usually this kind of comment got down voted easily.

Re: Postgres scaling advice

#42

Post by jandrewrogers »

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 produce pathological query plans. It is quite difficult to get around this scaling behavior. Consequently, I've moved away from using PostgreSQL for data models with distributions such that these query planner limitations will occur. These pathologies occur well before the "dozens of TB" range.

The statistics collector is not a scalable design generally, but that is another matter. In its current state it does not degrade gracefully with scale.

Re: Postgres scaling advice

#43

Post by zdragnar »

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…

In big enough organizations, it is very easy to lose track of who owns what, especially when it is those little ad-hoc internal tools. Manually managing the infrastructure for them is a recipe for them to become permanently enshrined in the wasteland of "services we think we use, but do not maintain because we don't remember who needed it or put it up or how to configure it".

K8s isn't the only answer, but if you are already using it for your large applications, it isn't much work to reuse the existing tooling and infrastructure, and now you st least have the dockerfile as a reference if nothing else.

OTOH, if you have an existing tooling setup / pipeline that is not K8s, there isn't a good reason to use it for a small application.

Re: Postgres scaling advice

#44

Post by claytonjy »

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.

What about Percona? I haven't used it, but a common sentiment on HN has been that Postgres doesn't have something quite on par with it.

Citus and, more recently, Patroni, seem to be the dominant Postgres analogues; have they caught up? Where do they dominate?

Re: Postgres scaling advice

#45

Post by [deleted] »

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.

[deleted]

Re: Postgres scaling advice

#46

Post by collyw »

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…

Speaking as someone with 20 years in the industry, what you say is correct. Most applications would be find on a single server and a classic LAMP stack. But that ain't cool these days.

Re: Postgres scaling advice

#47

Post by collyw »

wejick wrote: post #41
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 know one of the biggest Ecommerce shop in Asia were using 1 big DB with multiple read only slave in monolithic architecture for more than 5 years. However not only driven by DB performance, but also on organizing hundreds of engineers they adapted microservice architecture. Then they slowly migrating to per domain specific DB, it is just classic microservice migration story. While single DB may bring us pretty long…

Stack Overflow runs what is essentially a monolithic architecture. Though they do have a few services, it isn't what I would describe as a micro-service architecture.

https://stackexchange.com/performance

Re: Postgres scaling advice

#48

Post by andy_ppp »

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…

This sounds really in depth and useful, are there more in depth articles about how to avoid these types of query.

Re: Postgres scaling advice

#50

Post by claytonjy »

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…

This is intriguing; could you give an example of a distribution Postgres struggles with at scale, and an application that produces such a distribution?
Post reply on HN