6 ms·
> 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
by jonstaab 6y ago
> 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.
- mywittyname 6y agoYeah, 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.
- mbreese 6y agoThe 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.
- dathinab 6y agoI 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.
- fabian2k 6y agoThat 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.
- wetmore 6y agoHer
- jonstaab 6y agoThanks, corrected
- kulig 6y agoI remember reading some of her comments a while ago and she seems like a pretty arrogant person. Probably doesnt know how to use postgres properly.
- fabian2k 6y agoEven 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.
- jonstaab 6y agoI could see it taking that long if she was using a full movie database with millions of rows in it.
- joshxyz 6y agoat most cases it could also be 1.) how the user writes the code, and 2.) how the db api / library was coded
- luhn 6y agoAuthor 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.
- treeman79 6y agoBulk 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.
- vidugavia 6y ago12k is not a large number of records though
- luhn 6y ago12k episodes, each with cast list and reviews. That's easily hundreds of thousands of records, possibly millions.
- layoutIfNeeded 6y agoI bet they're using some stupid ORM that makes 12000 separate queries.