Many schema design choices have surprisingly large performance consequences. UUID primary keys of the v4 flavor are random and cause B-tree fragmentation because newly inserted rows land at unrelated pages; switching to UUID v7 (time-ordered) or pairing with a sortable column greatly improves insert locality. SERIAL is the old way to create auto-incrementing keys via a sequence plus a default; IDENTITY, written as GENERATED BY DEFAULT AS IDENTITY, is the SQL-standard equivalent and has cleaner permissions. A common footgun occurs when manually inserting into a serial column without invoking nextval, which lets the sequence lag behind MAX(id); a quick SELECT setval('seq', (SELECT MAX(id) FROM t)) realigns them. Generated columns, declared as colname int GENERATED ALWAYS AS (other_col * 2) STORED, work well as index expressions provided the underlying expression is truly immutable—a function index on now() fails because now() is STABLE rather than IMMUTABLE. Function volatility matters generally: IMMUTABLE functions may be inlined into index expressions and precomputed, STABLE functions may be inlined within a transaction, and VOLATILE functions must be re-evaluated for every row and cannot appear in indexes.
Several query idioms deserve particular attention. CTEs before PostgreSQL 12 were optimization fences: the planner could not push predicates into them. CTEs are inlined by default in PG12+, which is usually a win; if the old behavior is needed, prefix with MATERIALIZED. LATERAL joins evaluate a subquery per outer row, which is the natural shape for top-N-per-group queries. Window functions are the standard way to compute running totals—a pattern such as SUM(amount) OVER (PARTITION BY user_id ORDER BY ts ROWS UNBOUNDED PRECEDING) walks forward without collapsing rows. DISTINCT ON (col) ORDER BY col, ts is the idiomatic PostgreSQL way to pick a single representative row per group; ROW_NUMBER() OVER (PARTITION BY ...) WHERE rn = 1 is the portable equivalent. Random sampling via ORDER BY random() LIMIT n sorts the entire table and is almost always the wrong approach—TABLESAMPLE BERNOULLI(1) or a pre-sampled random column does the same job without sorting. ON CONFLICT (key) DO NOTHING or DO UPDATE SET ... makes inserts idempotent.
Online migrations of large tables are a recurring need. Adding a NOT NULL column with a default is metadata-only from PostgreSQL 11 onward, so the alter itself is instant; the real work is the backfill, which must be batched (UPDATE ... WHERE id BETWEEN ... with commits between batches) with pauses for VACUUM to keep bloat under control. Adding a foreign key to a busy table can be done without a long write-blocking scan by creating the constraint NOT VALID and then running VALIDATE CONSTRAINT in a second step; row-level security via CREATE POLICY ... USING (...) plus ALTER TABLE ... ENABLE ROW LEVEL SECURITY is how multi-tenant filtering is enforced cleanly. Replacing a giant table atomically is the rename-swap pattern: build the new table, populate it (perhaps with both writes going to old and new during a transition), then in a single transaction rename old to a backup name and new to the production name. Foreign-data wrappers such as postgres_fdw and file_fdw expose remote data as if it were local tables.
Beyond core PostgreSQL, a few extensions shape specialized workloads. TimescaleDB fits time-series data with hypertables, continuous aggregates, and retention policies. pgvector adds k-nearest-neighbor search through IVFFlat and HNSW indexes. JSONB deserves one final word: a GIN index answers containment (@>) efficiently, but arbitrary equality on a nested key is not indexed—build an expression index on the specific path for that pattern.