3 ms·

How far can you get with a single Postgres instance on a single machine? I know things like cockroach and citus existence but generally Postgres isn’t sharded a

How far can you get with a single Postgres instance on a single machine? I know things like cockroach and citus existence but generally Postgres isn’t sharded as far as I know.

ta988 5y ago
We are serving a several tens of TB database with tens of thousands of daily user doing very heavy queries on a single machine that did cost us $15k a few years ago (we have fallbacks and replication and whatnot don't worry). The same machine also has java services. You can really do a lot on today's machines.
zozbot234 5y ago
Postgres supports sharding out of the box. The documentation tells you how to do it, using foreign data wrapper and table partitioning.
zie 5y ago
Pretty far!
- endisneigh 5y ago
  How far was exactly? Like tps for reads and writes with what specs? I’ve been looking for real world performance.
  - zie 5y ago
    That's complicated based on workload, etc. A single PG node will obviously never scale to Google or Facebook levels. Attend a PG conference and you will run into plenty of people running PG with similar use cases(and maybe similar loads) to you. I can say we run a few hundred concurrent users backed by PG on a small to medium sized VPS without issues. Our DB is in the 3 digit GB range on disk, but not yet TB range.
  - bpicolo 5y ago
    50k-100k reads per second was pretty doable on mysql even back in 2014-2015 era. You can get a 60TB NVMe instance with 96 cores these days - https://aws.amazon.com/ec2/instance-types/i3en/ https://aws.amazon.com/ec2/instance-types/i3en/. Relational databases just scream on the dang things. > 2 million random IOPS at 4 KB block sizes and up to 16 GB/s of sequential disk throughput
dan-robertson 5y ago
You can scale up that one machine a lot. If you start with a normal sized machine you have a lot of overhead in increasing ram/cpu on that machine (eg you could start with say 16 cores and 100G ram or less and scale up to like 2TB ram and 64/128 cores). There’s also runway for scaling things by eg shooting down certain long-running queries that cause performance problems or setting up read replicas. So even if you’re a bit worried about scaling it, you can at least feel the problems are far away enough that you shouldn’t care until later.

How far can you get with a single Postgres instance on a single machine? I know things like cockroach and citus existence but generally Postgres isn’t sharded as far as I know.

ta988 5y ago

We are serving a several tens of TB database with tens of thousands of daily user doing very heavy queries on a single machine that did cost us $15k a few years ago (we have fallbacks and replication and whatnot don't worry). The same machine also has java services. You can really do a lot on today's machines.

zozbot234 5y ago

Postgres supports sharding out of the box. The documentation tells you how to do it, using foreign data wrapper and table partitioning.

zie 5y ago

Pretty far!

endisneigh 5y ago
How far was exactly? Like tps for reads and writes with what specs? I’ve been looking for real world performance.
- zie 5y ago
  That's complicated based on workload, etc. A single PG node will obviously never scale to Google or Facebook levels. Attend a PG conference and you will run into plenty of people running PG with similar use cases(and maybe similar loads) to you. I can say we run a few hundred concurrent users backed by PG on a small to medium sized VPS without issues. Our DB is in the 3 digit GB range on disk, but not yet TB range.
- bpicolo 5y ago
  50k-100k reads per second was pretty doable on mysql even back in 2014-2015 era. You can get a 60TB NVMe instance with 96 cores these days - https://aws.amazon.com/ec2/instance-types/i3en/ https://aws.amazon.com/ec2/instance-types/i3en/. Relational databases just scream on the dang things. > 2 million random IOPS at 4 KB block sizes and up to 16 GB/s of sequential disk throughput

dan-robertson 5y ago

You can scale up that one machine a lot. If you start with a normal sized machine you have a lot of overhead in increasing ram/cpu on that machine (eg you could start with say 16 cores and 100G ram or less and scale up to like 2TB ram and 64/128 cores). There’s also runway for scaling things by eg shooting down certain long-running queries that cause performance problems or setting up read replicas. So even if you’re a bit worried about scaling it, you can at least feel the problems are far away enough that you shouldn’t care until later.