PostgreSQL Style Guide

This book publishes the PostgreSQL style guide skill as browsable documentation.

The guide gives AI coding agents concrete, opinionated defaults for designing schemas, writing queries, authoring migrations, and configuring database access. It is not a SQL tutorial.

Start Here

• Skill router - choose the right guideline or workflow for a task.
• Guidelines index - find focused PostgreSQL style policy pages.
• Workflows - follow procedures for larger database tasks.

The book includes packaged skill content only: the router, guideline pages, and workflow pages. Drafting notes, decision records, and source research remain repository-maintenance material.

PostgreSQL Style Guide

Apply the loaded policy pages directly.

Routing

Workflows

Policy Fast Paths

Core Behavior

• Workflows own multi-step procedures and route to their policy pages.
• Fast paths load only the directly linked owner pages.
• Use the guideline index only when no workflow or fast path matches.
• Select conditional pages using task descriptions in this router, the guideline index, or workflow routing.
• After loading a conditional page, apply it only when its Activation section matches.
• Treat live-database migrations as stricter than greenfield DDL.
• Prefer concrete PostgreSQL guidance over SQL tutorials.
• Ask one focused question only when required project context is missing.

Guidelines

Use page descriptions to select pages before loading them. After loading a conditional page, apply it only when its Activation section matches.

Foundations

• House style and Postgres philosophy - load for overall posture: integrity in the database, logic in the application, normalized-first modeling, and version assumptions.
• Identifier casing and quoting - load when creating any named object; covers snake_case, quoting bans, reserved words, and length limits.
• Object naming - load when naming tables, columns, constraints, indexes, functions, triggers, or views.
• Schema layout and search_path - load when choosing schemas, qualifying references, or handling search_path.
• SQL formatting and comments - load for keyword casing, commas, indentation, aliases, and COMMENT ON.

Schema Design and Data Types

• Primary keys and row identity - load when choosing PK types, uuidv7() vs identity columns, or natural vs surrogate keys.
• Foreign keys and relationships - load when adding references, choosing ON DELETE actions, or modeling hierarchies.
• Scalar types - load when choosing string, numeric, integer, or boolean column types.
• Temporal data and time zones - load when adding timestamps, dates, intervals, or validity periods.
• JSONB, arrays, and normalization - load when deciding between relational columns, jsonb, and arrays.
• Enums, domains, and lookup tables - load when modeling status fields, categories, or reusable scalar validation.
• Standard columns and row lifecycle - load for created_at/updated_at, defaults, generated columns, and soft vs hard delete.

Constraints and Indexes

• Constraints and NULL semantics - load for NOT NULL policy, CHECK constraints, uniqueness, and temporal constraints.
• Index basics - load when adding ordinary indexes, indexing FKs, or choosing unique constraint vs unique index.
• Advanced indexes - load for partial, expression, multicolumn, covering, or GIN indexes.

Query Style

• SELECT structure and join style - load when writing joins, qualifying columns, or deciding on SELECT *.
• Subqueries, EXISTS, and LATERAL - load for semi-joins, NOT IN traps, ANY, and LATERAL.
• CTEs and query decomposition - load when structuring nontrivial queries or considering materialization.
• Aggregation, window functions, and pagination - load for GROUP BY, ranking, ordering, and pagination.
• DML, upserts, and RETURNING - load when writing INSERT/UPDATE/DELETE, upserts, MERGE, or RETURNING.

Database Logic

• Functions and procedures - load when writing database functions; covers language choice, volatility, and security modes.
• Views and materialized views - load when adding views or materialized views and their refresh strategies.
• Triggers - load when a trigger is proposed; covers the minimal-trigger doctrine and the narrow valid cases.

Security

• Roles, privileges, and row-level security - load when configuring roles, grants, default privileges, or RLS.

House Style and Postgres Philosophy

Rule

Write readable, explicit SQL for PostgreSQL 18+: the database owns data integrity through constraints, the application owns business logic, and data is modeled relationally first.

Why

These defaults keep schemas, queries, and migrations consistent across write paths.

Do

• Assume PostgreSQL 18+ for new work and follow each page’s fallback for older targets.
• Use constraints for integrity and keep workflow decisions in application code; database logic is limited to the cases sanctioned by functions and triggers.
• Model core data relationally; use JSONB and arrays only for their documented cases.
• Prefer decomposed queries and route live schema changes through the safe schema migration workflow.

Avoid

• Do not treat this overview as a substitute for the relevant owner page.
• Do not use a version-gated feature before confirming the target version.

Example

-- Integrity belongs in the database.
ALTER TABLE payments
  ADD CONSTRAINT payments_amount_check CHECK (amount > 0);

-- Refund eligibility remains application logic.

Exceptions

• Older target databases: keep these conventions but substitute the fallbacks named in each page’s Version Notes.
• Analytics and reporting contexts may relax query-shape rules where a page explicitly says so; schema rules still apply.

Identifier Casing and Quoting

Rule

Name every database object in lowercase snake_case and never quote identifiers.

Why

PostgreSQL folds unquoted identifiers to lowercase; quoting makes a name case-sensitive forever, forcing every future query, tool, and migration to reproduce the exact casing. Unquoted snake_case names work everywhere without ceremony.

Do

• Use lowercase letters, digits, and underscores: order_items, user_id, paid_at.
• Start every identifier with a letter.
• Keep identifiers under 63 bytes; PostgreSQL silently truncates longer names.
• Avoid reserved and near-reserved words as identifiers: user, order, group, table, check, default, limit, offset, primary, references, where.
• Spell words out; abbreviate only universally understood terms (id, url, ip).
• Keep acronyms lowercase like any other word: api_tokens, http_status, url_path.

Avoid

• Do not create quoted identifiers: "userAccounts", "Order", "created At".
• Do not use camelCase or PascalCase even unquoted; it folds to an unreadable lowercase run (useraccounts).
• Do not end identifiers with an underscore or use consecutive underscores.
• Do not invent project-specific abbreviations (usr_acct_bal) to save characters.
• Do not work around a reserved word by quoting it; pick a different name.

Example

-- Good: unquoted snake_case, no reserved words.
CREATE TABLE api_tokens (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  user_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  token_digest text NOT NULL,
  expires_at timestamptz NOT NULL
);

-- Bad: quoted mixed case; every consumer must now quote it forever.
-- CREATE TABLE "ApiTokens" ("userId" uuid, "tokenDigest" text);

Exceptions

• Interoperating with legacy objects that were created quoted: quote exactly those existing names, and do not create new ones.
• Generated interop columns matching an external system’s casing may be quoted when a translation layer is not practical; isolate them and comment why.

Object Naming

Rule

Name tables as plural nouns, columns by fixed conventions (id, <referenced_singular>_id, *_at, is_*), and give constraints and indexes canonical PostgreSQL suffix names.

Why

Systematic names make agent-generated DDL predictable: any object’s name can be derived from what it is, and migrations that reference constraints or indexes behave identically in every environment.

Do

• Name tables as plural snake_case nouns: users, orders, order_items.
• Name junction tables for the relationship when a natural name exists (memberships, enrollments); otherwise join the two table names in alphabetical order and pluralize the last word (categories_products).
• Name the primary key column id.
• Name foreign key columns <referenced_singular>_id: user_id references users; a second reference to the same table gets a role prefix (approver_id, also referencing users).
• Suffix timestamps with _at (created_at, confirmed_at) and dates with _on or a plain noun (due_on, birth_date).
• Prefix booleans with is_ or has_ (is_active, has_signature).
• Prefer descriptive column names over generic ones: year_founded, not year.
• Use the PostgreSQL default suffix pattern for constraints and indexes:
  • Primary key: {table}_pkey
  • Foreign key: {table}_{column}_fkey
  • Unique constraint or unique index: {table}_{columns}_key
  • Ordinary index: {table}_{columns}_idx
  • Check constraint: {table}_{column}_check (or {table}_{rule}_check for multi-column rules)
• Let PostgreSQL supply a constraint name only when it will generate the canonical name above unambiguously; otherwise use CONSTRAINT name. Index names are always explicit.
• Name triggers {table}_{action}_trigger (users_set_updated_at_trigger) and their functions after the behavior (set_updated_at).
• Name views as plural nouns describing the result rows (overdue_invoices); name materialized views the same way.

Avoid

• Do not accept a generated constraint name with a numeric disambiguator or noncanonical suffix; name that constraint explicitly.
• Do not use tbl_, col_, fk_, idx_ prefixes or other Hungarian notation.
• Do not name a junction table a_to_b or a_b_join.
• Do not encode types into names (name_text, count_int).
• Do not name columns after their table (user_name inside users); the qualification is the table’s job.

Example

CREATE TABLE order_items (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  order_id uuid NOT NULL,
  product_id uuid NOT NULL,
  quantity bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT order_items_order_id_fkey
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
  CONSTRAINT order_items_product_id_fkey
    FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT,
  CONSTRAINT order_items_quantity_check CHECK (quantity > 0),
  CONSTRAINT order_items_order_id_product_id_key UNIQUE (order_id, product_id)
);

CREATE INDEX order_items_product_id_idx ON order_items (product_id);

Exceptions

• Names that would exceed 63 bytes: shorten the column list portion while keeping the table name and suffix intact.
• Legacy tables with established singular names: match the surrounding convention within that table rather than mixing styles.
• Schemas managed by an ORM (Rails, Django): follow its surrounding naming convention, overriding only nondeterministic or truncated names.

Schema Layout and search_path

Rule

Keep a single-application database’s objects in public with CREATE revoked from PUBLIC, and pin search_path in every function body instead of trusting the caller’s path.

Why

One schema keeps DDL, tooling, and queries simple; the real risks are unprivileged users creating objects and functions resolving names through a caller-controlled search_path, both of which are closed by this rule.

Do

• Put application objects in public for single-application databases.
• Revoke schema creation from the world once per database: REVOKE CREATE ON SCHEMA public FROM PUBLIC; (default since PG15; keep it explicit in setup).
• Let application SQL rely on the default search_path in single-schema databases; do not scatter public. qualifiers through queries.
• Pin the path in every function definition: SET search_path = public, pg_temp.
• Schema-qualify object references inside SECURITY DEFINER functions even with a pinned path; they execute with the owner’s privileges.
• Split into domain schemas only when one database genuinely hosts multiple domains; then qualify all cross-schema references explicitly.

Avoid

• Do not create a parallel app schema for a single application; it adds path configuration everywhere for no isolation gain.
• Do not rely on search_path inside function bodies; the caller controls it unless pinned.
• Do not put application objects in extension-managed or catalog schemas.
• Do not set a custom search_path per role or per connection as a naming mechanism.

Example

-- One-time database setup:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Every function definition pins its path; name resolution cannot be
-- hijacked. Body elided; full definitions live with their owner pages
-- (set_updated_at in triggers, normalize_email in functions).
CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql
SET search_path = public, pg_temp
AS $$ ... $$;

Exceptions

• Multi-domain databases: use one schema per domain (billing, identity), qualify cross-schema references, and grant per schema.
• Extensions that install into their own schema: leave them there; do not relocate their objects into public.

SQL Formatting and Comments

Rule

Write UPPERCASE keywords with lowercase identifiers and built-in functions, trailing commas, 2-space indentation, one clause per line in nontrivial statements, and meaningful table aliases.

Why

A single mechanical layout makes agent-generated SQL diff-stable and reviewable. Keywords stand out without highlighting; simple indentation survives edits without re-alignment.

Do

• UPPERCASE SQL keywords (SELECT, FROM, WHERE, JOIN, ON, GROUP BY); lowercase identifiers and built-in functions (date_trunc, count, coalesce).
• Start each clause keyword on its own line in nontrivial statements; indent continuation lines 2 spaces.
• Use trailing commas; one select-list item per line once the list does not fit on one line.
• Wrap lines around 100 characters.
• Alias tables with a short name derived from the table: users as u, order_items as oi; single letters are fine with one or two tables, banned in larger queries.
• Use AS for column aliases: SUM(oi.quantity) AS total_quantity.
• Comment with -- line comments; use /* */ only for multi-line file headers.
• Comment intent and non-obvious choices, not mechanics; document schema meaning in migrations and application code, not COMMENT ON.
• Keep one-line statements on one line: SELECT count(*) FROM users needs no layout.

Avoid

• Do not use river alignment or column alignment that must be rebuilt on every edit.
• Do not use leading commas.
• Do not use COMMENT ON; catalog comments are unmaintained documentation in this house style.
• Do not commit commented-out SQL.
• Do not uppercase identifiers or built-in function names.
• Do not alias unless a query references more than one table or the alias genuinely shortens a long name.

Example

SELECT
  usr.id,
  usr.email,
  SUM(oi.quantity * oi.unit_price) AS lifetime_value
FROM users usr
JOIN orders ord ON ord.user_id = usr.id
JOIN order_items oi ON oi.order_id = ord.id
WHERE ord.state = 'paid'
  AND ord.created_at >= date_trunc('year', now())
GROUP BY usr.id, usr.email
ORDER BY lifetime_value DESC, usr.id
LIMIT 20;

Exceptions

• Generated or vendored SQL keeps its generator’s formatting; do not hand-reformat it.
• psql meta-commands and ad hoc exploratory queries are exempt from layout rules.

Primary Keys and Row Identity

Rule

Give every table id uuid DEFAULT uuidv7() PRIMARY KEY; enforce natural keys as unique constraints, never as primary keys.

Why

UUIDv7 values are time-ordered, so they index like sequential keys while staying globally unique, non-enumerable, and safe to expose in URLs and APIs. Surrogate keys keep identity stable when business attributes change.

Do

• Declare id uuid DEFAULT uuidv7() PRIMARY KEY on every new table.
• Expose the id directly in APIs and URLs by default; no second public identifier is needed.
• When the product requires prefixed identifiers (usr_V1StGXR8Z5), add public_id text NOT NULL with a unique constraint: the type prefix plus an independently generated random suffix. The uuid id stays internal for every FK; APIs and URLs then expose only public_id.
• Enforce natural keys (email, SKU, external reference) with unique constraints; see index basics for constraint vs index.
• Use bigint GENERATED ALWAYS AS IDENTITY instead only when key compactness or extreme insert concurrency demonstrably matters, and note why in the migration.
• Give junction tables their own id plus a unique constraint over the pair of foreign keys.

Avoid

• Do not use serial or bigserial; identity columns replaced them.
• Do not use random v4 UUIDs (gen_random_uuid()) as primary keys; random inserts fragment the B-tree.
• Do not use natural keys as primary keys, even “stable” ones; emails change and codes get recycled, and the change ripples through every foreign key.
• Do not use composite primary keys on business tables; use id plus a unique constraint.
• Do not mix key strategies within a schema without a documented reason.
• Do not derive public_id from the PK bits (TypeID-style encoding) or the PK from public_id; the two are independent values joined by an indexed lookup.

Example

CREATE TABLE products (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  sku text NOT NULL,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT products_sku_key UNIQUE (sku)
);

Version Notes

• uuidv7() requires PostgreSQL 18. On older targets, generate UUIDv7 values in the application, or fall back to bigint GENERATED ALWAYS AS IDENTITY (PG10+) when application-side generation is not practical.

Exceptions

• High-write-concurrency tables can contend on the rightmost index leaf with time-ordered keys; if measured, bigint identity or fillfactor tuning is the escape hatch.
• Static lookup tables (see enums, domains, and lookup tables) still get id keys; their natural codes stay unique-constrained columns.

Foreign Keys and Relationships

Rule

Declare a foreign key for every reference between durable tables, with ON DELETE RESTRICT unless the child is a true composition owned by the parent.

Why

Foreign keys make orphaned references impossible regardless of write path. RESTRICT makes deletes of referenced rows fail loudly instead of silently fanning out, keeping deletion behavior explicit in application code.

Do

• Add a foreign key constraint on every <referenced_singular>_id column between durable tables.
• Default to ON DELETE RESTRICT so a delete with dependents fails until the application handles them.
• Use ON DELETE CASCADE only when child rows are meaningless without the parent (orders -> order_items).
• Use ON DELETE SET NULL rarely, only when the reference is genuinely optional, with a comment explaining why.
• Model self-referential trees with a nullable parent_id FK to the same table.
• Index every FK column; the rule and rationale live in index basics.
• Name FK constraints per object naming.

Avoid

• Do not leave reference columns unconstrained because “the app guarantees it”.
• Do not use ON DELETE CASCADE as a convenience for cleanup across aggregate boundaries; a cascade through business entities is silent data loss.
• Do not point foreign keys at non-unique columns.
• Do not create circular FK dependencies between tables; restructure or make one side deferrable with a documented reason.

Example

CREATE TABLE orders (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  user_id uuid NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  -- Users own their orders but orders outlive user edits: RESTRICT.
  CONSTRAINT orders_user_id_fkey
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
);

CREATE TABLE order_items (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  order_id uuid NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  -- Items are part of the order: composition, so CASCADE.
  CONSTRAINT order_items_order_id_fkey
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE
);

CREATE INDEX orders_user_id_idx ON orders (user_id);
CREATE INDEX order_items_order_id_idx ON order_items (order_id);

Exceptions

• High-volume append-only event/log tables may hold soft references without FK constraints, with a documented reason; retention jobs should not block business-row deletes.
• Polymorphic actor/subject references use paired type-and-ID columns (actor_type text plus actor_id uuid) without FK constraints; constrain the type column with a named CHECK and document the reason.
• Cross-database or cross-service references cannot be FKs; name the column normally and validate in the application.

Scalar Types

Rule

Use text for strings, numeric for money and exact quantities, bigint for integers, and boolean NOT NULL for flags; char(n), money, and floating-point money are banned.

Why

PostgreSQL’s text and varchar perform identically, so length limits belong in constraints that can change cheaply. Exact and growing values need types that never overflow or round.

Do

• Use text for all strings; enforce business length limits with a named CHECK on char_length(...).
• Use numeric for money and exact decimal quantities; store a currency text column alongside amounts when multi-currency.
• Use bigint for counters and any integer that can grow; integer/smallint only for values with a known small bound (ages, positions, percentages).
• Use boolean NOT NULL with an explicit DEFAULT for flags; a nullable boolean is a three-state value in disguise.
• Use double precision only for genuinely approximate measurements (coordinates, scores).
• Use bytea for binary payloads; prefer external object storage with a text reference for large blobs.

Avoid

• Do not use varchar(n); changing the limit is a type change, while a CHECK swaps under light locks.
• Do not use char(n); it space-pads values and surprises comparisons.
• Do not use the money type; locale-dependent formatting and weak arithmetic.
• Do not use real/double precision for money or anything summed for business purposes.
• Do not store numbers or booleans as strings.
• Do not default to integer because the ORM does; overflowing a hot column in production is a notorious outage class.

Example

CREATE TABLE invoices (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  reference text NOT NULL,
  amount numeric NOT NULL,
  currency text NOT NULL DEFAULT 'USD',
  attempt_count bigint NOT NULL DEFAULT 0,
  is_paid boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT invoices_reference_check CHECK (char_length(reference) <= 40),
  CONSTRAINT invoices_amount_check CHECK (amount > 0)
);

Exceptions

• Interop schemas mirroring an external system may keep that system’s declared types, including varchar(n), to match the contract; comment the source.
• Timestamps and dates are owned by temporal data and time zones; identifiers by primary keys and row identity.

Temporal Data and Time Zones

Rule

Use timestamptz for all timestamps and date for calendar dates; model validity periods as range types with half-open [) bounds and enforce non-overlap with PG18 temporal constraints.

Why

timestamptz stores an unambiguous instant and renders in the session’s zone; plain timestamp is a wall-clock reading with no zone, a standing invitation to double-conversion bugs. Ranges make period logic one value with real operators instead of hand-rolled column-pair comparisons.

Do

• Use timestamptz for every point-in-time column (created_at, paid_at, expires_at).
• Use date for calendar concepts with no time component (due_on, birth_date).
• Use interval for durations only when the duration itself is the data; otherwise store the two instants.
• Use tstzrange or daterange with [) bounds for validity periods instead of start/end column pairs.
• Enforce non-overlap declaratively: UNIQUE (key, period WITHOUT OVERLAPS) on the range column (PG18).
• Install btree_gist (CREATE EXTENSION btree_gist;, in its own migration) before the first constraint that mixes a scalar key with a range: GiST has no default operator class for scalar types like uuid, so both WITHOUT OVERLAPS constraints and EXCLUDE USING gist fail without it.
• Pass ISO 8601 strings when writing timestamps from SQL ('2026-07-03T14:00:00Z').
• Compare and bucket in SQL with date_trunc and range operators (&&, @>), not string manipulation.

Avoid

• Do not use timestamp without time zone for instants.
• Do not store epoch seconds in numeric columns or timestamps as text.
• Do not model periods as starts_at/ends_at pairs; nothing stops starts_at > ends_at, and overlap checks become bug-prone inequalities.
• Do not use inclusive [] range bounds for continuous time; adjacent periods will overlap at the boundary.
• Do not enforce overlap rules with triggers or application checks when a temporal constraint can state them.

Example

-- Requires btree_gist for the scalar room_id key part.
CREATE TABLE room_bookings (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  room_id uuid NOT NULL REFERENCES rooms (id) ON DELETE RESTRICT,
  booked_during tstzrange NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  -- No two bookings for the same room may overlap (PG18):
  CONSTRAINT room_bookings_room_id_booked_during_key
    UNIQUE (room_id, booked_during WITHOUT OVERLAPS)
);

-- Find bookings active right now:
SELECT rb.id
FROM room_bookings rb
WHERE rb.booked_during @> now();

Version Notes

• WITHOUT OVERLAPS and temporal PERIOD foreign keys require PostgreSQL 18. On older targets, use an exclusion constraint: EXCLUDE USING gist (room_id WITH =, booked_during WITH &&). The btree_gist requirement applies to both forms.

Exceptions

• Future wall-clock events whose UTC offset may change under timezone-rule updates (appointments, scheduled local times): store the local time and the zone name (text), with a comment; convert at read time.
• Analytical rollup tables may store pre-truncated date buckets even for instant-derived data.

JSONB, Arrays, and Normalization

Rule

Model core business data as relational columns; use jsonb only for document-shaped payloads, and arrays only for flat lists of primitives.

Why

Data inside jsonb loses what the database provides: type checking, constraints, foreign keys, and planner statistics. Reserving it for genuinely document-shaped data keeps integrity where it matters and flexibility where it helps.

Do

• Model attributes that are queried, joined, constrained, or indexed as real columns.
• Use jsonb for document-shaped payloads: external API responses, webhook bodies, user-defined settings, sparse fast-evolving attribute bags. Litmus test: would storing it in object storage with a reference be acceptable? Then jsonb is fine.
• Use the hot/cold pattern for externally sourced data: promote the attributes you query into columns, keep the raw remainder in one jsonb column (payload, raw_attributes).
• Always use jsonb, never json; json stores text and lacks an equality operator.
• Use arrays only for flat lists of primitives with no FK targets and no per-element metadata (tags text[]).
• Reach for a child or junction table the moment list elements reference another table or carry attributes.
• Query jsonb and arrays with containment operators; the operator idioms and GIN indexing rules live in advanced indexes.
• Validate load-bearing jsonb structure with a named check, for example CONSTRAINT webhooks_payload_check CHECK (payload ? 'event_type').

Avoid

• Do not put core business attributes in jsonb to avoid a migration; that trades a cheap ADD COLUMN for permanent statistics blindness.
• Do not join on values inside jsonb documents.
• Do not use jsonb[]; use one jsonb column holding an array.
• Do not store arrays of IDs referencing other tables (array elements cannot have FK constraints, so orphans accumulate silently), outside the immutable-list exception below.
• Do not update single fields of large jsonb values on hot paths; every update rewrites the whole value.
• Do not mirror the same fact in both a column and a jsonb document; pick one owner.

Example

-- Hot/cold for imported data: queried fields are columns, the rest stays raw.
CREATE TABLE imported_listings (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  source text NOT NULL,
  external_id text NOT NULL,
  price numeric NOT NULL,
  city text NOT NULL,
  raw_attributes jsonb NOT NULL,
  tags text[] NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT imported_listings_source_external_id_key UNIQUE (source, external_id)
);

Exceptions

• Ingestion staging tables may be a single jsonb column plus bookkeeping fields; promotion to columns happens downstream.
• Read-model/cache tables rebuilt from canonical data may denormalize freely, including jsonb projections; they must be rebuildable, not sources of truth.
• Write-once ID arrays may reference tables that are never hard-deleted, with a documented reason at the column; without deletes, orphans cannot arise. A junction table is still required the moment membership carries authority, per-element attributes beyond order, or row-level constraints.

Enums, Domains, and Lookup Tables

Rule

Model closed value sets as text with a named CHECK constraint; promote to a lookup table when values carry attributes or the set is large; native enums only for truly static universal sets, and CREATE DOMAIN never.

Why

CHECK lists change cheaply (NOT VALID then VALIDATE) and stay visible in \d. Native enums cannot drop values without a type rebuild under an exclusive lock. Lookup tables earn their join when values are data, not just labels.

Do

• Use text plus a named CHECK ... IN (...) for app-owned state machines (state IN ('pending', 'paid', 'refunded')).
• Keep the application’s enum definition and the CHECK list in sync; changing one is a migration touching both.
• Promote to a lookup table with an FK when values carry attributes (label, ordering, activation), need i18n, are user-managed, or the set is large (US states and territories, country codes).
• Seed lookup tables in migrations; give them id keys with the natural code as a unique column, per primary keys and row identity.
• Reserve native ENUM types for truly static universal sets that will never lose a value (days of the week, compass directions).

Avoid

• Do not use CREATE DOMAIN; use plain types with per-column named CHECK constraints, even when a rule repeats across tables.
• Do not use native enums for business vocabularies; removing or renaming a value requires a table-rewriting type migration.
• Do not leave status columns as unconstrained text; bad writes from any path land silently.
• Do not create a lookup table for a three-value state machine that only the code interprets; that is a CHECK.
• Do not encode value sets in numeric codes (state smallint with meanings in the app).

Example

-- App-owned state machine: text + CHECK.
CREATE TABLE orders (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  state text NOT NULL DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT orders_state_check
    CHECK (state IN ('pending', 'paid', 'shipped', 'refunded'))
);

-- Rich/large closed set: lookup table.
CREATE TABLE us_states (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  code text NOT NULL,
  name text NOT NULL,
  is_territory boolean NOT NULL DEFAULT false,
  CONSTRAINT us_states_code_key UNIQUE (code)
);

Migration Notes

• Changing a CHECK list on a large table: the canonical name is taken until the old constraint drops, so add the new constraint NOT VALID under a temporary name ({table}_{column}_check_new), VALIDATE CONSTRAINT separately, drop the old constraint, then ALTER TABLE ... RENAME CONSTRAINT back to the canonical name; see the safe schema migration workflow.

Exceptions

• Third-party schemas or extensions that ship enums or domains: use them as provided; do not restructure external contracts.

Standard Columns and Row Lifecycle

Rule

Give every durable business table trigger-maintained created_at/updated_at columns, hard-delete rows by default, and derive computed columns with generated columns (virtual unless indexed or hot).

Why

Uniform lifecycle columns make debugging, syncing, and auditing possible everywhere. Trigger maintenance keeps updated_at honest on every write path. Hard deletes keep uniqueness, queries, and FKs simple; soft delete is real complexity that must be bought deliberately.

Do

• Add to every durable business table:
  • created_at timestamptz NOT NULL DEFAULT now()
  • updated_at timestamptz NOT NULL DEFAULT now()
• Maintain updated_at with the shared set_updated_at() trigger on every such table; the trigger pattern lives in triggers.
• Hard-delete rows with DELETE; rely on ON DELETE RESTRICT (see foreign keys and relationships) to surface dependents.
• Where the product genuinely needs recovery or audit history, use deleted_at timestamptz soft delete on that table, and then always:
  • enforce active-row uniqueness with partial indexes, per index basics;
  • define the child-row policy, because soft deletion does not activate FK delete actions;
  • scope application queries to deleted_at IS NULL;
  • include the active-row predicate in upserts, per DML, upserts, and RETURNING;
  • define a purge/retention job.
• Use generated columns for values derived from the same row; virtual by default, STORED when the column is indexed, hot on the read path, expensive to compute, or must flow through logical replication.
• Give business defaults with DEFAULT at the column (state text NOT NULL DEFAULT 'pending').

Avoid

• Do not maintain updated_at from application code; any write path outside the ORM silently leaves it stale.
• Do not add deleted_at to tables “just in case”; unpaired with partial unique indexes and query scoping it is a latent bug, and history without a consumer is dead weight.
• Do not mix soft- and hard-delete semantics for the same table.
• Do not reach for a derivation trigger or application writes when a generated column can express the rule; the constraint -> generated column -> trigger escalation lives in triggers.
• Do not index a virtual generated column; PostgreSQL requires STORED for that.

Example

CREATE TABLE documents (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  title text NOT NULL,
  body text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER documents_set_updated_at_trigger
  BEFORE UPDATE ON documents
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Version Notes

• Virtual generated columns are the PG18 default; on PG17 and older, all generated columns are STORED.

Exceptions

• Immutable append-only tables (events, audit logs) need created_at only; skip updated_at and its trigger.
• Regulatory retention requirements may mandate soft delete or archival regardless of product needs; document the driver on the table’s migration.

Constraints and NULL Semantics

Rule

Declare every column NOT NULL unless absence is semantically meaningful, and encode cheap, stable, single-row domain rules as named CHECK constraints.

Why

Nullable-by-default schemas leak three-valued logic into every query. Constraints in the database hold on every write path; application validation holds only on the paths that remember to run it.

Do

• Declare NOT NULL on every column unless NULL carries meaning (confirmed_at IS NULL means not yet confirmed).
• Let NULL mean exactly “absent/not yet”; never sentinel values ('', 0, epoch) standing in for it.
• Add named CHECK constraints for cheap, stable, single-row rules: positive amounts, length limits, value sets, updated_at >= created_at.
• Keep complex or fast-changing validation in the application; a rule that changes quarterly does not belong in DDL.
• Use UNIQUE NULLS NOT DISTINCT deliberately when NULL should count as a value for uniqueness (at most one row with no value).
• Make constraints deferrable only for a specific proven ordering problem, with a comment.
• Non-overlap and period rules are owned by temporal data and time zones; uniqueness mechanics by index basics; value-set checks by enums, domains, and lookup tables.

Avoid

• Do not leave columns nullable because the CREATE TABLE default is nullable.
• Do not re-implement a CHECK-expressible rule as a trigger or app-only validation.
• Do not write multi-row or cross-table rules as CHECK constraints; they only see the row at hand and give false confidence.
• Do not forget that plain UNIQUE treats NULLs as distinct: multiple rows with NULL pass; make the choice explicit where it matters.
• Do not keep noncanonical generated constraint names; see object naming.

Example

CREATE TABLE subscriptions (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  user_id uuid NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
  plan text NOT NULL,
  seats bigint NOT NULL,
  canceled_at timestamptz,           -- NULL = active: absence is meaningful.
  external_ref text,                 -- NULL = none; at most one row without one:
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT subscriptions_seats_check CHECK (seats > 0),
  CONSTRAINT subscriptions_user_id_external_ref_key
    UNIQUE NULLS NOT DISTINCT (user_id, external_ref)
);

Migration Notes

• Adding NOT NULL or a CHECK to a large existing table: add as NOT VALID, backfill, then VALIDATE CONSTRAINT; the procedure and its version notes live in the safe schema migration workflow.

Exceptions

• Staging/ingestion tables may be broadly nullable before cleansing; constraints apply where the data becomes canonical.
• Columns added by expand/contract migrations are temporarily nullable mid-flight; the contract step restores NOT NULL.

Index Basics

Rule

Index every foreign key column at creation and enforce unique business keys with unique constraints; add any other index only for a known query pattern.

Why

PostgreSQL does not index FK columns automatically, and unindexed FKs turn parent deletes and joins into child-table scans. Every index taxes every write, so speculative indexes are pure cost until a real query needs them.

Do

• Create each FK’s supporting index in the same migration as the FK.
• Enforce natural/business uniqueness with a UNIQUE constraint; it shows intent in \d and is FK-referenceable.
• Use a standalone CREATE UNIQUE INDEX only when the uniqueness is partial (WHERE deleted_at IS NULL) or over an expression (lower(email)).
• Add non-unique indexes only when a known query filters or sorts on the column; name the query pattern in the migration.
• Accept the implicit B-tree indexes that PK and unique constraints create; do not duplicate them.
• Name indexes per object naming.
• Drop indexes that no query uses; unused indexes still cost every write and vacuum.

Avoid

• Do not index low-cardinality flags (is_active) by themselves; a partial index on the interesting subset may qualify under advanced indexes.
• Do not add an index for every column that appears in any WHERE clause; demand-driven means a known, recurring pattern.
• Do not create a single-column index on a column already leading a composite index.
• Do not enforce uniqueness in application code; concurrent writers make check-then-insert racy.

Example

CREATE TABLE api_tokens (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  user_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  token_digest text NOT NULL,
  expires_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  -- Business key: unique constraint, not a bare index.
  CONSTRAINT api_tokens_token_digest_key UNIQUE (token_digest)
);

-- FK column indexed in the same migration.
CREATE INDEX api_tokens_user_id_idx ON api_tokens (user_id);

-- Known pattern: the reaper scans for expired tokens.
CREATE INDEX api_tokens_expires_at_idx ON api_tokens (expires_at);

Migration Notes

• On existing tables, always CREATE INDEX CONCURRENTLY (outside a transaction); see the safe schema migration workflow.

Exceptions

• Unconstrained soft-reference columns on high-volume append-only tables may skip the index when no known query needs one; document that choice with the soft-reference decision.
• Columns covered as the leading column of a composite index needed by a known query do not also get a single-column index.

Advanced Indexes

Activation

Apply this page when a partial, expression, multicolumn, covering (INCLUDE), or GIN index is proposed, or when indexing jsonb, arrays, or ranges. For ordinary single-column B-trees and FK indexing, index basics is enough.

Rule

Use advanced index forms only with a stated query pattern recorded in the migration, and index searched jsonb/array columns with GIN queried through containment operators.

Why

Advanced indexes are precision tools: each serves a specific query shape and is dead weight (or silently unused) outside it. Naming the pattern keeps the index honest and reviewable.

Do

• Record the query pattern every advanced index serves as a comment in the migration that creates it.
• Use partial indexes for consistently filtered hot subsets: WHERE deleted_at IS NULL, WHERE state = 'pending'.
• Use expression indexes when queries filter on a computed value: lower(email), date_trunc('day', created_at); the query must use the identical expression.
• Order multicolumn indexes with equality-filtered, most-selective columns first, then range/sort columns; a (a, b) index serves a-only queries, not b-only.
• Use INCLUDE columns to make a hot, measured query index-only, sparingly.
• Use GIN for searched jsonb and array columns, and query with containment (@>, <@, ?); plain = bypasses GIN.
• Use jsonb_path_ops for containment-only jsonb workloads (smaller, faster); default jsonb_ops when key-existence (?) queries are needed.
• Use GiST for range and exclusion cases, per temporal data and time zones.

Avoid

• Do not create an advanced index without a named query pattern; decorative complexity.
• Do not create overlapping partial indexes per state value when one B-tree on the column serves them all.
• Do not index whole jsonb documents with GIN “for flexibility” on tables that are never containment-queried.
• Do not stack INCLUDE columns into covering indexes by default; wide indexes tax every write.
• Do not use hash indexes without a measured reason; B-tree covers equality well.
• Do not guess between jsonb_ops and jsonb_path_ops; derive from the actual operators in the query.

Example

-- Pattern: login lookup is case-insensitive.
CREATE UNIQUE INDEX users_lower_email_key ON users (lower(email));

-- Pattern: dashboard lists a user's paid orders by recency.
CREATE INDEX orders_user_id_created_at_idx
  ON orders (user_id, created_at DESC)
  WHERE state = 'paid';

-- Pattern: containment search over imported attributes.
CREATE INDEX imported_listings_raw_attributes_idx
  ON imported_listings USING gin (raw_attributes jsonb_path_ops);

SELECT il.id
FROM imported_listings il
WHERE il.raw_attributes @> '{"heating": "gas"}';

Migration Notes

• All index creation on existing tables uses CREATE INDEX CONCURRENTLY; see the safe schema migration workflow.

Exceptions

• Very large append-mostly tables with time-correlated data may use BRIN for range scans over insertion order; measure before and after.
• Full-text search (tsvector + GIN) follows this page’s gating; a dedicated page is added only if search becomes a real feature.

SELECT Structure and Join Style

Rule

Write explicit JOIN ... ON joins, qualify every column in multi-table queries, and never use SELECT * in application SQL.

Why

Explicit joins keep the relationship between tables visible where it happens. Qualified columns and explicit select lists mean schema changes cannot silently change a query’s meaning or widen its results.

Do

• Join with explicit ANSI syntax: JOIN ... ON, LEFT JOIN ... ON.
• Prefer ON over USING; it survives column renames and stays unambiguous with three or more tables.
• Qualify every column with its table alias in any query that references more than one table.
• List selected columns explicitly in application queries, views, and functions.
• Put join conditions in ON and row filters in WHERE; for LEFT JOIN, conditions on the right table belong in ON (in WHERE they silently convert the join to inner).
• Order FROM items so the driving table comes first, then joins in the order the data flows.
• Alias per sql formatting and comments.

Avoid

• Do not use comma joins (FROM a, b WHERE ...).
• Do not use NATURAL JOIN; it silently re-matches when columns are added.
• Do not use SELECT * in committed application SQL; it is acceptable only for ad hoc exploration. Use SELECT 1 inside EXISTS, and count(*) for row counts.
• Do not use RIGHT JOIN; reorder the tables and use LEFT JOIN.
• Do not leave columns unqualified in multi-table queries; an added column in another table can make them ambiguous or, worse, silently re-bind.

Example

-- Users and their paid order count, including users with none:
SELECT
  u.id,
  u.email,
  count(o.id) AS paid_order_count
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
  AND o.state = 'paid'      -- filter on the right table stays in ON
WHERE u.created_at >= now() - interval '90 days'
GROUP BY u.id, u.email;

Exceptions

• CROSS JOIN is allowed when a cartesian product is the intent (generating combinations); say so with the explicit keyword.
• USING is tolerated in ad hoc psql exploration, not in committed SQL.

Subqueries, EXISTS, and LATERAL

Rule

Use EXISTS (SELECT 1 ...) for semi-joins, always NOT EXISTS instead of NOT IN against subqueries, = ANY for parameterized lists, and LATERAL for per-row derived rows.

Why

NOT IN returns zero rows when its subquery yields a single NULL, a silent wrong answer. EXISTS states membership intent directly and the planner treats it as a semi-join. LATERAL expresses per-row top-N without correlated-subquery contortions.

Do

• Test membership with EXISTS (SELECT 1 FROM ... WHERE ...).
• Test non-membership with NOT EXISTS, never NOT IN (subquery).
• Pass parameterized value lists as column = ANY($1) with an array parameter; one bind parameter regardless of list length.
• Use IN (...) only for short literal lists written in place.
• Use LATERAL joins for per-row derived rows: latest N children per parent, per-row computations reused across the select list.
• Use a scalar subquery only when it returns at most one row by construction; otherwise it is a runtime error waiting for data.

Avoid

• Do not use NOT IN against any subquery or nullable column; the NULL trap is silent.
• Do not rewrite semi-joins as JOIN + DISTINCT; the deduplication hides intent and multiplies rows before removing them.
• Do not nest subqueries more than one level; decompose per CTEs and query decomposition.
• Do not use correlated subqueries in the select list for values a join or LATERAL can produce once.

Example

-- Users with at least one paid order, none of them refunded:
SELECT u.id, u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.state = 'paid'
)
AND NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.state = 'refunded'
);

-- Latest two orders per user: LATERAL.
SELECT u.id, recent.id AS order_id, recent.created_at
FROM users u
CROSS JOIN LATERAL (
  SELECT o.id, o.created_at
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC, o.id
  LIMIT 2
) AS recent;

Exceptions

• NOT IN against a short literal list of non-null values (state NOT IN ('draft', 'archived')) is safe and readable.
• Use LEFT JOIN LATERAL ... ON true instead of CROSS JOIN LATERAL when parents without matches must be kept.

CTEs and Query Decomposition

Rule

Decompose any query with more than one level of nesting or more than one logical step into sequential named CTEs, each one comprehensible unit.

Why

Named steps read top to bottom like a pipeline; nested subqueries read inside out. Since PG12, single-use non-recursive CTEs inline into the outer query, so this clarity is free of the old optimization-fence penalty.

Do

• Name each CTE for what its rows are (paid_orders, latest_logins, overage_by_user), not t1/cte2.
• Keep each CTE one comprehensible unit: one join cluster, one aggregation, one filter stage.
• Let the final SELECT read as the summary of the steps above it.
• Trust inlining; write MATERIALIZED only to deliberately fence a step (with a comment saying why), and NOT MATERIALIZED only to force inlining of a multiply-referenced CTE.
• Use data-modifying CTEs (WITH ... UPDATE ... RETURNING) for multi-step writes per DML, upserts, and RETURNING.
• Use WITH RECURSIVE for tree and graph traversal; keep the base and recursive terms visually separate.

Avoid

• Do not create single-use CTEs for trivial one-step queries; SELECT ... FROM ... WHERE ... needs no pipeline.
• Do not reuse a CTE name within a statement or shadow a table name with a CTE.
• Do not build write pipelines that depend on seeing their own statement’s effects; all CTEs in one statement see the same snapshot.

Example

-- Which plans' users generated the most revenue this quarter?
WITH paid_orders AS (
  SELECT o.user_id, o.total
  FROM orders o
  WHERE o.state = 'paid'
    AND o.created_at >= date_trunc('quarter', now())
),
revenue_by_user AS (
  SELECT po.user_id, sum(po.total) AS revenue
  FROM paid_orders po
  GROUP BY po.user_id
)
SELECT
  s.plan,
  count(*) AS paying_users,
  sum(rbu.revenue) AS plan_revenue
FROM revenue_by_user rbu
JOIN subscriptions s ON s.user_id = rbu.user_id
GROUP BY s.plan
ORDER BY plan_revenue DESC, s.plan;

Exceptions

• A hot query where profiling shows inlined CTE shape hurts: rewrite with subqueries or fences, keep the EXPLAIN evidence in the commit, and comment the deviation.
• One level of simple subquery (WHERE EXISTS (...), a single derived table) is fine without a CTE; see subqueries, EXISTS, and LATERAL.

Aggregation, Window Functions, and Pagination

Rule

Always pair LIMIT with a deterministic ORDER BY, paginate application-facing lists by keyset, and use window functions instead of self-joins for ranking and running values.

Why

LIMIT without a total order returns arbitrary rows that shift between requests. OFFSET scans and discards everything it skips, so deep pages get slower and drift under concurrent writes. Window functions state analytic intent directly.

Do

• Write an explicit ORDER BY with a deterministic tiebreaker (append id) on every query with LIMIT.
• Paginate application-facing lists by keyset: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT $3; id values (uuidv7, time-ordered) make workable cursors.
• Use window functions for ranking, running totals, and neighbors: row_number(), rank(), sum() OVER, lag()/lead().
• Name repeated window definitions once with WINDOW w AS (...).
• Group by the natural key columns; keep HAVING for conditions on aggregates and WHERE for row filters.
• Use count(*) for row counts; count(col) only when skipping NULLs is the point.
• Use filter (WHERE ...) for conditional aggregates instead of sum(CASE WHEN ... THEN 1 ELSE 0 END).

Avoid

• Do not use OFFSET for application-facing pagination; reserve it for small, bounded admin/reporting pages.
• Do not implement top-N-per-group with self-joins; use row_number() or LATERAL (see subqueries, EXISTS, and LATERAL).
• Do not rely on DISTINCT to clean up row multiplication from a wrong join; fix the join.
• Do not use GROUP BY 1, 2 ordinal references in committed SQL; name the columns.
• Do not compute aggregates the page does not display.

Example

-- Keyset pagination: page 2 continues after the last row of page 1.
SELECT o.id, o.created_at, o.total
FROM orders o
WHERE o.user_id = $1
  AND (o.created_at, o.id) < ($2, $3)
ORDER BY o.created_at DESC, o.id DESC
LIMIT 25;

-- Each user's three largest paid orders: window function.
WITH ranked_orders AS (
  SELECT
    o.user_id,
    o.id,
    o.total,
    row_number() OVER (PARTITION BY o.user_id ORDER BY o.total DESC, o.id) AS rank
  FROM orders o
  WHERE o.state = 'paid'
)
SELECT ro.user_id, ro.id, ro.total
FROM ranked_orders ro
WHERE ro.rank <= 3;

Exceptions

• Numbered-page UIs over small bounded sets (admin tables, reports) may use LIMIT/OFFSET; keep the deterministic ORDER BY.
• Approximate counts for very large tables may read pg_class.reltuples instead of count(*) when exactness is not required; comment the approximation.

DML, Upserts, and RETURNING

Rule

Every UPDATE and DELETE carries a WHERE clause, post-write reads use RETURNING, and upserts use INSERT ... ON CONFLICT (reserving MERGE for multi-action bulk synchronization).

Why

An unqualified UPDATE or DELETE is the most destructive statement an agent can emit. RETURNING removes the write-then-read round trip and its race. ON CONFLICT is the atomic, idiomatic upsert; MERGE earns its weight only when one pass must insert, update, and delete.

Do

• Spell an intentional full-table write as WHERE true with an explicit marker comment.
• Use RETURNING old.col, new.col (PG18) when the delta matters: audit records, cache invalidation, change notifications.
• Reference incoming upsert values as excluded.col.
• Target ON CONFLICT at the specific constraint’s columns, not DO NOTHING without a conflict target.
• When the arbiter is a partial unique index (soft-delete tables), repeat its predicate in the conflict target: ON CONFLICT (email) WHERE deleted_at IS NULL DO UPDATE ...; a bare column list matches only full constraints and fails at runtime against a partial index.
• Use MERGE only when synchronizing a table against a source set with insert, update, and delete actions in one pass.
• Batch bulk changes with data-modifying CTEs or multi-row VALUES/UNNEST statements, not row-at-a-time loops.
• Batch very large updates/deletes in chunks (by key range) to bound lock time and WAL spikes; the batching procedure lives in the safe schema migration workflow.

Avoid

• Do not check-then-write for uniqueness in application code; concurrent writers race; ON CONFLICT exists for this.
• Do not follow an INSERT with a SELECT to learn what was written.
• Do not interleave chatty single-row DML in a loop when one set-based statement does the work.

Example

-- Upsert a setting, keeping track of what changed (PG18 RETURNING OLD/NEW):
INSERT INTO user_settings (user_id, key, value)
VALUES ($1, $2, $3)
ON CONFLICT (user_id, key) DO UPDATE
  SET value = excluded.value
RETURNING old.value AS previous_value, new.value AS current_value;

-- Multi-step write as one statement: cancel stale orders, log each one.
WITH canceled AS (
  UPDATE orders o
  SET state = 'canceled'
  WHERE o.state = 'pending'
    AND o.created_at < now() - interval '30 days'
  RETURNING o.id, o.user_id
)
INSERT INTO order_events (order_id, user_id, event_type)
SELECT c.id, c.user_id, 'auto_canceled'
FROM canceled c;

Version Notes

• RETURNING old.*/new.* requires PostgreSQL 18; on older targets, return the new row and fetch prior state inside the same data-modifying CTE if needed.
• MERGE requires PG15+; MERGE ... RETURNING requires PG17+.

Exceptions

• Framework-generated DML (ORM writes) follows the framework; these rules govern hand-written and agent-written SQL.
• Truncating a table is TRUNCATE, not an unqualified DELETE; it is DDL-adjacent and belongs in migrations or documented maintenance scripts.

Functions and Procedures

Activation

Apply this page when writing or reviewing any CREATE FUNCTION/CREATE PROCEDURE, including trigger functions. If the task is deciding whether logic belongs in the database at all, start with house style and Postgres philosophy.

Rule

Keep database functions few and small, label each with the strictest correct volatility, and default to SECURITY INVOKER.

Why

Database functions are harder to test and debug than application code, and a mislabeled volatility gives the optimizer a false contract.

Do

• Write a database function only for the sanctioned cases: trigger functions (see triggers), expression-index helpers, and constraint predicates too complex for inline CHECK.
• Use plain LANGUAGE sql when the body is a single statement; reserve plpgsql for control flow. (A function with a pinned search_path is never inlined into calling queries, so this choice buys clarity, not performance.)
• Label the strictest correct volatility: IMMUTABLE only for pure functions of their arguments; STABLE for anything reading tables; VOLATILE when it writes or depends on changing state.
• Declare STRICT (returns NULL on NULL input) when that is the real contract; it saves the null-handling boilerplate.
• Keep SECURITY INVOKER (the default). Use SECURITY DEFINER only for a specific privilege boundary; treat inputs as hostile, revoke default PUBLIC execution, and grant only the intended role.
• Apply the mandatory path pinning and qualification rules from schema layout and search_path.
• Use a procedure (CREATE PROCEDURE + CALL) only when the body genuinely needs transaction control (batched maintenance with periodic COMMIT).
• Name functions after their behavior (set_updated_at, normalize_email), per object naming.

Avoid

• Do not put business workflow logic in functions; it belongs in the application (see house style and Postgres philosophy).
• Do not mark a table-reading function IMMUTABLE to make it usable in an index; the index will hold stale values. Use a STORED generated column or fix the design.
• Do not default to plpgsql for one-statement bodies.
• Do not create overloaded function families that differ only in argument types; agents and humans both pick the wrong one.
• Do not hide SELECT-able logic behind functions when a view or plain query works; see views and materialized views.

Example

-- Expression-index helper: pure, so IMMUTABLE is correct.
CREATE FUNCTION normalize_email(email text) RETURNS text
LANGUAGE sql
IMMUTABLE
STRICT
SET search_path = public, pg_temp
RETURN lower(trim(email));

CREATE UNIQUE INDEX users_normalized_email_key
  ON users (normalize_email(email));

Exceptions

• Batch maintenance procedures with explicit transaction control are legitimate; keep them in migrations or documented maintenance scripts, not hidden in application flows.
• SECURITY DEFINER is required for controlled privilege elevation (for example, letting the read-only role refresh one materialized view); apply the full hardening from Do above and review with roles, privileges, and row-level security.

Views and Materialized Views

Activation

Apply this page when creating or reviewing a view or materialized view, or when deciding whether an analytics report should become one.

Rule

Create views sparingly — stable read models, column masking, and read-only analytics reports exposed to multiple surfaces — and materialized views only with a documented refresh strategy.

Why

Views are a public contract over the schema: cheap to create, expensive to evolve, and stacked views hide query cost. The sanctioned cases share one property: multiple consumers need the same defined result shape.

Do

• Define a view when a read-only analytics report is consumed by more than one surface (app, admin, BI); the view is the single definition of the report.
• Use views for column masking: exposing a safe subset of a sensitive table to a restricted role. A masking view runs with its owner’s privileges (the default): do not set security_invoker, grant the restricted role SELECT on the view only, keep its base-table access revoked per roles, privileges, and row-level security, and review the view like a SECURITY DEFINER boundary.
• Set security_invoker = true on views whose consumers already hold the base-table privileges, so the querying role’s own permissions apply. The two patterns are mutually exclusive per view: a masking view with security_invoker either fails with permission errors or forces the base-table grant it exists to avoid.
• Alias every output column explicitly; no SELECT * in a view body, where column changes underneath silently change the contract (see SELECT structure and join style).
• Write view bodies with the same CTE decomposition rules as queries.
• Use a materialized view only when the underlying query is measured-too-slow for live reads; document the refresh strategy (what refreshes it, how often, acceptable staleness) in the migration.
• Create the unique index every materialized view needs for REFRESH MATERIALIZED VIEW CONCURRENTLY, and refresh concurrently.

Avoid

• Do not use views as a general query-reuse mechanism; application-side query composition owns that.
• Do not stack views on views; one level deep.
• Do not create a materialized view without a refresh plan; it is a cache with no invalidation.
• Do not write through updatable views; write to tables.

Example

-- One report definition, consumed by app dashboard, admin, and BI.
-- All consumers hold base-table SELECT, so security_invoker applies
-- (a masking view would instead rely on owner privileges):
CREATE VIEW monthly_revenue_by_plan
WITH (security_invoker = true) AS
WITH paid_orders AS (
  SELECT o.id, o.total, o.created_at, s.plan
  FROM orders o
  JOIN subscriptions s ON s.user_id = o.user_id
  WHERE o.state = 'paid'
)
SELECT
  date_trunc('month', po.created_at) AS month,
  po.plan AS plan,
  count(*) AS order_count,
  sum(po.total) AS revenue
FROM paid_orders po
GROUP BY month, po.plan;

-- Materialized only after this is measured too slow live:
-- CREATE MATERIALIZED VIEW ... ; CREATE UNIQUE INDEX ... (month, plan);
-- REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_by_plan;

Exceptions

• Compatibility views that preserve an old shape mid-migration are legitimate and temporary; the contract step of the safe schema migration workflow removes them.
• BI-tool-owned views living in a dedicated reporting schema follow that tool’s conventions.

Triggers

Activation

Apply this page when creating or reviewing any trigger, or when deciding how audit trails or derived data should be maintained.

Rule

Use triggers for audit trails and derived-data maintenance, where they guarantee coverage on every write path; never for business workflow logic, and never where a constraint or generated column can express the rule.

Why

Triggers run no matter which path wrote the row — ORM, psql, script, another service — which is exactly right for bookkeeping that must never be skipped, and exactly wrong for business decisions, which become invisible control flow.

Do

• Maintain updated_at with the shared set_updated_at() trigger; the column requirement lives in standard columns and row lifecycle.
• Write audit trails with AFTER row triggers inserting into an append-only audit table, and make append-only real with grants: REVOKE UPDATE, DELETE on the audit table from the app role, per roles, privileges, and row-level security.
• Maintain denormalized aggregates (counter columns) with triggers when the source of truth is multiple write paths and the parent’s concurrent child-write rate is low: every child write takes a row lock on the parent, so hot parents serialize into lock convoys and deadlocks — prefer periodic recompute there.
• Prefer, in order: constraint, then generated column, then trigger; a trigger is the tool when the first two cannot express the rule.
• Use BEFORE triggers to adjust the row being written (updated_at), AFTER triggers to record side effects (audit rows, counters).
• Keep each trigger function small and single-purpose; one behavior per trigger.
• Name triggers {table}_{action}_trigger per object naming, and pin trigger-function paths per schema layout and search_path.
• Comment every trigger with what invariant or bookkeeping it maintains.

Avoid

• Do not put business workflow logic in triggers (state transitions, notifications-with-meaning, cross-service effects).
• Do not maintain values a STORED generated column can compute from the same row.
• Do not enforce non-overlap or cross-row uniqueness in triggers; temporal and exclusion constraints own that (see temporal data and time zones).
• Do not chain triggers so one trigger’s write fires another; that is invisible control flow. (A trigger’s write firing the target table’s own set_updated_at trigger is the tolerated exception: it adjusts the row in place and cascades no further.)
• Do not swallow errors inside trigger functions; a failing trigger must fail the write.
• Do not toggle triggers off during bulk operations without recording why and restoring them in the same migration.

Example

-- Shared updated_at maintenance:
CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql
SET search_path = public, pg_temp
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_set_updated_at_trigger
  BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Audit: every state change is recorded, no matter who wrote it.
CREATE FUNCTION record_order_state_change() RETURNS trigger
LANGUAGE plpgsql
SET search_path = public, pg_temp
AS $$
BEGIN
  IF NEW.state IS DISTINCT FROM OLD.state THEN
    INSERT INTO order_state_changes (order_id, previous_state, new_state)
    VALUES (NEW.id, OLD.state, NEW.state);
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_record_state_change_trigger
  AFTER UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION record_order_state_change();

Exceptions

• Statement-level triggers with transition tables are the right form for bulk-write auditing when row-level overhead is measured as too high.
• When the application is provably the only write path and needs richer context (actor, request ID), application-level auditing may replace the trigger for that table; document the decision on the audit table.

Roles, Privileges, and Row-Level Security

Activation

Apply this page when creating roles, granting privileges, configuring connections, or deciding how tenant rows are scoped.

Rule

Use an owner/migration role, an app DML role, and a read-only role; grant by role with deny-by-default privileges, and enforce row scoping in the application rather than RLS.

Why

Separating DDL from runtime access limits injection impact, and centralized grants make access reviewable.

Do

• Create three roles per database:
  • {app}_owner: login owner used only by the migration pipeline.
  • {app}_rw: no-login group role with application-table DML.
  • {app}_ro: no-login group role with read-only reporting access.
• Create the database with OWNER {app}_owner and run every migration as that role; default privileges apply only to objects created by the named owner.
• Give application and human login roles access through group membership, never direct grants.
• Revoke PUBLIC access and configure future-object grants once per database:
  • REVOKE CONNECT ON DATABASE {app} FROM PUBLIC
  • REVOKE CREATE ON SCHEMA public FROM PUBLIC
  • ALTER DEFAULT PRIVILEGES FOR ROLE {app}_owner ...
  • REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC
• Override blanket grants in the table’s migration: remove {app}_ro access to sensitive tables and UPDATE/DELETE access by {app}_rw to audit tables.
• Set statement_timeout and a short idle_in_transaction_session_timeout on each runtime login role. Never set them on {app}_owner or database-wide; long jobs may override them per session.
• Scope tenant and row access in application queries. Read-only connections remain unscoped, so multi-tenant human or BI access must be an explicit decision.
• Review SECURITY DEFINER functions with functions and procedures.

Avoid

• Do not connect the application as the owner or a superuser.
• Do not run DDL as any role except {app}_owner.
• Do not grant privileges directly to individual login roles.
• Do not use RLS under this guide’s defaults; hard database-side isolation is a separate architecture decision.
• Do not put one-off grants in application code or ad hoc sessions.
• Do not share a login between applications and humans.

Example

-- Run as the platform administrator.
CREATE ROLE shop_owner LOGIN;
CREATE ROLE shop_rw NOLOGIN;
CREATE ROLE shop_ro NOLOGIN;
CREATE DATABASE shop OWNER shop_owner;

-- Connected to shop:
REVOKE CONNECT ON DATABASE shop FROM PUBLIC;
GRANT CONNECT ON DATABASE shop TO shop_owner, shop_rw, shop_ro;
GRANT USAGE ON SCHEMA public TO shop_rw, shop_ro;

ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO shop_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO shop_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA public
  GRANT USAGE ON SEQUENCES TO shop_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA public
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

The new database setup workflow owns login creation, timeout values, and verification order.

Version Notes

• On PG17+, also set transaction_timeout on runtime login roles. Older targets rely on statement_timeout and idle_in_transaction_session_timeout.

Exceptions

• Multi-service databases may add per-service _rw roles when services need different table access.
• On managed platforms, use the provider’s admin role for setup and grant it {app}_owner before owner-scoped operations.

Safe Schema Migration

Use this workflow when changing the schema of an existing database that serves traffic: adding or dropping columns, adding constraints or indexes, changing types, or backfilling data.

Guideline Routing

Always load these guideline pages:

• Object naming
• Constraints and NULL semantics
• Index basics
• Standard columns and row lifecycle

Load advanced indexes for partial, expression, multicolumn, covering, GIN, JSONB, array, or range indexes.

Greenfield setup with no traffic can use plain DDL; this workflow’s rules exist because of live locks.

Workflow

1. Classify the change: additive (new table, new nullable column, new index), constraining (new constraint, NOT NULL, type narrowing), or breaking (rename, drop, type change, split/merge).
2. Use the project’s framework-native migration tool and file conventions; keep each migration one deliberate step.
3. Set a short lock_timeout (for example 5s) at the top of every migration so blocked DDL fails fast instead of queueing behind long transactions; retry rather than wait.
4. For additive changes:
   • ADD COLUMN with a constant or STABLE default (now()) is safe — no rewrite since PG11, though every existing row receives the same evaluated value. Truly volatile defaults (uuidv7(), gen_random_uuid()) force a rewrite on large tables: add the column, then set the default, backfill, then constrain.
   • Create every index on an existing table with CREATE INDEX CONCURRENTLY, outside a transaction; check for INVALID indexes after a failed run and drop them with DROP INDEX CONCURRENTLY.
5. For constraining changes, use the two-stage pattern:
   • Add the constraint NOT VALID (FK, CHECK, and on PG18 NOT NULL).
   • Backfill or fix violating rows in batches.
   • VALIDATE CONSTRAINT separately; it takes only a light lock.
6. For backfills:
   • Batch by key range (a few thousand rows per statement), committing between batches.
   • Run backfills as data migrations or scripts, not inside the DDL transaction.
   • Throttle if replication lag or lock waits climb.
   • Decide trigger behavior explicitly: a table-wide backfill bumps every row’s updated_at (re-syncing any downstream consumer keyed on it) and fires audit triggers per row. Either accept that and warn consumers, or disable the trigger for the run — documented and restored in the same migration.
7. For breaking changes, use expand/contract across releases:
   • Expand: add the new column/table/shape alongside the old; dual-write from the application (or a temporary sync trigger, commented and removed at contract).
   • Migrate: backfill old data into the new shape; verify counts and spot-check values.
   • Contract: switch reads, stop dual-writing, then drop the old shape in a later release once no deployed code references it.
8. To drop a column: remove all application references in one release, mark it ignored in the ORM if applicable, and DROP COLUMN in a later release.
9. Before running against production: state the expected lock level and duration for each statement, and test the migration against a production-sized copy when the table is large.
10. After running: confirm constraint validity (\d shows no NOT VALID leftovers, no INVALID indexes) and that the application error rate is clean.

Version Notes

• NOT NULL ... NOT VALID requires PostgreSQL 18. On PG12-17, add CHECK (col IS NOT NULL) NOT VALID, VALIDATE CONSTRAINT, then SET NOT NULL (which uses the validated check to skip the table scan), then drop the redundant check.

Avoid

• Do not run CREATE INDEX (non-concurrent), full-table UPDATE, or VALIDATE-at-add on large live tables.
• Do not batch multiple risky DDL statements in one transaction; each holds its locks until the transaction ends.
• Do not rename columns or tables in place on a live system; that is a breaking change and takes the expand/contract path.
• Do not change a column’s type in place when it forces a rewrite; add a new column and migrate.
• Do not leave NOT VALID constraints or INVALID indexes behind; validate or drop them in the same change series.
• Do not skip lock_timeout because the table “is small”; a lock queue behind an idle transaction blocks reads on any table.

New Database Setup

Use this workflow when standing up a new PostgreSQL database for an application: schemas, roles, privileges, extensions, and the first migrations.

Guideline Routing

Always load these guideline pages:

• House style and Postgres philosophy
• Schema layout and search_path
• Roles, privileges, and row-level security
• Object naming
• Primary keys and row identity
• Standard columns and row lifecycle

Workflow

1. Confirm the PostgreSQL version; target PG18+. Record the version assumption where the project documents its stack.
2. Create the three-role topology first ({app}_owner as the migration login, {app}_rw, {app}_ro), per the roles guideline.
3. Create the database with UTF8 encoding, OWNER {app}_owner (only the database owner holds CREATE on public since PG15, and migrations need it), and, unless the project has a locale requirement, a deterministic default collation.
4. Lock down world access per the roles guideline: revoke CONNECT on the database and CREATE on public from PUBLIC, and grant CONNECT to the three roles.
5. Apply the grants, ALTER DEFAULT PRIVILEGES, and standing role timeouts per the roles guideline, and create login roles as members.
6. Configure the migration tool to connect as {app}_owner (its only client) and the application as the {app}_rw login member; verify the app connection cannot run DDL and that objects created by migrations are owned by {app}_owner.
7. Install only extensions the project needs now, each in its own migration with a comment saying what uses it (for example btree_gist the first time a temporal or exclusion constraint needs it).
8. Create the shared set_updated_at() trigger function before the first table migration so tables can attach it immediately.
9. Write the first table migrations following the schema-design guidelines: uuidv7() keys, lifecycle columns with triggers, FKs with indexes, NOT NULL defaults, canonical constraint names.
10. Seed lookup tables in migrations, not by hand; the table shape lives in enums, domains, and lookup tables.
11. Set up the safe-migration guardrails from day one: lock_timeout in the migration template and the safe schema migration workflow linked from the project docs, so habits do not change when the database goes live.
12. Verify the topology before first deploy: connect as each login (owner, app member, read-only member) and confirm it can do exactly what it should (owner: DDL; rw: DML only, no DDL; ro: SELECT only).

Avoid

• Do not develop and deploy as a superuser from the application; the owner login belongs to the migration pipeline alone.
• Do not install extensions speculatively.
• Do not hand-create objects outside migrations, even during setup; the first environment rebuild will miss them.
• Do not defer the role topology “until production”; retrofitting grants across an accumulated schema is the painful version.
• Do not copy configuration from another project without checking the version assumptions and extension list.

Query Performance Investigation

Use this workflow when a query is slow, a page or job has a database bottleneck, or an index is proposed as a performance fix.

Guideline Routing

Always load index basics. Load the remaining pages only when the query uses their concern:

• Advanced indexes
• CTEs and query decomposition
• Aggregation, window functions, and pagination
• JSONB, arrays, and normalization

Workflow

1. Capture the actual slow query with its real bind values (from logs, pg_stat_statements, or application telemetry), not a paraphrase of it.
2. Record a baseline: EXPLAIN (ANALYZE, BUFFERS) on a production-representative dataset. Development databases with 100 rows prove nothing. EXPLAIN (ANALYZE) executes the statement, so wrap any INSERT/UPDATE/DELETE/MERGE in BEGIN; ...; ROLLBACK;.
3. Read the plan from the innermost expensive node outward. Classify the bottleneck:
   • Sequential scan on a large table with a selective filter: missing or unusable index.
   • Index exists but unused: expression mismatch, type mismatch, low selectivity, or operators the index cannot serve (GIN vs =).
   • Misestimated row counts (estimated vs actual off by orders of magnitude): stale statistics (ANALYZE the table) or correlated predicates.
   • Nested loop over many rows: join order/estimate problem.
   • Sort or hash spilling to disk: memory-bound aggregation or missing supporting index for the sort order.
   • Fast query, slow endpoint: N+1 statements at the application layer; fix the call pattern, not the query.
4. Fix in this order: rewrite the query to match existing access paths, refresh statistics, then add or adjust an index under the advanced indexes gating rule (stated query pattern, CREATE INDEX CONCURRENTLY per the safe schema migration workflow).
5. Change one thing at a time and rerun the same EXPLAIN (ANALYZE, BUFFERS); keep the change only on a material, repeatable win.
6. Check the write side of any new index: hot-path insert/update tables pay for every index they carry.
7. Record the outcome where the change lives (migration comment or commit): the query pattern, the before/after timings, and the plan node that changed.

Measurement Commands

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE ...;  -- DML baseline per step 2
ROLLBACK;

-- Statement-level view of what is actually slow:
SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time * calls DESC
LIMIT 20;

-- Are existing indexes used? Candidates for removal:
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

ANALYZE orders;  -- refresh statistics for one table

Avoid

• Do not add an index as the first move; most slow queries are shape problems (missing filter, N+1, offset pagination) the index would only mask.
• Do not tune against EXPLAIN without ANALYZE; estimates are the thing being debugged.
• Do not EXPLAIN (ANALYZE) DML outside an explicitly rolled-back transaction; ANALYZE performs the write.
• Do not test on unrepresentative data sizes.
• Do not add planner hints via MATERIALIZED/NOT MATERIALIZED or enable_* settings as a fix; they are diagnostics, and any kept fence needs a comment and evidence.
• Do not keep a “faster” query that violates the query-style guidelines without recording the measured justification.
• Do not touch server-wide settings (work_mem, shared_buffers) to fix one query.

Schema and Query Review

Use this workflow when reviewing a migration, a schema design, or SQL changes in a pull request, or when refactoring existing database code toward the guidelines.

Guideline Routing

Load only the pages matching the changed surface. For schema changes, always include:

• Object naming
• Primary keys and row identity
• Foreign keys and relationships
• Scalar types
• Constraints and NULL semantics
• Standard columns and row lifecycle

And for query changes:

• SELECT structure and join style
• CTEs and query decomposition
• DML, upserts, and RETURNING

Workflow

1. Identify what the change touches: DDL, queries, DML, database logic (functions/triggers/views), or grants. Load the matching guideline pages before reading the diff in detail.
2. For migrations against a live database, review safety first via the safe schema migration workflow: lock levels, CONCURRENTLY, NOT VALID/VALIDATE, batched backfills, lock_timeout.
3. Review new tables as a unit against the schema guidelines: key strategy, column types, NOT NULL posture, named constraints, FK actions and indexes, lifecycle columns and trigger.
4. Review queries against the query-style guidelines: join style, qualification, decomposition, SELECT *, pagination shape, unqualified UPDATE/DELETE.
5. Review any function, trigger, or view against its guideline’s sanctioned cases; flag business logic in the database and unpinned search_path.
6. Check consistency with the surrounding schema: names, patterns, and conventions should match neighbors unless the change deliberately migrates them.
7. Classify each finding by severity: correctness or data-loss risk, production-safety risk (locks, rewrites), convention violation, style nit. Lead with the first two.
8. For refactor work, change one convention at a time across the affected objects and route schema changes through the safe schema migration workflow; do not mix convention cleanup with behavior changes.
9. Confirm anything uncertain against the actual database (\d table, catalog queries) rather than assuming the diff shows the whole state.

Review Checklist

• Every new FK has an index and an explicit ON DELETE action.
• Every constraint and index has the canonical suffix name; noncanonical generated names are overridden explicitly.
• No serial/bigserial, gen_random_uuid() primary keys, varchar(n), char(n), money, timestamp without time zone, json, or CREATE DOMAIN in new DDL.
• New columns are NOT NULL or the nullability is meaningful.
• Durable tables carry created_at/updated_at and the set_updated_at trigger.
• No unqualified UPDATE/DELETE; writes needing results use RETURNING.
• Migrations on live tables state their lock expectations and use the two-stage constraint pattern.
• New indexes cite the query pattern they serve.
• No business workflow logic in functions or triggers.

Avoid

• Do not review style before safety on migrations; a well-named table rewrite still takes the site down.
• Do not demand guideline compliance from untouched legacy code in an unrelated change; file it as follow-up refactor work.
• Do not approve “temporary” deviations without a comment marking them and a contract step that removes them.
• Do not rewrite a working query during review for style alone without checking its plan on real data first.