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. ...
Two hundred thousand rows over the wire to compute forty numbers. That was our monthly report on MySQL 5.7: fetch all transactions for the period, loop in PHP, accumulate a running balance, compare each row with the previous one, rank customers by volume. Classic 5.7 shape, because the database could not say “previous row” or “rank within group”. We are finally moving that project to MySQL 8. Two years after GA, which by database standards is reckless haste. The first win had nothing to do with performance. We deleted PHP. ...
Error 1213 in the logs, and the chat lights up: the database is broken. It is not. Two transactions locked rows in opposite order, A waits for B, B waits for A, and InnoDB did the only sane thing: picked a victim and killed it. The cycle is gone. This is a feature. The classic shape is a money transfer. One request moves funds from account 1 to account 2, another from 2 to 1, both update the first account and then the second. Opposite order, instant cycle under load. Tests never show it, because tests do not run two of these in the same millisecond. ...
Someone inserts a row while the client walks the pages, and the whole window shifts. Page two shows an item the client already saw on page one. Or an item falls between pages and the client never sees it. For a feed this is annoying. For an export or a sync endpoint this is a data loss bug that nobody can reproduce. I used to think keyset pagination is a performance trick. Deep OFFSET is slow, keyset is fast, end of story. Now I think the performance part is the boring half. Offset pagination over a changing dataset lies to the client. That is the interesting half. ...
grep -rn "WITH " src/ was the first thing I ran after upgrading to PostgreSQL 12 this month. I was looking for a trick that had stopped working. For years WITH was an optimization fence. The planner materialized the CTE first and only then ran the outer query. Everyone used this both ways. As a bug: you wrap a subquery into a CTE for readability, the planner stops pushing conditions inside, a fast query becomes a scan of half a table. As a feature: you write a CTE on purpose, to pin evaluation order and stop the planner from being creative. Half of the CTE advice on the internet is really advice about the fence. ...
Ten rows in the dev database, on the same machine, 0.1 ms per query. Ten extra queries is one millisecond. The page feels instant, the code ships. Production has a thousand rows and the database one network hop away. Round trip is about a millisecond even in a good datacenter. A thousand queries is a second of pure network waiting. Not slow SQL. Each query is fast. The plural is slow. ...
Statement three of a migration fails. Column name typo. On PostgreSQL the first two ALTERs roll back with it, the schema returns to the exact state before the migration, you fix the typo and run again. On MySQL the first two ALTERs are already permanent. Same up() method, same php artisan migrate, same green output. The migration tool gives one abstraction over two very different databases. The abstraction covers syntax. It does not cover what happens on failure. ...
jit = on, restart, run the API test suite. Same numbers as before. That was my first evening with PostgreSQL 11, released last week, and it was the correct result. JIT is the loud feature of this release and the misunderstood one. Postgres can now compile expression evaluation into machine code through LLVM. People read “compilation” and expect their endpoints to get faster. They will not. A primary key lookup takes a fraction of a millisecond. There is nothing in it worth compiling, and the compilation itself costs more than the whole query. JIT is for the other kind of query: an aggregate chewing through millions of rows, where the same expression runs so many times that generating machine code for it pays back. ...
Using index in the Extra column of EXPLAIN. That is the cheapest read MySQL can do, and you often get it almost for free. SELECT user_id, created_at FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20; With an index on (user_id, created_at) everything the query needs is in the index leaves. The table is never touched. A composite index finds rows fast, a covering index answers the query on its own. Postgres calls this Index Only Scan, with one condition: the visibility map must be fresh, so a table that vacuum never visits quietly falls back to heap fetches. ...