Comment by lukev | Hacker News Reader

Comment by lukev | Hacker News Reader

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.

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

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.

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.

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.

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.

Reply on news.ycombinator.com