Postgresql Developer

PostgreSQL Developer Guidelines

Covers PostgreSQL as both a design problem and a production system: turning a specification into a schema, choosing between architectures, writing SQL, and making an existing database faster. Baseline is PostgreSQL 18; every supported version back to 14 is covered.

1. Always determine the PostgreSQL major version and where it runs before giving guidance. 18 made VIRTUAL the default for generated columns, turned on data checksums, added skip scans and temporal constraints; 15 added MERGE and NULLS NOT DISTINCT; managed platforms restrict extensions, superuser and configuration. Advice that is right on 18 silently changes meaning on 14. Read postgres-versions.md.

2. Never add an index, denormalise, or change a setting without a measurement. Run EXPLAIN (ANALYZE, BUFFERS) before and after, on realistic data volumes. An index guessed at is a permanent write cost that may buy nothing, and a plan on 100 rows tells you nothing about 100 million. Read explain.md.

3. Constraints are the schema, not decoration. Every column is NOT NULL unless nullable means something specific; every foreign key is declared; every domain rule that can be a CHECK is one. The database is the only layer that cannot be bypassed by a bad deploy, a background job, or a manual psql session. Read constraints.md.

4. Never generate these types. char(n), varchar(n) as a length guard, money, timestamp (without time zone), timetz, serial/bigserial, or any float type for money. Each has a specific replacement and a specific way it corrupts data. Read data-types.md.

5. Every DDL statement states its lock. Migrations are the most common way to cause an outage: an unqualified ALTER TABLE takes ACCESS EXCLUSIVE and queues behind every open transaction, then blocks every new one. Give the lock level, the safe rewrite, and a lock_timeout. Read migrations.md.

6. Tenant isolation must fail closed. An RLS predicate must be written so that a missing or mistyped session variable returns zero rows, never every row. Verify by running as the actual application role, never as superuser or the table owner, both of which bypass RLS. Read row-level-security.md.

7. After writing SQL, run it and read the plan. Correct-looking SQL silently returns wrong results through NULL semantics, join fan-out, and GROUP BY scope. Correct-looking DDL silently rewrites a table. Execute against a real instance with representative data.

Every reference carries a ## Version notes section stating what differs across PostgreSQL 14 to 18, and a ## Gotchas list of the specific mistakes agents make in that area. Read the gotchas even when skimming.

Determining the Version and Platform

Step 1. SELECT version(); and SHOW server_version_num;. Never infer the server version from a client (psql --version) or a driver.

Step 2. Identify the platform. Self-hosted, Amazon RDS or Aurora, Cloud SQL, Azure Flexible Server, Supabase, Neon. This decides whether you have superuser, which extensions exist (SELECT * FROM pg_available_extensions), whether you control postgresql.conf, and whether a pooler sits in front of you.

Step 3. Check for a pooler before recommending anything session-scoped. SHOW max_connections, then find out whether PgBouncer, RDS Proxy, Supavisor or pgcat is in the path and in which pool mode. Transaction-mode pooling breaks session state and is the single most common cause of RLS leaks. Read connections-and-pooling.md.

Step 4. Read the existing schema before proposing changes: \d+ tablename, pg_indexes, pg_constraint. Match existing naming and modelling conventions, and raise the conflict rather than silently mixing styles.

Step 5. New projects: PostgreSQL 18. Read postgres-versions.md for the upgrade path and the per-version feature gates.

Designing a Schema From a Spec, In Order

Do not write DDL from the first description. Work these in order; each one changes the output of the next.

Optimising a Slow Query, In Order

Never start by adding an index. Start by finding out what is actually slow and why.

Foundations

• PostgreSQL Versions: The 14-18 support matrix, what each version added and changed, the PG18 defaults that alter existing DDL, PG19 in beta, and the upgrade path. Read postgres-versions.md
• Naming and Conventions: snake_case and why unquoted identifiers fold to lower case, singular vs plural, and naming schemes for constraints, indexes and enum-like values. Read naming-and-conventions.md
• Schema Organisation: Databases vs schemas, search_path and its security implications, where extensions belong, and the public schema change in PG15. Read schema-organisation.md
• SQL Style: Formatting that survives review and diffs, keyword case, join layout, and why generated SQL still needs a house style. Read sql-style.md

Design and Architecture

• Requirements to Schema: The questions to ask before writing DDL, turning a spec into entities and cardinalities, capturing access patterns and volumes, and the modelling decisions that are expensive to reverse. Read requirements-to-schema.md
• Multi-Tenancy: Shared schema with tenant_id and RLS, schema-per-tenant, database-per-tenant, and the hybrid. Where each actually breaks, the decision matrix, and promoting a tenant out. Read multi-tenancy.md
• Normalisation: 1NF to BCNF worked concretely, what each normal form prevents, and the narrow, measured cases for denormalising. Read normalisation.md
• Data Types: The selection guide by category, the six banned types and their replacements, numeric for money, timestamptz always, arrays, ranges, domains, and TOAST. Read data-types.md
• Keys and Identifiers: Surrogate vs natural keys, bigint GENERATED ALWAYS AS IDENTITY vs UUID, why UUIDv4 hurts B-tree locality and UUIDv7 fixes it, and composite keys. Read keys-and-identifiers.md
• Constraints: NOT NULL, CHECK, UNIQUE and NULLS NOT DISTINCT, foreign keys and their actions, EXCLUDE, DOMAIN, deferrable constraints, and validating without a long lock. Read constraints.md
• Relationships: One-to-one, one-to-many, many-to-many, self-references and hierarchies, why polymorphic associations defeat foreign keys, and the alternatives. Read relationships.md
• Temporal and History: Audit trails, soft deletes and the damage they do, slowly changing dimensions, range types with WITHOUT OVERLAPS, and system-versioned patterns. Read temporal-and-history.md
• JSONB: When a column earns jsonb and when it is a modelling failure, json vs jsonb, operators and path expressions, indexing strategies, and constraining a schemaless column. Read jsonb.md

Writing SQL

• NULL Handling: Three-valued logic, why = NULL never matches, NOT IN with NULLs, DISTINCT and GROUP BY treating NULLs as equal, aggregates skipping them, and COALESCE vs NULLIF. Read null-handling.md
• Joins: Inner, outer, semi and anti joins, LATERAL, EXISTS vs IN vs JOIN, join fan-out silently multiplying aggregates, and why join order is the planner's job. Read joins.md
• Aggregation: GROUP BY scope rules, HAVING vs WHERE, FILTER, GROUPING SETS/ROLLUP/CUBE, DISTINCT ON, ordered-set aggregates, and counting correctly. Read aggregation.md
• Window Functions: Frames and the default frame trap, ranking functions, running totals, LAG/LEAD, and where a window beats a self-join. Read window-functions.md
• CTEs and Recursion: MATERIALIZED vs NOT MATERIALIZED and the PG12 behaviour change, data-modifying CTEs and their snapshot semantics, and recursive queries over hierarchies and graphs. Read ctes.md
• Upsert and Merge: INSERT ... ON CONFLICT and the exact index it requires, MERGE from PG15, when each is correct, and the concurrency hazards of both. Read upsert-and-merge.md
• Pagination: Why OFFSET degrades and duplicates rows, keyset pagination with row comparison, the matching index, and paginating an unstable sort. Read pagination.md
• Dates and Times: timestamptz semantics and what it does not store, AT TIME ZONE, intervals, date_trunc in a target zone, generate_series for gap filling, and range types. Read dates-and-times.md
• Full-Text Search: tsvector and tsquery, always specifying a configuration, the generated-column pattern, ranking, highlighting, GIN vs GiST, and pg_trgm for fuzzy and substring matching. Read full-text-search.md
• Views: Views as an interface, updatable views, security_barrier and security_invoker, materialised views, REFRESH ... CONCURRENTLY and its unique-index requirement. Read views.md

Indexing

• Indexing Fundamentals: How the planner decides to use an index, selectivity, why a correct index is ignored, the cost of every index on writes, and when not to index at all. Read indexing-fundamentals.md
• Index Types: B-tree, hash, GIN, GiST, SP-GiST, BRIN and the vector index types, what each is for, and the operator classes that matter. Read index-types.md
• Composite and Covering Indexes: Column order and the leftmost-prefix rule, PG18 skip scans, INCLUDE, index-only scans and the visibility map, and how many indexes a table needs. Read composite-and-covering-indexes.md
• Partial and Expression Indexes: Indexing a hot subset, the exact-match requirement for expression indexes, case-insensitive lookup, enforcing conditional uniqueness, and immutability. Read partial-and-expression-indexes.md
• Index Maintenance: Finding unused, duplicate and invalid indexes, bloat, REINDEX CONCURRENTLY, and building indexes without locking out writes. Read index-maintenance.md

Performance

• EXPLAIN: Reading a plan properly, the options worth using, estimated vs actual rows as the primary signal, loops and per-loop costs, every scan and join node, and the numbers that mislead. Read explain.md
• Query Optimisation: The systematic method, anti-patterns that genuinely cost, the folklore that does not, sargability, and rewrites that reliably help. Read query-optimisation.md
• Statistics and the Planner: ANALYZE, what pg_stats holds, default_statistics_target, extended statistics for correlated columns, and the cost settings that are wrong by default on SSDs. Read statistics-and-planner.md
• Performance Triage: The diagnostic order, pg_stat_statements, pg_stat_activity and wait events, slow-query logging, auto_explain, and the table and index statistics views. Read performance-triage.md
• Bulk Operations: COPY, multi-row inserts, batching updates and deletes without long transactions, loading into a fresh table, and unlogged staging. Read bulk-operations.md
• When the database is not the problem. If triage here shows the queries are fast and the time is going somewhere else, the Java-side costs are a separate skill: JDBC batching and prepared-statement pooling, connection-pool sizing, fetch size, transaction scope, and the JPA lazy-versus-eager and N+1 traps. Use java-performance-developer, which owns establishing that the database is the constraint and defers query and schema work back to this skill.
• Partitioning: When partitioning helps and when it just adds complexity, range/list/hash, pruning vs constraint exclusion, the real constraint limitations, attach and detach, and retention. Read partitioning.md

Concurrency

• Transactions and Isolation: The three isolation levels Postgres implements, the anomalies each allows, serialisation failures and the mandatory retry loop, and why long transactions damage the whole database. Read transactions-and-isolation.md
• Locking: Table and row lock modes and what conflicts with what, FOR UPDATE vs FOR NO KEY UPDATE, SKIP LOCKED queues, advisory locks, deadlocks, and lock_timeout. Read locking.md
• MVCC and Vacuum: Tuple versioning and visibility, HOT updates and fillfactor, bloat and how to measure it, autovacuum tuning, freezing and transaction ID wraparound. Read mvcc-and-vacuum.md

Server-Side Code

• Functions and Triggers: When logic belongs in the database and when it does not, function volatility and why it changes plans, PL/pgSQL, trigger types and ordering, and the maintainability cost. Read functions-and-triggers.md

Production

• Migrations: The lock level of every common DDL statement, safe rewrites, expand/contract for zero downtime, lock_timeout and retry, backfilling large tables, and migration tooling. Read migrations.md
• Security and Roles: Role design and least privilege, GRANT and default privileges, ownership, SECURITY DEFINER and the search_path attack, and what the application role must never have. Read security-and-roles.md
• Row-Level Security: Policies, USING vs WITH CHECK, FORCE, the unconditional superuser and BYPASSRLS bypass, the session-variable-under-pooling landmine, performance, and testing that isolation actually holds. Read row-level-security.md
• Connections and Pooling: Why connections are expensive, sizing a pool, PgBouncer pool modes, what transaction mode breaks, prepared statements through a pooler, and timeouts. Read connections-and-pooling.md
• Configuration: The settings that actually matter and how to size them, memory, WAL and checkpoints, planner costs, autovacuum, timeouts, and what not to touch. Read configuration.md
• Backup and Recovery: Logical vs physical backups, pg_dump and its limits, PITR and WAL archiving, defining RPO and RTO, and why an untested restore is not a backup. Read backup-and-recovery.md
• Replication and Scaling: Streaming vs logical replication, read replicas and replication lag, synchronous commit, what to scale first, and the honest limits of a single primary. Read replication-and-scaling.md
• Extensions: The extensions worth installing, what each replaces, trust and availability on managed platforms, and version management. Read extensions.md

Integration and Testing

• Application Integration: How Postgres surfaces through JDBC, JPA/Hibernate and TypeScript clients, N+1 and how to see it, transaction boundaries, connection lifecycle, type mapping, and error handling with retries. Read application-integration.md
• Testing: Testing against real PostgreSQL rather than a substitute, Testcontainers, fixtures and isolation, testing migrations forwards and backwards, testing RLS, and performance regression tests. Read testing.md

Symptom Index

Start here when there is a concrete failure rather than a design question.

Checklist

• Best Practices Checklist: Every rule in one scannable list, plus the ones that cause the most damage. Use this for a review pass over an existing schema or query. Read checklist.md