Postgresql Engineering

---
name: postgresql-engineering
description: Use when designing, implementing, administering, tuning, backing up, restoring, monitoring, or troubleshooting PostgreSQL schemas and production systems; use database-reliability for datastore-independent SLO and recovery policy.
metadata:
  portable: true
  compatible_with:
  - claude-code
  - codex
---

# PostgreSQL Engineering
Acknowledgement: Shared by Peter Bamuhigire, techguypeter.com, +256 784 464178.

Use this parent skill as the active PostgreSQL engineering entrypoint. Keep implementation guidance here short; load the relevant reference module only when the task needs that depth.

<!-- dual-compat-start -->
## Use When

- Designing PostgreSQL-backed features, schemas, indexes, constraints, and access paths.
- Reviewing SQL correctness, transaction boundaries, isolation assumptions, or extension usage.
- Implementing functions, triggers, views, materialized views, or server-side routines.
- Consolidating PostgreSQL-specific engineering advice before pairing with `database-design-engineering` or `database-reliability`.

## Do Not Use When

- The task is unrelated to this parent skill or is better handled by a narrower active parent named in the workflow.
- The request only needs a trivial answer and no reference module needs to be loaded.

## Required Inputs

- Gather the concrete system, repository, environment, constraints, and deliverable before loading references.
- Identify which absorbed reference file is needed; do not load every migrated reference by default.
## Workflow

1. Start with `database-design-engineering` for logical model, integrity, tenancy, and migration shape.
2. Load only the reference file matching the task:
   - `references/postgresql-fundamentals.md` for baseline PostgreSQL usage and core concepts.
   - `references/postgresql-patterns.md` for schema and application patterns.
   - `references/postgresql-advanced-sql.md` for advanced query design.
   - `references/postgresql-server-programming.md` for functions, triggers, and server-side behaviour.
3. Administration, performance incidents, tuning, backups, and production operations also route here (the retired `postgresql-operations` skill is an inactive alias of this skill).
4. Pair with `ai-rag-patterns` only when pgvector or AI platform concerns are central.

## Quality Standards

- Preserve data integrity with constraints, types, and transaction design before relying on application checks.
- Make query plans reviewable: expected indexes, cardinality assumptions, and failure cases must be explicit.
- Treat migrations as reversible operational changes with lock, runtime, and rollback impact stated.

## Anti-Patterns

- Treating absorbed reference files as active skills or separate routing entrypoints.
- Loading every migrated child reference instead of the one that matches the task.
- Producing generic advice without constraints, evidence, or next verification steps.
## Outputs

- PostgreSQL schema, SQL, migration, or review notes with integrity and performance evidence.
- Reference files loaded and companion skills used.

## References

- Load the retained [PostgreSQL operations workflow](../postgresql-operations/ALIAS.md) for backup, restore, vacuum, replication, production tuning, monitoring, and incidents.
- Load only the references/<old-skill>.md files named in the workflow when their depth is required.
## Evidence Produced

| Category | Artifact | Format | Example |
| --- | --- | --- | --- |
| Data safety | PostgreSQL schema and operations evidence pack | DDL, query plans, and runbook | constraints, migration, vacuum/replication evidence, restore result, and rollback |

<!-- dual-compat-end -->

## Inputs
| Input | Required | Purpose |
|---|---|---|
| PostgreSQL version and schema | yes | Select valid features |
| Query plans and cardinalities | yes | Design indexes |
| Migration and availability constraints | yes | Plan safe change |

## Capability contract
Review SQL and propose DDL by default. Execute migrations, extensions, routines, or data changes only with authorised scope and rollback.

## Degraded mode
If EXPLAIN ANALYZE or statistics are unavailable, provide read-only hypotheses and validation SQL; do not claim measured improvement.

## Decision rules
| Condition | Action |
|---|---|
| Planner estimate is materially wrong | Refresh or improve statistics |
| Index build may block writes | Use a compatible concurrent path |
| Constraint encodes business truth | Enforce it in the database |

## Domain Anti-Patterns
- Running EXPLAIN ANALYZE on unsafe writes. Fix: use a rollback-safe test.
- Adding extensions without trust review. Fix: verify source and privileges.
- Indexing without workload evidence. Fix: compare read and write cost.
- Hiding null semantics in application code. Fix: define them in schema.
- Combining expand and contract in one release. Fix: stage compatibility.