Postgres in practice: a tiny production playbook

Here are sensible defaults I reach for when spinning up a Postgres‑backed service.

Schema & types

• Use UUID primary keys (uuid_generate_v4() or app‑side ULIDs).
• Add created_at and updated_at with DEFAULT now().
• Prefer TEXT over under‑sized VARCHAR(n) unless you truly need the limit.

Indexes

• Unique indexes for external identifiers (email, username, slug).
• Partial indexes for soft‑deleted rows (e.g., WHERE deleted_at IS NULL).
• Composite indexes in the query order you filter/sort.

Migrations

• One feature = one migration file; never edit history, always add.
• Wrap destructive changes with safe rollouts (create new column, backfill, swap).

-- Example: safe rename via new column
ALTER TABLE users ADD COLUMN full_name TEXT;
UPDATE users SET full_name = first_name || ' ' || last_name WHERE full_name IS NULL;
-- Switch reads/writes in app, then drop old columns in a later migration

Connections

• Use a pool (PGbouncer or library pool). Close idle connections.
• Keep transactions short; do work in app memory before/after the TX.

Observability

• Enable slow query logging; sample queries over N ms.
• Track connection counts and queue times.

Simple, boring defaults reduce 90% of “why is prod slow?” incidents.