About this skill

What problem it solves

PostgreSQL issues often appear after scale: VARCHAR(n), nullable booleans, missing FK indexes, OFFSET pagination, and SELECT * turn into slow queries, lock contention, and index bloat. In production migrations, plain CREATE INDEX, direct NOT NULL additions, or in-place ALTER TYPE can block writes and complicate rollback. JSONB, RLS, and concurrent writes introduce subtle semantic traps, such as deleting the wrong key, treating SQL NULL as JSON null, or assuming FOR UPDATE prevents phantom inserts. compound-eng-postgresql turns these practices into an executable engineering checklist.

How the skill works

Types and schema: prefer BIGINT GENERATED ALWAYS AS IDENTITY, TIMESTAMPTZ, TEXT, NUMERIC(p,s), BOOLEAN NOT NULL DEFAULT, and JSONB; require FK indexes, CHECK constraints, and created_at / updated_at.
Migration safety: migrations are immutable and forward-only; renames and removals use expand-contract; build indexes with CREATE INDEX CONCURRENTLY; batch large backfills and use FOR UPDATE SKIP LOCKED where appropriate.
Indexes and JSONB: choose B-tree, GIN, GiST, or BRIN by access pattern; delete JSONB keys with #-, and avoid col - 'a,b', chained - 'a' - 'b', or treating SQL NULL in jsonb_set as key removal.
Concurrency and queries: SELECT ... FOR UPDATE does not prevent phantom inserts, so get-or-create should use unique indexes plus ON CONFLICT or advisory locks; use cursor pagination; start tuning with EXPLAIN (ANALYZE, BUFFERS).

Boundaries and notes

It is best used for code review, migration design, performance investigation, and database standards. Some rules depend on PostgreSQL versions, such as NULLS NOT DISTINCT; full-text search, concurrency scripts, and operations strategies still need business-specific and version-specific validation.

Screenshots

Use Cases

Review PostgreSQL table designs for type defaults, FK indexes, and CHECK constraints.
Assess production migrations for expand-contract, CONCURRENTLY, and safe batch backfills.
Diagnose slow queries and unused indexes using EXPLAIN BUFFERS and pg_stat_user_indexes.
Prevent JSONB key loss, SQL NULL mistakes, and read-modify-write clobbering in updates.

Best For

Backend engineers owning core schemas who need consistent PostgreSQL type, index, and constraint rules.
Migration maintainers who must evaluate DDL lock risk, rollback safety, and zero-downtime rollout.
SREs or database engineers debugging slow queries through execution plans, stats, and index strategy.
Backend engineers handling JSONB and concurrent writes who need to avoid key-loss and clobbering pitfalls.