postgres

Follows universal SQL style from sql-skill (lowercase, snake_case, trailing commas, CTEs).

Version Policy

Target PostgreSQL 18. Assume 14+ (the oldest supported release). Gate anything newer explicitly.

Philosophy

PostgreSQL is a computation platform, not just a data store. Push logic down. Use its type system, indexing, search, queuing, and pub/sub capabilities before reaching for external services.

Schema Design

create table orders (
  id bigint generated always as identity primary key,
  customer_id bigint not null references customers (id),
  status text not null default 'pending'
    check (status in ('pending', 'confirmed', 'shipped', 'cancelled')),
  total_cents bigint not null check (total_cents > 0),
  metadata jsonb not null default '{}',
  tags text[] not null default '{}',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- FK columns are NOT auto-indexed (unlike MySQL/InnoDB) — create the index yourself,
-- or parent deletes/updates and joins seq-scan the child table
create index idx_orders_customer_id on orders (customer_id);

Choose on delete explicitly: restrict by default (fail loudly), cascade only when child rows are meaningless without the parent.

Data Type Selection

not null by Default

Every column is not null unless you have a specific reason for nullability. Nullable columns spread uncertainty through every query.

email text not null,
deleted_at timestamptz,  -- intentionally nullable (soft delete)

Domain Types (Database-Level Newtypes)

create domain positive_int as bigint check (value > 0);
create domain email_address as text check (value ~* '^.+@.+\..+$');

create table invoices (
  id bigint generated always as identity primary key,
  to_email email_address not null,
  amount_cents positive_int not null
);

JSONB

Semi-structured data with full indexing. Avoid schema-per-variation sprawl.

create table products (
  id bigint generated always as identity primary key,
  name text not null,
  attributes jsonb not null default '{}'
);

-- GIN index for containment queries; jsonb_path_ops variant is smaller but serves @> only
create index idx_products_attrs on products using gin (attributes);

-- containment + extraction
select name, attributes->>'color' as color
from products
where attributes @> '{"category": "electronics"}'

-- path queries
select name from products
where jsonb_path_exists(attributes, '$.tags[*] ? (@ == "sale")')

Operators:

Array Types

GIN indexes serve @> and && on arrays.

Partitioning

Declarative partitioning for large tables. The optimizer prunes partitions automatically.

-- range partitioning (most common: time-based)
create table events (
  id bigint generated always as identity,
  event_type text not null,
  payload jsonb not null,
  created_at timestamptz not null default now(),
  primary key (id, created_at)
) partition by range (created_at);

create table events_2024_q1 partition of events
  for values from ('2024-01-01') to ('2024-04-01');
create table events_2024_q2 partition of events
  for values from ('2024-04-01') to ('2024-07-01');

-- list partitioning
create table orders_partitioned (
  id bigint generated always as identity,
  region text not null,
  total_cents bigint not null,
  primary key (id, region)
) partition by list (region);

create table orders_us partition of orders_partitioned for values in ('us');
create table orders_eu partition of orders_partitioned for values in ('eu');

-- default catches the rest
create table events_default partition of events default;

Unique constraints (including the PK) must include the partition key — global uniqueness across partitions does not exist. primary key (id) alone fails on a table partitioned by created_at.

Full-Text Search

Weighted tsvector columns + GIN cover ~80% of search use cases without Elasticsearch. Implementing in-database search (ranking, prefix search): read references/full-text-search.md.

Upsert + returning

Eliminate round-trips.

-- upsert: insert or update on conflict
insert into users (email, name)
values ('alice@example.com', 'Alice')
on conflict (email)
do update set name = excluded.name, updated_at = now()
returning *;

-- insert and get generated values back
insert into orders (customer_id, total_cents)
values (1, 5000)
returning id, created_at;

-- bulk upsert
insert into inventory (sku, quantity)
values ('sku-1', 10), ('sku-2', 20), ('sku-3', 30)
on conflict (sku)
do update set quantity = inventory.quantity + excluded.quantity
returning *;

-- inspect both sides of an upsert (PG 18+)
insert into inventory (sku, quantity)
values ('sku-1', 10)
on conflict (sku) do update set quantity = excluded.quantity
returning old.quantity as before, new.quantity as after;

-- delete and return deleted rows
delete from expired_sessions
where expires_at < now()
returning id, user_id;

merge vs on conflict

• on conflict is the concurrency-safe upsert: requires a unique constraint, arbitrates concurrent writers atomically.
• merge (PG 15+) is for multi-action sync: update + delete + conditional insert in one statement; returning with merge_action() and when not matched by source from PG 17.
• merge lacks on conflict's atomic concurrency guarantee — concurrent merges can raise unique violations. Wrap in a retry loop or serialize writers; for plain upsert, prefer on conflict.

Queue Pattern (select for update skip locked)

Use Postgres as a job queue without external dependencies.

-- dequeue: atomically claim next job
with next_job as (
  select id
  from jobs
  where status = 'pending'
  order by priority desc, created_at
  limit 1
  for update skip locked
)
update jobs
set status = 'processing', started_at = now()
from next_job
where jobs.id = next_job.id
returning jobs.*;

-- batch dequeue
with batch as (
  select id
  from jobs
  where status = 'pending'
  order by created_at
  limit 100
  for update skip locked
)
update jobs
set status = 'processing', started_at = now()
from batch
where jobs.id = batch.id
returning jobs.*;

Every dequeue is an update — dead tuples accumulate faster than default autovacuum (scale_factor 0.2) reclaims them, and the hot status = 'pending' index degrades. Tune per-table and keep the table small:

alter table jobs set (autovacuum_vacuum_scale_factor = 0.01, fillfactor = 70);

fillfactor = 70 leaves page room for HOT updates. Delete or archive completed jobs — don't let them sit.

Advisory Locks + LISTEN / NOTIFY

Application-level locks and built-in pub/sub — both are session-bound and break through transaction-mode PgBouncer. Coordinating workers, cache invalidation, or event push: read references/listen-notify.md.

generate_series + System Queries

Gap-free time series, synthetic test data, and operational queries (unused indexes, slow queries, table sizes): read references/ops-queries.md.

Materialized Views

Pre-computed aggregations refreshed without blocking reads (concurrently needs a unique index). Caching expensive aggregations in-database: read references/materialized-views.md.

Index Types

Covering Indexes (Index-Only Scans)

create index idx_users_status_covering
  on users (status) include (id, email, name);

Partial Indexes

-- index only the rows you query
create index idx_users_email_active on users (email)
where deleted_at is null;

Functional Indexes

create index idx_users_email_lower on users (lower(email));

BRIN for Time-Series

-- tiny index for append-only time-series data (~1000x smaller than B-tree)
create index idx_events_created_brin on events using brin (created_at);

Bulk Operations

copy beats multi-row insert by an order of magnitude for bulk loads. Loading files or streaming through drivers: read references/bulk-load.md.

Transactions & Isolation

• Default is read committed — right for most OLTP work.
• serializable failures are retryable by design: catch SQLSTATE 40001 and retry the whole transaction in a loop.
• begin isolation level repeatable read read only for reports — one consistent snapshot across all queries.

CTE Materialization Control

-- force materialization (optimization fence)
with cached as materialized (
  select * from expensive_view where ...
)
select * from cached;

-- force inlining (let planner optimize through)
with inlined as not materialized (
  select * from users where status = 'active'
)
select * from inlined where created_at > '2024-01-01';

Safe Migrations

-- add column with default: instant only when the default is non-volatile;
-- volatile defaults (gen_random_uuid(), clock_timestamp()) rewrite every row —
-- add without default, backfill in batches, then set the default
alter table users add column verified boolean not null default false;

-- add index: always concurrently (no table lock)
create index concurrently idx_users_email on users (email);

-- add not null without a long lock (PG 18+): not valid skips the scan
alter table users add constraint users_email_nn not null email not valid;
alter table users validate constraint users_email_nn;  -- background scan; column becomes catalog not null

-- PG ≤17: check-constraint workaround, then promote
alter table users add constraint users_email_nn check (email is not null) not valid;
alter table users validate constraint users_email_nn;
alter table users alter column email set not null;  -- uses the validated check, skips the scan
alter table users drop constraint users_email_nn;   -- now redundant

-- rename column
alter table users rename column name to display_name;

Connection Management

• PgBouncer in transaction mode for connection pooling.
• Transaction mode supports NO session state: set/reset and listen never work through it — a session-level set lands on an arbitrary server connection and leaks to other clients.
• Protocol-level prepared statements work through transaction mode only with max_prepared_statements > 0 in pgbouncer config.
• Set timeouts at the role or database level, not per-session:

alter role app_user set statement_timeout = '30s';
alter role app_user set idle_in_transaction_session_timeout = '60s';

-- one-off override, safe through transaction pooling (scoped to the transaction)
begin;
set local statement_timeout = '5min';
-- ...
commit;

explain Deep Dive

explain (analyze, buffers, timing, format text)
select ...;

sql-skill's red flags (seq scan on large tables, estimate mismatches, nested-loop blowups, external-merge sorts) apply unchanged. Postgres-specific:

• Buffers: shared read ≫ hit — cold cache or oversized working set: check indexes, consider partitioning.

-- update statistics manually
analyze users;

-- increase statistics target for better estimates on skewed columns
alter table users alter column status set statistics 1000;

Comment Doctrine

Comments are for the person reading and reviewing the SQL: say why a filter, join, or CTE exists, and anything that is not obvious. Write the reason as a plain sentence (no WHY: label).

Keep the work plan out of comments: no phases, plan steps, ticket numbers, or words like "for now", "temporary", or "later" (migrations attract this rot the most). Those go stale and someone has to find and delete them. Unfinished work belongs in plan.md.

Non-trivial queries, views, migrations, and SQL functions get a header comment above them: one line saying what it is for, plus the cost driver. Do not write big-O or O(...) on SQL — the planner picks the plan, so naming O(...) would be a lie. Name the cost driver instead: a work_mem spill to disk, the index type a scan rides on, or what explain (analyze, buffers) would show. This header is the SQL analogue of the big-O header the code skills require.

-- active users joined to their latest order
-- cost driver: hash join on customer_id; large sort spills to disk past work_mem,
-- so confirm idx_orders_customer_id with explain (analyze, buffers)
select u.id, u.email, o.total_cents
from users u
join orders o on o.customer_id = u.id
where u.deleted_at is null;

Editing counts too: when you change a query, migration, or function, keep the comments already there, fix any your change made wrong, and add the missing header, cost note, or reason. Do this only for SQL you add or change, not for nearby SQL you did not touch.

Before Committing (Self-Review)

Schema

• not null on every column unless explicitly nullable
• timestamptz not timestamp
• text not varchar(n)
• bigint for money (cents), never float
• generated always as identity not serial
• Check constraints on bounded values
• Foreign keys with appropriate on delete behavior — and an index on the FK column

Indexes

• B-tree for equality/range (default)
• GIN for JSONB, arrays, full-text
• BRIN for naturally ordered append-only data
• Partial indexes where queries always filter by a condition
• Covering indexes (include) for frequent index-only scan opportunities
• Created concurrently in production

Queries

• returning clause to avoid second round-trip
• on conflict for upsert instead of check-then-insert
• for update skip locked for queue patterns
• CTE materialization hints where optimizer makes wrong choice
• explain (analyze, buffers) validated
• Non-trivial queries/migrations carry a header comment: what it is for + the cost driver (not O(...))

Operations

• Migrations are safe (no table locks, concurrent index creation)
• Statement timeout set (via alter role, not session set)
• Connection pooling configured
• pg_stat_statements enabled for query monitoring