4 ms·

On a related note, how far can a single Postgres instance get you? If you had some behemoth with 32TB ram and 1PB storage could all of motion fit? I’m obvious

On a related note, how far can a single Postgres instance get you? If you had some behemoth with 32TB ram and 1PB storage could all of motion fit? I’m obviously ignoring the obvious single point of failure here, but sometimes the simplicity could be worth it if you’re willing to handle that. I’d be curious to hear about a site that’s in the Alexa 1000 architected how I describe.

aeyes 5y ago
> If you had some behemoth with 32TB ram and 1PB storage could all of motion fit? Postgres has a bunch of low level locks and buffers (protected by locks) which are essentially single threaded. So even if you had a 500 CPU instance at some point you'd not be able to get more throughput out of it. Of course it also depends on what you are doing. Large tables with high update rate are harder to handle than large tables which are insert only. My personal opinion is that Postgres tables with >100GB data (without indexes) are a starting to be a pain to work with, no matter how much RAM, CPU or IOPS you have.
- endisneigh 5y ago
  Is there a relational database that could efficiently leverage such a single powerful machine?
  - gfody 5y ago
    sql server scales incredibly well with the hardware but so does its price
  - haneefmubarak 5y ago
    I imagine you want NUMA aware software specifically designed for multi-socket systems; off the top of my head the ones I've heard of are IBM DB2, SAP HANA, and Microsoft SQL Server — although I've had a chance to actually use any of them.
    - jiggawatts 5y ago
      There was a study done recently that no major DBMS product can scale past 64 cores for similar reasons that Postgres can't -- too much contention on shared resources like locks.
  - jandrewrogers 5y ago
    Not currently for relational databases specifically as far as I know, though it is possible to build one in principle. The kinds of use cases and applications that really benefit from that kind of deployment hardware tend not to have classically relational data models.
    - softawre 5y ago
      Right, and at that point you should be using some sort of NoSQL anyway.
jandrewrogers 5y ago
Postgres starts to struggle when you push it past 10TB, the details of which will depend on your specific data model and workload. I've seen it pushed to 50TB but I would not recommend it. The architecture simply isn't designed for that kind of scale. There are fast database engines specifically designed for single servers with 1PB of storage, and these servers commonly have hundreds of cores. This is much more efficient for some use cases. It is the kind of thing you find on edge platforms designed to manage and operate on sensor data models. You build database engine internals very differently when working at this storage density and number of cores; many good architectural ideas in databases at much smaller scales become severe bottlenecks even on a single server.
- endisneigh 5y ago
  Thanks for the reply - I'm not super familiar with databases. Do you have a few in mind (preferably open-source for reference)? Also, are you referring to a single-instance of Postgres, or that Postgres would struggle with 10TB even with something like Citus or TimescaleDB?
  - jandrewrogers 5y ago
    I was referring to a single instance of classic PostgreSQL running on a single server. Scale-out databases are a completely different animal, it is difficult to compare them to scale-up databases because they make a different set of tradeoffs. Extreme scale-up database engines are relatively rare and usually bespoke, I am not aware of anything close in open source. They only have one major advantage -- they are easily deployable outside the data center, e.g. at the edge, because it is a single self-contained box and relatively power efficient. Most people opt for scale-out because 1) there are many to choose from in open source and 2) it will work just as well as extreme scale-up for most applications, though you'll need more hardware for the same workload. I've started to appreciate the utility of the extreme scale-up engines in real-world applications, though I've mostly worked on scale-out engines. They solve a real problem at the edge for data intensive applications but most currently people deploy in the data center so there isn't a pressing need.

aeyes 5y ago

> If you had some behemoth with 32TB ram and 1PB storage could all of motion fit? Postgres has a bunch of low level locks and buffers (protected by locks) which are essentially single threaded. So even if you had a 500 CPU instance at some point you'd not be able to get more throughput out of it. Of course it also depends on what you are doing. Large tables with high update rate are harder to handle than large tables which are insert only. My personal opinion is that Postgres tables with >100GB data (without indexes) are a starting to be a pain to work with, no matter how much RAM, CPU or IOPS you have.

endisneigh 5y ago
Is there a relational database that could efficiently leverage such a single powerful machine?
- gfody 5y ago
  sql server scales incredibly well with the hardware but so does its price
- haneefmubarak 5y ago
  I imagine you want NUMA aware software specifically designed for multi-socket systems; off the top of my head the ones I've heard of are IBM DB2, SAP HANA, and Microsoft SQL Server — although I've had a chance to actually use any of them.
  - jiggawatts 5y ago
    There was a study done recently that no major DBMS product can scale past 64 cores for similar reasons that Postgres can't -- too much contention on shared resources like locks.
- jandrewrogers 5y ago
  Not currently for relational databases specifically as far as I know, though it is possible to build one in principle. The kinds of use cases and applications that really benefit from that kind of deployment hardware tend not to have classically relational data models.
  - softawre 5y ago
    Right, and at that point you should be using some sort of NoSQL anyway.

jandrewrogers 5y ago

Postgres starts to struggle when you push it past 10TB, the details of which will depend on your specific data model and workload. I've seen it pushed to 50TB but I would not recommend it. The architecture simply isn't designed for that kind of scale. There are fast database engines specifically designed for single servers with 1PB of storage, and these servers commonly have hundreds of cores. This is much more efficient for some use cases. It is the kind of thing you find on edge platforms designed to manage and operate on sensor data models. You build database engine internals very differently when working at this storage density and number of cores; many good architectural ideas in databases at much smaller scales become severe bottlenecks even on a single server.

endisneigh 5y ago
Thanks for the reply - I'm not super familiar with databases. Do you have a few in mind (preferably open-source for reference)? Also, are you referring to a single-instance of Postgres, or that Postgres would struggle with 10TB even with something like Citus or TimescaleDB?
- jandrewrogers 5y ago
  I was referring to a single instance of classic PostgreSQL running on a single server. Scale-out databases are a completely different animal, it is difficult to compare them to scale-up databases because they make a different set of tradeoffs. Extreme scale-up database engines are relatively rare and usually bespoke, I am not aware of anything close in open source. They only have one major advantage -- they are easily deployable outside the data center, e.g. at the edge, because it is a single self-contained box and relatively power efficient. Most people opt for scale-out because 1) there are many to choose from in open source and 2) it will work just as well as extreme scale-up for most applications, though you'll need more hardware for the same workload. I've started to appreciate the utility of the extreme scale-up engines in real-world applications, though I've mostly worked on scale-out engines. They solve a real problem at the edge for data intensive applications but most currently people deploy in the data center so there isn't a pressing need.