bsamiee [H1][CODING-PG]
Dictum: PostgreSQL schema design, query algebra, and security posture govern all SQL work.
All SQL follows five governing principles:
- Polymorphic — one function/query per concern, generic over specific via parameter dispatch and dynamic SQL
- Set-algebraic — express operations as set transformations; zero row-at-a-time iteration
- Strongly typed — domain types, composite types, range types; zero untyped
textcolumns for structured data - Programmatic — variable-driven predicates, parameterized DDL, zero stringly-typed identifiers
- Declarative-first — constraints, generated columns, and RLS policies enforce invariants at the schema level; application logic is last resort
- Source-current — PostgreSQL 18/current docs are the truth baseline; examples must state current semantics, not stale point-version folklore
Paradigm
- Immutability: append-only event tables, temporal versioning via
tstzrange+WITHOUT OVERLAPS, soft-delete viaarchived_attimestamp — zero in-place mutation of historical records - Type anchoring: one
CREATE TYPEorCREATE DOMAINper semantic concept — derive column declarations from domain types, never redeclare equivalentCHECKconstraints across tables - Expression control flow:
CASE-free query design viaCOALESCE,NULLIF,GREATEST/LEAST, lateral joins, andFILTER (WHERE ...)— reserveCASEfor irreducible multi-branch projection only - Set composition: CTEs as named relational algebra steps;
UNION ALLover procedural accumulation;MERGEover conditional INSERT/UPDATE sequences - Constraint-driven integrity:
CHECK,EXCLUDE,WITHOUT OVERLAPS,GENERATED ALWAYS AS— push validation into DDL; application-layer checks are redundant defense, not primary enforcement - Extension-first: pgvector for embeddings, pg_trgm for fuzzy search, PostGIS for spatial, TimescaleDB for time-series — never hand-roll what an extension provides
Conventions
| Layer | Mechanism | Owns |
|---|---|---|
| Identity | uuidv7() |
PK generation — timestamp-ordered, B-tree friendly |
| Temporal | WITHOUT OVERLAPS + PERIOD |
Range integrity — temporal PKs, FKs, exclusion constraints |
| Projection | Virtual generated columns | Computed fields — zero storage, instant ALTER TABLE |
| Write strategy | MERGE ... RETURNING OLD/NEW |
Upsert + audit trail in single statement |
| Type safety | Domain types + composite types | Semantic column typing — branded scalars, structured records |
| Search | GIN + pg_trgm / pgvector | Full-text, trigram similarity, vector nearest-neighbor |
| Partitioning | Declarative + pg_partman | Time/hash/list partitioning with automatic lifecycle |
| Scheduling | pg_cron | In-database job scheduling — maintenance, materialized views |
| Security | RLS + SECURITY INVOKER | Row-level tenant isolation, least-privilege function execution |
| Observability | pg_stat_statements + auto_explain | Query fingerprinting, automatic slow-query plan capture |
- Effect-SQL (
@effect/sql+@effect/sql-pg) is the assumed TypeScript integration layer. Model.Classfield modifiers (Generated,FieldOnly,FieldExcept,Sensitive) must align with DDL constraints —Generatedfields map toDEFAULTorGENERATED ALWAYS AScolumns.- All SQL identifiers use
snake_case; TypeScript receivescamelCaseviatransformResultNames.
Contracts
Type discipline
- One domain type per semantic concept; column declarations reference the domain, never inline
CHECKconstraints. - Composite types for structured return values from functions — never
OUTparameter proliferation. - Range types (
tstzrange,int4range, custom) for interval semantics — never dualstart/endcolumns. NOT NULLis default posture; nullable columns require documented justification.
Query algebra
- CTEs for named intermediate results; recursive CTEs with
SEARCH BREADTH FIRSTorCYCLEfor graph traversal. MERGEwithRETURNING OLD.*, NEW.*, merge_action()for write-audit fusion.- Window functions with
GROUPS/EXCLUDEframing where row-count framing is insufficient. JSON_TABLE/jsonb_path_queryfor structured JSON extraction — never application-side JSON parsing of database-resident JSON.LATERAL JOINfor correlated subquery materialization — never scalar subqueries in SELECT list.
Index strategy
- Every
WHEREclause pattern has a corresponding index; partial indexes for selective predicates. INCLUDEcolumns for index-only scans on high-frequency read paths.- GIN for JSONB containment (
@>), array overlap (&&), full-text (@@). - GiST for range overlap, spatial queries, nearest-neighbor when combined with
ORDER BY <-> LIMIT. - BRIN for append-only monotonic columns (timestamps, serial IDs) — orders of magnitude smaller than B-tree.
Security
- RLS enabled on every tenant-scoped table; policies use fail-closed
nullif(current_setting('app.current_tenant', true), '')tenant scoping. - Functions default to
SECURITY INVOKER(always the default);SECURITY DEFINERonly withSET search_path = pg_catalog, public. - Column-level
GRANTfor sensitive fields — never rely on view-based column hiding alone.
Performance
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)is the verification tool; assertions against plan shape, not just row counts.- AIO (
io_method = io_uring) for sequential scan and vacuum workloads on Linux. - Parallel query enabled for aggregation, hash join, index scan —
max_parallel_workers_per_gathertuned to workload. FOR UPDATE SKIP LOCKEDfor concurrent batch processing — neverSELECT ... FOR UPDATEwithoutSKIP LOCKEDon queue tables.
Anti-Patterns
| Label | Symptom |
|---|---|
| STRING_TYPING | text for structured data (JSON, enum values, composite structures) |
| DUAL_COLUMN_RANGE | start_date/end_date instead of tstzrange with exclusion constraint |
| IMPERATIVE_BATCH | PL/pgSQL LOOP with row-at-a-time UPDATE instead of set-based MERGE/CTE |
| INDEX_SPRAWL | Redundant single-column indexes subsumed by existing composite indexes |
| NULLABLE_DEFAULT | Columns nullable without documented justification |
| TRIGGER_LOGIC | Business logic in triggers instead of MERGE RETURNING or generated columns |
| OFFSET_PAGINATION | LIMIT/OFFSET instead of keyset pagination for client-facing endpoints |
| SECURITY_DEFINER_LEAK | SECURITY DEFINER without SET search_path — search_path injection vector |
| STRINGLY_POLICY | RLS policy with hardcoded literals instead of current_setting() |
| FUNCTION_PROLIFERATION | Separate functions for each query variant instead of one polymorphic function |
| APPLICATION_SIDE_JSON | Fetching raw JSONB and parsing in application instead of jsonb_path_query |
| MANUAL_PARTITION | Hand-written partition creation instead of pg_partman lifecycle management |
| UNVALIDATED_CONSTRAINT | ADD CONSTRAINT ... NOT VALID without subsequent VALIDATE CONSTRAINT |
| IF_THEN_DISPATCH | PL/pgSQL IF p_op = 'get' THEN ... ELSIF instead of VALUES-based dynamic SQL |
| NONCOMPOSABLE_CAGG | PERCENTILE_CONT in hierarchical CAGG; non-composable across tiers |
| LEGACY_UUID | Non-ordered UUID generation on new ordered PKs where uuidv7() fits better |
| STALE_HEALTH_VIEW | Materialized views for real-time health monitoring instead of inline queries |
| EXCLUDE_OVER_WITHOUT_OVERLAPS | EXCLUDE instead of WITHOUT OVERLAPS PK/UNIQUE for temporal overlap in PG 18 |
| RAW_UUID_ID | Raw S.UUID for PK/FK instead of S.UUID.pipe(S.brand('EntityId')) |
| BARE_FOR_UPDATE | FOR UPDATE without SKIP LOCKED on batch/queue processing patterns |
| NULL_UNSAFE_ANTIJOIN | NOT IN (SELECT ...) instead of NOT EXISTS; NULL in subquery yields UNKNOWN |
| DISTINCT_OVER_EXISTS | SELECT DISTINCT on joined data; use EXISTS semi-join (avoids sort/dedup) |
Load Sequence
Foundation (always load):
references/validation.md— compliance checklist, Effect-SQL alignment, migration safety
Task-routed references (load when the task matches):
references/ddl.md— schema design, domain/composite/range types, temporal constraints, generated columns, partitioning, lock levelsreferences/queries.md— CTE algebra, MERGE, window functions, JSON_TABLE, recursive patternsreferences/indexes.md— index type selection, partial indexes, covering indexes, maintenancereferences/functions.md— polymorphic functions, custom aggregates, procedures, PL/pgSQL dispatchreferences/extensions.md— load when: pgvector, pg_trgm, PostGIS, TimescaleDB, pg_cron, pg_partman, pg_duckdb, embeddings, similarity search, time-series, spatial, partitioning automation, analytics, OLAP, analyticalreferences/security.md— load when: RLS, row-level security, tenant isolation, privileges, audit, pgauditreferences/observability.md— load when: monitoring, statistics, auto_explain, wait events, lock contentionreferences/performance.md— load when: tuning, io_uring, JIT, parallel query, vacuum, SSD, NVMe, EXPLAINreferences/replication.md— load when: replication, publications, subscriptions, conflict tracking, CDC
First-Class Extensions
| Extension | Owns | Load When |
|---|---|---|
pgvector |
Vector storage, HNSW/IVFFlat/DiskANN indexes | Embedding search, similarity queries |
pgvectorscale |
DiskANN index, Statistical Binary Quantization | >1M vectors, memory-constrained |
pg_search |
BM25 full-text via Tantivy, @@@ operator |
Search-quality ranking, hybrid search |
pg_trgm |
Trigram similarity, GIN trigram indexes, % operator |
Fuzzy text search, typo tolerance |
PostGIS |
Geometry/geography types, spatial indexes, ST_* funcs | Geospatial queries, proximity search |
TimescaleDB |
Hypertables, continuous aggregates, compression | Time-series ingestion, rollup queries |
pg_cron |
In-database scheduled jobs | Materialized view refresh, maintenance |
pg_partman |
Partition lifecycle management | Non-time-series partitioning |
pg_duckdb |
Embedded DuckDB, analytical acceleration, lake access | OLAP queries, Parquet/Iceberg reads |
pg_jsonschema |
JSONB validation via JSON Schema CHECK constraints | Structured JSONB columns |
btree_gist |
GiST equality operators for EXCLUDE constraints | Range + equality exclusion constraints |
bloom |
Bloom filter index for wide-table equality | >5 columns, arbitrary WHERE combos |
pg_stat_statements |
Query fingerprinting, execution statistics | Performance analysis, regression detect |
pgaudit |
Compliance-grade audit logging (session + object) | SOC 2, HIPAA, PCI-DSS compliance |
Validation Gate
After writing or modifying SQL, run in order:
- Automated lint:
bash scripts/pg_lint.sh [PATH...]from this skill directory — anti-pattern detectors (rg-based + structural). Errors (E) block merge; warnings (W) require justification. Supports--json,--sql-only,--ts-only, and--self-test. - Manual checklist:
references/validation.md— compliance gates not automatable (Effect-SQL alignment, migration safety, lock-level awareness).