Queries, migrations, deadlocks, replication, performance.
We switched our semantic search to a cheaper 1024-dimension embedding model and every insert and query failed against the 1536-dimension column. Worse, the half-finished fix would have mixed incompatible vectors. How we migrated with versioned columns, a backfill and dual reads.
A one-off data backfill filled our Postgres RDS instance, storage autoscaling grew it from 400 GB to 2 TB, and after we deleted the temporary tables the bill stayed. RDS allocated storage cannot be reduced in place. We shrank it with a blue/green switchover and capped autoscaling.
A trading system that cannot explain its balances to the cent will eventually make decisions on wrong numbers. This guide shows how we keep an internal double-entry ledger for trades, fees, funding and transfers, reconcile it with exchange balances, and handle the differences we find.
Page 1 of our activity feed loaded in 9 ms, page 4,000 in 3.2 seconds, and a partner's sync job was walking every page. OFFSET makes Postgres read and discard every earlier row. We switched to keyset pagination with a tie-breaker and a matching index.
Our MySQL primary hung and then aborted with the data volume at 100%. Binary logs had grown to 410 GB because a backfill tripled write volume while retention had been raised for a lagging replica. How we recovered, purged safely and stopped it recurring.
A one-second ALTER TABLE ... ALGORITHM=INSTANT hung for 11 minutes and every query on the table queued behind it. The blocker was a reporting job holding an open transaction. How we found it with performance_schema and made migrations fail fast.
Every value in our orders API was a bound parameter, except the sort column, which was pasted into ORDER BY from the query string. A pentest proved it was injectable. We replaced it with an allowlist map and added tests that fuzz the sort input.
Running the test suite inside our app container dropped every table in the development database. Docker Compose injected DB_DATABASE as a real environment variable, and phpunit.xml's env entries do not override existing variables. Here is the full precedence chain and three layers of defence.
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.
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.
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.
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.
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.
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.