After an OOM kill, our warehouse consumer restarted from its last committed offset and applied a few seconds of stock movements a second time. A quick fix that committed first lost events instead. Storing offsets in the same database transaction as the results fixed both.
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.
A security review of a legacy customer portal found the password column was only Base64-encoded. Here is how we confirmed it, why encoding is not encryption, and how we moved 180,000 accounts to Argon2id with rehash-on-login and a targeted reset.
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.
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.
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 support chat showed messages out of order, double ticks that never turned blue, and duplicates after reconnects. We rebuilt delivery around per-conversation sequence numbers, client message ids, device cursors and read watermarks. This is the protocol, schema and code.
Saving a row and then publishing to Kafka looks harmless until the process dies between the two. We walk through the dual-write bug we shipped, a working outbox with a polling relay you can run locally, the Debezium variant, and how sagas build on it across services.
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.
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.
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.
Tenants upload terabytes of audio, video, PDFs and images, and expect an LLM to answer questions over all of it, with their data isolated and a bill that matches what they used. Here is the AWS design we shipped: ingestion pipeline, isolation layers and metering.