The Postgres Guy
Postgres internals, performance, and operations — without the years of trial-and-error.
You have a ledger table for all your customers' accounts. You want two transactions on the same account to serialize, but transactions on different accounts to run in parallel. This post describes a solution using an advisory lock keyed on the account id.
Autovacuum being enabled doesn't mean it's keeping up. The default threshold scales with table size, and the fix isn't the command you'd reach for first.
The default thresholds were sized for tables that don't exist anymore. Here's the math that actually scales — and the per-table overrides that fix it.
Two WHERE clauses that look identical to you can look completely different to the PostgreSQL planner. A short tour of function volatility — IMMUTABLE, STABLE, VOLATILE — and why now() changes how your query gets planned.
PostgreSQL never modifies a row in place. Every UPDATE writes a new version and leaves the old one behind. Here's the machinery — and why it explains half your performance problems.
Every connection to PostgreSQL spawns a dedicated OS process. That isolation is a feature — until you have 500 of them sitting idle, eating RAM, and your app starts getting FATAL errors. Here's what's actually happening under the hood.
The third piece in a series on PostgreSQL memory. Why your CREATE INDEX crawled on a 500GB table, why autovacuum runs but doesn't finish the job, and the one setting you should be generous with.
A misunderstood setting, a misread symptom, and the concurrency math nobody warns you about. The second piece in a series on PostgreSQL memory, this time on work_mem and why your sort spilled to disk.
Your dashboard re-aggregates millions of rows on every page load. A plain materialized view goes stale and rebuilds the whole thing. TimescaleDB continuous aggregates fix both — incrementally — and most people set them up in a way that quietly recomputes the present every time. Here's how they actually work.
The first piece in a series on PostgreSQL memory. Why the RAM that looks idle in htop is already working, why bigger shared_buffers stops helping past a point, and the one diagnostic that actually tells you whether you need more.
Page 1 of 3 · 27 posts