Postgres moves any row value over roughly 2KB (TOAST_TUPLE_THRESHOLD) out of the main table and into a hidden pg_toast table, compressing or splitting it along the way. That's TOAST — The Oversized-Attribute Storage Technique — and it's why a table full of jsonb or text columns can behave very differently from a table of integers, …
What Postgres Row-Level Security Actually Does Row-level security (RLS) lets Postgres filter and check rows per query, per role, based on a policy you define in SQL. Instead of adding WHERE tenant_id = :current_tenant to every query in every service, you write the rule once and the database enforces it — on your API, your …
A team I worked with added Elasticsearch for a search box over a documents table with roughly 400,000 rows. Six weeks later they owned a CDC pipeline, a nightly reindex job that nobody could run in business hours, a queue that backed up whenever someone bulk-edited a category, and a support ticket class I still …
"No pg_hba.conf Entry for Host": What the Error Actually Means and How to Fix It If Postgres just handed you FATAL: no pg_hba.conf entry for host "10.0.2.15", user "app", database "orders", SSL off, here's the short version: your connection reached the server, but no line in pg_hba.conf matched the client address, database, user, and encryption …
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 …