postgres-patterns

name	postgres-patterns
description	PostgreSQL query, index, and schema patterns for PROMPT's per-service databases (pgx + sqlc + golang-migrate). Use when writing queries or migrations, designing schema, or diagnosing slow queries.
metadata	{"origin":"ECC"}

PostgreSQL Patterns

PostgreSQL patterns for PROMPT. Each microservice owns a separate database; schema changes go through golang-migrate and Go access is generated by sqlc (never hand-written SQL strings in Go). For the migration/query workflow use the sqlc-migration skill and the database/migrations.md rule; this skill is the query- and index-design reference that sits underneath them.

When to Activate

• Writing SQL queries (db/query/*.sql) or migrations (db/migration/NNNN_*.up.sql)
• Designing or reviewing a schema
• Diagnosing a slow query

Index Cheat Sheet

Query Pattern	Index Type	Example
WHERE col = value	B-tree (default)	CREATE INDEX idx ON t (col)
WHERE col > value	B-tree	CREATE INDEX idx ON t (col)
WHERE a = x AND b > y	Composite	CREATE INDEX idx ON t (a, b)
WHERE jsonb @> '{}'	GIN	CREATE INDEX idx ON t USING gin (col)
WHERE tsv @@ query	GIN	CREATE INDEX idx ON t USING gin (col)
Time-series ranges	BRIN	CREATE INDEX idx ON t USING brin (col)

Always index foreign keys — Postgres does not create an index for a FK automatically, and an unindexed FK makes every join and cascading delete a sequential scan.

Data Types (PROMPT conventions)

Use Case	Type	Notes
IDs / primary keys	uuid	PROMPT identifies course phases, participations, etc. by UUID (parsed with uuid.Parse in handlers) — not serial/bigint
Strings	text	Don't use varchar(n); add a CHECK constraint if you need a length bound
Timestamps	timestamptz	Never naive timestamp — always store the zone
Flags	boolean	Not int/varchar
Columns & tables	snake_case	Matches the sqlc-generated Go field mapping

Common Query Patterns

Composite index column order — equality columns first, then the range column:

CREATE INDEX idx ON participations (course_phase_id, created_at);
-- Serves: WHERE course_phase_id = $1 AND created_at > $2

Covering index — include extra columns to skip the table lookup:

CREATE INDEX idx ON participants (email) INCLUDE (name, created_at);

Partial index — smaller and faster when queries always filter the same predicate:

CREATE INDEX idx ON participants (email) WHERE deleted_at IS NULL;

UPSERT:

INSERT INTO settings (course_phase_id, key, value)
VALUES ($1, $2, $3)
ON CONFLICT (course_phase_id, key)
DO UPDATE SET value = EXCLUDED.value;

Cursor (keyset) pagination — O(1) per page vs OFFSET's O(n):

SELECT * FROM products WHERE id > $1 ORDER BY id LIMIT 20;

Queue / claim-a-row — FOR UPDATE SKIP LOCKED lets concurrent workers avoid contending:

UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

Diagnostics

-- Find unindexed foreign keys (each is a latent seq-scan on join/cascade)
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

-- Slowest queries (requires pg_stat_statements)
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- Table bloat / vacuum health
SELECT relname, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Run EXPLAIN (ANALYZE, BUFFERS) on a suspect query before adding an index — confirm it's a seq scan on a large table, not a plan the planner would already improve with fresh statistics.

Related

• Rule: database/migrations.md — migration numbering, sqlc regeneration, CI naming
• Skill: sqlc-migration — end-to-end migration + query + sqlc generate workflow
• Agent: migration-auditor — reviews migration safety

---

Index/query patterns based on Supabase Agent Skills (credit: Supabase team, MIT License). RLS and server-configuration sections were dropped: PROMPT authorizes at the API layer (Keycloak RBAC via prompt-sdk), not with Postgres Row Level Security, and does not tune server GUCs from app code.