25 ms·

Postgres scaling advice

392 points by BrentOzar 6y ago

beck5 6y ago
When it comes to replication & automatic failover in PG what is the 2021 gold standard setup?
- __s 6y ago
  pg_auto_failover is good https://www.citusdata.com/blog/2019/05/30/introducing-pg-auto-failover https://www.citusdata.com/blog/2019/05/30/introducing-pg-aut... Disclosure: I work for Citus
- ksec 6y ago
  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.
  - dijit 6y ago
    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.
    - claytonjy 6y ago
      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?
    - evanelias 6y ago
      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 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-replicasets.html#set-replicaset-primary 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.
- hintbits 6y ago
  Use built-in physical replication, it works and it's good. Patroni is widely used for automatic failover, at least where you don't want a possibility of a split brain.
fabian2k 6y ago
One point I found very interesting was the following paragraph: > 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! In my own very unscientific experiments I never got values as high as that, but in the area of around 4k transactions per second total on multiple cores. Of course I'm comparing very different things, I was looking at more than just the database and there are lots of other aspects I probably did in a different way. I find this interesting as it probably means that my bottleneck wasn't entirely where I thought it was. I have to play around with that some time again. If I wanted to find out the baseline Postgres performance on my hardware for trivial read queries like looking up individual rows by primary key with a database that fits entirely in RAM, how would I do that? I know there is pgbench, but that performs more complex queries as far as I understand.
- __s 6y ago
  You can give pgbench files to have it run the query you want (see -f flag) https://www.postgresql.org/docs/current/pgbench.html https://www.postgresql.org/docs/current/pgbench.html
- ahachete 6y ago
  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/ 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.
  - mmacedo_en 6y ago
    HN is missing an user follow and user tagging button :-)
    - ahachete 6y ago
      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 ;)
wilsonrocks 6y ago
This was an interesting read for a database novice. It seems like a lot of the quoted stats are about in memory datasets - is that realistic?
- dap 6y ago
  It depends a lot on what you’re doing. In my last role, keeping the Postgres database in memory was absolutely not an option, and we ran into major issues related to physical I/O.
- bmcahren 6y ago
  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.
  - ahachete 6y ago
    EC2's u-24tb1.metal is 224/448 cores/hyperthreads and 24TB RAM ;) https://aws.amazon.com/sap/instance-types/ https://aws.amazon.com/sap/instance-types/
    - wiredfool 6y ago
      And nearly 2 million for a 3 year reserved instance.
      - atonse 6y ago
        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.
kevincox 6y ago
I wonder when using a distributed database (like CockroachDB) will be the default for new applications. Right now it seems that they are less feature and harder to set up than traditional RDBMSes but I can only assume that this gap will narrow and at some point in the future things will be "scalable by default". (Of course no DB is going to prevent all ways to shoot yourself in the foot)
- WJW 6y ago
  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. Clustering introduces all sort of problems, from network splits to slow(er) joins across nodes.
  - __s 6y ago
    To back up how far one server can go, Stack Overflow used a single database for a long time https://nickcraver.com/blog/2016/02/17/stack-overflow-the-architecture-2016-edition https://nickcraver.com/blog/2016/02/17/stack-overflow-the-ar... (2013 post had much less redundancy, but even their 2016 architecture is pretty undistributed in terms of being able to recreate everything from the single source of truth database)
    - bcrosby95 6y ago
      Yeah, it's been a while since I've been in a high traffic situation, but back in the late 00s we had a couple sites with 3-5 million daily unique users. Each site had a single master, a couple read replicas, and some queries were cached in memcached. The problem in MySQL we eventually ran into was the read replicas fell behind master during peak load (by 30-60 minutes depending upon the day). So we still had to hit the master for certain queries. I left before we took the next step to fix this issue.
  - 6y ago
jjice 6y ago
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 different situation, but in the case of making a real application that people will use, it seems like a lot of us can get away with a single DB, a few workers of our app, and maybe a cache. I'm speaking out of little experience though. I just think that a lot of us can get away with traditional vertical scaling and not think too much about it.
- pg_bot 6y ago
  What you're describing is called resumé driven development. It happens every few years when people want to cash in on trends/buzzwords that people believe will be disruptive to all industries but are just tools to have in the toolbox for most. New tools pop up all the time that fit this mould. Over the past ten years I can think of Hadoop (Big data), MongoDB (NoSQL), Kubernetes, "Serverless" computing, and TensorFlow. While all these tools have legitimate use cases, they are often overused due to marketing or industry hype. Adding artificial intelligence to your recipe application is unlikely to make any sense, but people do it because they want to have AI software engineer on their resumé.
  - nforest 6y ago
    For artificial intelligence, I think it's more often marketing driven development. It's easier to seem disruptive if you claim to have AI in your product. Easier to get funding and have people talk about your company. I feel like it comes more often from business executives than technical people.
    - takeda 6y ago
      You reminded me of the Phillips toothbrush with AI. Marketing like this makes the term AI worthless.
- 6y ago
bmcahren 6y ago
Avoiding sharding and complex replication is very smart to postpone as late as possible with any database (mysql, postgres, mongodb). It can be very fragile or fail in unexpected or unusual ways and most importantly it can take much longer to fix. E.g. 18 hours instead of 2 hours of downtime.
- evanelias 6y ago
  Once your data grows very large, a successfully-implemented sharding solution actually improves availability, rather than reducing it. With a huge monolithic database, a failure causes downtime for your entire product/company. Replica cloning and backups are slow. Major version upgrades are stressful because it's all-or-nothing. With a sharded environment, a single shard failure only impacts a portion of your userbase, and smaller databases are faster to perform operational actions on. There are definitely major downsides to sharding, but they tend to be more on the application side in my experience.
  - codyb 6y ago
    Wouldn’t sharding generally result in overlap between the key ranges so that a database shard going down doesn’t have to result in any downtime? Then your issue is replication of writes I suppose. Probably depends on use case what configuration you choose.
    - Tostino 6y ago
      Totally depends on how you shard. In my case (b2b), i'd be sharding by tenant. Having a single tenant go down would not have the same impact as every single tenant going down.
    - evanelias 6y ago
      With a typical sharded relational database setup, each sharding key value maps to exactly one shard. There should be replicas of that shard, which can be promoted in case of a master failure. But in rare cases all replicas may also be degraded or inconsistent and therefore non-promotable. When this happens to a giant monolithic non-sharded database, the impact is far more catastrophic than when it happens to a single smaller shard. In any case, replication is a separate concern from sharding. Each sharded database replica set (traditionally 1 master + N replicas) has a data set that is unique / independent of other sharded database replica sets. That said, some of the "NewSQL" distributed databases may arrange their data in more complex ways. [Edit to clarify: I mean in respect to the combination of both sharding and replication. With a traditional sharded relational database, you have replica sets where each replica contains the exact same shard or shards; with a NewSQL distributed DB, data may be replicated in a more complex arrangement]
ComodoHacker 6y ago
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 6y ago
  [deleted]
- mumblemumble 6y ago
  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.
StreamBright 6y ago
>> A single PostgreSQL instance can easily do hundreds of thousands of transactions per second 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! With some tuning (by the way, tuning reads is much harder in Postgres than tuning writes!) I was able to increase it to ~32k TPS per core, meaning: a top-notch, dedicated hardware server can do about 1 million short reads! With reads, you can also usually employ replicas – so multiply that by 10 if needed! You then need to somehow solve the query routing problem, but there are tools for that. In some cases, the new standard LibPQ connection string syntax (target_session_attrs) can be used – with some shuffling. By the way, Postgres doesn’t limit the number of replicas, though I personally have never witnessed more than 10 replicas. With some cascading, I’m sure you could run dozens without bigger issues. This sort of hand-wavy "benchmark" is not really good for anybody other then the author's satisfaction. Real world scenarios are not like that.
jandrewrogers 6y ago
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.
- andy_ppp 6y ago
  This sounds really in depth and useful, are there more in depth articles about how to avoid these types of query.
- claytonjy 6y ago
  This is intriguing; could you give an example of a distribution Postgres struggles with at scale, and an application that produces such a distribution?
  - mkilling 6y ago
    We’ve recently been surprised by wrong estimates made by the planner resulting in inefficient query plans. It seems that power law distributions over multiple tables aren’t handled very well: https://stackoverflow.com/questions/65861780/is-there-a-way-to-improve-postgresql-estimates-when-joining-skewed-relations/65862472#65862472 https://stackoverflow.com/questions/65861780/is-there-a-way-...
- silon42 6y ago
  Is it easy to disable it? Personally I would not want any unpredictable behavior from query planner anyway, especially at scale.
marcus_holmes 6y ago
TLDR: As always, don't fix a problem until just before you have it.
isoprophlex 6y ago
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.
- samf 6y ago
  Good info; I came here looking for experience with managed databases. Does anyone have experience with managed instances on other platforms?
mmacedo_en 6y ago
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...
- majewsky 6y ago
  You classifying 11 writes per second as "heavy use" reminds me of how people on average completely underestimate how fast computers actually are (when they're not bogged down by crappy programs).
  - mixmastamyk 6y ago
    I don't believe the grandparent's simulation actually took three years, it was likely operations with a particular data size that was tested. Still, your main point stands. Around 2001 I wrote a C-program to record every file and size on a large hard disk. We were all amazed that it finished (seemingly) before the enter key had come back up. Must be a bug somewhere, right? Nope. Much earlier I wrote a Pascal program on a 486 in school that did some calculations over and over again, writing the output to the screen. It blew my mind then how fast the computer could do it.
f430 6y ago
anybody know if this applies to Temporal Tables? I've thought of using Datomic but it seems like Temporal Tables does the job, not sure about immutability and how that might impact performance because you are now dealing with keeping a timestamp record of every transactions.
ccleve 6y ago
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 your server, and it will slow down the other tenants. In that case, the only answer is to distribute the queries across other boxes. I agree with the advice in general -- delay moving to a distributed system as long as possible -- but sometimes you just can't.
- giantrobot 6y ago
  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.
- mmacedo_en 6y ago
  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 and so on.
  - CharlesW 6y ago
    > 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 a scale where you've learned that query optimization can't solve every database performance problem.
SPBS 6y ago
If you are going to allocate sharded databases per client with identical schemas, might as well give each of them an sqlite database? Since you're massively cutting down the writes if it's one database per customer.
- brandmeyer 6y ago
  And then just one query comes along where you need to make an update that should be globally visible, and not just visible to the shard. I can see why you would stick with full Postgres for as long as practicable.
  - csharptwdec19 6y ago
    If it's that global of an update, it's probably something that could be run as part of a schema migration and could just be handled during update rollout. If it's some form of state that needs a global update on the regular, it's likely a sign that your data is not sharded properly.
numlock86 6y ago
I like how the article starts with the metrics of what a single node can already do. The trend appears to be to scale right at the start, even before you have your first real customer ... the implications and results are obvious.
petr25102018 6y ago
For anyone interested in general advice regarding scaling traditional SQL databases, check out my article [0] where I collected various techniques when I investigated this topic in my last workplace. [0] https://stribny.name/blog/2020/07/scaling-relational-sql-databases/ https://stribny.name/blog/2020/07/scaling-relational-sql-dat...
sharadov 6y ago
Over- engineering has become a disease, and has reached pandemic -level proportions, primarily driven by resume-padders and clueless management folk.
orlovs 6y ago
I am totally pro-hosted solutions. However, hosted postgres is always none of it. I had experience on same size machine some queries took 30x time longer on hosted vs selfhosted
AdrianB1 6y ago
I have experience with other relational DB products, but the principle should be similar; I have a few databases over 1 TB, but scaling to tens of TB would require more RAM than what a typical 1S or 2S can support. CPU's are not that problematic with the huge number of cores in AMD Epyc, but RAM is a serious limitation, in my world I need between 15% and 60% RAM to database size ration, depending if it is transactional, reporting or somewhere in between. Taking a 50 TB database as an example, it is too much for a 4TB RAM 2 socket Epyc system.
FpUser 6y ago
>"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.
smolder 6y ago
> A single node instance is literally bullet-proof That is exactly the wrong way to use that word.

392 points by BrentOzar 6y ago

beck5 6y ago

When it comes to replication & automatic failover in PG what is the 2021 gold standard setup?

__s 6y ago
pg_auto_failover is good https://www.citusdata.com/blog/2019/05/30/introducing-pg-auto-failover https://www.citusdata.com/blog/2019/05/30/introducing-pg-aut... Disclosure: I work for Citus
ksec 6y ago
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.
- dijit 6y ago
  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.
  - claytonjy 6y ago
    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?
  - evanelias 6y ago
    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 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-replicasets.html#set-replicaset-primary 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.
hintbits 6y ago
Use built-in physical replication, it works and it's good. Patroni is widely used for automatic failover, at least where you don't want a possibility of a split brain.

fabian2k 6y ago

One point I found very interesting was the following paragraph: > 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! In my own very unscientific experiments I never got values as high as that, but in the area of around 4k transactions per second total on multiple cores. Of course I'm comparing very different things, I was looking at more than just the database and there are lots of other aspects I probably did in a different way. I find this interesting as it probably means that my bottleneck wasn't entirely where I thought it was. I have to play around with that some time again. If I wanted to find out the baseline Postgres performance on my hardware for trivial read queries like looking up individual rows by primary key with a database that fits entirely in RAM, how would I do that? I know there is pgbench, but that performs more complex queries as far as I understand.

__s 6y ago
You can give pgbench files to have it run the query you want (see -f flag) https://www.postgresql.org/docs/current/pgbench.html https://www.postgresql.org/docs/current/pgbench.html
ahachete 6y ago
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/ 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.
- mmacedo_en 6y ago
  HN is missing an user follow and user tagging button :-)
  - ahachete 6y ago
    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 ;)

wilsonrocks 6y ago

This was an interesting read for a database novice. It seems like a lot of the quoted stats are about in memory datasets - is that realistic?

dap 6y ago
It depends a lot on what you’re doing. In my last role, keeping the Postgres database in memory was absolutely not an option, and we ran into major issues related to physical I/O.
bmcahren 6y ago
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.
- ahachete 6y ago
  EC2's u-24tb1.metal is 224/448 cores/hyperthreads and 24TB RAM ;) https://aws.amazon.com/sap/instance-types/ https://aws.amazon.com/sap/instance-types/
  - wiredfool 6y ago
    And nearly 2 million for a 3 year reserved instance.
    - atonse 6y ago
      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.

kevincox 6y ago

I wonder when using a distributed database (like CockroachDB) will be the default for new applications. Right now it seems that they are less feature and harder to set up than traditional RDBMSes but I can only assume that this gap will narrow and at some point in the future things will be "scalable by default". (Of course no DB is going to prevent all ways to shoot yourself in the foot)

WJW 6y ago
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. Clustering introduces all sort of problems, from network splits to slow(er) joins across nodes.
- __s 6y ago
  To back up how far one server can go, Stack Overflow used a single database for a long time https://nickcraver.com/blog/2016/02/17/stack-overflow-the-architecture-2016-edition https://nickcraver.com/blog/2016/02/17/stack-overflow-the-ar... (2013 post had much less redundancy, but even their 2016 architecture is pretty undistributed in terms of being able to recreate everything from the single source of truth database)
  - bcrosby95 6y ago
    Yeah, it's been a while since I've been in a high traffic situation, but back in the late 00s we had a couple sites with 3-5 million daily unique users. Each site had a single master, a couple read replicas, and some queries were cached in memcached. The problem in MySQL we eventually ran into was the read replicas fell behind master during peak load (by 30-60 minutes depending upon the day). So we still had to hit the master for certain queries. I left before we took the next step to fix this issue.
- 6y ago

jjice 6y ago

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 different situation, but in the case of making a real application that people will use, it seems like a lot of us can get away with a single DB, a few workers of our app, and maybe a cache. I'm speaking out of little experience though. I just think that a lot of us can get away with traditional vertical scaling and not think too much about it.

pg_bot 6y ago
What you're describing is called resumé driven development. It happens every few years when people want to cash in on trends/buzzwords that people believe will be disruptive to all industries but are just tools to have in the toolbox for most. New tools pop up all the time that fit this mould. Over the past ten years I can think of Hadoop (Big data), MongoDB (NoSQL), Kubernetes, "Serverless" computing, and TensorFlow. While all these tools have legitimate use cases, they are often overused due to marketing or industry hype. Adding artificial intelligence to your recipe application is unlikely to make any sense, but people do it because they want to have AI software engineer on their resumé.
- nforest 6y ago
  For artificial intelligence, I think it's more often marketing driven development. It's easier to seem disruptive if you claim to have AI in your product. Easier to get funding and have people talk about your company. I feel like it comes more often from business executives than technical people.
  - takeda 6y ago
    You reminded me of the Phillips toothbrush with AI. Marketing like this makes the term AI worthless.
6y ago

bmcahren 6y ago

Avoiding sharding and complex replication is very smart to postpone as late as possible with any database (mysql, postgres, mongodb). It can be very fragile or fail in unexpected or unusual ways and most importantly it can take much longer to fix. E.g. 18 hours instead of 2 hours of downtime.

evanelias 6y ago
Once your data grows very large, a successfully-implemented sharding solution actually improves availability, rather than reducing it. With a huge monolithic database, a failure causes downtime for your entire product/company. Replica cloning and backups are slow. Major version upgrades are stressful because it's all-or-nothing. With a sharded environment, a single shard failure only impacts a portion of your userbase, and smaller databases are faster to perform operational actions on. There are definitely major downsides to sharding, but they tend to be more on the application side in my experience.
- codyb 6y ago
  Wouldn’t sharding generally result in overlap between the key ranges so that a database shard going down doesn’t have to result in any downtime? Then your issue is replication of writes I suppose. Probably depends on use case what configuration you choose.
  - Tostino 6y ago
    Totally depends on how you shard. In my case (b2b), i'd be sharding by tenant. Having a single tenant go down would not have the same impact as every single tenant going down.
  - evanelias 6y ago
    With a typical sharded relational database setup, each sharding key value maps to exactly one shard. There should be replicas of that shard, which can be promoted in case of a master failure. But in rare cases all replicas may also be degraded or inconsistent and therefore non-promotable. When this happens to a giant monolithic non-sharded database, the impact is far more catastrophic than when it happens to a single smaller shard. In any case, replication is a separate concern from sharding. Each sharded database replica set (traditionally 1 master + N replicas) has a data set that is unique / independent of other sharded database replica sets. That said, some of the "NewSQL" distributed databases may arrange their data in more complex ways. [Edit to clarify: I mean in respect to the combination of both sharding and replication. With a traditional sharded relational database, you have replica sets where each replica contains the exact same shard or shards; with a NewSQL distributed DB, data may be replicated in a more complex arrangement]

ComodoHacker 6y ago

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 6y ago
[deleted]
mumblemumble 6y ago
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.

StreamBright 6y ago

>> A single PostgreSQL instance can easily do hundreds of thousands of transactions per second 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! With some tuning (by the way, tuning reads is much harder in Postgres than tuning writes!) I was able to increase it to ~32k TPS per core, meaning: a top-notch, dedicated hardware server can do about 1 million short reads! With reads, you can also usually employ replicas – so multiply that by 10 if needed! You then need to somehow solve the query routing problem, but there are tools for that. In some cases, the new standard LibPQ connection string syntax (target_session_attrs) can be used – with some shuffling. By the way, Postgres doesn’t limit the number of replicas, though I personally have never witnessed more than 10 replicas. With some cascading, I’m sure you could run dozens without bigger issues. This sort of hand-wavy "benchmark" is not really good for anybody other then the author's satisfaction. Real world scenarios are not like that.

jandrewrogers 6y ago

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.

andy_ppp 6y ago
This sounds really in depth and useful, are there more in depth articles about how to avoid these types of query.
claytonjy 6y ago
This is intriguing; could you give an example of a distribution Postgres struggles with at scale, and an application that produces such a distribution?
- mkilling 6y ago
  We’ve recently been surprised by wrong estimates made by the planner resulting in inefficient query plans. It seems that power law distributions over multiple tables aren’t handled very well: https://stackoverflow.com/questions/65861780/is-there-a-way-to-improve-postgresql-estimates-when-joining-skewed-relations/65862472#65862472 https://stackoverflow.com/questions/65861780/is-there-a-way-...
silon42 6y ago
Is it easy to disable it? Personally I would not want any unpredictable behavior from query planner anyway, especially at scale.

marcus_holmes 6y ago

TLDR: As always, don't fix a problem until just before you have it.

isoprophlex 6y ago

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.

samf 6y ago
Good info; I came here looking for experience with managed databases. Does anyone have experience with managed instances on other platforms?

mmacedo_en 6y ago

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

majewsky 6y ago
You classifying 11 writes per second as "heavy use" reminds me of how people on average completely underestimate how fast computers actually are (when they're not bogged down by crappy programs).
- mixmastamyk 6y ago
  I don't believe the grandparent's simulation actually took three years, it was likely operations with a particular data size that was tested. Still, your main point stands. Around 2001 I wrote a C-program to record every file and size on a large hard disk. We were all amazed that it finished (seemingly) before the enter key had come back up. Must be a bug somewhere, right? Nope. Much earlier I wrote a Pascal program on a 486 in school that did some calculations over and over again, writing the output to the screen. It blew my mind then how fast the computer could do it.

f430 6y ago

anybody know if this applies to Temporal Tables? I've thought of using Datomic but it seems like Temporal Tables does the job, not sure about immutability and how that might impact performance because you are now dealing with keeping a timestamp record of every transactions.

ccleve 6y ago

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 your server, and it will slow down the other tenants. In that case, the only answer is to distribute the queries across other boxes. I agree with the advice in general -- delay moving to a distributed system as long as possible -- but sometimes you just can't.

giantrobot 6y ago
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.
mmacedo_en 6y ago
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 and so on.
- CharlesW 6y ago
  > 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 a scale where you've learned that query optimization can't solve every database performance problem.

SPBS 6y ago

If you are going to allocate sharded databases per client with identical schemas, might as well give each of them an sqlite database? Since you're massively cutting down the writes if it's one database per customer.

brandmeyer 6y ago
And then just one query comes along where you need to make an update that should be globally visible, and not just visible to the shard. I can see why you would stick with full Postgres for as long as practicable.
- csharptwdec19 6y ago
  If it's that global of an update, it's probably something that could be run as part of a schema migration and could just be handled during update rollout. If it's some form of state that needs a global update on the regular, it's likely a sign that your data is not sharded properly.

numlock86 6y ago

I like how the article starts with the metrics of what a single node can already do. The trend appears to be to scale right at the start, even before you have your first real customer ... the implications and results are obvious.

petr25102018 6y ago

For anyone interested in general advice regarding scaling traditional SQL databases, check out my article [0] where I collected various techniques when I investigated this topic in my last workplace. [0] https://stribny.name/blog/2020/07/scaling-relational-sql-databases/ https://stribny.name/blog/2020/07/scaling-relational-sql-dat...

sharadov 6y ago

Over- engineering has become a disease, and has reached pandemic -level proportions, primarily driven by resume-padders and clueless management folk.

orlovs 6y ago

I am totally pro-hosted solutions. However, hosted postgres is always none of it. I had experience on same size machine some queries took 30x time longer on hosted vs selfhosted

AdrianB1 6y ago

I have experience with other relational DB products, but the principle should be similar; I have a few databases over 1 TB, but scaling to tens of TB would require more RAM than what a typical 1S or 2S can support. CPU's are not that problematic with the huge number of cores in AMD Epyc, but RAM is a serious limitation, in my world I need between 15% and 60% RAM to database size ration, depending if it is transactional, reporting or somewhere in between. Taking a 50 TB database as an example, it is too much for a 4TB RAM 2 socket Epyc system.

FpUser 6y ago

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

smolder 6y ago

> A single node instance is literally bullet-proof That is exactly the wrong way to use that word.