postgres

If you store flags/settings in JSONB, query performance hinges on indexing. Partial indexes are a great compromise: index only the rows that matter for the hot path (e.g., enabled flags).

If you have a “top list” that’s expensive to compute, a materialized view is a clean approach. Refresh concurrently on a schedule to keep reads fast without blocking.

The moment you split ‘write to DB’ and ‘publish to a queue’ into two independent operations, you create a place to lose data. Publish first and a DB failure means consumers act on something that never happened. Write first and a publish failure means

I reached for idempotency the moment retries started duplicating side effects. In Advisory lock helper, I generate a deterministic lock ID and use pg_try_advisory_lock to ensure only one worker owns the critical section; the ensure block always calls

After getting burned by long-lived connections that slowly accumulate bad state (or get killed by the network) and then explode during peak traffic, I got strict about pg pooling. I keep the pool size small per instance and scale horizontally instead

Race conditions happen. The correct “find or create” in production uses a unique constraint and a retry on conflict, not a naive check-then-insert. Let the database serialize the race.

When adding a new query path, run EXPLAIN in CI or a smoke task to catch missing indexes before production. You don’t need full query plans everywhere—just guard the hot paths.

Offset pagination falls apart as soon as rows are inserted or deleted between page fetches—users see duplicates or missing items. Cursor pagination fixes that with stable ordering and ‘seek’ queries. I use a compound cursor that includes both the prim

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.

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

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.