If you're trying to figure out what changed between Postgres versions — or which one to upgrade to next — here's the short version: 14 fixed connection scaling and added pipeline mode, 15 brought MERGE, 16 opened up logical replication from standbys, 17 overhauled vacuum memory and added JSON_TABLE, and 18 shipped real asynchronous I/O …
pg_cron: Scheduling Maintenance Inside PostgreSQL Without Losing the Plot The 2 a.m. DELETE that lives on a laptop Every database I inherit has maintenance SQL scattered in at least three places. There is a crontab -l on the primary that nobody has read since the last host migration. There is a Jenkins job called db-cleanup-nightly …
Aurora PostgreSQL doesn't write pages to disk the way Postgres does. It ships write-ahead log records to a distributed, log-structured storage layer and lets that layer build pages on demand. That one design choice explains almost every "why does this metric look wrong" question that shows up when a Postgres-trained team inherits an Aurora cluster …
Postgres Time-Series Partition Maintenance, Done Right Partitioning a time-series table in Postgres is the easy part — CREATE TABLE … PARTITION BY RANGE takes ten minutes. Keeping it fed with new partitions, pruned of old ones, and monitored so a failure pages you instead of your users is the part that actually determines whether this …
pgvector Index Tuning: What Actually Matters If you're running vector search on Postgres with pgvector, three decisions determine whether you get a search service you can reason about or one that quietly lies to you: which index type you pick, how you tune it, and how you handle filtered queries. Get the first two wrong …
Intuition is what gets you a two-hour outage. You "have a feeling" it's the connection pool, you bounce the app tier, it doesn't help, and now you've lost fifteen minutes plus whatever evidence was sitting in the app logs. The fastest path back is a fixed sequence run in a fixed order: disk, connections, OOM, …
pg_cancel_backend vs pg_terminate_backend: a DBA's runbook It was 02:14 on a Tuesday. A migration job fired an UPDATE against a 40M-row orders table without a WHERE clause narrow enough to matter, took an exclusive row lock on half of it, and the checkout path started queueing behind it. PagerDuty went off at the twelve-minute mark. …
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 …