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

121–130 of 207 posts

Re: Postgres scaling advice

#121

Post by gher-shyu3i »

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…

> PostgreSQL's statistics collection, which is used by the query planner, doesn't scale with storage size.

Out of curiosity, do statistics collectors for other offerings (e.g. MySQL, SQL Server, Oracle) scale with storage size?

Re: Postgres scaling advice

#123

Post by ajsharp »

btilly wrote: post #100
ccleve wrote: post #81

The difficulty with this advice is that it assumes that you have many small transactions. Yes, of course, you should try to build your application so queries and transactions are very short. That solves a great many problems. But sometimes you can't. Sometimes you just have to do joins across large tables. There just isn't any other way. Your query is going to run for 5, 10, maybe 30 seconds. That's a huge burden on…

Point #1, there is a world of difference between a reporting database and a transactional database. If you need a reporting database, ship logs and set up a reporting database separate from your transactional one. That solves most of the problem. Point #2, the fact that you've hit performance problems does not mean that you need to distribute. Every real system that I've seen has had order of magnitude performance im…

My thoughts exactly on point #1. Nothing in a hot path should take multiple seconds.

Re: Postgres scaling advice

#124

Post by giantrobot »

ccleve wrote: post #81

The difficulty with this advice is that it assumes that you have many small transactions. Yes, of course, you should try to build your application so queries and transactions are very short. That solves a great many problems. But sometimes you can't. Sometimes you just have to do joins across large tables. There just isn't any other way. Your query is going to run for 5, 10, maybe 30 seconds. That's a huge burden on…

Postgres has materialized views for the case of expensive queries. Instead of doing a view query in real-time it physically caches the results and has the same accessibility as a regular view.

They don't solve all situations with expensive queries but they help a lot. The fact they behave like dynamic views means you can migrate a dynamic view to materialized if you run into performance issues without making changes to the client application. With views in general you can shuffle around tables and relationships without the client application knowing or caring.

Re: Postgres scaling advice

#125

Post by waynesonfire »

zdragnar wrote: post #43
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…

docker and k8s adoption can force a company that over years has developed and perfected a standard practice of deploying application, with a half-ass'd solution that ends up solving the wrong problems and costing way more. The "shipping beats perfecting" mantra is very much at play here. This is due to the amount of time it would take to achieve parity. At the end of the day, the new solution ends up looking like a step-back.

Such a practice combined with the mentality that software engineers should do their own dev-ops can easily lead to an environment of spaghetti applications where every developer working on the new platform does things slightly different because the replacement solution wasn't complete and had to be addressed by countless band-aids by engineers across the band of talent.

Furthermore, for the features that were able to achieve parity, you're now forcing your entire organization to re-learn the new development process. The docs our abysmal and the software engineers that developed the original solution have moved on since the hard work of "productionalization" remains and they're not interested in that.

Re: Postgres scaling advice

#126

Post by objektif »

Silhouette wrote: post #37
avereveard wrote: post #32

Earlier quoted context omitted.

Kubernetes isn't only about scaling. The repeatability of deployment process is a great asset to have as well.

But there are much simpler ways than K8s to achieve automated/repeatable deployments, if that is your goal.

Can you please name a few?

Re: Postgres scaling advice

#127

Post by barrkel »

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…

Kubernetes is for when you need to allocate CPU like you allocate RAM, and you don't want to be tied to a higher level API sold by a vendor.

Re: Postgres scaling advice

#128

Post by ben509 »

Tostino wrote: post #78
silon42 wrote: post #51

Earlier quoted context omitted.

Is it easy to disable it? Personally I would not want any unpredictable behavior from query planner anyway, especially at scale.

You would get predictably bad behavior and performance without the statistics collected.

Predictably bad won't result in a sudden spike in IO usage that brings down a production system. This is why Oracle has SQL profiles.

Re: Postgres scaling advice

#129

Post by Tostino »

ben509 wrote: post #128
Tostino wrote: post #78

Earlier quoted context omitted.

You would get predictably bad behavior and performance without the statistics collected.

Predictably bad won't result in a sudden spike in IO usage that brings down a production system. This is why Oracle has SQL profiles.

Then use something like pg_hint_plan: https://pghintplan.osdn.jp/pg_hint_plan.html (never used it myself)

I've been bitten by bad optimizer choices which took way too long to figure out how to debug / fix, so I know the pain. I do hope the PG optimizer continues improving. It's one of the weakest areas in comparison to commercial DB's IMO.

Re: Postgres scaling advice

#130

Post by andreimatei1 »

kevincox wrote: post #19
WJW wrote: post #10

Earlier quoted context omitted.

This question is similar to asking on a car forum when using a 40 foot lorry will be the default starter car for everyone. The answer is "probably never" because while it does offer superior cargo transport scalability, the tradeoffs are not worth it for the vast majority of users. The question is posed like distributed databases have no disadvantages over non-distributed databases, but that is simply not the case. C…

I don't think it is quite the same. - Switching from a car to a van to a lorry is fairly low cost. You don't need to recreate your product (probably). - You don't need to run distributed databases in a cluster to start. But I think most importantly the decrease in dev speed and performance is an investment in future scalability. And I only imagine that this different will shrink over time to where for example a 1 nod…

> I agree that right now it doesn't make sense.

This CRDB engineer respectfully disagrees. This thread takes it as a given that a non-distributed DB is better if you don't need to scale up (i.e. if you run a single "node"). Let me offer a couterpoint: it's easier to embed CRDB into some software you're distributing than it is to embed Postgres. This is to say, we do try to compete at every scale (well, perhaps not at SQLite scale). CRDB doesn't have all the SQL features of PG, but it does have its pluses: CRDB does online schema changes, it is generally simpler to run, comes with a UI, comes with more observability, can backup to the cloud, can be more easily embedded into tests.

Online schema changes are a big deal; the other thing that I hope will help us win small-scale hearts and minds is the ever-improving observability story. I hope CRDB will develop world-class capabilities here. Other open-source databases traditionally have not had many capabilities out of the box.

Post reply on HN