Best Practices

This page pulls together the recurring pieces of advice from across the whole PostgreSQL series into a single checklist. None of these rules are complicated on their own — the value is in applying them consistently, every time, so they become habits rather than things you remember only after an incident.

The checklist

Always pair UPDATE/DELETE with a WHERE clause, and run the equivalent SELECT first to confirm exactly which rows will be affected before running the write.
Use TIMESTAMPTZ instead of TIMESTAMP for anything user-facing — it stores an unambiguous point in time and avoids silent timezone bugs.
Prefer JSONB over JSON unless you specifically need to preserve the original text formatting or key order; JSONB is indexable and generally faster to query.
Use GENERATED ALWAYS AS IDENTITY columns instead of SERIAL for new schemas — it is the SQL-standard approach and avoids some of the sequence-ownership quirks of SERIAL.
Always use parameterized queries or prepared statements from application code — never build SQL by concatenating user input.
Index the columns your queries actually filter, join, or sort on — an index that doesn't match a real query pattern is dead weight.
Verify performance changes with EXPLAIN ANALYZE rather than assuming a change helped — plans and timings are cheap to check and easy to be wrong about intuitively.
Wrap multi-step operations that must succeed or fail together in a transaction, so a failure partway through can't leave data in an inconsistent state.
Apply least-privilege roles: give each application or user only the permissions it needs, and never use the superuser account for routine application connections.
Keep regular, tested backups — a backup you have never restored from is unverified and cannot be trusted in an emergency.

A few of these in practice

Confirm before you commit to a destructive statement:

SQL

-- First, see exactly what will be affected:
SELECT * FROM orders WHERE status = 'pending' AND created_at < now() - interval '30 days';

-- Only then run the write with the identical WHERE clause:
DELETE FROM orders WHERE status = 'pending' AND created_at < now() - interval '30 days';

Multi-step operations belong in a transaction:

SQL

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
-- If either UPDATE fails, roll back instead of committing a half-done transfer.

Tip

Treat this checklist as defaults, not absolute laws — there are legitimate exceptions to almost every rule here. The point is that deviating from a default should be a deliberate, reasoned choice, not an oversight.