Partition Pruning Isn’t Magic — Here’s When It Actually Works A few weeks ago I sat down with a team that had just “fixed” a slow dashboard by partitioning a 12‑million‑row orders table by month. One hundred partitions, clean range boundaries, no more full‑table scans. Then they showed me the week‑end report query, and the …
It’s 02:17, your phone buzzes. pg_stat_statements says query f8472a91 has a mean runtime of 6.2 seconds over the last hour. The raw text is a six-table join with a couple of sub-selects. You copy it, paste it into a psql session, slap EXPLAIN (ANALYZE, BUFFERS) on front, and get a tight index scan that finishes …
Postgres jsonb_array_elements: Turn JSON arrays into queryable rows inside SQL The problem: JSON payloads jammed into a column We have all seen it in production systems: an upstream API sends down a nested payload, and instead of designing a proper relational schema, someone decides to dump the raw JSON straight into a single jsonb column. …
Two Replication Mechanisms, Not Two Flavors of the Same Thing Before choosing between Postgres replication strategies, it is helpful to reference our companion video, which steps through the visual layout of these architectures. That video serves as a quick visual guide, but this article cuts straight to the underlying machinery. We often talk about replication …
Zero Downtime Postgres Major Upgrade: Replacing pg_upgrade with Logical Replication Let's talk about Postgres major upgrades. If you search for solutions, the standard recommendation is almost always pg_upgrade with the –link flag. It is fast, but it is also a binary choice. Once you run it, your old cluster is modified, and your new cluster …
The 'password you never set' prompt isn't a bug — it's pg_hba.conf doing exactly what you told it The moment it happens You just installed PostgreSQL. You followed the getting-started guide, or maybe you didn't — maybe you just ran brew install postgresql or apt install postgresql and typed psql to see what would happen. …
Postgres vs Redis, Mongo, RabbitMQ: The Real Decision Framework I’ve consolidated stacks under a deadline with production on fire. I’ve also ripped Postgres back out six months later and put Redis back where it belonged — on its own instance, doing a single job. No hedging. There’s a sweet spot where a single Postgres database …
Most Postgres performance disasters start with a schema decision nobody thought twice about If you’ve watched the accompanying video, you already know the premise: Postgres rarely blows up in smoke. It bleeds. The query that’s slow today has a root cause that was laid down six months ago in a CREATE TABLE nobody reviewed. The …
Postgres Autovacuum: Why Your Deletes Don’t Free Up Space and What to Do About It If you want the visual walkthrough, there’s a companion video that covers these internals on screen. This article stands entirely on its own—read straight through, and you’ll come away knowing how autovacuum works and what to pay attention to. When …
I have spent the better part of a decade working with PostgreSQL in production. Not the kind of PostgreSQL where you run a tutorial, create a users table, and call it a day — the kind where you wake up at 3am because replication lag just crossed 30 seconds and nobody knows why, or where a query …