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

141–150 of 207 posts

Re: Postgres scaling advice

#141

Post by fanf2 »

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…

Does statistics collection scale better with partitioned tables? TFA mentions that partitioning helps with vacuum, and AIUI vacuum workers also collect statistics.

Re: Postgres scaling advice

#142

Post by FpUser »

>"try all the common vertical scale-up approaches and tricks. Try to avoid using derivative Postgres products, or employing distributed approaches, or home-brewed sharding at all costs – until you have, say, less than 1 year of breathing room available."

Very healthy approach. I've always followed the idea of vertical scalability when writing my modern C++ app servers with local Postgres. Since I do not sell those to FAANG I've never failed finding decent very reasonably priced piece of hardware be it dedicated hosting or on prem that would not satisfy client's need for any foreseeable future. More then that. I've never needed even top of the line hardware for that. I concentrate on features and robustness instead. Using C++ also gives nice speedup.

Re: Postgres scaling advice

#143

Post by srcreigh »

jandrewrogers wrote: post #99
laurenz_albe wrote: post #53

Earlier quoted context omitted.

This seems to be unfounded criticism. When statistics are gathered, PostgreSQL samples a certain percentage of the table, so that obviously scales. The number of "most common values" and histogram buckets scales up to 10000, which should be good even for large tables. While I'll readily admit that not all aspects of cross-column dependencies are dealt with, and cross-table distributions are not considered, that has n…

It is ironic that you accuse me of "unwarranted conclusions". I've been customizing and modifying PostgreSQL internals for almost two decades, I know how to read the source. You aren't as familiar with PostgreSQL as you think you are. This wasn't my problem, I was asked by a well-known company with many large PG installations and enterprise support contracts to look at the issue because no one else could figure it ou…

Naive question. Wouldn't sampling a power law dataset be straightforward? The idea is there's only a few outlier values, and the rest are uncommon. This distribution seems extremely common. Ie column with mostly NULL values and the rest somewhat unique non null strings.

I'm curious what data you saw and why the sampling didn't work?

Re: Postgres scaling advice

#144

Post by akiselev »

waynesonfire wrote: post #125
zdragnar wrote: post #43

Earlier quoted context omitted.

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 s…

> 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.

docker and k8s adoption can also force a company that over years has developed and perfected a half-ass'd solution to deploying applications, with a single(-ish) source of truth that ends up solving the right organizational problems instead of the wrong technical ones (and ends up costing way more, at least in the short term).

Re: Postgres scaling advice

#145

Post by YorickPeterse »

jajool wrote: post #64
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 querie…

> 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).

Hot-standby instances are fine. We've been using these for GitLab.com for a number of years now, and never have they caused any issues. We do have some code in place that retries queries when they are cancelled by the hot-standby, but IIRC this doesn't happen often.

Re: Postgres scaling advice

#146

Post by throwdbaaway »

evanelias wrote: post #110
bmcahren wrote: post #84

Earlier quoted context omitted.

Shards increase the number of failure modes and increase the complexity of those failure modes. For most businesses, the recommendation holds true... keep it simple, don't shard until you need . I find it somewhat concerning that MongoDB has a better architecture for upgrades than Postgres. You add a replica to the cluster running the new major version and then switch that replica over as your primary once you've rep…

> Shards increase the number of failure modes and increase the complexity of those failure modes. I would only agree with this during the initial implementation of sharding. Once deployed and stable, I have not found this to be the case, at all. I say this as someone who has directly architected a sharded database layer that scaled to over a trillion rows, and later worked on core automation and operations for sharde…

One of those companies is facebook right? I think this blog post provides a much more balanced view: http://yoshinorimatsunobu.blogspot.com/2017/11/towards-bigge...

Re: Postgres scaling advice

#147

Post by wdb »

corty wrote: post #60
zdragnar wrote: post #43

Earlier quoted context omitted.

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…

Having a Dockerfile that copies a few binary blobs into an age-old distro image isn't an improvement, it's a huge liability. And most of that stuff that no one knows anything about anymore is like that. Same as with an old VM or PM. I'd rather have that old crap as a physical machine. Why? Because the hardware lifetime "naturally" limits the lifetime of such applications. If the hardware dies, it forces a decision to…

Can still remember the multinational were wanted to host the webapplciation we developed from them at their data centre. They wanted to charge us (not the business unit for some odd reason) nearly €20.000 per year to host a RoR web application.

Ended up, hosting it ourselves, and in the last year they were paying €100k per year for it. As we would just sell the same setup for each deployment for their customers. They probably been cheaper off to host it themselves.

Re: Postgres scaling advice

#148

Post by btilly »

jeffbee wrote: post #117
mmacedo_en wrote: post #88

Earlier quoted context omitted.

I've seen queries running for 1 minute 2 minutes raising user complaints. Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution. Before thinking about distributed systems, there is an entire database optimization toolkit to make use of: primary key review, secondary index creation, profiling, view or stored procedure creation, temporary tables, memory tables a…

Indexes are not free, they take up space and they make mutations more costly. Also, building the index may not even be possible while your application is running, because postgresql and other RDBMS have inadequate facilities for throttling index construction such that it doesn't harm the online workload. You might have to build indexes at midnight on Sundays, or even take your whole system offline. It can be a nightm…

I've seen https://www.postgresql.org/docs/12/sql-createindex.html#SQL-... work well in practice on busy transactional databases. I'd be interested in knowing about cases where it doesn't work well.

My experience on systems without indexes differs strongly from yours. Yes, they can work well. But if you have multiple use cases for how your data is being queried, they push you into keeping multiple copies of your data. And then it is very, very easy to lose consistency. And yes, I know about "eventual consistency" and all that - I've found that in practice it is a nightmare of special cases that winds up nowhere good.

Re: Postgres scaling advice

#149

Post by ahachete »

mmacedo_en wrote: post #94
ahachete wrote: post #29

Earlier quoted context omitted.

As this is a public reference: GitLab's Postgres cluster handles peaks of 300K tps, where the master node alone supports around 60K-80K. And this is not in-memory (datasize in the order of 8TB, RAM 600GB). https://about.gitlab.com/blog/2020/09/11/gitlab-pg-upgrade/ And there's still room for vertical scaling. Disclaimer: we provide Postgres support for GitLab.

HN is missing an user follow and user tagging button :-)

If that's for me, I really appreciate that :)

There's obviously that option on Twitter (same username as here), if you want to follow there ;)

Re: Postgres scaling advice

#150

Post by CharlesW »

btilly wrote: post #106
CharlesW wrote: post #90

Earlier quoted context omitted.

> Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution. This is exactly what the parent comment said: "you should try to build your application so queries and transactions are very short". If you're claiming that the parent is incorrect about "sometimes, the only answer is to distribute the queries across other boxes", my guess is that probably don't work at…

Based on my past experiences, I'd be happy to take an even money bet that more than 95% of organizations that go distributed for performance reasons actually caused themselves more problems than they solved. This does NOT mean that there are no use cases for distributed - I've seen Google's internals and it would be impossible to do that any other way. But it does mean that when someone is telling you with a straight…

> Based on my past experiences, I'd be happy to take an even money bet that more than 95% of organizations that go distributed for performance reasons actually caused themselves more problems than they solved.

Oh, I'm absolutely positive you're right! And as you know, even in distributed architectures there's a huge range of solutions from "let's do these n reasonably simple things" to "let's just rebuild our system to solve every conceivable future problem". I don't know of a scenario where the latter has ever worked.

Post reply on HN