matejformanek
UserTurn Claude Code into a long-term collaborator on PostgreSQL internals — cited knowledge corpus, agent skills, slash commands, and task-shaped scenarios for backend hacking.
Categories
Indexed Skills (24)
pg-feature-plan
Drop a heavy, citation-rich implementation plan for a scoped PostgreSQL backend feature — Phase 2 of the two-phase PG planner, the bridge from a brainstorm-with-picked-approach to /pg-implement. Names every src/backend or src/include file that must change with file:line cites at a pinned anchor, enumerates catalog / CATALOG_VERSION_NO / WAL / on-disk / lock-order / extension-ABI risks, proposes the test surface (regress / iso / TAP), structures the patch into independently-reviewable phases, picks a CommitFest landing strategy, and emits the plan-mode plan that /pg-implement executes phase-by-phase with plan-linked commits. **Use proactively whenever the user invokes /pg-plan, says "plan this PG feature", "make a plan for X in PG", "drop a heavy plan", "plan-mode plan for [PG feature]", "i picked option [A/B/C] in the brainstorm, now plan it", "we settled on the [approach] for the [PG topic], write me the phase plan with file:line cites", "spec-to-plan this pgsql-hackers thread", "shadow-implementation plan a
access-method-apis
Implement or modify a PostgreSQL pluggable index AM or table AM — covers IndexAmRoutine callbacks (ambuild, aminsert, amgettuple, amgetbitmap, ambulkdelete, amvacuumcleanup, amparallelrescan), TableAmRoutine callbacks (scan_begin, scan_getnextslot, tuple_insert, tuple_insert_speculative, slot_callbacks, index_fetch_*), opclass / strategy numbers / support functions, TID semantics for non-heap stores, genam.c and tableam.h wrappers, plus CREATE ACCESS METHOD + pg_am / pg_opclass / pg_amproc / pg_amop catalog registration. Use whenever a PG patch implements or modifies an index AM (btree variant, hash, gin, gist, spgist, brin, custom) or a table AM (heap, columnar, in-memory, custom store), or designs TID semantics for a non-heap storage. Skip user-facing "which index type should I use" / "should this be a btree or hash index" advice, EXPLAIN tuning questions, GIN / GIST / btree CONCURRENTLY operational guidance, application-side ORM index hints, and non-PG storage engines (MySQL InnoDB / MyRocks, RocksDB, Leve
bgworker-and-extensions
Register a PostgreSQL background worker or layer extension hooks on _PG_init — covers RegisterBackgroundWorker (static at shared_preload_libraries time) vs RegisterDynamicBackgroundWorker (runtime), the BackgroundWorker struct (bgw_flags BGWORKER_SHMEM_ACCESS / BGWORKER_BACKEND_DATABASE_CONNECTION, bgw_start_time, bgw_restart_time, bgw_main_arg, bgw_notify_pid), BackgroundWorkerInitializeConnection, the signal handler skeleton (SignalHandlerForConfigReload + die), the WaitLatch idiom with WL_EXIT_ON_PM_DEATH, and the GetBackgroundWorkerPid / WaitForBackgroundWorkerStartup / TerminateBackgroundWorker lifecycle calls. Use whenever a PG patch or extension registers a bgworker, writes a worker_main function, debugs a worker that fails to start or restart, layers ProcessUtility_hook / planner_hook / ExecutorStart_hook on _PG_init, or coordinates bgw_notify_pid signaling. Skip for Celery / Sidekiq / RQ / Resque / Kafka-consumer worker pools, AWS Lambda / Cloud Run background jobs, Cron / systemd timers, OS-level da
build-and-run
Build, install, initdb, and start PostgreSQL from source in the `dev/` clone for backend hacking — covers meson setup (PG ≥ 16 default) with cassert + debug flags, the autoconf ./configure fallback, ninja install, initdb + pg_ctl start / stop, PGDATA / PATH wiring, single-user mode for postmaster startup debugging, attaching gdb / lldb under the per-connection fork model, and -O0 -g3 debug builds. Use whenever a task involves compiling PG from source in dev/, running ninja install on the dev clone, initdb-ing a fresh data directory, starting or stopping the dev cluster via pg_ctl, picking between the debug profile (5432) and ASan profile (5433), or attaching a debugger to a forked backend. Skip for brew / apt / yum / Docker / k8s installation of release PG, Aurora / Cloud SQL / Supabase / Neon-managed PG provisioning, generic CMake / make / Bazel build questions, Linux-kernel builds, Node.js / Python / Go application builds, and pgAdmin / DBeaver client installation.
catalog-conventions
Add or modify a PostgreSQL system-catalog entry — covers adding a pg_proc.dat builtin function, pg_operator.dat operator, pg_type.dat type, pg_cast.dat cast, pg_opclass.dat opclass, adding a new column on pg_class / pg_aggregate / pg_attribute / etc., BKI bootstrap entries, OID assignment policy (genbki.pl, unused_oids), catversion (CATALOG_VERSION_NO) bumping, and regenerating postgres.bki. Use whenever a PG patch edits anything under src/include/catalog/ (.h or .dat), adds a SQL-visible builtin (function/operator/type/cast/opclass), assigns or recycles an OID, or bumps the catversion. Skip for user-level information_schema queries on a running server, Django / Alembic / Rails / Flyway / Liquibase migrations, Oracle DBA_* / MySQL information_schema / Snowflake INFORMATION_SCHEMA catalog questions, schema design and normalization advice, ER-diagram tooling, and adding constraints to user-application tables.
coding-style
Format C code to upstream PostgreSQL house style for src/backend / src/include — covers hard tabs at width 4, BSD braces, postgres.h-first include order, C99 subset rules (no // comments, no VLA, no mid-block declarations), naming conventions (struct typedef + field naming, function names matching typedef names), function-header comment format, ~78-char line length, and pgindent expectations. Use whenever a PG patch edits, adds, or reviews .c / .h files under source/src/ or dev/src/, or when a reviewer flags pgindent churn on a posted patch. Skip for Linux-kernel style (CodingStyle), clang-format / rustfmt / prettier / black / shfmt configurations, non-PG C / C++ style (Google style, LLVM style, Mozilla style), Java checkstyle, JavaScript ESLint, EditorConfig tuning, and general "what's a good C style" advice.
debugging
Debug a running PostgreSQL backend with lldb / gdb — covers attaching to the right forked backend via pg_backend_pid, single-user mode for postmaster / InitPostgres startup paths, breakpoints in ExecInitNode / heap_update / LWLockAcquire, elog(LOG, ...) printf-debugging, macOS / Linux core-dump configuration, and SQL-level inspection via pg_buffercache / pageinspect / pg_visibility. Use whenever the user wants to step through backend C code, attach lldb / gdb to a specific dev-cluster backend, chase a SIGSEGV / hang / loop / assertion-failure in PG, instrument with elog, inspect shmem / buffer / lock state at runtime, or read a PG core dump. Skip for app-level debugging in Node.js / Python / Ruby / Go / Rust (use language-specific debuggers), JVM hprof / jstack, browser DevTools, Chrome Tracing, application performance profiling, and production PG tuning (autovacuum / shared_buffers — that's DBA work).
error-handling
Write or review a PostgreSQL backend ereport / elog call — covers ereport vs elog, picking a SQLSTATE from errcodes.txt, errcode_for_file_access, errmsg / errdetail / errhint capitalisation rules, soft errors via escontext, PG_TRY / PG_CATCH longjmp-safe cleanup blocks, and the DEBUG / LOG / NOTICE / WARNING / ERROR / FATAL / PANIC elevel ladder. Use whenever a PG patch adds, edits, or reviews C in source/src/backend or contrib/ that reports an error or logs a message — picking elevel, choosing a SQLSTATE, formatting errmsg, wiring PG_TRY/PG_CATCH around a longjmp-unsafe block, or migrating a call to soft-error style. Skip for Python try/except, Go error returns, Rust Result / anyhow / thiserror, C++ exceptions, Java checked exceptions, Sentry / pino / Winston / log4j application logging, Oracle ORA-* / MySQL error codes, and general error-handling philosophy questions.
executor-and-planner
Edit the PostgreSQL executor or planner — covers src/backend/executor/ (nodeXxx.c, ExecInitNode/ExecProcNode/ExecEndNode/ExecReScan dispatch, PlanState lifecycle, EXPLAIN wiring) and src/backend/optimizer/ (Path → Plan via createplan.c, RelOptInfo lifecycle, add_path cost-dominance pruning, cost_* units in cost.h). Use whenever a PG patch adds or modifies a plan-node executor, introduces a new Path or Plan type, changes cost-model fields in cost.h, adds EXPLAIN output for a node, plumbs a node into execParallel.c, or tweaks join-path enumeration. Skip for end-user query tuning, EXPLAIN ANALYZE of a production query, work_mem / shared_buffers tuning, MySQL / MongoDB / BigQuery / Snowflake / DuckDB / Spark / Trino query engines, ORM query-builder optimization, and pandas / polars dataframe operations.
extension-development
Build a PostgreSQL backend loadable extension (.so / contrib module) — covers the .control file, the foo--1.0.sql install script + foo--1.0--1.1.sql upgrade scripts, PGXS vs meson build wiring, the `_PG_init` entry point, shared_preload_libraries vs LOAD vs CREATE EXTENSION load timing, chained hook installation (ProcessUtility_hook, planner_hook, ExecutorStart_hook), trusted vs untrusted extensions, and SQL-callable C function declarations (PG_FUNCTION_INFO_V1, PG_RETURN_*). Use whenever a PG extension is being written or modified — wiring _PG_init, registering hooks, picking PGXS vs meson, writing install/upgrade SQL, declaring CREATE FUNCTION ... LANGUAGE C, or marking the extension trusted. Skip for VS Code / Chrome / Firefox / Safari / browser extensions, NPM / pip / RubyGems / Cargo packages, IntelliJ / Eclipse plugins, and shell completion scripts.
fmgr-and-spi
Write a SQL-callable C function or call PostgreSQL fmgr / SPI from C — covers PG_FUNCTION_INFO_V1, PG_GETARG_* / PG_RETURN_*, PG_ARGISNULL, SRF_* set-returning function ValuePerCall and Materialize modes, composite / polymorphic returns, DirectFunctionCall* / OidFunctionCall* / FunctionCallInvoke fmgr entry points, plus SPI_connect / SPI_execute / SPI_prepare / SPI_finish, plan caching, SPI cursors, subxact rollback, and SPI return codes. Use whenever a PG patch or extension adds a `Datum foo(PG_FUNCTION_ARGS)` entry point, exposes a set-returning function, calls fmgr from backend C, or embeds SQL via SPI in a backend / trigger / PL handler. Skip for plpgsql / PL/Python / PL/Perl user-side function authoring, libpq / psycopg / JDBC / node-postgres client-side query execution, generic executor questions (use executor-and-planner), and non-PG embedded SQL (Oracle OCI, SQLite C API).
gucs-config
Add or modify a custom GUC variable in a PostgreSQL backend patch or extension — covers DefineCustomBoolVariable / IntVariable / RealVariable / StringVariable / EnumVariable, picking the right GucContext (PGC_POSTMASTER / PGC_SIGHUP / PGC_SUSET / PGC_USERSET), MarkGUCPrefixReserved, the check/assign/show hook trio, GUC_LIST_INPUT / GUC_LIST_QUOTE / GUC_UNIT_MS / GUC_UNIT_KB / GUC_REPORT / GUC_EXPLAIN flags, and string-GUC guc_malloc rules. Use whenever a PG patch or extension calls DefineCustom*Variable, picks a GucContext, wires check/assign/show hooks, debugs a placeholder GUC, or marks a GUC reserved via MarkGUCPrefixReserved. Skip for DBA tuning of shared_buffers / max_connections / work_mem in production, dotenv / Viper / Dynaconf / Spring @Value configuration libraries, Kubernetes ConfigMap, Terraform variables, and non-PG application config systems.
locking
Pick a PostgreSQL backend lock primitive (atomic / spinlock / LWLock / heavyweight / predicate / buffer-pin-content) or debug a lock-ordering bug, silent LWLock hang, deadlock-detector report, or a multixact / tuple-lock interaction — the six-layer PG lock taxonomy in operational form. Covers `pg_atomic_u32 / u64` ordering + memory barriers, `SpinLockAcquire / SpinLockRelease` (with the "no calls / no ereport / no CHECK_FOR_INTERRUPTS" rules), `LWLockAcquire LW_SHARED / LW_EXCLUSIVE` and tranches (lwlocklist.h + wait_event_names.txt), heavyweight `LockAcquire` / `LockRelationOid` / `LOCKTAG_*`, predicate (SSI) locks, buffer pin / content locks, the `BufferMapping / LockManager / PredicateLockManager` partition rank rules, multi-XID `HEAP_XMAX_IS_MULTI` interaction with `FOR UPDATE / FOR KEY SHARE`, parallel-worker lock-group semantics, and the lldb-attach recipe for silent-LWLock-deadlock diagnosis. **Use this skill proactively whenever the user is writing or reviewing C in `src/backend` or `dev/src/backend`
memory-contexts
Allocate memory in PostgreSQL backend C — pick the right MemoryContext and use palloc / palloc0 / pstrdup / psprintf correctly. Covers CurrentMemoryContext / TopMemoryContext / per-query / per-tuple / ExecutorState context choice, MemoryContextSwitchTo discipline, the OOM-throws-ereport contract (no NULL checks), pfree vs MemoryContextReset vs MemoryContextDelete, the AllocSet vs Slab vs Generation vs Bump context-type cheat sheet, and leak-scoping in long-running backends. Use whenever a PG patch or extension calls palloc / palloc0 / MemoryContextAlloc, creates or switches a MemoryContext, picks AllocSet vs Slab vs Generation vs Bump, or debugs a context-shaped leak. Skip for plain malloc / free / jemalloc / mimalloc / tcmalloc, JVM / Go / .NET GC tuning, Rust Box / Rc / Arc / lifetimes, shared_buffers / work_mem production tuning, valgrind / heaptrack on non-PG programs, and C++ smart pointers.
memory-keeping
Close out a pg-claude session — sync progress/STATE.md, progress/coverage.md, progress/files-examined.md, and append a sessions/ log entry whenever a session produced durable output (a new knowledge/idioms or knowledge/subsystems doc, a [verified-by-code] fact, a discovered gotcha, a file-by-file deep read, or a locked decision from pg-claude-plan.md §14). Use proactively when the user says "wrap up", "close out the session", "sync memory files", "record this gotcha", "we're done for the day", or "log this for next time" — and whenever durable output was just produced. Skip for PG MemoryContext / palloc / pfree internals questions (use memory-contexts), LangChain / LangGraph / LlamaIndex agent memory and vector stores, application memory leaks / valgrind / heaptrack, conversational chat history persistence, finding old Claude sessions (use find-session), and ChatGPT / Claude.ai conversation export.
parallel-query
Add parallel-aware C code in a PostgreSQL backend patch or extension — covers ParallelContext lifecycle (EnterParallelMode / CreateParallelContext / InitializeParallelDSM / LaunchParallelWorkers / WaitForParallelWorkersToFinish / DestroyParallelContext / ExitParallelMode), shm_toc DSM allocation + key lookup, ExecXXXInitializeDSM / ExecXXXInitializeWorker / ExecXXXReInitializeDSM hooks on plan nodes, and parallel-safety markings (pg_proc.proparallel s/r/u; PARALLEL SAFE / RESTRICTED / UNSAFE). Use whenever a PG patch or extension adds parallel-aware code, picks PARALLEL SAFE/RESTRICTED/UNSAFE for a SQL-callable function, extends execParallel.c, plumbs a worker shmem state via shm_toc, or debugs a parallel worker DSM/TOC issue. Skip for DBA tuning of max_parallel_workers GUC, OpenMP / CUDA / pthread / Tokio / Go-goroutine parallelism, JavaScript Promise.all and async-iter parallel fetches, generic worker-pool questions, and ML data-parallel training.
parser-and-nodes
Hack the PostgreSQL parser or add a new Node type — covers scan.l flex tokenizer, gram.y bison grammar, parse_analyze in analyze.c, kwlist.h keywords, and adding/modifying Node types in parsenodes.h / primnodes.h / plannodes.h that flow through copy/equal/out/read funcs auto-generated by gen_node_support.pl. Spans the AST→Query→Plan three-tree pipeline, mutator/walker conventions (expression_tree_walker, query_tree_mutator), the node-type reference table, List/lappend/foreach idioms, and shift-reduce conflict resolution. Use whenever a PG patch edits src/backend/parser/**, edits src/include/nodes/*.h, adds a new SQL keyword to kwlist.h, extends a parse-tree node, writes a tree mutator/walker, or resolves a gram.y shift-reduce conflict. Skip for non-PG parsers (ANTLR, Babel, nom, tree-sitter, LALRPOP, Yacc/Bison on other projects, LLVM IR / Clang AST), JSON / XML / YAML parsing, and executor-node additions (use executor-and-planner instead).
pg-implement
Execute a PostgreSQL `planning/<slug>/plan.md` phase-by-phase under upstream-grade discipline — Phase 3 of the PG planner suite. Per-phase commits, per-phase regress/iso/TAP runs, plan-linked commit messages, and a running notes log; enforces .claude/rules/pg-implement-discipline.md (R1-R12 — every commit references the plan slug + phase number; every code claim has a file:line cite; phase-end check must pass before the next phase starts). Use when the user says "/pg-implement <slug>", "implement the plan", "let's start implementing the X plan", "execute the planning/<slug>/plan.md", or has a finalized planning/<slug>/plan.md ready to execute. Skip for ad-hoc coding without a plan (no phase structure), non-PG implementation (app code, infra, scripts), the generic /implement flow (multi-project, doesn't enforce PG R1-R12 rules), and exploratory hacking where the plan is still being shaped (use pg-feature-plan instead).
pg-patch-review
Run a multi-agent comprehensive review of a PostgreSQL patch (CommitFest entry, GitHub PR, or local .patch file) — orchestrates the mechanical pre-amble (fetch + apply + build + regress / iso / TAP) and then fans out 5 critic sub-agents IN PARALLEL (architecture / invariants critic cross-checking knowledge/subsystems/*.md INV-* tags, breaking-change critic for on-disk / WAL / catalog / extension-ABI, test-coverage critic, style / commit-message critic, reviewer-reflex critic against knowledge/calibration/gap-catalog.md), then synthesizes one PG-house-style review email. Stage 3 verdict supports REJECT-A/B/C grades for design-level rejections. Use when the user says "/pg-review <CF# | PR# | patchfile>", "deep-review this patch", "comprehensive review of CF NNNN", "mailing-grade review of this patch", or "run the 5-critic fan-out on <patch>". Skip for non-PG patch review, self-review-before-mail (use patch-submission), the lightweight 7-phase walk (use review-checklist), generic GitHub PR review (use review-cha
pg-shadow-implement
Shadow-implement a pgsql-hackers thread to calibrate the pg-claude planner suite — read the COVER + discussion only (NOT the attached patch), produce a spec, run pg-feature-brainstorm / pg-feature-plan / pg-implement as-if the user asked for the feature, then fetch the upstream patch, diff against ours, score the gap, and emit comparison.md + skill-gaps.md. This is the "Phase E" calibration loop. Use when the user invokes `/pg-shadow <thread-url>`, says "shadow-implement that thread", "run Phase E against <thread>", "run a shadow against this hackers thread", or names a pgsql-hackers thread + asks how our planner would handle it. Do NOT trigger for real upstream patches we plan to send (use `pg-feature-plan` + `pg-implement` directly), for already-committed threads (re-implementing landed code is meaningless), or for non-PG calibration.
replication-overview
Hack or review PostgreSQL replication internals — covers physical streaming, archive shipping, logical decoding, logical replication PUB/SUB, and synchronous-commit machinery. Names walsender / walreceiver / replication slot / output plugin callbacks plus the relevant GUCs (wal_level, max_wal_senders, max_replication_slots, max_logical_replication_workers, primary_conninfo, primary_slot_name, synchronous_standby_names) and the source files behind each flavor. Use whenever a PG patch touches walsender, walreceiver, replication slots, logical decoding, output plugins, publications/subscriptions, conflict detection, sync replication, failover slots, pg_basebackup, or pg_receivewal — or when picking which mechanism fits a feature design. Skip for MySQL row-based / GTID replication, MongoDB replica sets, Cassandra hinted handoff, Kafka MirrorMaker / Confluent Replicator, Debezium / Flink CDC pipelines, AWS DMS, and Galera / Group Replication.
review-checklist
Review a PostgreSQL patch using the wiki's seven-phase checklist — covers Phase 0 reviewer-reflex gates + REJECT-A/B/C grade rubric for design rejections, plus the wiki's Phases 1-7 (apply/build, regress + check-world, pgindent, design fit, docs, comments, committer-readiness). Applies to reviewing someone else's pgsql-hackers / CommitFest submission OR self-reviewing your own patch before mailing it. Use whenever the user says "review this patch", "is it ready to send to hackers", "CF entry NNNN review", "pre-submission review", "REJECT this patch", or pastes a pgsql-hackers thread asking for a structured review. Skip for generic GitHub PR code review (app code), Terraform / Helm chart review, Python / Ruby / Rust / Go application code review, security audit (use security-review), API design review on REST / GraphQL endpoints, and documentation-only review for non-PG projects.
testing
Pick the right PostgreSQL test flavor for a core / contrib patch — covers pg_regress .sql / .out pairs, isolationtester specs and permutations for concurrency / deadlock races, TAP (PostgreSQL::Test::Cluster) for multi-node and pg_basebackup / replication / recovery scenarios, and src/test/modules for in-tree C test modules. Spans where test files live, how to wire them into the right schedule / meson.build, and how to run a single test fast (REGRESS_OPTS, --temp-instance, PROVE_FLAGS). Use whenever adding tests to a PG patch, reviewing a patch's test coverage, picking between regress / isolation / TAP for a new feature, debugging a regression diff, or asked "where should the test for X live". Skip for pytest / unittest / Jest / Vitest / Mocha / RSpec / JUnit / Go testing / Rust cargo-test app testing, pgbench / sysbench / HammerDB performance benchmarking, end-to-end / Selenium / Playwright web testing, and load / stress testing of production PG.
wal-and-xlog
Add or modify a PostgreSQL WAL / XLOG record — covers choosing built-in rmgr vs Generic WAL (generic_xlog.c) vs custom rmgr (RegisterCustomRmgr), the XLogInsert + XLogRegisterBuffer idiom, writing a correct redo function, FPI / hint-bit (MarkBufferDirtyHint) rules, full-page-write handling, updating rmgrdesc + pg_waldump output, and bumping XLOG_PAGE_MAGIC when format changes. Use whenever a PG patch emits XLogInsert, adds an access method that needs durability, writes or modifies a redo function, registers a custom rmgr via RegisterCustomRmgr, or reviews a patch that changes WAL-record formats. Skip for MySQL binlog / GTID / row-based logging, SQLite WAL journal mode, Kafka log compaction / segment retention, pgbackrest / barman / wal-g operational config, recovery.conf / standby.signal DBA-side configuration, and generic write-ahead-logging theory or ARIES algorithm questions.
Bio shown is the top-scored skill's repo description as a fallback — real GitHub bios land in a future update.