Your query is slow. Postgres already told you why. The usual sequence when something goes sideways: someone pastes a slow query into Slack, someone else runs EXPLAIN, a third person SSHes into the box and stares at top. Forty minutes later you know the plan is fine and the CPU is at 30% and you …
Postgres Replication Lag Is Four Numbers, Not One The page comes in at 03:14: "replica is lagging." That sentence tells you nothing useful. Lagging where? Postgres doesn't have a lag counter. It has a pipeline with four measurable checkpoints, and the primary exposes all four side by side in pg_stat_replication. Know which gap is growing …
UUID vs BigInt Primary Keys in Postgres If you're choosing between bigint and uuid for a primary key, the short answer is: bigint (via bigserial or generated always as identity) wins on raw performance and index size, but uuid wins on distributed generation and merge-friendliness. The real decision hinges on how your data is inserted …
To increase max_connections in Postgres, run ALTER SYSTEM SET max_connections = N; (or edit postgresql.conf) and restart the server — a reload alone won't apply it, because max_connections is a postmaster-context parameter read once at startup. That's the easy part. The part that bites you three days later is sizing that number against real RAM, …
PostgreSQL transaction ID wraparound happens when the 32-bit XID counter runs out of safe room and the database stops accepting new writes to protect committed data from silently disappearing. It's not corruption — it's a deliberate safety interlock. Here's the mechanism, the exact diagnostic queries, and the recovery path that actually works. 📖 Read the …
Postgres Free Space Map: Why Deletes Don't Shrink Tables Why doesn't Postgres delete free disk space? You ran DELETE FROM events WHERE created_at < now() – interval '90 days'. It reported 40 million rows deleted. You ran VACUUM ANALYZE events afterward, like a responsible adult. Then you checked: 📖 Read the full guide: Postgres Free …
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 …