8 ms·

How far can one get these days with vertical scaling of Postgres? I dont know how well the engine could make use of the extra resources. If it scales well verti

How far can one get these days with vertical scaling of Postgres? I dont know how well the engine could make use of the extra resources. If it scales well vertically, I expect it to cover 80% - 95% of use cases for people using Postgres. Probably with less complexity, and less overhead than scaling horziontally

endymi0n 3y ago
Far. As in, really, really far. We started out with Postgres because it was just the simple and sensible option for a production prototype, and when somebody came around telling me we need something more scalable recently, I calculated that there's not even enough addressable market in the world for our business for more than 4x our size. That's exactly the two remaining vertical doublings of our DB instance (to ridiculously looking RAM & CPU numbers) we could still do in case we need it. Other than that, thanks to a lot of the recent work on connection handling and concurrency since PG 11, Postgres is getting better and better actually using these additional resources well: https://www.enterprisedb.com/blog/performance-comparison-major-PostgreSQL-versions https://www.enterprisedb.com/blog/performance-comparison-maj...
- ThinkBeat 3y ago
  Several years ago I was a contractor on a project for that would sip up data from all forms of sensor and moving objects around a large city. It was decided that we needed Kafka and a few similar tools to handle it. It was not hard to calculate the current max traffic or estimate the traffic growth over the next 10 years. I did a demo of the system running on my laptop (all of it) + Postgres handling 100x the current data without too much difficulty. Still they went with the "scale" solution because it was the right design. (and of course the consultants and me got quite lot more work todo so made a good deal more money)
- mdasen 3y ago
  At this point you can get 24TB of RAM in an EC2 instance (along with 448 vCPUs, 100Gbps of network bandwidth and 38Gbps of EBS bandwidth). That won't scale forever, but Stack Overflow has been running on a single primary/standby setup with 1.5TB of RAM so that would be 16x Stack Overflow's RAM. I think a lot of work goes into horizontal scaling which is necessary at a certain scale, but very few people actually get anywhere near that scale. It can be important to understand which things are needed at your scale and where you can simply buy some beefier hardware. I've been at places where people run a dozen sharded DB servers with each server having 16GB of RAM. Maybe that's resume-driven-development where someone wants to say they've done that.
  - SJC_Hacker 3y ago
    A bunch of smaller distributed instances could be cheaper than one big one at equivalent size/compute. It also allows you to grow as needed, without worrying about things like DB transfer, instead of absorbing a big upfront cost. I agree it adds alot of complexity to the problem, which is another cost. I guess this would be another argument for pay-as-you go cloud-managed DBs, despite being more expensive than rolling your own.
  - rbjorklin 3y ago
    Scaling horizontally undoubtedly introduces complexity but it also comes with some upsides: * DB backups are now (much) faster. * Smaller backups means faster restores which reduces your RTO (Recovery Time Objective) * If you have a well architectured application a catastrophic DB failure will now only impact a portion of your userbase instead of all of them. There are probably more good reasons but these are the ones I could think of now.
    - mbreese 3y ago
      Is high availability or easier backups why people look to horizontal scaling though? I don't think that's ever been a primary reason for any story I've read. It's a great "bonus", but I can't think that it would be a compelling reason to choose horizontal vs vertical scaling. (There are other reasons, of course...)
- zht 3y ago
  how long does it take to unencrypted and back up your database at its current size?
- hot_gril 3y ago
  I remember using PG 10 at a previous company that was kinda abusing Postgres as a data processing tool with temp tables. Even with the parallel scans etc, we found it was a lot faster to split our queries (mostly INSERT(SELECT...)) into separate ones operating on separate ranges of rows, one for each CPU core. We'd run EXPLAIN to print out the plan then shard on the innermost or outermost join. I even implemented a huge sparse matrix addition/multiplication calculator this way, chaining multiple operations into a single huge query, far exceeding the limits of numpy. I've always wondered if Postgres could be used as a more efficient Spark backend. It usually scaled linearly. We had a 32-core (64-vcore) server, saturating all cores and running a bit more than 32x as fast as a single query. In some cases, it was less than linear but much better than singular, and I think that was only cause of mistakes like uuid4 pkeys.
karlmdavis 3y ago
From personal experience, it scales very well vertically. Have a system in production with tens of billions of rows and north of 12 TB of storage total. That system is read-heavy with large batched inserts, not many deletes or updates. Biggest limiter is memory, where the need for it grows linearly with table index size. Postgres really really wants to keep the index pages hot in the OS cache. Gets very sad and weird if it can’t: will unpredictably resort to table scans sometimes. We are running on AWS Aurora, on a db.r6i.12xlarge. Nowhere even close to maxed out on potential vertical scaling.
- SOLAR_FIELDS 3y ago
  I recently did some db maintenance on a write heavy workload and I found that eventually it will bloat over time with a table with 500 million records. Switching it to use a proper partitioning scheme helped a lot. So people should not read this and assume you can just dump massive workloads into pg and they will be screamingly performant without some tuning and thoughtful design (I don’t think this is what you are implying, just a PSA)
  - riku_iki 3y ago
    is there a chance you run some older version of PG? They reduced bloating significantly in last few releases.
    - SOLAR_FIELDS 3y ago
      Yes! PG12 in this case. Thanks, this definitely motivates my inflight project to get these db’s up to PG16. Very happy to hear that
      - wlll 3y ago
        If you need more motivation check out the feature matrix, there have been a fair number of pretty big advances since 12. https://www.postgresql.org/about/featurematrix/ https://www.postgresql.org/about/featurematrix/
- 3y ago
ManBeardPc 3y ago
Depends on what you want to scale. Memory, CPU and disc scale well. High concurrency can become a problem due to the 1 process per connection architecture. Keeping latency low between your app and db server and understanding how and where locks are used helps. So far I have used and seen it being used in pretty big companies successfully in a single instance (+ standby) setup.
- denchick 3y ago
  > High concurrency can become a problem due to the 1 process per connection architecture. If I understand correctly what you mean, then this is no longer a problem. You will simply need to use a connection pool, such as Odyssey or PgBouncer. Even SPQR has its own pool of connections for each shard.
  - m0rshu 3y ago
    [dead]
  - ManBeardPc 3y ago
    PgBouncer just pools the connection, but each connection still needs its own process in PostgreSQL itself. Each query blocks the whole process. That limits the amount of queries that can run in parallel/concurrently to the amount of connections. Long-running queries can easily clog up everything. No tool can fix this, you need to be aware of it and consider it in your design.
    - hot_gril 3y ago
      It's not really about the processes. Even if each query ran on a thread within a process, or some form of greenthreading were in use, there are I/O constraints and locking to consider.
      - ManBeardPc 3y ago
        What I talk about is blocking of limited resources. Processes/connections are expensive and therefore you want/have to limit the max amount of them. Each query/transaction requires its own process and blocks it for everyone else until it is done. I/O or compute constraints are another issue, if your CPU or disc is already saturated you get probably no additional benefit. But if you wait for something (locks, I/O) the connection/process can't do other things. High latency between app/database and long running transactions can also use up your available processes, even if they don't consume a lot of CPU or I/O or fight for the same locks. Lock contention is its own problem, but makes the blocking of processes/connections worse.
brightball 3y ago
Really far. Partial indexes and partitioning go a loooooooong way.
notnullorvoid 3y ago
Scaling vertically will work well in scenarios where you have consistently flat load. Horizontal scaling is good for when you have predictable valleys or spikes in load. In my experience if you want to be cost effective you need both. A decent amount of vertical scaling to have headroom for baseline and some amount of unpredictable spikes, horizontal scaling for the valleys of traffic that match your primary markets day/night cycle.
- thelastparadise 3y ago
  For bursty load why not restart a slave node on a bigger vm, promote said slave to master? Repeat with bigger and bigger nides as needed. To scale down, do the inverse.
  - hot_gril 3y ago
    Wouldn't there be a significant hit to availability or latency during the master promotion?
deleted 3y ago
[deleted]
riku_iki 3y ago
To me the largest bottleneck is when you do large insert/update/create table as etc, all final writes serialize as single threaded ops, which makes it hard to scale for heavy ETL workflows.
hot_gril 3y ago
Whenever I've had bottlenecks on a single Postgres instance, it's been because of patterns causing excessive lock contention. Redesigning your stuff to be compatible with a horizontal Postgres in the first place involves eliminating forms of shared state, which could also greatly improve the performance on a vanilla single-machine Postgres instance, so you can get very far. Like, Citus's FAQ says "if you use Citus, you do not need to manually shard your application, and you do not need to re-architect your application in order to scale out." But the line between application-level and DB-level sharding isn't this sharp. The fundamental limitations of distributed systems surface in their rules* about what you can/cannot do across shards, and you might find yourself re-architecting your application anyway. * https://docs.citusdata.com/en/stable/develop/reference_workarounds.html https://docs.citusdata.com/en/stable/develop/reference_worka...
mhuffman 3y ago
Pretty far for pretty cheap! I had a Postgres installation that ran for 6 years without issue with 11 billion rows in one of the larger tables, 1-2 million hits per day, on a single hetzner machine that ran around $130-$150 per month. Same machine ran the web server, reverse proxy, web app back ends, and ML processing in batches.

endymi0n 3y ago

Far. As in, really, really far. We started out with Postgres because it was just the simple and sensible option for a production prototype, and when somebody came around telling me we need something more scalable recently, I calculated that there's not even enough addressable market in the world for our business for more than 4x our size. That's exactly the two remaining vertical doublings of our DB instance (to ridiculously looking RAM & CPU numbers) we could still do in case we need it. Other than that, thanks to a lot of the recent work on connection handling and concurrency since PG 11, Postgres is getting better and better actually using these additional resources well: https://www.enterprisedb.com/blog/performance-comparison-major-PostgreSQL-versions https://www.enterprisedb.com/blog/performance-comparison-maj...

ThinkBeat 3y ago
Several years ago I was a contractor on a project for that would sip up data from all forms of sensor and moving objects around a large city. It was decided that we needed Kafka and a few similar tools to handle it. It was not hard to calculate the current max traffic or estimate the traffic growth over the next 10 years. I did a demo of the system running on my laptop (all of it) + Postgres handling 100x the current data without too much difficulty. Still they went with the "scale" solution because it was the right design. (and of course the consultants and me got quite lot more work todo so made a good deal more money)
mdasen 3y ago
At this point you can get 24TB of RAM in an EC2 instance (along with 448 vCPUs, 100Gbps of network bandwidth and 38Gbps of EBS bandwidth). That won't scale forever, but Stack Overflow has been running on a single primary/standby setup with 1.5TB of RAM so that would be 16x Stack Overflow's RAM. I think a lot of work goes into horizontal scaling which is necessary at a certain scale, but very few people actually get anywhere near that scale. It can be important to understand which things are needed at your scale and where you can simply buy some beefier hardware. I've been at places where people run a dozen sharded DB servers with each server having 16GB of RAM. Maybe that's resume-driven-development where someone wants to say they've done that.
- SJC_Hacker 3y ago
  A bunch of smaller distributed instances could be cheaper than one big one at equivalent size/compute. It also allows you to grow as needed, without worrying about things like DB transfer, instead of absorbing a big upfront cost. I agree it adds alot of complexity to the problem, which is another cost. I guess this would be another argument for pay-as-you go cloud-managed DBs, despite being more expensive than rolling your own.
- rbjorklin 3y ago
  Scaling horizontally undoubtedly introduces complexity but it also comes with some upsides: * DB backups are now (much) faster. * Smaller backups means faster restores which reduces your RTO (Recovery Time Objective) * If you have a well architectured application a catastrophic DB failure will now only impact a portion of your userbase instead of all of them. There are probably more good reasons but these are the ones I could think of now.
  - mbreese 3y ago
    Is high availability or easier backups why people look to horizontal scaling though? I don't think that's ever been a primary reason for any story I've read. It's a great "bonus", but I can't think that it would be a compelling reason to choose horizontal vs vertical scaling. (There are other reasons, of course...)
zht 3y ago
how long does it take to unencrypted and back up your database at its current size?
hot_gril 3y ago
I remember using PG 10 at a previous company that was kinda abusing Postgres as a data processing tool with temp tables. Even with the parallel scans etc, we found it was a lot faster to split our queries (mostly INSERT(SELECT...)) into separate ones operating on separate ranges of rows, one for each CPU core. We'd run EXPLAIN to print out the plan then shard on the innermost or outermost join. I even implemented a huge sparse matrix addition/multiplication calculator this way, chaining multiple operations into a single huge query, far exceeding the limits of numpy. I've always wondered if Postgres could be used as a more efficient Spark backend. It usually scaled linearly. We had a 32-core (64-vcore) server, saturating all cores and running a bit more than 32x as fast as a single query. In some cases, it was less than linear but much better than singular, and I think that was only cause of mistakes like uuid4 pkeys.

karlmdavis 3y ago

From personal experience, it scales very well vertically. Have a system in production with tens of billions of rows and north of 12 TB of storage total. That system is read-heavy with large batched inserts, not many deletes or updates. Biggest limiter is memory, where the need for it grows linearly with table index size. Postgres really really wants to keep the index pages hot in the OS cache. Gets very sad and weird if it can’t: will unpredictably resort to table scans sometimes. We are running on AWS Aurora, on a db.r6i.12xlarge. Nowhere even close to maxed out on potential vertical scaling.

SOLAR_FIELDS 3y ago
I recently did some db maintenance on a write heavy workload and I found that eventually it will bloat over time with a table with 500 million records. Switching it to use a proper partitioning scheme helped a lot. So people should not read this and assume you can just dump massive workloads into pg and they will be screamingly performant without some tuning and thoughtful design (I don’t think this is what you are implying, just a PSA)
- riku_iki 3y ago
  is there a chance you run some older version of PG? They reduced bloating significantly in last few releases.
  - SOLAR_FIELDS 3y ago
    Yes! PG12 in this case. Thanks, this definitely motivates my inflight project to get these db’s up to PG16. Very happy to hear that
    - wlll 3y ago
      If you need more motivation check out the feature matrix, there have been a fair number of pretty big advances since 12. https://www.postgresql.org/about/featurematrix/ https://www.postgresql.org/about/featurematrix/
3y ago

ManBeardPc 3y ago

Depends on what you want to scale. Memory, CPU and disc scale well. High concurrency can become a problem due to the 1 process per connection architecture. Keeping latency low between your app and db server and understanding how and where locks are used helps. So far I have used and seen it being used in pretty big companies successfully in a single instance (+ standby) setup.

denchick 3y ago
> High concurrency can become a problem due to the 1 process per connection architecture. If I understand correctly what you mean, then this is no longer a problem. You will simply need to use a connection pool, such as Odyssey or PgBouncer. Even SPQR has its own pool of connections for each shard.
- m0rshu 3y ago
  [dead]
- ManBeardPc 3y ago
  PgBouncer just pools the connection, but each connection still needs its own process in PostgreSQL itself. Each query blocks the whole process. That limits the amount of queries that can run in parallel/concurrently to the amount of connections. Long-running queries can easily clog up everything. No tool can fix this, you need to be aware of it and consider it in your design.
  - hot_gril 3y ago
    It's not really about the processes. Even if each query ran on a thread within a process, or some form of greenthreading were in use, there are I/O constraints and locking to consider.
    - ManBeardPc 3y ago
      What I talk about is blocking of limited resources. Processes/connections are expensive and therefore you want/have to limit the max amount of them. Each query/transaction requires its own process and blocks it for everyone else until it is done. I/O or compute constraints are another issue, if your CPU or disc is already saturated you get probably no additional benefit. But if you wait for something (locks, I/O) the connection/process can't do other things. High latency between app/database and long running transactions can also use up your available processes, even if they don't consume a lot of CPU or I/O or fight for the same locks. Lock contention is its own problem, but makes the blocking of processes/connections worse.

brightball 3y ago

Really far. Partial indexes and partitioning go a loooooooong way.

notnullorvoid 3y ago

Scaling vertically will work well in scenarios where you have consistently flat load. Horizontal scaling is good for when you have predictable valleys or spikes in load. In my experience if you want to be cost effective you need both. A decent amount of vertical scaling to have headroom for baseline and some amount of unpredictable spikes, horizontal scaling for the valleys of traffic that match your primary markets day/night cycle.

thelastparadise 3y ago
For bursty load why not restart a slave node on a bigger vm, promote said slave to master? Repeat with bigger and bigger nides as needed. To scale down, do the inverse.
- hot_gril 3y ago
  Wouldn't there be a significant hit to availability or latency during the master promotion?

deleted 3y ago

[deleted]

riku_iki 3y ago

To me the largest bottleneck is when you do large insert/update/create table as etc, all final writes serialize as single threaded ops, which makes it hard to scale for heavy ETL workflows.

hot_gril 3y ago

Whenever I've had bottlenecks on a single Postgres instance, it's been because of patterns causing excessive lock contention. Redesigning your stuff to be compatible with a horizontal Postgres in the first place involves eliminating forms of shared state, which could also greatly improve the performance on a vanilla single-machine Postgres instance, so you can get very far. Like, Citus's FAQ says "if you use Citus, you do not need to manually shard your application, and you do not need to re-architect your application in order to scale out." But the line between application-level and DB-level sharding isn't this sharp. The fundamental limitations of distributed systems surface in their rules* about what you can/cannot do across shards, and you might find yourself re-architecting your application anyway. * https://docs.citusdata.com/en/stable/develop/reference_workarounds.html https://docs.citusdata.com/en/stable/develop/reference_worka...

mhuffman 3y ago

Pretty far for pretty cheap! I had a Postgres installation that ran for 6 years without issue with 11 billion rows in one of the larger tables, 1-2 million hits per day, on a single hetzner machine that ran around $130-$150 per month. Same machine ran the web server, reverse proxy, web app back ends, and ML processing in batches.