Postgresql

About once a month an order existed and nobody heard about it. The order service saved the order, then published order.created to RabbitMQ, and once a month the process died between the two. Rare enough to be mysterious. Frequent enough to ruin a weekend. Two operations, two systems, no common transaction. Whatever order you do them in, you lose. Commit first, then publish: the order exists, the event never leaves. Publish first, then commit: the commit fails, consumers are already busy with an order that does not exist. We had the first variant in production. ...

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. ...

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. ...

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. ...

DROP TABLE events_2017_10. That is how you delete October now, and it is the reason I am reading PostgreSQL 10 release notes on a Thursday evening instead of waiting a year like usual. Partitioning existed before, through inheritance, CHECK constraints and an insert trigger you wrote yourself and hoped was right. Now the database owns it: CREATE TABLE events ( id bigserial NOT NULL, created_at timestamptz NOT NULL, payload jsonb ) PARTITION BY RANGE (created_at); CREATE TABLE events_2017_10 PARTITION OF events FOR VALUES FROM ('2017-10-01') TO ('2017-11-01'); The win is the data lifecycle. An events table grows forever, and deleting a year of history with DELETE is a night of I/O plus a bloated table in the morning. With partitions, retiring a month is one DROP. Instant. And a query that filters by created_at visits only the partitions in range, the planner skips the rest. ...

LIMIT 50 OFFSET 500000. Page 10001 of an events table, from a paginator someone wrote in an afternoon. Postgres has no shortcut to row 500000. It walks the index through half a million entries, fetches them, throws them away, and returns fifty. Page one is fast. Page ten thousand is slow, and every page after it is slower. Run EXPLAIN ANALYZE on both: the plans are identical, the numbers are not. ...

38 seconds to 11. Same query, same forty million rows, no index added, no SQL changed. The only difference is PostgreSQL 9.6, released yesterday, and one setting. The query is a typical report: count and sum over an events table, grouped by day, three months of data. On 9.5 the plan is one process grinding through the table. On 9.6 with parallelism enabled: Finalize HashAggregate -> Gather Workers Planned: 4 -> Partial HashAggregate -> Parallel Seq Scan on events Four workers scan their own chunks, aggregate partially, the leader merges. Just more hands. For years we optimized SQL as if the database had exactly one worker per query. That assumption expired yesterday. ...

$row = $db->fetchOne('SELECT id FROM counters WHERE name = ?', array($name)); if ($row) { $db->execute('UPDATE counters SET value = value + 1 WHERE name = ?', array($name)); } else { $db->execute('INSERT INTO counters (name, value) VALUES (?, 1)', array($name)); } Works on the laptop. In production two requests arrive in the same millisecond. Both SELECT, both see nothing, both INSERT. One dies with a duplicate key error. Or worse, there is no unique constraint, and now you have two rows and a bug report you cannot reproduce. The window between SELECT and INSERT is tiny, so it fires once a week, always for someone else. ...