A comparison to https://www.citusdata.com/ would be nice
for one thing:
https://github.com/pg-sharding/spqr/blob/1.3.0/LICENSE (BSD2)
https://github.com/citusdata/citus/blob/v12.1.2/LICENSE (AGPLv3)
SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
41–50 of 78 posts
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#42How 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
[deleted]
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#43Earlier quoted context omitted.
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.
> 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.
[dead]
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#44Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#45How 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 ridi…
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.
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#46Earlier quoted context omitted.
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.
> 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.
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.
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#47How 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
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.
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#48Earlier quoted context omitted.
> 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.
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.
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.
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#49How 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
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_worka...
Re: SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL
#50sono pazzi questi romani
"On the first page where we find the map of France we see a Roman banner with S.P.Q.R. which means "Senatus Populus Que Romanus" (The senate and people of Rome). The Italian translator made a great pun about it and translated it into "Sono Pazzi questi Romani". The latter can be translated to "These Romans are crazy"." https://www.asterix-obelix.nl/index.php?page=hjh/dos-italy.i...
Unrelated, but you reminded me of this clip from The Life of Brian:
https://www.youtube.com/watch?v=wjOfQfxmTLQ
It's funnier if you know that the ear-twisting Roman at the start was basically a caricature of many Latin teachers that were around when Latin was taught more widely in schools (such as when the Monty Python team were young).