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

61–70 of 78 posts

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

#61

Post by mrbonner »

mrbonner wrote: post #30
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…

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

No, we do not store log or IoT. The data are all business related metrics. I didn't choose aurora but inherited from another team. We have 4 replication reads to scale out the read access. The internal team owns the ingestion (insert) to the write node. All other external accesses are read.

I think the reason behind aurora pick is to support arbitrary aggregation, filtering and low latency read (p90 The other contender I consider is Elastic search. But, I do think using it in this case is akin to fitting a square peg in round hole saying.

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

#62

Post by mbreese »

rbjorklin wrote: post #60
mdasen wrote: post #45

Earlier quoted context omitted.

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

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

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

#63

Post by manquer »

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?

You are thinking of normalized ( bcnf if not 3nf) well architectures application storing structured data , unless the app is 100 million+ users or grew super fast 250TB size would be hard to get to .

Timeseries (like IoT you mentioned ) or binary blobs or logs or any other data in SQL storage that shouldn’t be really there can hit any size wouldn’t be all that interesting.

Can’t speak for OP, however managing data for few million user apps, what I have observed is most SQL stores hit single TB range and then start getting broken down into smaller dbs either coz now teams have grown want their own Micro-service or DB or infra wants easier to handle in variety of ways including Backup /recovery larger DBs are extremely difficult to get reasonable RTO/RPO numbers for.

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

#64

Post by manquer »

SJC_Hacker wrote: post #56
LunaSea wrote: post #37

Earlier quoted context omitted.

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.

You wouldn’t say less to worry about when you have to do full backup or show recovery from backup within a set recovery time .

This one data store is easier is a myth , it just offloads complexity from developer to infra teams who are now provisioning premium NVMe storage instead of cold object stores for binary data .

Binary data is not indexed or aggregated in a SQL store there is no value in doing this is one place expect dev experience at the cost of infra team experience.

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

#65

Post by mhuffman »

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

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.

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

#66

Post by hot_gril »

thelastparadise wrote: post #39
notnullorvoid wrote: post #38

Earlier quoted context omitted.

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…

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.

Wouldn't there be a significant hit to availability or latency during the master promotion?

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

#68

Post by alexvoda »

SketchySeaBeast wrote: post #17
bheadmaster wrote: post #12

Earlier quoted context omitted.

Like the one where they failed to conquer that village of Gauls?

As usual, Asterixes are to be avoided.

Besides, there are plenty of Obelixs to be found in Egypt. There is no need to get your phalic stone monuments from the Gauls.

(I expected Roman puns and was not disappointed)

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

#69

Post by hot_gril »

ManBeardPc wrote: post #58
hot_gril wrote: post #48

Earlier quoted context omitted.

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…

What I meant is, even if processes had no cost, you will probably bottleneck on the I/O. So it doesn't matter a lot how many Postgres connections you have.

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

#70

Post by hot_gril »

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…

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.

Post reply on HN