Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

github.com

Post reply on HN
Print view View original

51–60 of 78 posts

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

#51

Post by riku_iki »

SOLAR_FIELDS wrote: post #27
karlmdavis wrote: post #24

Earlier quoted context omitted.

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 i…

is there a chance you run some older version of PG? They reduced bloating significantly in last few releases.

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

#52

Post by zht »

endymi0n wrote: post #23
ThinkBeat wrote: post #22

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

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…

how long does it take to unencrypted and back up your database at its current size?

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

#53

Post by SJC_Hacker »

mdasen wrote: post #45
endymi0n wrote: post #23

Earlier quoted context omitted.

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 a…

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.

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

#54

Post by scraplab »

lebski88 wrote: post #25
geenat wrote: post #4

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

CrunchyBridge will run you a Citus cluster on any of the major cloud providers.

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

#55

Post by wavemode »

LunaSea wrote: post #37
mrbonner wrote: post #30

Earlier quoted context omitted.

My cluster is clocked in at 230TB in Aurora. It is hitting a hard limit of 250TB AWS can support.

Being curious I was wondering what type of applications could generate this quantity of data. Is it IoT / remote sensing related?

Super easy to generate arbitrary amounts of data if you start using postgres as a log, of any sort.

I worked for a company that had only a few thousand active customers yet had dozens of terabytes of data, for this reason.

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

#56

Post by SJC_Hacker »

LunaSea wrote: post #37
mrbonner wrote: post #30

Earlier quoted context omitted.

My cluster is clocked in at 230TB in Aurora. It is hitting a hard limit of 250TB AWS can support.

Being curious I was wondering what type of applications could generate this quantity of data. Is it IoT / remote sensing related?

If you want to store video data as BLOBs in a DB, you can get there easily.

Maybe not the best idea, I guess a file system would be better for that, and just use the DB for metadata.

But OTOH all the data is one place, so you just migrate the DB. Less to worry about.

I just looked up, all of English Wikipedia (including images) is barely even 100 GB ... crazy world we live in.

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

#57

Post by waldrews »

mdaniel wrote: post #41
Mortiffer wrote: post #8

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)

I still have a hard time understanding where the AGPL copyleft feature kicks in on a product like Citus. Are you safe just deploying it as part of your backend? Like, do you have to actually touch Citus's own code to trigger it? What if you start using some sprocs or sample connectivity code? Anybody know a good set of guidelines?

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

#58

Post by ManBeardPc »

hot_gril wrote: post #48
ManBeardPc wrote: post #46

Earlier quoted context omitted.

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.

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.

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

#60

Post by rbjorklin »

mdasen wrote: post #45
endymi0n wrote: post #23

Earlier quoted context omitted.

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 a…

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.
Post reply on HN