4 ms·

This is really a very good article, and I've seen this same behavior manifest on numerous client projects. BUT. I have also grown a bit skeptical of the "Just

by lukev 8y ago

This is really a very good article, and I've seen this same behavior manifest on numerous client projects. BUT. I have also grown a bit skeptical of the "Just use a RDBMS!" mantra. The same advice applies. Think about your use case. Even if your data isn't exoscale, a relational DB might not be the best choice. My current project has data on the scale of hundreds of billions of rows. Nothing crazy, easily handled by a good Postgres box (by the numbers.) And for certain access patterns, it would be. Unfortunately, it turns out that a lot of the analytics queries our business users need end up being joins and aggregations involving full or nearly-full table scans. PG is not particularly well optimized for this; it gets CPU and disk-access bound. Queries take tens of minutes, causing timeout errors in our BA tools. Loading the data into a Presto (Facebook product) cluster instead dropped query times for the same aggregation into the tens of seconds range. Sure, our data doesn't even really count as "big" and Presto is probably overkill if you're only looking at size, but it is optimized and built for highly partitioned parallel aggregations.

DanFeldman 8y ago
Exactly same case here - we're using Athena (packaged presto from AWS) and have a Postgres instance w/ identical data in a different data model. Presto is mindblowingly good at the low latency queries and scales well to anything nontrivial. It seems opening files for reading is the biggest source of delay in presto! Postgres is not handling the scale well, but it was a nice experiment.
- Erwin 8y ago
  Your Athena data set are immutable S3 blobs of data however, while Postgres reads data that could be modified while it's reading them, concurrently. Postgres has to deal with a transaction modifying 100,000 rows in a table, needing that transaction to then see its changes but then aborting, leaving the data exactly as it was before. Athena/Presto don't have to worry about any transactions, table files bloated by changes that need vacuuming, etc. etc. So I don't think it's fair to claim PG does not scale, when you are comparing a transactional and analytical database.
  - lukev 8y ago
    That's rather the point though. The people saying "YAGNI, just use Postgres" aren't, a priori, any more correct than people saying "You definitely need Hadoop if you're gonna scale!" You need to analyze our own use case critically, understand the tradeoffs of different tools and choose pragmatically.
jacques_chester 8y ago
There are multiple RDBMS data warehouse systems based on PostgreSQL that scale pretty well. I work for a company that develops one -- Greenplum. There are others like Citus.
tetha 8y ago
I would object that your experience is actually a good example how to choose a larger scale solution. Start with some RDBMS, and switch once it stops working. I think that's the better way of saying "just use a RDBMS" - "If you're not sure, use an RDBMS to delay a decision until you have more experience and knowledge".

by lukev 8y ago

DanFeldman 8y ago

Exactly same case here - we're using Athena (packaged presto from AWS) and have a Postgres instance w/ identical data in a different data model. Presto is mindblowingly good at the low latency queries and scales well to anything nontrivial. It seems opening files for reading is the biggest source of delay in presto! Postgres is not handling the scale well, but it was a nice experiment.

Erwin 8y ago
Your Athena data set are immutable S3 blobs of data however, while Postgres reads data that could be modified while it's reading them, concurrently. Postgres has to deal with a transaction modifying 100,000 rows in a table, needing that transaction to then see its changes but then aborting, leaving the data exactly as it was before. Athena/Presto don't have to worry about any transactions, table files bloated by changes that need vacuuming, etc. etc. So I don't think it's fair to claim PG does not scale, when you are comparing a transactional and analytical database.
- lukev 8y ago
  That's rather the point though. The people saying "YAGNI, just use Postgres" aren't, a priori, any more correct than people saying "You definitely need Hadoop if you're gonna scale!" You need to analyze our own use case critically, understand the tradeoffs of different tools and choose pragmatically.

jacques_chester 8y ago

There are multiple RDBMS data warehouse systems based on PostgreSQL that scale pretty well. I work for a company that develops one -- Greenplum. There are others like Citus.

tetha 8y ago

I would object that your experience is actually a good example how to choose a larger scale solution. Start with some RDBMS, and switch once it stops working. I think that's the better way of saying "just use a RDBMS" - "If you're not sure, use an RDBMS to delay a decision until you have more experience and knowledge".