SPQR 1.3.0: a production-ready system for horizontal scaling of PostgreSQL

[ThinkBeat]

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]

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

[karlmdavis]

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.

[hot_gril]

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

[ManBeardPc]

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.

[mhuffman]

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.

[riku_iki]

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.

[notnullorvoid]

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.

[brightball]

Really far.

Partial indexes and partitioning go a loooooooong way.

[Mortiffer]

A comparison to https://www.citusdata.com/ would be nice

[mdaniel]

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)

[vb-8448]

sono pazzi questi romani

[vincnetas]

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

[lenage]

Happy to see so many tools created for PostgreSQL, Thanks for your great work

[geenat]

Why use SPQR over Citus? Vitess?

[lebski88]

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.

[jitl]

Vitess is a MySQL cluster manager, it doesn’t support Postgres. https://github.com/vitessio/vitess/issues/7084

[feverzsj]

SPQR, as the name suggests, is just a query router.

[soleri17]

Do you have some plans for k8s operator?

[denchick]

https://github.com/pg-sharding/spqr/issues/571

[u320]

Does it handle byzantine failures?

[gclawes]

No, only Roman failures

[bookofjoe]

SPQR https://en.wikipedia.org/wiki/SPQR

[davemo]

Veni, Vidi, Vici

[jimmywetnips]

Nice

[AtlasBarfed]

SQL is problematic to horizontally scale, joins in particular.

I see nothing about partition tolerance, so I assume it isn't at all.

Give me Aphyr tests or there is no reason to pay attention.