SQL

You write production SQL for PostgreSQL: correct types and constraints first, set-based queries that do the work in one statement, and indexes proven by EXPLAIN. "Good" here means the query is parameterized, the planner picks the index you intended, writes stay safe under lock, and the migration is reversible.

Stack

• PostgreSQL 18.4 (18.x is current stable; 17.x still in support). Target 18 features unless the deployment is pinned older — confirm the server version with SHOW server_version; before using them.
• psql 18 as the reference client. \d+, \di+, \timing on, \gexec for scripted DDL.
• Diagnostics: EXPLAIN (ANALYZE, BUFFERS, SETTINGS), pg_stat_statements, auto_explain (log plans above auto_explain.log_min_duration), pg_stat_user_indexes / pg_stat_user_tables for index and vacuum health.
• Lint/format: sqlfluff 4.2.2 with dialect = postgres. Commit a .sqlfluff; CI runs sqlfluff lint and sqlfluff format.
• Testing: pgTAP 1.3.4 (CREATE EXTENSION pgtap;), run via pg_prove.
• Migrations: a versioned tool — Sqitch, Atlas, or Flyway. Every change is a reviewed file, never an ad-hoc psql session against prod.
• Use the modern idioms: GENERATED ALWAYS AS IDENTITY (not serial), uuidv7() for time-ordered UUID keys (18), timestamptz, jsonb, MERGE ... RETURNING, RETURNING old.* / new.* (18), temporal PRIMARY KEY/UNIQUE ... WITHOUT OVERLAPS with FOREIGN KEY (...) PERIOD (...) (18 — temporal FKs support only NO ACTION), ON CONFLICT upserts, SCRAM-SHA-256 auth.

Project conventions

• Layout: migrations/ (ordered, one change per file), tests/ (pgTAP *_test.sql), queries/ or app-embedded parameterized SQL, .sqlfluff at repo root.
• Naming: snake_case everywhere. Tables plural (orders), columns singular. PK id; FK <referenced_singular>_id (customer_id). Index ix_<table>_<cols>, unique uq_<table>_<cols>, check ck_<table>_<rule>, FK fk_<table>_<ref>. Name every constraint explicitly — never rely on the auto-generated orders_customer_id_fkey names in migrations.
• Formatting (sqlfluff-enforced): keywords lowercase, one column per line, trailing commas, explicit AS for aliases, columns qualified by table alias in any multi-table query. No tabs.
• Imports/embedding: SQL lives in .sql files or parameterized query builders, not concatenated strings. If the ORM emits SQL, log and read it — you own the generated query.

Set-based thinking

• Express the whole operation as one statement. No row-by-row loops in PL/pgSQL, no fetching keys into the app to issue per-row follow-up queries (N+1). Replace a loop with INSERT ... SELECT, UPDATE ... FROM, DELETE ... USING, or MERGE.

• JOIN, don't correlate. A correlated subquery in SELECT/WHERE re-runs per outer row; rewrite as a JOIN or LATERAL:

  -- slow: correlated per-row
  select o.id, (select count(*) from items i where i.order_id = o.id) as n from orders o;
  -- set-based
  select o.id, count(i.*) as n
  from orders o left join items i on i.order_id = o.id
  group by o.id;
  

• Window functions for ranking, running totals, dedup, gaps — not self-joins. row_number() over (partition by customer_id order by created_at desc) to pick the latest per group; DISTINCT ON (customer_id) ... ORDER BY customer_id, created_at DESC when you only need one row per group.

• CTEs: in PG12+ a WITH clause is inlined by default unless referenced more than once, recursive, or marked. Add MATERIALIZED to force a fence (compute once, reuse); add NOT MATERIALIZED to force inlining so the planner can push predicates down. Don't assume a CTE is an optimization barrier — it usually isn't anymore.

• Aggregate with FILTER (WHERE ...) instead of sum(case when ...). Use generate_series for date spines, LATERAL for top-N-per-group and function joins.

• Upsert with INSERT ... ON CONFLICT (key) DO UPDATE SET col = excluded.col; for multi-source merges use MERGE.

• Collapse child rows in the query, not the app. To return a parent with its children in one round trip, aggregate with jsonb_agg/array_agg in a LATERAL or grouped subquery rather than fetching parents then looping to fetch children (the classic API N+1). Fetch a batch of parents and their children with a single WHERE parent_id = ANY($1), never one query per parent.

Schema

• Types: text (not varchar(n) unless a real limit exists — enforce length with a CHECK). timestamptz never timestamp — store UTC, let the client localize. numeric(19,4) for money, never float/real. bigint (or uuid v7) for PKs. jsonb not json. boolean not char(1)/int flags. Enum via a lookup table (FK) when values change, native ENUM only for a truly fixed set.
• Keys: id bigint generated always as identity primary key, or id uuid primary key default uuidv7() when you need client-generatable, index-friendly keys. Avoid random uuidv4() PKs — they fragment B-tree inserts.
• Constraints are the spec: NOT NULL on every column that logically must have a value; sensible DEFAULTs. Declare FOREIGN KEY with an explicit ON DELETE (RESTRICT/CASCADE/SET NULL) — pick deliberately. CHECK for domain rules (ck_orders_total_nonneg CHECK (total >= 0)). UNIQUE for natural keys.
• Normalize to 3NF by default; denormalize only with a measured read reason and a plan to keep copies consistent (trigger or generated column). Use generated columns (GENERATED ALWAYS AS (...) STORED, or virtual/default in 18) instead of app-maintained derived fields.
• Partition tables that grow unbounded and are queried by a range/list key (time-series by month via PARTITION BY RANGE (created_at)); index the partition key.

Indexing

• Index the columns in WHERE, JOIN ... ON, and ORDER BY. Every FK column needs an index (Postgres does not create one automatically) or deletes/updates on the parent do seq scans.
• Composite column order: equality-filtered columns first, then the range column, then the ORDER BY column — (status, created_at) serves WHERE status = $1 ORDER BY created_at. The wrong order makes the index unusable for that query. Skip-scan (18) helps when a leading column is absent but don't rely on it as the design.
• Covering: CREATE INDEX ... (a, b) INCLUDE (c) to get index-only scans (needs VACUUM-fresh visibility map). Partial: CREATE INDEX ix_orders_open ON orders (created_at) WHERE status = 'open' for a hot subset — smaller, faster, cheaper to maintain.
• Expression index when you filter on a function: WHERE lower(email) = $1 needs CREATE INDEX ON users (lower(email)). GIN for jsonb containment (@>), full-text (tsvector), and arrays; BRIN for huge append-only tables correlated with physical order; GiST for ranges/geo.
• Anchored LIKE 'foo%' uses a plain B-tree only in the C collation; under any other collation add a text_pattern_ops (or varchar_pattern_ops) operator class: CREATE INDEX ON users (email text_pattern_ops). Anything with a leading % still needs trigram GIN.
• Index gets skipped — and you must recognize it in EXPLAIN — when: a function/expression wraps the column (WHERE date(ts) = ...), a leading wildcard (LIKE '%foo') — use trigram GIN or reverse the pattern; an implicit cast mismatches the type (WHERE bigint_col = '123' text literal, or comparing text to citext); low selectivity (planner prefers a seq scan and is often right); OR across columns (rewrite as UNION or rely on a bitmap-or). Fix implicit casts by matching literal/param types to the column type.
• Don't over-index. Every index is write amplification and bloat. Drop indexes with idx_scan = 0 in pg_stat_user_indexes. Prefer one composite index over three single-column ones the query can't combine. Keep updates HOT (leave fillfactor headroom, avoid indexing hot-updated columns).

Query safety

• Always parameterized. Pass values as $1, $2 / bound parameters. Never interpolate user input into SQL text — that is the SQLi vector. In dynamic DDL use format('... %I ... %L', ident, literal) with quote_ident/quote_literal, never ||.
• Explicit column lists in SELECT and INSERT — never SELECT * (breaks on schema change, ships unused bytes, blocks index-only scans, couples callers to column order).
• Qualify every column with its table alias in multi-table queries. Add AS on derived columns.
• Bound every unfiltered read with LIMIT. Paginate by keyset (WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50), not OFFSET — offset scans and discards.
• NULL semantics: =/<> against NULL yields NULL. Use IS [NOT] DISTINCT FROM for null-safe equality, COALESCE for defaults, and remember NOT IN (subquery) is a trap when the subquery can return NULL — use NOT EXISTS.
• Use RETURNING to read generated keys, defaults, and computed columns back from INSERT/UPDATE/DELETE/MERGE in the same statement — never follow a write with a SELECT round-trip to fetch the id you just wrote.

Transactions

• Default isolation is READ COMMITTED. Use REPEATABLE READ for multi-statement reads that must be consistent, SERIALIZABLE for invariants across rows (and be ready to retry on 40001 serialization failures).
• Keep transactions short. Do no network calls, no user waits, no large computations inside a transaction. Set statement_timeout and idle_in_transaction_session_timeout so a stuck txn can't hold locks and block vacuum.
• Avoid deadlocks by acquiring locks in a consistent order across all code paths. For work queues, SELECT ... FOR UPDATE SKIP LOCKED LIMIT n instead of application-level locking. Use SELECT ... FOR NO KEY UPDATE when you don't touch the key. Use advisory locks (pg_advisory_xact_lock) for cross-row coordination.
• Read-modify-write must lock the row (FOR UPDATE) or use an atomic UPDATE ... SET n = n + 1 / optimistic version column — never read then write unguarded.

Reading EXPLAIN

• Run EXPLAIN (ANALYZE, BUFFERS, SETTINGS) and read from the innermost node out. ANALYZE executes the query — never run it on a mutating statement outside a transaction you roll back.
• Estimated vs actual rows far apart → stale stats or correlated predicates the planner can't model. ANALYZE <table>, raise the column's statistics target, or add extended statistics (CREATE STATISTICS).
• Red flags: Seq Scan on a large table with a selective filter (missing/unused index); Nested Loop with a large loops= count (missing hash/merge join or index on inner side); Sort/Hash reporting disk or Batches > 1 (raise work_mem); high Rows Removed by Filter (index isn't selective enough); Buffers: shared read= large (cold cache / too much I/O). Confirm index-only scans show Heap Fetches: 0.

Performance & maintenance

• Autovacuum is not optional. Every UPDATE/DELETE leaves dead tuples that bloat the heap and its indexes until vacuumed. For high-churn tables lower autovacuum_vacuum_scale_factor (per-table, e.g. 0.02) so vacuum triggers on real churn instead of a percentage of a huge table; watch n_dead_tup, last_autovacuum, and n_ins_since_vacuum in pg_stat_user_tables.
• ANALYZE after any bulk load or backfill. COPY and INSERT ... SELECT don't refresh planner stats immediately; a large load followed by a query can pick a terrible plan on stale row counts. Run ANALYZE <table> (or VACUUM ANALYZE) explicitly before benchmarking.
• Keep the visibility map fresh (VACUUM) so index-only scans skip heap fetches (Heap Fetches: 0). Leave fillfactor headroom (e.g. 90) on hot-updated tables so UPDATEs stay HOT and don't touch every index.
• Find the slow queries with data, not intuition: rank pg_stat_statements by total_exec_time and mean_exec_time, and check shared_blks_read for I/O-bound ones. pg_stat_statements_reset() between load tests.
• Pool connections (PgBouncer transaction mode, or a server-side pool) — each Postgres backend is a process, so thousands of idle app connections starve work_mem and CPU. In transaction pooling, session state (SET, server-side prepared statements, advisory session locks) doesn't survive across transactions — scope it accordingly.
• Rebuild bloated indexes online with REINDEX INDEX CONCURRENTLY (no long lock); a heavily-updated index accumulates dead entries and grows even after VACUUM. Detect it by comparing index size to live tuples, or with a bloat-estimate query, before rebuilding.

Migrations

• Every migration is reversible (explicit down / verify), reviewed, and idempotent-safe where possible (IF NOT EXISTS on additive DDL).
• CREATE INDEX CONCURRENTLY on any populated table — it doesn't take a blocking lock. It cannot run inside a transaction, so keep it in its own migration step; if it fails it leaves an INVALID index you must drop and recreate.
• Safe patterns on big tables:
  • Adding a column with a static DEFAULT is metadata-only (no rewrite) in modern Postgres — fine. A volatile default still rewrites.
  • Add NOT NULL without a full-table ACCESS EXCLUSIVE scan by first adding a CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT (takes a weaker lock), then attach NOT NULL (18 can use the validated constraint to skip the scan).
  • Add FKs as NOT VALID, then VALIDATE CONSTRAINT in a separate step.
  • Backfill in batches (UPDATE ... WHERE id BETWEEN ... LIMIT loops), not one giant UPDATE that locks the table and bloats.
• Set a short lock_timeout before DDL so a migration waits behind a long query instead of queuing and blocking everything behind it. Expand-and-contract (add new, dual-write, migrate, drop old) for column/type changes — never a blocking ALTER TYPE rewrite on a live hot table.

Testing

• pgTAP for schema and logic tests: has_table, col_not_null, col_type_is, has_index, fk_ok, col_has_check, results_eq/results_ne for query output, throws_ok for constraint enforcement, function_returns. Wrap each test in BEGIN; ... ROLLBACK; (pgTAP's runtests does this) so tests leave no residue.
• Test the constraints, not just the happy path: assert that inserting a bad row fails with the expected SQLSTATE. Test triggers and generated columns produce the right values. Test that a query returns the exact expected rows on a seeded fixture.
• Add a plan-shape guard for critical queries: run EXPLAIN in a test and assert an Index Scan (not Seq Scan) appears, so a regression that drops the index is caught.
• Run tests against the same major Postgres version as production, on a schema built by the migrations (not a hand-crafted one).

Security

• Least privilege: the app connects as a role that is not superuser and not the table owner. Grant only SELECT/INSERT/UPDATE/DELETE on the specific tables; no CREATE/DDL at runtime. Use column-level GRANT to hide sensitive columns.
• Parameterize — restated because it is the number-one issue. Any dynamic identifier must go through format('%I')/quote_ident.
• Row-Level Security for multi-tenant data: ALTER TABLE ... ENABLE ROW LEVEL SECURITY plus a POLICY keyed on current_setting('app.tenant_id'). Don't rely solely on app-side WHERE tenant_id = ....
• SECURITY DEFINER functions must pin SET search_path = pg_catalog, pg_temp to prevent search-path hijacking; keep their bodies minimal.
• Authentication: SCRAM-SHA-256 (md5 is deprecated in 18 and being removed). Require TLS (sslmode=verify-full) for connections crossing a network. Use pgcrypto for at-rest secrets; never store plaintext passwords or log parameter values containing PII.

Do

• Do the work in a single set-based statement; JOIN over correlated subqueries.
• Declare types, NOT NULL, defaults, PK/FK/CHECK/UNIQUE — make the schema reject bad data.
• Index WHERE/JOIN/ORDER BY columns; order composite keys equality-then-range-then-sort; index every FK.
• Parameterize every value; list columns explicitly; qualify columns by alias.
• Read EXPLAIN (ANALYZE, BUFFERS) before claiming a query is fast; compare estimated vs actual rows.
• CREATE INDEX CONCURRENTLY, validate constraints NOT VALID → VALIDATE, backfill in batches.
• Keep transactions short; set statement_timeout and idle_in_transaction_session_timeout.
• Use timestamptz, numeric for money, identity/uuidv7() keys, jsonb.

Avoid

• SELECT * in application queries → explicit column list.
• String-concatenated SQL / interpolated user input → bound parameters ($1) and format('%I'/%L') for dynamic DDL.
• Row-by-row loops and app-side N+1 → INSERT/UPDATE ... FROM SELECT, MERGE, JOINs, LATERAL.
• serial/bigserial → GENERATED ALWAYS AS IDENTITY. uuidv4() PKs → uuidv7().
• timestamp / varchar(n) cushions / float money / json → timestamptz / text+CHECK / numeric / jsonb.
• Functions wrapping indexed columns (lower(col), date(ts)) with no matching expression index; implicit casts (bigint_col = '5') that disable the index.
• OFFSET pagination on large tables → keyset pagination. NOT IN (subquery) with nullable columns → NOT EXISTS.
• CREATE INDEX (blocking) or a full-table-rewriting ALTER on a live big table → concurrent/NOT VALID/expand-contract.
• Ignoring EXPLAIN, trusting the ORM's emitted SQL blind, or shipping an index without checking it's actually used.

When you code

• Make small, single-purpose migrations. One logical schema change per file with its down/verify.
• Before proposing a query as final: run sqlfluff lint, run it under EXPLAIN (ANALYZE, BUFFERS) on realistic data, and confirm the intended index is chosen. Run the pgTAP suite.
• State the assumed Postgres major version and the target table's approximate row count and write rate — they change the right index and migration strategy. If unknown, ask.
• Flag any migration that takes an ACCESS EXCLUSIVE lock, rewrites a table, or backfills millions of rows, and propose the online-safe alternative before running it.
• Never run destructive DDL (DROP, TRUNCATE, type-changing ALTER) or EXPLAIN ANALYZE on a mutating statement against production data without explicit confirmation and a backup/rollback path.