Queries, migrations, deadlocks, replication, performance.
An order says COMPLETED, but nobody can say what it was before, who changed it, or when. We compare status history tables, trigger-based audit logs, temporal rows and event sourcing, with a runnable Postgres example and the rule we use to pick one.
An index plan that is perfect at a million rows becomes a liability at a billion. We walk one events table through four sizes, show where the planner changes its mind, and what we added, removed or replaced at each stage.
A shipment timeline query had a compound index yet still took 2.4 seconds. explain() showed a blocking SORT stage and 180,000 documents examined for 50 results. Reordering the index by Equality, Sort, Range fixed it.
Users were logged out at random, mostly in the afternoon. Sessions and a fast-growing cache shared one Redis with allkeys-lru, so every hourly cache warm-up evicted idle sessions. Separate DB numbers would not have helped; separate instances did.
The same query ran in 2 ms from psql and 4 seconds from our Go service, but only for some tenants and only after a connection had been alive for a while. Postgres had switched the prepared statement to a generic plan on its sixth execution.
EXPLAIN ANALYZE prints a wall of nodes, costs and loops that most of us skim for the word Seq Scan. This guide walks a real slow query node by node: estimates vs actuals, loops, buffers, and the fixes each pattern points to.
Customers got two copies of the same invoice email because two workers claimed one job row. The claim query looked atomic but was not under READ COMMITTED. One CTE with FOR UPDATE SKIP LOCKED, plus a lease, fixed it.
After importing 410,000 tickets with their original ids, most new tickets failed with duplicate key value violates unique constraint tickets_pkey, while a few succeeded. The id sequence was still at 1. Here is why, and how we resynced every sequence safely.
Every ten minutes our catalog API spiked to 4-second responses and the database hit 100% CPU. A hot Redis key expired and 600 requests rebuilt it at once. Single-flight, a short Redis lock, stale-while-revalidate and TTL jitter fixed it.
When MySQL sits at 100% CPU, the guilty query is rarely the slowest one; it is usually a fast query run millions of times. A practical sequence for finding it: the processlist for right now, performance_schema statement digests for the last hours, the sys schema for shortcuts, and pt-query-digest for slow log history.
Small tenants in our RAG product got 2 or 3 chunks back instead of 10, and sometimes none. HNSW finds ef_search candidates first and applies the tenant filter afterwards. Iterative index scans in pgvector 0.8 plus partial indexes for big tenants fixed recall.
Semantic search in our docs assistant took 1.9 seconds and returned odd rankings. The query sorted by L2 distance while the HNSW index was built for cosine, so Postgres scanned every row. Matching the operator to the opclass brought it to 14 ms.
Our job board listing took 1.8 seconds and ran 312 queries per page. Blade was lazy loading company, tags and application counts for every row. We fixed it with with(), withCount() and made lazy loading throw in development.
A one-line ALTER TABLE in a deploy hung for eleven minutes and took the whole site down with it. An idle-in-transaction worker held a lock, the ALTER queued behind it, and every query queued behind the ALTER. Here is the diagnosis and the timeouts we now set.
Our monthly-partitioned orders table was supposed to make the daily report touch one partition. EXPLAIN showed all 36. A date cast on the partition key and a join-driven filter were blocking pruning, and two small query rewrites brought it back.
Most reporting queries that end up as nested subqueries or app-side loops are one window function away from simple. A practical tour of LAG for gaps, running totals, moving averages and the frame clause defaults that quietly give wrong numbers.
Stock updates from supplier feeds started failing with Deadlock found when trying to get lock during peak hours. Concurrent upsert batches touched the same unique keys in different orders under InnoDB next-key locking. Sorting, smaller batches and a retry fixed it.
Our invoice list took 3 seconds even though a composite index covered both filter columns. The range column was first, and two other queries wrapped the column in a function or a cast. Reordering and rewriting fixed all three.
Product reviews containing emoji failed with error 1366 after we upgraded MySQL to 8.0, while a nightly job quietly turned them into question marks. Legacy utf8mb3 tables, a wrong connection charset and an index length limit were all involved.