postgres

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

Some read paths want a denormalized shape without materializing a new table. Postgres views are a clean option. Keep the view definition in a migration and map it with a read-only model.

Not all queries benefit from a full-table index, especially when most rows are ‘inactive’ or archived. A partial index lets you index only the subset you care about (like active users or un-deleted records), which shrinks index size and improves cache

The fastest way to surprise yourself in production is ADD COLUMN ... NOT NULL DEFAULT ... on a large table. My safe pattern is: add the column nullable with no default, backfill in batches, then add the NOT NULL constraint. If I need a default for new

Under real concurrency, even ‘simple’ write paths can hit serialization failures, especially with stricter isolation levels. Instead of pretending it won’t happen, I wrap the transaction in a small retry loop with jitter. I only retry on known transie

I used a transactional outbox when I needed my database write and my event publish to succeed or fail together. In OutboxEvent model, I treated the outbox like a queue: a durable row per event, a dedupe_key for idempotency, and a ready scope that pull

Scheduling/booking is tricky. Postgres exclusion constraints prevent overlapping time ranges at the database layer—far more reliable than application checks. Rails can still validate, but the DB is the source of truth.