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

31–40 of 78 posts

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

#31

Post by brightball »

karlmdavis wrote: post #24
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

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…

Isn’t Aurora horizontal by default?

EDIT: Here's what I was thinking about. It's chunked in 10gb increments that are replicated across AZs.

> Fault-tolerant and self-healing storage

Aurora's database storage volume is segmented in 10 GiB chunks and replicated across three Availability Zones, with each Availability Zone persisting 2 copies of each write. Aurora storage is fault-tolerant, transparently handling the loss of up to two copies of data without affecting database write availability and up to three copies without affecting read availability. Aurora storage is also self-healing; data blocks and disks are continuously scanned for errors and replaced automatically.

https://aws.amazon.com/rds/aurora/features/

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

#32

Post by dalyons »

brightball wrote: post #31
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…

Isn’t Aurora horizontal by default? EDIT: Here's what I was thinking about. It's chunked in 10gb increments that are replicated across AZs. > Fault-tolerant and self-healing storage Aurora's database storage volume is segmented in 10 GiB chunks and replicated across three Availability Zones, with each Availability Zone persisting 2 copies of each write. Aurora storage is fault-tolerant, transparently handling the los…

No? it’s a standard single node primary with replicas setup. With a fancy log based storage layer.

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

#33

Post by ThinkBeat »

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…

Several years ago I was a contractor on a project for that would sip up data from all forms of sensor and moving objects around a large city. It was decided that we needed Kafka and a few similar tools to handle it.

It was not hard to calculate the current max traffic or estimate the traffic growth over the next 10 years.

I did a demo of the system running on my laptop (all of it) + Postgres handling 100x the current data without too much difficulty.

Still they went with the "scale" solution because it was the right design. (and of course the consultants and me got quite lot more work todo so made a good deal more money)

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

#37

Post by LunaSea »

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.

Being curious I was wondering what type of applications could generate this quantity of data.

Is it IoT / remote sensing related?

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

#38

Post by notnullorvoid »

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

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.

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

#39

Post by thelastparadise »

notnullorvoid wrote: post #38
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

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.

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

#40

Post by denchick »

ManBeardPc wrote: post #26
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

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.

Post reply on HN