postgres

name

postgres

description

Comprehensive PostgreSQL reference for developers and DBAs covering versions 14–18. Use whenever the user asks about PostgreSQL syntax, DDL/DML/DQL, joins, LATERAL, CTEs, window functions, GROUPING SETS, DISTINCT ON, RETURNING, ON CONFLICT, PL/pgSQL, functions, procedures, triggers, views, materialized views, indexes (B-tree/GIN/GiST/BRIN/Hash/Bloom), MVCC, VACUUM, autovacuum, WAL, TOAST, partitioning, replication (streaming/logical), backup, PITR, HA (Patroni/repmgr), pgBouncer, EXPLAIN ANALYZE, RLS, roles, extensions (pgvector, PostGIS, TimescaleDB, Citus, pg_trgm, pg_cron), JSON/JSONB, full-text search, UUID, timestamptz, COPY, system catalogs, collations, large objects, cursors, GUC, or any Postgres administration, performance, security, replication, backup, or recovery topic.

PostgreSQL Skill

This skill is the working reference for PostgreSQL 16, 17, and 18 (current baseline: PG16). Cross-version notes go back to PG14 where behavior changed; PG13 and earlier are end-of-life and out of scope. PG14 reaches end-of-life on 2026-11-12 — flag upgrade urgency when a user is on it.

The skill is provider-neutral: bare-metal/self-hosted, containers, and Kubernetes operators are all covered. Managed-service limitations are called out categorically ("most managed providers disable X") without naming or recommending any specific vendor.

Each topic lives in its own reference file under references/. SKILL.md routes a user question to the right reference; the reference contains the executable detail, version admonitions, and primary-source citations.

Usage Workflow

When this skill is loaded, follow these steps to answer the user's question:

1. Identify the topic. Match the user's question against the Routing Table below using the keyword column. For multi-topic questions (e.g. "the locking behavior of REINDEX CONCURRENTLY") select multiple files.
2. Load the matched reference file(s). Use the Read tool against references/NN-topic.md. Do not paraphrase from memory — the reference holds the authoritative version notes and source URLs.
3. Follow See Also. Each reference ends with a See Also section linking related files. Follow it for cross-cutting questions (e.g. a VACUUM question pulls in MVCC, autovacuum, and wraparound).
4. Answer using the file. Lead with the direct answer or code, then version notes via admonitions, then caveats, then citation URLs.
5. Never answer from memory alone when a reference file exists for the topic. The references are the source of truth for this skill; the model's training data is not version-specific enough.
6. Assume PG16 unless told otherwise. If the user does not state a version, answer for PG16 and add a one-line note for differences on PG17/PG18. Only ask the user which version they are on if the answer materially changes across supported versions (e.g. wraparound mechanics, planner statistics behavior, archive_command vs archive_library).

[!NOTE] When a Postgres question does not obviously match a single file, load these cross-cutting entry points first: references/102-skill-cookbook.md (symptom-driven recipes spanning multiple files), references/22-indexes-overview.md (index decision routing), references/56-explain.md (plan reading), references/64-system-catalogs.md (catalog introspection).

User Response Format

Structure every answer using this skill as follows:

• Lead with the direct answer or pattern. If a SQL snippet or psql command satisfies the question, put that first.
• Add version admonitions inline. Use > [!NOTE] PostgreSQL 17 for new-in-17 features, > [!WARNING] Removed/Deprecated for things gone (with the version they were removed in and the replacement).
• Call out managed-service limitations categorically when relevant. Phrase as "most managed providers disable X" — never name a specific provider.
• End with source links. Cite the official PostgreSQL docs pinned to the correct major version (e.g. https://www.postgresql.org/docs/16/sql-vacuum.html, not /docs/current/).
• For longer answers, use headers matching the reference file's section headings so the user can jump to the source.
• For version-sensitive answers, default to PG16 + add inline > [!NOTE] PG17 / > [!NOTE] PG18 deltas. Only ask the user to confirm version when the answer flips between supported majors.
• For any performance investigation document or slow-query guide, always include an explicit section explaining how to read EXPLAIN plans bottom-up: start at the deepest-indented leaf node (scans execute first), work upward through parents (joins, sorts, aggregates), reach the root last. Identify the first node where actual rows diverges 10× or more from estimated rows — that is the misestimate source; everything above it operates on bad cardinality.

Routing Table

Keywords are matched case-insensitively. A single file can match multiple keyword phrases.

Keywords	File	Scope
CREATE TABLE, ALTER TABLE, DROP TABLE, schema, sequence, generated column, identity column, IF NOT EXISTS	references/01-syntax-ddl.md	DDL syntax reference
SELECT, JOIN, LATERAL, subquery, UNION, INTERSECT, EXCEPT, DISTINCT ON, LIMIT, OFFSET, FETCH FIRST	references/02-syntax-dql.md	Query (DQL) reference
INSERT, UPDATE, DELETE, RETURNING, ON CONFLICT, upsert, MERGE, DEFAULT VALUES	references/03-syntax-dml.md	DML reference
WITH, CTE, recursive CTE, WITH RECURSIVE, MATERIALIZED, modifying CTE	references/04-ctes.md	Common Table Expressions
view, CREATE VIEW, updatable view, INSTEAD OF, security_barrier, security_invoker, materialized view, REFRESH MATERIALIZED VIEW CONCURRENTLY	references/05-views.md	Views and materialized views
CREATE FUNCTION, LANGUAGE, IMMUTABLE, STABLE, VOLATILE, PARALLEL SAFE, SECURITY DEFINER, RETURNS TABLE, polymorphic	references/06-functions.md	Functions
CREATE PROCEDURE, CALL, transaction control in procedure, COMMIT in procedure	references/07-procedures.md	Procedures
PL/pgSQL, plpgsql, DECLARE, BEGIN, EXCEPTION, RAISE, FOR LOOP, cursor in plpgsql, GET STACKED DIAGNOSTICS	references/08-plpgsql.md	PL/pgSQL deep dive
plpython3u, plperl, plperlu, pltcl, plv8, procedural language, untrusted PL	references/09-procedural-languages.md	Non-pgSQL procedural languages
EXECUTE, dynamic SQL, format(), quote_ident, quote_literal, SQL injection in plpgsql	references/10-dynamic-sql.md	Dynamic SQL
OVER, PARTITION BY, window function, ROWS BETWEEN, RANGE BETWEEN, GROUPS, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, RANK, DENSE_RANK, ROW_NUMBER, NTILE

Disambiguation Tips

Some terms route to multiple files — load both when in doubt.

Term	Primary file	Secondary file	Why
VACUUM blocking	28-vacuum-autovacuum.md	27-mvcc-internals.md, 29-transaction-id-wraparound.md	Why VACUUM exists is MVCC; what it must do is bounded by wraparound
EXPLAIN plan	56-explain.md	55-statistics-planner.md, 59-planner-tuning.md	Misestimates point to stats; tuning may need GUC changes
Index choice	22-indexes-overview.md	23-btree-indexes.md through 25-brin-hash-spgist-bloom-indexes.md	Overview routes to specifics
Deadlock	43-locking.md	42-isolation-levels.md	Predicate locks (SSI) cause different deadlocks
Replication is lagging	73-streaming-replication.md	75-replication-slots.md, 82-monitoring.md	Lag could be stream pressure or slot retention
Upgrade	86-pg-upgrade.md	87-major-version-upgrade.md	pg_upgrade is one strategy; the other file covers blue/green and logical-repl-based upgrades
JSON column performance	17-json-jsonb.md	24-gin-gist-indexes.md	JSONB indexing happens via GIN
Full-text search ranking	20-text-search.md	24-gin-gist-indexes.md	FTS uses GIN

Versioning & Provider Neutrality

• Target compatibility baseline: every SQL example must run on PostgreSQL 16 unless explicitly marked otherwise.
• Version-pin docs URLs. Cite https://www.postgresql.org/docs/16/..., not /docs/current/. The current alias moves with each annual release.
• Provider neutrality is mandatory. Never recommend a managed provider over another. When discussing managed-service limitations, phrase categorically ("most managed providers disable untrusted PLs"), not by name.

Sources

Primary documentation roots (re-fetch before citing in any reference file; version-pin URLs to the matching major):

• PostgreSQL 14 manual (cross-version notes only): https://www.postgresql.org/docs/14/index.html
• PostgreSQL 15 manual (cross-version notes only): https://www.postgresql.org/docs/15/index.html
• PostgreSQL 16 manual (default baseline): https://www.postgresql.org/docs/16/index.html
• PostgreSQL 17 manual: https://www.postgresql.org/docs/17/index.html
• PostgreSQL 18 manual: https://www.postgresql.org/docs/18/index.html
• Per-version release notes: https://www.postgresql.org/docs/release/
• Versioning + support policy: https://www.postgresql.org/support/versioning/
• PostgreSQL Wiki: https://wiki.postgresql.org/
• Source code mirror: https://github.com/postgres/postgres