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.

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.
5. Before selecting a server or client version, verify supported majors, current minor releases, and security fixes on the official PostgreSQL support and security pages; record the target version and check date. Do not infer the supported-version range from this skill or an example.

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 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

Inputs

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

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.
• Using a SEQUENCE, IDENTITY or serial for a visible document number; nextval is never rolled back, so it leaves gaps. Fix: counter table row locked FOR UPDATE in the save transaction (tenant-document-numbering).