PostgreSQL Agent Skills

Pre-built skills and knowledge for AI coding agents working on PostgreSQL — from database application best practices to community patch work.

28 skills 8 steering files 41 best-practice rules CC0-1.0 · public domain GitHub Codeberg (source of truth)

This is a shared community resource. The content is dedicated to the public domain (CC0-1.0): use it, change it, ship it, no attribution required. Everyone is welcome to comment, contribute, correct, and extend it — see Contributing.

Install

It is one repository, one copy for every agent (Agent Skills Open Standard: <collection>/<skill>/SKILL.md with YAML front matter; no per-agent branches, no per-agent copies). Clone it once:

git clone https://github.com/gburd/postgres-agent-skills.git postgres-agent-skills

Then make it visible to your agent by putting it where that agent looks for skills (move the clone there, or symlink it). The content is identical; only the location differs:

AgentPut it here
Claude Code~/.claude/skills/postgres
Kiro (and Pi, which reads Kiro's dir)~/.kiro/skills/postgres
Any other MCP-aware agentwhatever skills directory it is configured to read

For example, straight into Claude Code's skills directory:

git clone https://github.com/gburd/postgres-agent-skills.git ~/.claude/skills/postgres

Finally, point your agent at AGENTS.md at the repo root (most read it automatically; CLAUDE.md is a stub that imports it). Topic collections: postgres/, tooling/, ai-life-skills/, steering/; shared knowledge in community/, generic/, examples/.

Skills

PostgreSQL skills (7)

Grouped by how you interact with the database. Load the persona that matches your role.

postgres-best-practices

PostgreSQL best practices for Postgres running anywhere — schema, queries, indexing, security, concurrency, data access, monitoring, operations, and advanced features. Load this skill BEFORE writing or changing anything that touches a Postgres database: creating or altering tables and columns (including choosing types), schema design, migrations and online DDL, row security policies, indexes, triggers, functions, queues and scheduled jobs, full-text and JSONB queries, partitioning, logical replication, connection pooling, and extension upgrades. Also load it when diagnosing slow queries, high CPU, timeouts, EXPLAIN plans, connection exhaustion, locking, deadlocks, bloat, autovacuum/wraparound trouble, or rows visible to the wrong tenant. Schema, migration, security, and SQL authoring tasks all need these rules — even a one-column change or a single query.

SKILL.md →

postgres-dba

PostgreSQL skills for the DBA who owns a running database: designing and altering schemas, choosing column types and keys, writing safe online migrations, managing roles and privileges, running backups and restores, and keeping the database healthy (autovacuum, bloat, connection limits). Use for schema changes, migrations, GRANT/REVOKE, backup/restore, and routine maintenance. For reviewing another team's design choices use postgres-overseer; for incident response use postgres-triage.

SKILL.md →

postgres-developer

PostgreSQL skills for developing code that runs inside the server: patches to core PostgreSQL (C, the backend, executor, planner, WAL, buffer manager) and server-side extensions. Covers the C coding conventions, patch-series discipline, the build-and-test loop (cassert, meson/make, per-commit verification), community submission norms, the security/trust model, and the internals gotchas that bite. Use when writing or reviewing a patch for upstream PostgreSQL or an extension, or debugging backend internals.

SKILL.md →

postgres-overseer

PostgreSQL skills for the reviewer/architect who vets other people's database work: schema design, PL/pgSQL, partitioning strategy, index selection and bloat, extension choice, and server + OS configuration. Use when reviewing a migration or design proposal, deciding whether an extension earns its place, judging an index set, or tuning server/OS parameters. This is the judgement seat; for executing changes use postgres-dba, for incidents use postgres-triage.

SKILL.md →

postgres-replication

PostgreSQL skills for the replication specialist: physical streaming replication and hot standbys, replication slots and slot invalidation, synchronous vs asynchronous, cascading and delayed replicas, logical replication (publications/subscriptions) and its restrictions, failover and switchover, and major-version upgrades via logical replication. Use when setting up, operating, debugging, or failing over any form of Postgres replication, or when a standby falls behind or a slot is lost.

SKILL.md →

postgres-triage

PostgreSQL skills for when things go wrong: incident response, crash recovery, decoding mysterious errors in the server log, diagnosing a hung or stuck database, finding the blocking query, corruption detection and repair, disk-full and out-of-memory events, and transaction-ID wraparound emergencies. Use when the database is down, slow, stuck, throwing errors you don't recognise, or refusing writes, and you need to stabilise it fast without making it worse.

SKILL.md →

postgres-user

PostgreSQL skills for someone who writes and runs queries against an existing database: SELECTs, DML, reporting, application queries. Use when writing or reviewing SQL, choosing how to filter/join/paginate, batching work, avoiding N+1 round trips, or wondering why a query is slow. This is the query-author's view; for schema design use postgres-dba, for reviewing someone else's design use postgres-overseer.

SKILL.md →

Tooling (12)

Integration with the tools you use while developing code for PostgreSQL. Secondary to the Postgres skills.

benchmark

Run a reproducible performance benchmark with a measure-correctly workflow, on the local machine or on a remote host over SSH (a LAN box, a lab server, or a cloud instance). Covers A/B methodology, warm-up, repetition and variance, controlling for drift, sizing the working set against cache, and collecting machine-readable results. Use for ANY benchmark — CPU, memory, IO, database, application. For picking a cloud instance to run on, pair with the `choose-instance` skill; for making the OS measurement-grade first, pair with `tune-os-for-benchmark`.

SKILL.md →

choose-instance

Choose a cloud instance type (or a short list of candidates) to run a benchmark on, across providers (AWS, GCP, Azure, Hetzner, Equinix Metal, OVH, and others). Covers matching the instance to what the benchmark stresses (CPU, memory bandwidth, IO, NUMA topology), when a bare-metal instance is required vs. a shared VM, sizing storage and network, spot/preemptible trade-offs, and cost control. Use before launching anything to benchmark on cloud infrastructure; pair with `benchmark` (methodology) and `tune-os-for-benchmark` (making it measurement-grade).

SKILL.md →

coccinelle

Run semantic patches on C/C++ via spatch. Use for AST-level refactoring, finding API usage patterns, enforcing coding standards structurally, migrating deprecated APIs, and finding null-deref / resource-leak patterns across large codebases. Pairs with the Pi `coccinelle` extension's pre-defined patterns (null-checks, error-handling, memory-leaks, resource-leaks, dead-code, simplify-conditionals, const-correctness).

SKILL.md →

flex-bison-to-lime

Port flex+bison parser/scanner pairs to the Lime parser generator. Covers mechanical bison-to-Lime conversion, hand-rolled scanners replacing flex, push-parser drivers, and reverse converters for tools that still consume bison output. Distilled from porting 12 PostgreSQL grammar/scanner pairs (~36k lines).

SKILL.md →

hegel

Write property-based tests using Hegel across Rust, C, C++, Go, and TypeScript projects. Use this skill whenever the user asks to write tests, add test coverage, or improve testing for functions, modules, or libraries — especially when the code has properties like round-trips, invariants, or contracts that hold across many inputs. Also triggers on: "property-based tests", "PBT", "hegel", "fuzz", "generative tests", "randomized testing", "test with random inputs", "shrinking", or when existing tests use proptest, quickcheck, rapid, gopter, rapidcheck, or fast-check.

SKILL.md →

pg-benchmark-rigor

The measurement discipline PostgreSQL committers apply before they believe a benchmark or a profile: preflight of CPU boost, idle states, pinning, THP, SSD state and counters; A/A noise floor; interleaved run order with waits; multiple builds against code-layout noise; the right summary statistic; and a STABLE / NOISY / UNSTABLE verdict instead of a bare number. Use whenever you benchmark, compare, or profile PostgreSQL (pgbench, a microbenchmark, perf), when results look unstable, or when deciding whether a cloud host or owned hardware can answer the question. Layers on the generic `benchmark` and `tune-os-for-benchmark` skills.

SKILL.md →

pg-ddx-research

Research the PostgreSQL community and codebase through the pg.ddx.io MCP server (The Database Development neXus: for PostgreSQL, https://pg.ddx.io/mcp/). Use when investigating PostgreSQL internals or extension development, reviewing or writing patches, understanding a historical design decision, finding prior art, or tracing a feature through the pgsql-hackers archive and the upstream git history. This is the rule of first resort for "why was this designed this way?", "has this been proposed before?", "who else hit this?", and "what discussion led to this commit?".

SKILL.md →

pg-numa-benchmark

Run PostgreSQL buffer-manager (clock-sweep) A/B benchmarks on NUMA hardware: stock vs patched, under memory pressure (shared_buffers < dataset), with pgbench and HammerDB TPC-C. Covers the PostgreSQL-specific build, data load, shared_buffers sizing, --with-libnuma, run matrix, and result collection. Use when benchmarking PostgreSQL buffer-manager changes on NUMA hardware. The generic substrate — choosing an instance, OS tuning, and the A/B measurement methodology — lives in the choose-instance, tune-os-for-benchmark, and benchmark skills; this layers the PostgreSQL specifics on top.

SKILL.md →

pg-patch-upkeep

Upkeep of a PostgreSQL patch branch that is out for review: read the pgsql-hackers thread, the cfbot results and the branch's PR CI for anything that needs an answer or a fix, address it, rebase on origin/master, pass test-world, and force-push the branch. Use when asked to check a patch's thread, cfbot or CI for activity, to rebase a commitfest branch and push it, or to bring a patch series current before a new version.

SKILL.md →

review-as-author

Predict how a named pgsql-hackers reviewer (a PostgreSQL committer such as Tom Lane or Andres Freund, or any contributor) would review a patch, commit or design, with every point grounded in that person's own quoted messages or commits, through the pg.ddx.io MCP tools. Use to anticipate a committer's objections before posting a patch, for an author's-perspective pre-review, or when asked to review something "as X would". Evidence first: a point with no quote or commit of theirs is not attributed to them.

SKILL.md →

review-diff

Review a git diff for regressions, style violations, complexity, and security issues. Use for cross-tool monitoring — one agent reviews another's changes.

SKILL.md →

tune-os-for-benchmark

Configure an operating system so a benchmark run is accurate and reproducible, on any OS: Linux, the BSDs (FreeBSD/OpenBSD/NetBSD), illumos (OmniOS/SmartOS), and Windows. Covers CPU frequency scaling and governors, transparent hugepages and explicit hugepages, NUMA placement, interrupt and thread affinity, filesystem and mount choice, disabling background jitter (auto-update, indexing, telemetry), and verifying the machine is actually quiesced. Use after choosing a host/instance and before the first measured run; pair with `benchmark` and `choose-instance`.

SKILL.md →

AI life skills (9)

Generic, domain-agnostic agent habits, offered as a standalone set.

btw

Quick aside or observation without derailing the current task. Use when the user says "btw" or wants to note something without changing context.

SKILL.md →

checkpoint

Summarize current progress, what's done, what's left, and any blockers. Use between tasks or when context is getting long.

SKILL.md →

dream

Brainstorm freely about an idea, architecture, or approach without writing any code. Use when the user wants to explore possibilities before committing.

SKILL.md →

maintain-docs

Audit and update CLAUDE.md/AGENTS.md files. Compares documented state against actual project state and suggests updates.

SKILL.md →

persistent-memory

Choose and use a local persistent memory store so lessons, corrections, and project knowledge survive across sessions. Tool-agnostic: works with memelord, an MCP knowledge-graph memory server, a plain notes file, or any similar store. Use at the START of any non-trivial or long-running task to recall prior context, when you self-correct or the user corrects you, when you learn something durable about a codebase, and before finishing a task to record what was learned. Also use to initialise memory in a project that has none.

SKILL.md →

stop-slop

Remove the predictable tells of AI-written prose. Use when drafting, editing, or reviewing any text that ships under a human's name: commit messages, PR descriptions, docs, issue comments, emails, design notes. Produces prose that reads as written by a person who knows the subject, not generated.

SKILL.md →

subagent-teams

Run coordinated teams of sub-agents to parallelize work and raise quality through independent review. Use when a task is big enough to split across sub-agents, when independent review of a change would catch what the implementer cannot see, or when fanning out genuinely independent work. Covers the worker/reviewer/re-reviewer pattern, when to parallelize vs. stay serial, how to brief a sub-agent, and the harness constraints that make a sub-agent die instantly.

SKILL.md →

think-hard

Deep analysis mode. Use when facing a complex bug, design decision, or problem that needs careful step-by-step reasoning before acting.

SKILL.md →

watchdog

Comprehensive project health check. Run periodically to catch drift, regressions, and quality issues. Checks build, tests, lint, format, and architecture constraints.

SKILL.md →

Steering

Always-on rules an agent reads at the start of every session (as opposed to skills, which load on demand). The universal files load everywhere; the domain files (e.g. postgresql) are opt-in per project. See steering/README.md for how to wire these into Claude Code, Kiro, Pi, or any AGENTS.md-based agent, and how to set up your environment for the best agentic results.

FileGoverns
coding-standardsHow to write code in this project. Correctness and clarity first; these are
must-rulesNon-negotiable rules for any agent working in or around this project. If a
opinionsSubjective preferences: how generated output (prose, commits, PRs, docs,
postgresqlRequired reading for any PostgreSQL task: a patch, an internals question, a
prose-mechanicsConventions for anything that ships under the human's name and outlives the
toolsPrefer fast, structured tools over their slower counterparts. These are
voiceDirect, calibrated, evidence-bound, and written for humans. Everything you
workflowThe order of operations for making a change.

postgres-best-practices rules

The postgres/best-practices library is the shared ruleset the role personas cite. Each rule names an antipattern, shows the fix in runnable SQL, and cites the canonical PostgreSQL documentation that informed it. 41 rules in 9 categories:

Query performance (`query-`) CRITICAL · 6 rules
RuleWhat it says
query-missing-indexesindex WHERE and JOIN columns
query-composite-indexesmulti-column indexes and column order
query-covering-indexesINCLUDE columns for index-only scans
query-partial-indexesindex only the rows you query
query-index-typesB-tree vs GIN vs GiST vs BRIN vs Hash vs SP-GiST
query-not-in-nullthe `NOT IN (subquery)` NULL trap
Connection management (`conn-`) CRITICAL · 5 rules
RuleWhat it says
conn-poolingpool, don't open a connection per request
conn-pool-modestransaction vs session pooling
conn-prepared-statementsprepared statements under a pooler
conn-limitssize `max_connections` to RAM, not optimism
conn-idle-timeoutreclaim idle and idle-in-transaction connections
Security (`security-`) CRITICAL · 3 rules
RuleWhat it says
security-rls-basicsenable row security for multi-tenant data
security-rls-performancewrite policies that stay fast
security-privilegesleast privilege; never run the app as superuser
Schema design & correctness (`schema-`) HIGH · 8 rules
RuleWhat it says
schema-data-typestext/numeric/boolean over varchar(n)/float/strings
schema-timestamptzalways `timestamptz`, never bare `timestamp`
schema-primary-keysidentity and UUIDv7 over serial and UUIDv4
schema-constraintsadd constraints idempotently (no ADD IF NOT EXISTS)
schema-foreign-key-indexesPostgres does not index FKs for you
schema-partitioningwhen and how to partition large tables
schema-enum-vs-lookupenum vs lookup table trade-offs
schema-lowercase-identifiersunquoted snake_case for portability
Concurrency & locking (`lock-`) MEDIUM-HIGH · 4 rules
RuleWhat it says
lock-short-transactionshold locks for milliseconds, not API calls
lock-deadlock-preventionlock in a consistent order
lock-skip-lockedSKIP LOCKED work queues
lock-advisoryadvisory locks for app-level mutual exclusion
Data access patterns (`data-`) MEDIUM · 4 rules
RuleWhat it says
data-n-plus-onebatch with ANY(array) or a JOIN
data-batch-insertsmulti-row INSERT and COPY
data-paginationkeyset pagination over OFFSET
data-upsertINSERT ... ON CONFLICT done right
Monitoring & diagnostics (`monitor-`) MEDIUM · 4 rules
RuleWhat it says
monitor-explain-analyzeread EXPLAIN (ANALYZE, BUFFERS)
monitor-pg-stat-statementstriage top queries (and its cumulative trap)
monitor-bloatdetect table and index bloat
monitor-locksfind the blocking query
Operations & maintenance (`ops-`) MEDIUM-HIGH · 5 rules
RuleWhat it says
ops-migration-safetyonline DDL without locking out the app
ops-timeout-hygienelock_timeout / statement_timeout / idle timeouts
ops-autovacuumtune autovacuum; watch XID wraparound
ops-extension-upgradesupgrade extensions without breaking the database
ops-logical-replicationthe restrictions that bite in production
Advanced features (`advanced-`) LOW-MEDIUM · 2 rules
RuleWhat it says
advanced-full-text-searchtsvector + GIN over LIKE '%...%'
advanced-jsonb-indexingGIN operator classes and expression indexes

Suggested external skills & MCPs

These third-party skill sets and MCP servers are worth adding but are not bundled here (different owners and licenses). Install the ones you want:

Sources & cross-references

Every rule and skill is written from scratch in original words and dedicated to the public domain. The ideas are drawn from, and cross-referenced to, these authoritative sources so you can connect each skill back to its origin:

Contributing — everyone is welcome

This is a shared resource for the whole PostgreSQL community. Corrections, additions, removals, and arguments are all welcome.