🔥 The DB Grill 🔥

Alright, let's see what we have here. Another blog post, another silver bullet. "Select first row in each GROUP BY group?" Fascinating. You know what the most frequent question in my team’s Slack channel is? "Why is the production database on fire again?" But please, tell me more about this revolutionary, high-performance query pattern. I’m sure this will be the one that finally lets me sleep through the night.

So, we start with good ol' Postgres. Predictable. A bit clunky. That DISTINCT ON is a classic trap for the junior dev, isn't it? Looks so clean, so simple. And then you EXPLAIN ANALYZE it and see it read 200,000 rows to return ten. Chef's kiss. It's the performance equivalent of boiling the ocean to make a cup of tea. And the "better" solution is a recursive CTE that looks like it was written by a Cthulhu cultist during a full moon. It’s hideous, but at least it’s an honest kind of hideous. You look at that thing and you know, you just know, not to touch it without three cups of coffee and a senior engineer on standby.

But wait! Here comes our hero, MongoDB, riding in on a white horse to save us from... well, from a problem that's already mostly solved. Let's see this elegant solution. Ah, an aggregation pipeline. It's so... declarative. I love these. They’re like YAML, but with more brackets and a higher chance of silently failing on a type mismatch. It’s got a $match, a $sort, a $group with a $first... it’s a beautiful, five-stage symphony of synergy and disruption.

And the explain plan! Oh, this is my favorite part. Let me put on my reading glasses.

totalDocsExamined: 10

executionTimeMillis: 0

Zero. Milliseconds. Zero.

You ran this on a freshly loaded, perfectly indexed, completely isolated local database with synthetic data and it took zero milliseconds. Wow. I am utterly convinced. I'm just going to go ahead and tell the CFO we can fire the SRE team and sell the Datadog shares. This thing runs on hopes and dreams!

I've seen this magic trick before. I've got a whole drawer full of vendor stickers to prove it. This one will fit nicely between my "RethinkDB: The Open-Source Database for the Real-time Web" sticker and my "CouchDB: Relax" sticker. They all had a perfect explain plan in the blog post, too.

Let me tell you how this actually plays out. You're going to build your "real-world" feature on this, the one for the "most recent transaction for each account." It'll fly in staging. The PM will love it. The developers will get pats on the back for being so clever. You’ll get a ticket to deploy it on a Friday afternoon, of course.

And for three months, it'll be fine. Then comes the Memorial Day weekend. At 2:47 AM on Saturday, a seemingly unrelated service deploys a minor change. Maybe it adds a new, seemingly innocuous field to the documents. Or maybe a batch job backfills some old data and the b timestamp is no longer perfectly monotonic.

Suddenly, the query planner, in its infinite and mysterious wisdom, decides that this beautiful, optimized DISTINCT_SCAN isn't the best path forward anymore. Maybe it thinks the data distribution has changed. It doesn't matter why. It just decides to revert to a full collection scan. For every. Single. Group.

What happens next is a tale as old as time:

• The query that took zero milliseconds in your pristine test environment now takes 30 seconds.
• The application's connection pool is instantly exhausted waiting for these queries to return.
• Your "web scale" cluster, which was running at a cool 15% CPU, spikes to 100% and starts falling over.
• Your monitoring—which you were told you wouldn't need to change because this was an application-level optimization—is screaming about CPU and memory, but has no idea which specific query is the murderer.
• The pager goes off. I stumble out of bed, knock over a glass of water, and log into a VPN that seems to be powered by a hamster on a wheel.
• I'll spend the next two hours trying to figure out why a service that hasn't been touched in three months is suddenly melting our most expensive piece of infrastructure, all while the on-call developer is telling me "but it works on my machine!"

By 5 AM, we’ll have rolled back the unrelated service, even though it wasn’t the cause, and I’ll be writing a post-mortem that gently explains the concept of "brittle query plans" to a room full of people who just want to know when the "buy" button will work again.

So please, keep writing these posts. They're great. They give me something to read while I'm waiting for the cluster to reboot. And hey, maybe I can get a new sticker for my collection.