A production-like dump, a separate PostgreSQL 19 beta instance, one evening plus machine time. That is the whole method, and it is the only thing a database beta is for: finding out where the new version changes the behavior of your application while the change is still a bug report and not your incident. Restore the dump first. Data size matters. A plan that is fine on a thousand rows goes bad on fifty million. ...
PostgreSQL 18 went GA yesterday. I ran the beta in July. Now it is not a preview, it is a ticket, and these are the notes from planning it. The quiet feature that matters most for the upgrade itself: pg_upgrade now carries planner statistics over. Before, the minutes or hours after switching versions were the scary part. Fresh cluster, empty statistics, the planner guessing, production limping until ANALYZE finished. That cliff is mostly gone. This alone changes how a major upgrade feels at 2 a.m. ...
A staging copy of one database, restored over the weekend onto PostgreSQL 18 beta. GA is expected in autumn. Two things I wanted to touch: asynchronous I/O and the built-in uuidv7(). Start with uuidv7, because it is about a mistake many of us already made, me included. Random UUIDv4 primary keys scatter inserts across the whole index. Every insert lands on a random page, the working set is the entire index, buffers churn. UUIDv7 is time-ordered: new rows go to the same few pages, like a sequence, but still globally unique. ...
A reporting transaction open for hours, and n_dead_tup on the hot table climbing all day while autovacuum ran non-stop and changed nothing. That was one project. PostgreSQL 17 came out yesterday, and the headline work is exactly this plumbing: vacuum got a new memory structure for tracking dead tuples, far less memory and no longer capped the old way, plus better throughput for high-concurrency writes and bulk loads. Maintenance internals. Which is why backend people should care. ...
A nightly import, a few hundred million rows through COPY, then reporting queries in the morning. That is the only workload I care about, so that is how I read the PostgreSQL 16 release notes: with one pipeline in mind and everything else skipped. Three items survived the filter. COPY got faster, noticeably when several loads run at once. Our import is exactly this shape, parallel COPY streams into partitioned tables. I will take the free speed. But the old lesson stays: if your import still does row-by-row INSERT from PHP, no Postgres release will save you. Batch into COPY first, then talk about versions. ...
The same table, the same rows, about 1.5 times the disk. That was the measurement after someone made UUIDv4 the primary key in InnoDB, and that is before counting secondary indexes. Every new API wants UUIDs. Distributed generation, no coordination, nothing leaks about row counts. All true. Then the database quietly starts paying. InnoDB clusters the table by primary key. Auto-increment inserts always land in the rightmost page, warm and predictable. Random UUIDs land anywhere in the tree: page splits everywhere, half-empty pages, and the set of hot pages becomes the whole index. Secondary indexes store the primary key in every entry, so each of them inherits the full 16 bytes too. In PostgreSQL the heap does not cluster, so the pain is smaller, but index bloat and lost locality are still there. ...
A supplier price list, loaded into staging_prices, then reconciled against prices. Since PostgreSQL 15 came out two weeks ago, that is one statement: MERGE INTO prices p USING staging_prices s ON p.sku = s.sku WHEN MATCHED AND s.price IS NULL THEN DELETE WHEN MATCHED AND p.price <> s.price THEN UPDATE SET price = s.price, updated_at = now() WHEN NOT MATCHED THEN INSERT (sku, price) VALUES (s.sku, s.price); Before 15 this was three statements in a transaction, or a stored procedure, or a loop in PHP. Now it is one statement that says what it does. Update changed rows, delete withdrawn ones, insert new ones. People coming from Oracle and SQL Server waited a decade for this. The conditional WHEN MATCHED AND ... branches are the real value. ON CONFLICT cannot express “delete when the source says so” at all. ...
Audit snapshots, product attributes, and a document with two hot keys. Three cases from one Postgres project, one question for each: column or JSONB. I answer it by looking at queries, not at data. Audit snapshots. When an order changes, we store the whole previous state. Nobody queries inside it. It is read as a blob, whole, rarely, by a human during an incident. Perfect JSONB. Normalizing it would mean a dozen tables for data nobody joins. ...
Sixty million rows in orders, a new nullable column total_cents from expand-and-contract, and a Friday afternoon. The question is how to fill it. The naive answer is one statement. UPDATE orders SET total_cents = ROUND(total * 100). On a test database it works. On production it holds row locks on the whole table for the whole run, writes one giant chunk of WAL, and the replica falls minutes behind. On MySQL the binlog gets the same present. One project I worked on learned this on that Friday. Since then I backfill only in batches. ...
A few hundred connections, mostly idle, and the whole server gets slower. That was Postgres for as long as I remember it: a process per connection, and past some count the snapshot bookkeeping starts to eat everything. PostgreSQL 14 is released today. I had the RC on a test box for a week, so a few notes from a backend seat. The headline for me is that this internal work got a serious rewrite. On our connection-heavy profile, many workers, short queries, lots of idle time, the difference is visible without squinting. Idle connections finally cost close to what people always assumed they cost. ...