Queries, migrations, deadlocks, replication, performance.
Every Postgres database we inherit has the same hidden costs: indexes nobody reads, tables twice their real size, and tables without a primary key. These are the catalog queries we run on day one, how to read them safely, and how to clean up without downtime.
After saving their profile, some users saw the old values on the next page and saved again. Laravel's sticky option only covers a single request, and the redirect read from a replica that was seconds behind. We fixed it with a WAL position check.
Our monthly 'top 3 sellers per region' bonus report left out a sales rep who tied for third, and a rerun picked the other one. ROW_NUMBER breaks ties arbitrarily. We show how to pick RANK, DENSE_RANK or WITH TIES on purpose.
Two withdrawals hit the same wallet within milliseconds, both saw enough balance, and the user spent more than they had. The code ran inside a transaction, but READ COMMITTED does not protect a read-then-write. We compare atomic updates, row locks and stricter isolation.
We moved an 800-million-row events table to monthly range partitions with a cutover lock of under a second. The trick was to attach the existing table as one big legacy partition, with every slow step done beforehand under weak locks.
When a small self-hosted service lost its disk, we restored the MySQL Docker volume from last night's tar backup, and MySQL refused to start with InnoDB corruption errors. The backup had been a file copy of a running database. How we recovered from an older dump, and the backup approach and restore drills we use now.
Insert throughput on our events table fell by a factor of six as it passed 400 million rows, while CPU stayed idle. The cause was random UUIDv4 keys scattering writes across the whole primary key index. Postgres 18 uuidv7() brought it back, without changing the column type.
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.
Our nightly pg_dump job reported success every night for four months. On the day we needed one, every dump in the bucket was truncated. A replica query cancellation plus a missing pipefail hid it. Here is the postmortem and the restore-tested backup job we run now.
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.
A traffic spike scaled our API from 8 to 30 pods, each with a 20-connection pool, and Postgres started refusing connections. Raising max_connections would have made it worse. We put PgBouncer in transaction mode in front and sized pools from the database side.
A 'random tip of the day' widget used ORDER BY RAND() LIMIT 1. It was fine at 5,000 rows and pinned the primary at 100 percent CPU at 1.4 million. Here is how we proved it and three random-row strategies that use an index instead.
Our PostgreSQL primary went read-only at 4 a.m. when its disk filled. The pg_wal directory held 380 GB, because a logical replication slot created for a CDC trial weeks earlier was inactive and still retained WAL. How we found the slot, recovered safely, and capped WAL retention with max_slot_wal_keep_size.
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.
We removed Redis from a mid-sized SaaS stack and moved its cache, job queue and pub/sub into Postgres. Here is the schema we used, the limits we measured, the traps we hit, and the signals that tell you to bring Redis back.
Postgres and MySQL both speak SQL, but their internals push applications in different directions. We compare connections, MVCC and cleanup, clustered versus heap storage, and replication, and list what each difference changes in your code and operations.
We moved a service on EKS from a database password to RDS IAM authentication. It worked at startup, then new connections failed with PAM authentication failed 15 minutes later: the app generated one token at boot and used it as the pool's password. How we generate tokens per connection.
Our game released 100 limited items at 20:00, and about 20,000 players tapped claim within seconds. 140 players received one. The endpoint checked remaining stock, then decremented it separately. How we proved the race and fixed it with an atomic conditional update, a constraint and a Redis gate.
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.
Our monthly statement job used a Redis SET NX lock with a 30 second TTL. One slow run outlived the TTL, a second worker took the lock, and 214 customers got duplicated statement lines. Here is why a TTL lock alone cannot be safe and how fencing tokens closed the gap.
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.
Replication, partitioning and sharding solve different problems, and picking the wrong one buys complexity without relief. This guide starts from the bottleneck you can measure and walks to the option that fixes it, including when Citus is worth it.