Hacker News Reader

Comment by jonstaab | Hacker News Reader

> On my laptop, PostgreSQL takes about a minute to get denormalized data for 12,000 episodes, while retrieval of the equivalent document by ID in MongoDB takes a fraction of a second.

What? Her database can't possibly be indexed properly.

View on HN

Yeah, this sounds like a design defect. But since the author doesn't really describe what they did, it is hard to really figure it out. I'm guessing this is some sort of query with a self-join going on, where the mongo request is a basic fetch by id.

The use of the term “denormalized” suggests to me that it was a query that involved a lot of joins. Which is certainly something that could have been otherwise addressed with a different design.

Comparing fetching from a normalized design to a denormalized one isn’t really a fair comparison.

I fear the problem is that the person doesn't do joins but separate sub-queries which then get recombined in RAM in the client. Given the software stack described there is a realistic chance of this happening implicitly due to ORM mappers.

But then the way tables don't map well to 1-to-many mappings and joins still returning data in tables this can also be a problem. Especially if a large field get duplicated a lot. RDBMS really should go from 2d-Tables to proper nested types for results IMHO.

That would be nice, though I suspect that it is really much more complicated than it seems. You can emulate this to some extent in Postgres with the various JSON functions and essentially return a tree from a single query. But my experience was that I quickly got to a point where the query plan got really complex and planning time started to dominate.

Author is using Rails, so my guess would be the bottleneck is ActiveRecord. I've never used ActiveRecord, so I can't speak to it directly, but in my experience when dealing with large numbers of records in an ORM (and author easily is working with hundreds of thousands), things grind to a halt, even with eager loading. There's a lot more overhead to create thousands of ORM objects than it is to serialize an equivalent chunk of BSON.

Bulk operations are were a lot of rails programmers struggle.

Active record is awesome in many ways, but it can shoehorn you into n+1 solutions.

12k is not a large number of records though

12k episodes, each with cast list and reviews. That's easily hundreds of thousands of records, possibly millions.

Even without an index that sounds too long (though obviously hardware and Postgres itself both have come a long way since 2013). At 12,000 rows even a brute force query should be quick.

I would suspect something like bad statistics or some other reason that caused a pathological query plan. In any case this is not a good representation of any potential performance difference between Postgres and MongoDB.

I could see it taking that long if she was using a full movie database with millions of rows in it.

Her

Thanks, corrected

at most cases it could also be 1.) how the user writes the code, and 2.) how the db api / library was coded

I bet they're using some stupid ORM that makes 12000 separate queries.

Reply on news.ycombinator.com