🔥 The DB Grill 🔥

Ah, another one. I have to commend the author's diligence here. It's always a nostalgic trip to see someone painstakingly rediscover the beautiful, intricate tapestry of edge cases and "gotchas" that we used to call a feature roadmap. It warms my cold, cynical heart.

Reading this feels like finding one of my old notebooks from my time in the trenches. The optimism, the simple goal—"Let's just make PostgreSQL do what Mongo does!"—followed by the slow, dawning horror as reality sets in. It’s a classic.

I mean, the sheer elegance of the jsonb_path_exists (@?) versus jsonb_path_match (@@) operators is something to behold. It’s a masterclass in user-friendly design when two nearly identical symbols mean "find if this path exists anywhere, you idiot" and "actually do the comparison I asked for." Peak intuition. It’s the kind of thing that gets a product manager a promotion for “simplifying the user experience.”

And the GIN index! Oh, the GIN index. I remember the slide decks for that one.

Unlocks the power of NoSQL inside your relational database! Seamlessly query unstructured data at scale!

Seeing the EXPLAIN plan here is just... chef's kiss. The part where the "index" proudly announces it found all possible rows (rows=2.00) and then handed them over to the execution engine to actually do the filtering (Rows Removed by Index Recheck: 1) is just beautiful. It’s not a bug; it’s a two-phase commit to disappointing you. The index does its job: it finds documents that might have what you're looking for. The fact that it can't check the value within that path is just a minor detail, easily glossed over in a marketing one-pager. We called that "performance-adjacent."

But my favorite part, the part that really brings a tear to my eye, is the descent into madness with expression-based indexes.

• First, the simple, obvious solution fails because of a syntax error. Classic. Builds character.
• Then, the corrected version fails because you can't create an index on an expression that returns a set, like, you know, the contents of an array. Which is, of course, the entire reason you'd be using a document-style field in the first place. A truly synergistic failure.
• And finally, the grand finale: creating an IMMUTABLE function for something that is explicitly, demonstrably not immutable.

This is the kind of solution you come up with at 2 AM before a big demo, praying nobody on the client's side knows what a timezone is. You ship it, call it an "advanced technique," write a blog post, and move on to the next fire. The fact that it still doesn't even solve the array problem is just the bitter icing on the cake. It solves a problem that doesn't exist while spectacularly failing at the one that does.

The author concludes that you should use the right tool for the job. And they're right, of course. But what they so wonderfully illustrate is the sheer amount of technical debt, broken promises, and clever-but-wrong workarounds you have to wade through to even figure out what the "right tool" is anymore. Every database now claims to do everything, and the documentation always shows you the one perfect, sanitized example where it works.

You have to admire the effort, though. Trying to bolt a flexible, schema-on-read document model onto a rigid, schema-on-write relational kernel is the software equivalent of putting racing stripes on a tractor. Sure, it looks fast in the brochure, but you're still gonna have a bad time at the Formula 1 race.

Sigh. Just another Tuesday in the database wars. At least the bodies are buried under a mountain of EXPLAIN plans that nobody reads.