postgres

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.

Endpoints with optional filters often devolve into messy SQL string concatenation. I prefer building queries with squirrel so I can conditionally add WHERE clauses while keeping the final query parameterized. The pattern also helps keep ordering stabl

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

Postgres stability depends on respecting its limits. I configure pgxpool with explicit MaxConns and MaxConnLifetime so the service doesn't accidentally open too many connections during bursts. I also set a session statement_timeout in AfterConnect, wh

Prepared statements are useful when the same query runs in hot loops, but they come with lifecycle responsibilities. I prepare once (usually at startup), store the *sql.Stmt, and always close it on shutdown. The important detail is still using QueryRo

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

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

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.

If you have a queue table, avoid races by selecting and updating in one statement. Postgres UPDATE … RETURNING is the simplest building block for a correct custom queue / maintenance pipeline.

Slug generation is deceptively racy under concurrency. Use a unique index plus retry with a suffix. Keep it deterministic and fast; don’t query in a loop without bounds.

For Postgres search beyond trivial ILIKE, maintain a tsvector column and a GIN index. Update it via trigger or application logic. This keeps search fast and predictable even as your dataset grows.