Software Development Skills

Implement or modify a PostgreSQL pluggable index AM or table AM — covers IndexAmRoutine callbacks (ambuild, aminsert, amgettuple, amgetbitmap, ambulkdelete, amvacuumcleanup, amparallelrescan), TableAmRoutine callbacks (scanbegin, scangetnextslot, tupleinsert, tupleinsertspeculative, slotcallbacks, indexfetch), opclass / strategy numbers / support functions, TID semantics for non-heap stores, genam.c and tableam.h wrappers, plus CREATE ACCESS METHOD + pgam / pgopclass / pgamproc / pgamop 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, LevelDB).

PostgreSQL's async I/O subsystem + the read-stream API — src/backend/storage/aio/ (introduced PG 17, matured PG 18). Loads when the user asks about the readstream API, AIO methods (sync / worker / iouring), how sequential + bitmap scans issue prefetch, migrating a code path from StartReadBuffer+WaitReadBuffer to readstream, adding a new read-stream consumer, tuning iomaxconcurrency / ioworkers / iomethod, or debugging AIO-related buildfarm failures. Also for the read-stream callbacks (per-block-lookup / per-buffer-release), completion callbacks (aiocallback.c), and the iouring linkage. Skip when the ask is about client-side async (libpq) or about the WAL writer / walreceiver (those have their own I/O paths).

PostgreSQL's backup + point-in-time recovery — pgbasebackup + WAL archiving (archivecommand / archivelibrary) + restorecommand + pgwalreplay targets + the pgbackupstart/stop API + pgreceivewal. Covers src/backend/backup/ (basebackup server code) + src/backend/postmaster/pgarch.c (archiver aux process) + src/backend/access/transam/xlogrecovery.c (recovery driver). Loads when the user asks about how base backups work, WAL archiving vs streaming replication, PITR targets (recoverytarget), pgbackupstart/pgbackupstop low-level API, .backup files, restorecommand semantics, or incremental backup (PG 17+ with WALSUMMARIZED). Skip when the ask is about pgdump (logical dump — different tool) or about physical replication streaming (physical-replication — related but sibling).

Register a PostgreSQL background worker or layer extension hooks on PGinit — covers RegisterBackgroundWorker (static at sharedpreloadlibraries time) vs RegisterDynamicBackgroundWorker (runtime), the BackgroundWorker struct (bgwflags BGWORKERSHMEMACCESS / BGWORKERBACKENDDATABASECONNECTION, bgwstarttime, bgwrestarttime, bgwmainarg, bgwnotifypid), BackgroundWorkerInitializeConnection, the signal handler skeleton (SignalHandlerForConfigReload + die), the WaitLatch idiom with WLEXITONPMDEATH, and the GetBackgroundWorkerPid / WaitForBackgroundWorkerStartup / TerminateBackgroundWorker lifecycle calls. Use whenever a PG patch or extension registers a bgworker, writes a workermain function, debugs a worker that fails to start or restart, layers ProcessUtilityhook / plannerhook / ExecutorStarthook on PGinit, or coordinates bgwnotifypid signaling. Skip for Celery / Sidekiq / RQ / Resque / Kafka-consumer worker pools, AWS Lambda / Cloud Run background jobs, Cron / systemd timers, OS-level daemons, and Kubernetes Jobs / CronJobs.

PostgreSQL's shared-buffer manager — src/backend/storage/buffer/bufmgr.c — the 8 KB shared-memory page cache underneath every heap/index read. Covers pin/unpin lifecycle, BufferDesc + hash table, clock-sweep replacement, BufferAccessStrategy (ring buffers for VACUUM / bulk read / bulk write), local buffers for temp tables, and pinning discipline. Loads when the user asks about sharedbuffers sizing, pin/unpin semantics, buffer-pin deadlock scenarios, adding a new BufferAccessStrategy ring, BufferDesc state bits (VALID / DIRTY / IOINPROGRESS), the clock-sweep replacement algorithm, or debugging "buffer leak" WARNINGs. Skip when the ask is about the AIO layer sitting between shmem and disk (see aio-readstream) or about visibility/hint bits (see the sibling idiom hint-bits-setbufferdirty).

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 + pgctl 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 pgctl, 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.

Add or modify a PostgreSQL system-catalog entry — covers adding a pgproc.dat builtin function, pgoperator.dat operator, pgtype.dat type, pgcast.dat cast, pgopclass.dat opclass, adding a new column on pgclass / pgaggregate / pgattribute / etc., BKI bootstrap entries, OID assignment policy (genbki.pl, unusedoids), catversion (CATALOGVERSIONNO) 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 informationschema queries on a running server, Django / Alembic / Rails / Flyway / Liquibase migrations, Oracle DBA / MySQL informationschema / Snowflake INFORMATIONSCHEMA catalog questions, schema design and normalization advice, ER-diagram tooling, and adding constraints to user-application tables.

Coding style, testing, and PR guidelines for the Kubb ecosystem. Use when writing or reviewing code for the Kubb ecosystem.

PostgreSQL's collation-provider abstraction — the 3-way split between builtin (PG-owned), icu (ICU library), and libc (OS-provided). Covers src/backend/utils/adt/pglocale.c (the dispatcher) + pglocalebuiltin.c / pglocaleicu.c / pglocalelibc.c (per-provider implementations) plus the encoding conversion layer (src/backend/utils/mb/). Loads when the user asks about CREATE COLLATION, provider semantics, LCCOLLATE / LCCTYPE, ICU integration + version tracking, the builtin provider added PG 17, collversion upgrade detection, why an ORDER BY differs between PGs on the same OS, pgcutf8 builtin, or multibyte encoding conversion. Skip when the ask is about client encoding (server-client convert is included but psql \encoding is client-side), full-text search dictionaries (different subsystem tsearch), or CREATE TEXT SEARCH CONFIGURATION.

Write a commit message in upstream PostgreSQL house style — imperative single-line title (NO conventional-commits feat:/fix: prefix), paragraph body wrapped near 76 chars, Author / Reviewed-by / Reported-by / Discussion / Backpatch-through trailer block; explicitly NO Co-authored-by, NO emoji, NO ticket numbers, NO bullet lists, NO Signed-off-by. Use proactively when drafting a commit log for a PG patch destined for master. Do NOT trigger for conventional-commits messages, Linux kernel Signed-off-by commits, Rust/JS/Go project commits, or GitHub PR descriptions.

Understand and modify the COPY FROM / COPY TO family in PostgreSQL — src/backend/commands/copy.c. Loads when the user asks about COPY-command internals, bulk-load performance, custom COPY formats, extending COPY options, COPY error-handling / ONERROR, COPY progress reporting, tablesync (which is built on COPY), or any patch touching copy.c / copyfrom.c / copyfromparse.c / copyto.c. Also use when investigating why COPY behaves differently from INSERT (partition routing, defaults, generated columns, extended statistics, RLS, triggers, DEFAULT expressions), and when adding a new bulk-load code path that reuses the COPY machinery. Skip when the question is about pgdump / psql \copy (client-side) or logical-replication apply (different subsystem — but note that tablesync's initial copy IS this machinery, see knowledge/idioms/tablesync-initial-copy.md).

Traverse the pg-claude knowledge graph to answer "what does this feature/file/pattern touch across the corpus?" — pulls scenarios, idioms, call-site file examples, sibling patterns, subsystem ownership, and analogous past features from planning/ + sessions/ into a single chain map. Uses the graph edges built by scripts/populate-idiom-callsites.py (idiom → files) and scripts/build-scenario-idiom-matrix.py (scenario ↔ idiom bidirectional). Use proactively when brainstorming a new PG feature, planning §3 file table, investigating an unfamiliar subsystem, or trying to find "have we touched something like this before?". Also use inside pg-feature-brainstorm step 1 (subsystem framing) and pg-feature-plan before §3 to seed the file list from existing evidence rather than pure grep. Skip when you already have the anchor's downstream chain memorized, when the task is a one-file bug fix, or when the ask is about non-PG code.

PostgreSQL's Custom Scan / Custom Path API — the pluggable executor node interface that lets extensions add new physical operators (custom joins, custom aggregates, custom scan providers like columnar backends). Covers CustomScanMethods + CustomExecMethods + CustomPathMethods in src/backend/executor/nodeCustom.c + src/backend/optimizer/util/plannodes.c (for CustomPath / CustomScan). Loads when the user asks about custom scan providers, how citus / timescaledb / greenplum-style columnar backends integrate, CustomPath cost registration, RegisterCustomScanMethods, or "how does the planner know about my extension's node type". Skip when the ask is about extending the built-in AM (table AM / index AM — different pluggable interface) or about GetForeignPaths (FDW — sibling but has its own skill).

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, addpath 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, workmem / sharedbuffers tuning, MySQL / MongoDB / BigQuery / Snowflake / DuckDB / Spark / Trino query engines, ORM query-builder optimization, and pandas / polars dataframe operations.

PostgreSQL's extended statistics — CREATE STATISTICS, pgstatisticext, pgstatisticextdata — the 4 stat kinds (dependencies / ndistinct / mcv / expressions) that help the planner handle correlated columns, multi-column NDVs, and expression cardinality. Covers src/backend/statistics/extendedstats.c + dependencies.c + mcv.c + mvdistinct.c. Loads when the user asks about extended stats syntax, when to CREATE STATISTICS, why a query has bad estimates on correlated columns, ndistinct for GROUP BY estimation, MCV lists in the extended-stats context, or the planner's clauselistselectivityext selectivity path. Skip when the ask is about pgstatistic (single-column stats — the sibling; see analyze-block-and-reservoir-sampling idiom) or about ANALYZE mechanics (that's analyze.c — different code path).

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 PGinit entry point, sharedpreloadlibraries vs LOAD vs CREATE EXTENSION load timing, chained hook installation (ProcessUtilityhook, plannerhook, ExecutorStarthook), trusted vs untrusted extensions, and SQL-callable C function declarations (PGFUNCTIONINFOV1, PGRETURN). Use whenever a PG extension is being written or modified — wiring PGinit, 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.

Building or modifying a Foreign Data Wrapper (FDW) — the SQL/MED interface for accessing external data as if it were PG tables. Covers the FdwRoutine callback set (GetForeignRelSize / GetForeignPaths / GetForeignPlan / BeginForeignScan / IterateForeignScan / ReScanForeignScan / EndForeignScan for scans, BeginForeignInsert / ExecForeignInsert / ... for DML), the postgresfdw reference implementation, plan pushdown (WHERE / JOIN / AGG / LIMIT), extended-explain support, and the FDW-related core code (src/backend/foreign/foreign.c, foreign-table catalog, user mappings). Loads when the user asks about writing an FDW, CREATE FOREIGN TABLE, why postgresfdw is fast, pushdown decisions (useremoteestimate, fdwstartupcost), user mappings and password security (passwordrequired), the loopback-back-to-self bypass-RLS pattern from postgresfdw, or adding a new pushdown-shape. Skip when the ask is about specific non-postgresfdw wrappers (filefdw, oraclefdw external, mysqlfdw external) — those are separate implementations.

Write a SQL-callable C function or call PostgreSQL fmgr / SPI from C — covers PGFUNCTIONINFOV1, PGGETARG / PGRETURN, PGARGISNULL, SRF set-returning function ValuePerCall and Materialize modes, composite / polymorphic returns, DirectFunctionCall / OidFunctionCall / FunctionCallInvoke fmgr entry points, plus SPIconnect / SPIexecute / SPIprepare / SPIfinish, plan caching, SPI cursors, subxact rollback, and SPI return codes. Use whenever a PG patch or extension adds a Datum foo(PGFUNCTIONARGS) 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).

PostgreSQL's Free Space Map (FSM) — the tree-of-pages that tracks per-heap-page free space so INSERT / COPY / heapmultiinsert can find a page with room without scanning the whole relation. Covers src/backend/storage/freespace/ (freespace.c + fsmpage.c + indexfsm.c), the tree layout (leaf-page nodes + 2 upper levels), category encoding (bucketed free-byte counts), the FSM lock discipline, VACUUM's post-scan FreeSpaceMapVacuum, and index FSM's simpler use case. Skip when the ask is about visibility map (VM — sibling but different subsystem) or about pgfreespacemap contrib module (that's the SQL introspection wrapper).

Add or modify a custom GUC variable in a PostgreSQL backend patch or extension — covers DefineCustomBoolVariable / IntVariable / RealVariable / StringVariable / EnumVariable, picking the right GucContext (PGCPOSTMASTER / PGCSIGHUP / PGCSUSET / PGCUSERSET), MarkGUCPrefixReserved, the check/assign/show hook trio, GUCLISTINPUT / GUCLISTQUOTE / GUCUNITMS / GUCUNITKB / GUCREPORT / GUCEXPLAIN flags, and string-GUC gucmalloc rules. Use whenever a PG patch or extension calls DefineCustomVariable, picks a GucContext, wires check/assign/show hooks, debugs a placeholder GUC, or marks a GUC reserved via MarkGUCPrefixReserved. Skip for DBA tuning of sharedbuffers / maxconnections / workmem in production, dotenv / Viper / Dynaconf / Spring @Value configuration libraries, Kubernetes ConfigMap, Terraform variables, and non-PG application config systems.

PostgreSQL's SQL/JSON support — jsonpath (SQL:2016 path language) + jsonb (binary-encoded JSON) + the SQL/JSON operators like ->, ->>, @?, @@, jsonbpathquery. Covers src/backend/utils/adt/jsonb.c (jsonb storage + operators + GIN opclass + subscripting) and jsonpath.c (path language parser + executor). Loads when the user asks about jsonpath semantics, path variables ($, @, current), lax vs strict mode, predicate expressions, jsonbpathquery / jsonbpathexists, JSONTABLE (SQL:2023 / PG 17+), jsonbgin operator class, memory management in jsonpathexec (Tom Lane's 5a2043bf713 rewrite), or "why is my JSON search slow" (usually GIN or path complexity). Skip when the ask is about the older json type (json.c is separate — text representation) or about JSON output formats (that's formattype territory).

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 pgatomicu32 / u64 ordering + memory barriers, SpinLockAcquire / SpinLockRelease (with the "no calls / no ereport / no CHECKFORINTERRUPTS" rules), LWLockAcquire LWSHARED / LWEXCLUSIVE and tranches (lwlocklist.h + waiteventnames.txt), heavyweight LockAcquire / LockRelationOid / LOCKTAG, predicate (SSI) locks, buffer pin / content locks, the BufferMapping / LockManager / PredicateLockManager partition rank rules, multi-XID HEAPXMAXISMULTI 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 that touches shared memory (MainLWLockArray, PGPROC, ProcArray, BufferDesc, SLRU, any Shmem allocation), adds a new LWLock tranche (RequestNamedLWLockTranche / LWLockNewTrancheId / PGLWLOCK / PGLWLOCKTRANCHE), picks between spinlock / LWLock / atomic / heavyweight for new shmem state, asks about LWLock partition rank order, debugs an LWLock hang where pgstatactivity shows waiteventtype='LWLock' but no deadlock detected ever fires, hits an assertion like IsRelationExtensionLockHeld, gets bitten by HEAPXMAXISMULTI and GetMultiXactIdMembers, asks about parallel-worker lock-group conflicts, or reviews a patch that adds shmem state / new wait events — even when the user doesn't use the literal word "lock". Skip for pthreadmutext / std::mutex / Java synchronized / ReentrantLock / Go sync.Mutex / Rust std::sync::Mutex / parkinglot / tokio::sync::Mutex / C lock(obj) / Monitor.Enter, Redis pessimistic / Redlock / ZooKeeper / etcd / Chubby distributed locks, ORM optimistic locking (version int column), user-level / application-side SELECT FOR UPDATE design questions (how to model inventory claim), application-level lock-manager design (key→holder hash with TTLs), and general "what's the right lock primitive" non-PG advice.

PostgreSQL's logical replication — publisher-side WAL decoding + subscriber-side apply — plus everything under src/backend/replication/logical/. Loads when the user asks about logical decoding, pglogicalslotgetchanges, publications / subscriptions, output plugins (pgoutput / testdecoding), reorder buffer, historic snapshots (SnapBuild), the apply worker + parallel apply, conflict resolution (PG 18+ conflict tracking), replication origins, replication slots (physical vs logical, slot.c + slotsync.c), streaming mode for in-progress transactions, tablesync's initial COPY, or debugging apply-worker crashes / slot advancement / catalogxmin retention. Skip when the ask is about physical streaming replication (walsender/walreceiver, replication/basebackup.c, replication/walsender.c in isolation) — those are the sibling replication subsystem, mostly disjoint code paths.

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, sharedbuffers / workmem production tuning, valgrind / heaptrack on non-PG programs, and C++ smart pointers.