Veni, Vidi, Vici
SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
21–30 of 78 posts
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#22How 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
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#23How 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
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-maj...
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#24How 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
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.
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#25Why use SPQR over Citus? Vitess?
The fact that it's a router and not a PG extension is helpful if you are using cloud hosted postgres and don't want to get into the business of managing backups etc yourselves.
Only azure supports Citus as far as I know.
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#26How 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
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.
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#27How 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
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…
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)
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#28Veni, Vidi, Vici
[deleted]
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#29How 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
Really far.
Partial indexes and partitioning go a loooooooong way.
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#30How 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
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…
My cluster is clocked in at 230TB in Aurora. It is hitting a hard limit of 250TB AWS can support.