postgres

Deadlocks happen under load. When the operation is safe to retry, rescue the deadlock exception and retry with jitter. Keep retries bounded and log when it happens.

CSV imports are where memory usage quietly explodes if you read everything at once. I stream rows with encoding/csv, validate each record, and batch inserts to keep DB overhead low. The important detail is backpressure: if the database is slow, the im

I stopped doing row-by-row imports once they started hammering the DB. In Migration (unique index), I added a real uniqueness guarantee on provider + uid, because bulk writes only stay correct if the database can enforce constraints. Then, in Bulk ups

When multiple clients can update the same record, I prefer optimistic locking over heavy row locks. The idea is simple: every row has a version that increments on each update. The update statement includes WHERE id=$1 AND version=$2, so if someone els

Rails enums are nice, but the DB should enforce allowed values. Use a CHECK constraint (or native enum type) plus the Rails enum mapping. It prevents bad writes from console scripts and future migrations.

For leaderboards, let the database do ranking. Window functions are fast and expressive. Use them to compute daily top N without Ruby loops.

The first time a CSV import OOM’d a production process, I stopped trusting ‘just read it into memory’. Now I stream the upload to disk (or S3), stream-parse rows, and batch inserts into the DB. The win is that memory usage stays flat, even when the fi

Counters are classic race-condition bait. If two requests read, increment, and write, you’ll lose updates under load. I prefer letting Postgres do the atomic work with an upsert: INSERT ... ON CONFLICT ... DO UPDATE to increment the existing value. Th

A per-request statement_timeout is a great “seatbelt” for endpoints that can run bad queries when parameters are unexpected. You can set it for a block; Postgres enforces the wall clock limit.

For apps with replicas, route read-only code paths to reading role and enforce prevent_writes. This is a strong reliability move: accidental writes on replicas become exceptions instead of silent data loss.

Retries are inevitable: mobile clients, flaky networks, and load balancers will resend POST requests. Without idempotency you end up double-charging or double-creating records. I store an Idempotency-Key with a sha256 hash of the request body and the

Sometimes you just need ‘only one worker does this thing at a time’, and building distributed locks from scratch is risky. Postgres advisory locks are a pragmatic option when your DB is already the source of truth. I derive a deterministic lock key (l