Postgresql SQL Engineering

PostgreSQL And SQL Engineering

Use this skill with sql-engineering for database-native PostgreSQL work independent of the application language. Let the database-neutral skill establish shared SQL behavior and use this skill for PostgreSQL semantics. Keep database invariants explicit, changes deployable, and query behavior proven with the target database.

Use When

• Designing or reviewing tables, constraints, indexes, views, functions, triggers, migrations, transactions, privileges, RLS, or PostgreSQL extensions.
• Writing or reviewing SQL queries, query plans, pagination, bulk operations, reporting queries, or data repair scripts.
• A change needs database correctness, performance, security, observability, or rollback evidence.
• Rust code uses SQLx or SeaQuery against PostgreSQL: use this skill for database design and rust-persistence-sql for Rust adapter, execution, and query-builder details.

Do not use this skill for generic in-memory domain logic, ORM-only API usage with no SQL/schema behavior, or SQLite/MySQL/MariaDB-specific behavior except when comparing it to PostgreSQL. Use mysql-mariadb-sql-engineering for MySQL- or MariaDB-native design.

Security Review Routing

Load security-review when PostgreSQL work touches roles, privileges, RLS/policies, SECURITY DEFINER, extensions, dynamic SQL or injection risk, tenant isolation, audit logs, sensitive migrations/backfills, data repair scripts, secrets, or production-data access. Pair it with security-review-evidence when evidence includes redacted SQL, EXPLAIN output, schema diffs, audit/log samples, dumps, or migration artifacts.

API and Observability Routing

• Load api-design when PostgreSQL migrations, views, functions, outbox/event rows, generated schemas, reporting outputs, or constraint-error mapping affect an external contract or compatibility promise.
• Load observability-engineering when PostgreSQL work changes slow-query, migration, pool, lock wait, deadlock, replication, maintenance, dashboard, alert, or runbook signals.

Workflow

1. Inspect the real database surface: migrations, schema dumps, SQL files, query builders, ORM mappings, seed data, test fixtures, migration tool, target PostgreSQL major version, extensions, and CI database setup. Verify version-sensitive behavior in the official documentation for that target version, not only the current documentation.
2. State the data behavior before implementation. Use BDD-style examples for observable rules such as uniqueness, authorization, lifecycle transitions, conflict handling, and partial-write prevention.
3. Model the boundary. Use DDD language for entities, value objects, aggregate-like consistency boundaries, repositories, and invariants. Do not let table shape leak into core domain APIs without a deliberate adapter.
4. Design schema and migration order: new objects, backfill, constraints, indexes, locks, deploy compatibility, rollback, and validation queries.
5. Implement SQL with bound parameters, explicit columns, deliberate transaction scope, and safe error mapping at the application edge.
6. Verify with the target engine: migration from empty database, migration from a representative previous schema, focused query tests, and plan inspection for performance-sensitive paths.

Schema And Migration Checklist

• Tables have stable primary keys when identity matters, meaningful foreign keys, NOT NULL for required facts, and CHECK, UNIQUE, exclusion, or domain constraints for invariants the database must enforce.
• Normalize first. Denormalize, materialize, or duplicate only for measured read needs with an ownership and refresh story.
• Choose types by semantics: TEXT for ordinary strings, NUMERIC for exact decimal money-like values, TIMESTAMPTZ for instants, DATE for dates, BOOLEAN for two-state facts, JSONB only for semi-structured data, and enums/domains/lookup tables when they fit the change cadence.
• Index foreign key columns manually when joins or parent updates/deletes need them; PostgreSQL does not create those indexes automatically.
• Indexes have a query rationale. Match predicates, join keys, sort order, uniqueness, and partial or expression predicates to real access paths.
• Migration files are ordered for safe rollout. Call out lock-heavy DDL, backfills, table rewrites, CREATE INDEX CONCURRENTLY, data loss, and app/schema compatibility windows.
• Views and materialized views declare security, staleness, refresh, ownership, and indexing assumptions.

Query And Transaction Checklist

• Select explicit columns. Avoid SELECT * unless the whole row is the contract.
• Bind user-controlled values. Never concatenate untrusted input into SQL.
• Predicates are sargable where possible and line up with available indexes.
• Joins preserve intended cardinality and do not accidentally multiply rows.
• Pagination is stable under inserts and deletes; prefer keyset pagination for large ordered result sets.
• NULL, collation, case sensitivity, timezone, numeric precision, and enum evolution are explicit where they affect behavior.
• Transactions cover exactly one consistency boundary. Name isolation, lock-ordering, retry, idempotency, and deadlock assumptions when concurrent writes matter.
• Bulk writes use set-based SQL, COPY, or batched statements when appropriate instead of row-at-a-time loops.

PostgreSQL Features

• Use B-tree indexes for normal equality/range/order queries; GIN for JSONB, arrays, and full-text search; GiST/SP-GiST for ranges, geometry, and exclusion constraints; BRIN for very large naturally ordered data.
• JSONB works best for optional or variable attributes. Promote frequently filtered or constrained fields to columns, generated columns, expression indexes, or constraints when they become core behavior.
• Arrays are useful for ordered scalar lists, not many-to-many relationships that need referential integrity.
• Range types plus exclusion constraints are strong tools for non-overlap rules such as bookings and schedules.
• Row-level security and privileges are security boundaries. Review actor, role, policy predicate, bypass paths, and test coverage before relying on them.
• Extensions such as pg_trgm, pgcrypto, citext, PostGIS, TimescaleDB, or pgvector must have a clear product need, migration story, and operational ownership.

RLS, Privileges, And Privileged Functions

• Model the actual application role, migration role, and object-owner role; test the policy with representative connections or SET ROLE, not just a privileged development connection. Superusers and roles with BYPASSRLS always bypass RLS; table owners usually do too unless the table uses FORCE ROW LEVEL SECURITY.
• Scope each policy deliberately by command and TO role. USING controls which existing rows are visible or targetable by SELECT, UPDATE, and DELETE; WITH CHECK controls proposed rows for INSERT and UPDATE. Ordinary table grants still authorize commands: RLS filters authorized access, it does not replace grants.
• Test allowed and denied reads, inserts, updates, and deletes for the app role, migration/owner behavior where relevant, and expected bypass behavior. Include cross-tenant and policy-absent cases.
• Use SECURITY DEFINER only for a narrow, reviewed privilege boundary. Give the function a trusted owner, fully qualify referenced objects, and set a trusted search_path with pg_temp last (for example, SET search_path = app_private, pg_temp); do not search schemas writable by callers.
• Create privileged functions transactionally, revoke default PUBLIC execute immediately, then grant execute only to intended roles. Keep the function owner and its table privileges least-privileged and explicit.

Commands

Prefer repository recipes when they exist, especially for SQL scripts. When direct commands are needed:

psql -X -v -f path/to/query.sql
psql -c "\\d+ table_name"
psql -c "EXPLAIN (ANALYZE, BUFFERS) SELECT ..."
sqlx migrate run
sqlx migrate revert

By default, psql continues after SQL errors. ON_ERROR_STOP stops processing and makes a noninteractive script invocation fail when an error occurs.

Use SQLx migration commands only when the repository uses SQLx. Otherwise use the repository's migration tool or SQL deployment recipe. Use a repository-owned recipe, libpq defaults, or a named PGSERVICE configured outside the command; never put credentialed DSNs in argv, shell history, logs, examples, or reports.

For mutating EXPLAIN ANALYZE, use a transaction and rollback on disposable or safe data:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE ...;
ROLLBACK;

Testing And Review

• Use TDD for query bugs when practical: write a failing fixture or integration test that proves the old behavior is wrong.
• Use BDD examples for data behavior users can observe: duplicate prevention, authorization, lifecycle state, conflict responses, and atomicity.
• Test constraints and transactions against PostgreSQL, not only mocks or SQLite.
• Review plans for expensive queries with representative data and current statistics. Do not claim performance improvement without comparable evidence.
• Review security for injection, least privilege, RLS policy coverage, secret handling, audit logs, and safe error messages.
• Review observability for slow queries, migration failures, pool saturation, deadlocks, lock waits, and maintenance signals.

Anti-Patterns

• Treating application validation as a substitute for database constraints on critical invariants.
• Adding indexes speculatively without query, cardinality, and write-cost rationale.
• Assuming EXPLAIN ANALYZE is read-only.
• Hiding long or multi-domain transactions behind generic repository helpers.
• Storing relational facts in JSONB because it is convenient.
• Claiming PostgreSQL behavior from tests that only ran on SQLite or mocks.

Successful Use

The final handoff names the data behavior protected, migrations or SQL changed, database-specific checks run, query-plan or performance evidence when relevant, and any deploy or rollback risk that remains.