PostgreSQL-Claude-Skill-Package

Use when reviewing a database migration for production safety before it runs, or auditing a migration file in a pull request. Prevents shipping a plain CREATE INDEX that locks writes, a full-rewrite ALTER COLUMN TYPE, a breaking DROP/RENAME without expand-contract, and migrations that stall all traffic without lock_timeout. Covers a deterministic migration safety checklist (CONCURRENTLY, rewrite-triggering ALTERs, NOT VALID + VALIDATE, breaking changes, lock_timeout, chunked backfill, CONCURRENTLY-in-transaction), severity grading, safe-rewrite suggestions, SAFE / NEEDS-CHANGES / UNSAFE verdict. Keywords: migration review, migration safety, zero downtime, review migration, CREATE INDEX CONCURRENTLY, ALTER TABLE lock, breaking change, expand contract, is this migration safe, will this migration lock the table, migration checklist, schema change review

Use when reviewing a PostgreSQL schema or DDL design before it ships, or auditing an existing schema for structural problems. Prevents shipping tables with no primary key, un-indexed foreign keys, multi-tenant tables with no RLS strategy, SERIAL where IDENTITY belongs, and timestamp instead of timestamptz for event times. Covers a deterministic schema review checklist (snake_case naming, IDENTITY vs SERIAL, missing PK, FK indexing, redundant indexes, RLS coverage, jsonb vs CSV-in-text, timestamptz, normalization), severity grading, cite-sibling-skill per finding, GOOD / NEEDS-WORK / POOR verdict. Keywords: schema review, DDL review, database design review, table design, naming convention, missing primary key, foreign key index, RLS coverage, normalization, IDENTITY vs serial, review my schema, is this schema good, audit database design, schema checklist

Use when a replica is behind, a replication slot is bloating WAL, or a logical subscription is stuck. Prevents primary disk filling from an inactive slot, discovering lag only at failover time, and ignoring a stuck subscription that never self-heals after a hard conflict. Covers streaming lag monitoring (pg_stat_replication write/flush/replay lag, LSN diff), replication slot bloat (pg_replication_slots), max_slot_wal_keep_size safety cap, logical subscription lag + stuck-subscription recovery (conflict resolution, SKIP), lag root causes. Keywords: replication lag, pg_stat_replication, replay_lag, replication slot, pg_replication_slots, max_slot_wal_keep_size, could not start WAL streaming, subscription stuck, pg_stat_subscription, replica is behind, WAL not being removed, slot bloat, standby lag, logical replication stuck

Use when reviewing SQL queries for correctness and performance before they ship, or auditing existing queries in a codebase. Prevents rubber-stamping queries without checking index usage, missing a WHERE-less UPDATE/DELETE, and overlooking implicit casts or un-indexed JSONB and LIKE patterns that force seqscans. Covers a deterministic SQL review checklist (SELECT *, missing WHERE, implicit casts, JSONB GIN gaps, leading-wildcard LIKE, N+1, OFFSET pagination, NOT IN with NULLs, fan-out), severity grading, and which sibling skill to cite per finding. Keywords: SQL review, query review, code review, query reviewer, SELECT star, missing WHERE, N+1 query, implicit cast, seqscan, slow query review, review my SQL, is this query good, audit queries, query checklist

Use when queries are canceled by timeout, sessions hold locks while idle, or you need to bound query and transaction duration safely. Prevents a forgotten open transaction blocking vacuum forever (set idle_in_transaction_session_timeout), blindly retrying a statement_timeout caused by a missing index, and migrations stalling all traffic without lock_timeout. Covers statement_timeout (57014), lock_timeout (55P03), idle_in_transaction_session_timeout (25P03), idle_session_timeout (v14+), transaction_timeout (v17+), per-role/per-database scoping, fail-fast vs retry decision. Keywords: statement_timeout, lock_timeout, idle_in_transaction_session_timeout, idle_session_timeout, transaction_timeout, 57014, query_canceled, 55P03, 25P03, query canceled due to statement timeout, idle in transaction, query takes too long, how to limit query time, canceling statement

Use when connections fail with authentication errors, no pg_hba.conf entry, SSL handshake problems, or too-many-connections. Prevents trust auth on production hosts, sslmode=require giving a false sense of security (no server verification), editing pg_hba.conf without reloading, and raising max_connections instead of using a pooler. Covers pg_hba.conf rule format and ordering, auth methods (trust / peer / md5 / scram-sha-256 / cert), "no pg_hba.conf entry" and "password authentication failed" diagnosis, password_encryption, SSL sslmode levels, SQLSTATE 28000 / 28P01 / 53300, connection pooling guidance. Keywords: pg_hba.conf, authentication failed, no pg_hba.conf entry for host, scram-sha-256, md5, peer, trust, sslmode, SSL connection, password authentication failed, too many connections, 28P01, 53300, cannot connect to postgres, connection refused, max_connections

Use when choosing a transaction isolation level, handling serialization_failure errors, or preventing write-skew anomalies. Prevents treating 40001 as a hard error instead of retrying, retrying only the last statement instead of the whole transaction, and assuming REPEATABLE READ prevents write skew (only SERIALIZABLE does). Covers READ COMMITTED vs REPEATABLE READ vs SERIALIZABLE, SSI predicate locks, SQLSTATE 40001 serialization_failure, the whole-transaction retry pattern with backoff, SET TRANSACTION ISOLATION LEVEL, read-only deferrable transactions, isolation-level decision tree. Keywords: serialization failure, 40001, isolation level, READ COMMITTED, REPEATABLE READ, SERIALIZABLE, SSI, predicate lock, write skew, retry transaction, could not serialize access, transaction keeps failing, which isolation level, phantom read

Use when inserts or updates fail with unique, foreign-key, not-null, check, or exclusion constraint errors. Prevents the catch-23505-then-UPDATE race (use ON CONFLICT atomically), slow cascading deletes from un-indexed FK columns, and impossible inserts from circular FKs without DEFERRABLE. Covers SQLSTATE 23505 unique_violation, 23503 foreign_key_violation, 23502 not_null_violation, 23514 check_violation, 23P01 exclusion_violation, deferrable constraints, FK indexing, NOT VALID constraints, ON CONFLICT handling. Keywords: constraint violation, 23505, unique violation, 23503, foreign key violation, 23502, not null violation, 23514, check violation, exclusion violation, deferrable constraint, duplicate key error, foreign key error, insert fails, cannot delete row referenced, ON CONFLICT

Use when transactions fail with deadlock detected, designing locking order, or building a queue with row locks. Prevents retrying only the failed statement instead of the whole transaction, inconsistent lock-acquisition order across code paths, and long transactions amplifying lock contention. Covers SQLSTATE 40P01 deadlock_detected, deadlock_timeout, the automatic victim-abort, log_lock_waits diagnosis, pg_locks + pg_blocking_pids inspection, consistent lock-ordering prevention, SELECT FOR UPDATE NOWAIT / SKIP LOCKED, whole-transaction retry pattern. Keywords: deadlock, deadlock detected, 40P01, deadlock_timeout, lock wait, SELECT FOR UPDATE, NOWAIT, SKIP LOCKED, pg_blocking_pids, log_lock_waits, retry transaction, deadlock detected error, transactions keep deadlocking, how to avoid deadlocks, lock ordering

Use when building cache invalidation, job-queue wakeups, or real-time change notifications between PostgreSQL clients without polling. Prevents lost notifications through a transaction-mode PgBouncer pool, relying on NOTIFY for guaranteed delivery (no replay), and payloads over the 8000-byte limit. Covers LISTEN / NOTIFY / pg_notify, UNLISTEN, commit-time delivery semantics, payload size limit, session-bound no-replay behavior, trigger-driven notification pattern, PgBouncer pooling incompatibility, client-side notification handling. Keywords: LISTEN, NOTIFY, pg_notify, UNLISTEN, pub sub, notification, real-time, cache invalidation, job queue wakeup, PgBouncer, notifications not arriving, listen notify not working with pgbouncer, payload too long, how to push changes to clients

Use when coordinating across application instances, ensuring a scheduled job runs at most once, or building a distributed mutex with PostgreSQL. Prevents session-level lock leaks (held until disconnect), key collisions between unrelated subsystems, and advisory locks misbehaving through a transaction-mode connection pooler. Covers session-level vs transaction-level advisory locks, pg_advisory_lock / pg_advisory_xact_lock / pg_try_advisory_lock, shared variants, single-key vs two-key namespacing, pg_locks inspection, cron-at-most-once and distributed-mutex patterns. Keywords: advisory lock, pg_advisory_lock, pg_advisory_xact_lock, pg_try_advisory_lock, distributed lock, mutex, cron at most once, job coordination, SKIP LOCKED, advisory lock leak, run job once across instances, application lock, pg_locks advisory

Use when altering a table on a live production database, adding indexes or constraints without downtime, or backfilling a new column. Prevents plain CREATE INDEX locking writes (use CONCURRENTLY), ALTER COLUMN TYPE full-rewrite locks, migrations stalling all traffic behind a long query (set lock_timeout), and single huge backfill UPDATEs. Covers ALTER TABLE rewrite rules (metadata-only vs full rewrite), CREATE INDEX / REINDEX CONCURRENTLY, ADD COLUMN with default (v11+), safe NOT NULL + FK via NOT VALID + VALIDATE, lock_timeout, chunked backfill, expand-contract pattern. Keywords: zero downtime migration, ALTER TABLE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, NOT VALID, VALIDATE CONSTRAINT, lock_timeout, backfill, expand contract, table rewrite, migration locked the table, ALTER blocks writes, how to add column safely, add not null without downtime

Use when exploring an unfamiliar database, finding what references a table, locating unused or redundant indexes, or auditing a mature schema. Prevents slow introspection from using information_schema where pg_catalog is faster, DROP cascade surprises from skipping pg_depend, and missing orphan rows or wraparound risk in a legacy database. Covers information_schema vs pg_catalog, FK-graph navigation via pg_constraint, table + index size queries, unused index detection (pg_stat_user_indexes), redundant index detection, orphan row queries, sequence vs IDENTITY audit, pg_depend dependency graph, psql introspection shortcuts. Keywords: pg_catalog, information_schema, pg_class, pg_attribute, pg_constraint, pg_depend, pg_stat_user_indexes, pg_relation_size, foreign key graph, unused index, redundant index, orphan rows, schema introspection, how to explore a database, what references this table, find unused indexes, inherited a legacy database

Use when setting up backups, choosing logical vs physical, doing point-in-time recovery, or configuring v17 incremental backup. Prevents slow restore from plain-format dumps (no parallel), missing roles after restore (forgot pg_dumpall globals), and being unable to recover to a precise time without WAL archiving. Covers logical vs physical decision, pg_dump formats (plain / custom / directory / tar) and --jobs parallelism, pg_restore, pg_dumpall globals, pg_basebackup, PITR with WAL archiving, v17 incremental backup (summarize_wal + pg_combinebackup). Keywords: pg_dump, pg_restore, pg_basebackup, pg_dumpall, backup, restore, PITR, point in time recovery, WAL archiving, incremental backup, pg_combinebackup, custom format, parallel restore, how to back up postgres, restore is slow, recover to a specific time, lost my roles after restore

Use when a query is slow, reading an EXPLAIN plan, finding the worst queries on a server, or diagnosing lock waits. Prevents trusting EXPLAIN cost as real time, running EXPLAIN ANALYZE on a destructive query in production (it executes), and chasing the plan when stale statistics are the real cause. Covers EXPLAIN / EXPLAIN ANALYZE / BUFFERS, reading plan nodes (Seq/Index/Bitmap/Nested-Loop/Hash/Merge/Sort/Aggregate/Memoize v14+), cost model, estimate-vs-actual rows, pg_stat_statements forensics, pg_stat_activity, pg_locks + pg_blocking_pids, extended statistics (CREATE STATISTICS). Keywords: EXPLAIN, EXPLAIN ANALYZE, BUFFERS, query plan, Seq Scan, Index Scan, Bitmap, Nested Loop, Hash Join, Memoize, pg_stat_statements, pg_stat_activity, pg_locks, pg_blocking_pids, CREATE STATISTICS, query is slow, how to read explain, find slow queries, why is this query slow, lock wait

Use when a table is bloated, queries slow down over time, autovacuum is not keeping up, or you see transaction-ID wraparound warnings. Prevents VACUUM FULL locking a live production table, wraparound emergency from disabled autovacuum, and index bloat from frequent indexed-column updates at fillfactor 100. Covers MVCC dead tuples, VACUUM vs VACUUM FULL vs ANALYZE, autovacuum scale-factor tuning (global + per-table), bloat detection via pg_stat_user_tables, HOT updates, FILLFACTOR, transaction-ID + multixact wraparound, pg_repack alternative. Keywords: VACUUM, autovacuum, VACUUM FULL, bloat, dead tuples, n_dead_tup, FILLFACTOR, HOT update, transaction id wraparound, relfrozenxid, pg_repack, table is bloated, queries getting slower, database not accepting commands wraparound, autovacuum not keeping up, disk usage growing

Use when importing large datasets, choosing COPY vs INSERT, querying CSV files as tables, or setting up cross-database queries. Prevents row-by-row INSERT loops that are orders of magnitude slower than COPY, stale planner stats from skipping ANALYZE after load, and "permission denied" from server-side COPY on a client file (use \copy). Covers COPY FROM/TO/STDIN, CSV/TEXT/BINARY formats, COPY FREEZE, v17 ON_ERROR ignore + HEADER MATCH, v16 COPY DEFAULT, file_fdw for CSV-as-table, postgres_fdw cross-database queries, load optimization (drop indexes, UNLOGGED staging), prepared statements. Keywords: COPY, bulk load, COPY FROM STDIN, \copy, CSV import, COPY FREEZE, ON_ERROR, file_fdw, postgres_fdw, foreign data wrapper, IMPORT FOREIGN SCHEMA, import is slow, fastest way to load data, query csv as table, cross database query, insert loop slow

Use when replicating selected tables between databases, setting up zero-downtime upgrades, or debugging a stuck subscription. Prevents UPDATE/DELETE silently not replicating (REPLICA IDENTITY missing on PK-less table), unbounded WAL growth from abandoned slots, and broken replication after an un-synced DDL change. Covers CREATE PUBLICATION / SUBSCRIPTION, REPLICA IDENTITY (DEFAULT / FULL / INDEX), row filters + column lists (v15+), logical-from-standby (v16+), bidirectional (v16+), pg_createsubscriber (v17+), slot management, initial sync, DDL-not-replicated caveat. Keywords: logical replication, CREATE PUBLICATION, CREATE SUBSCRIPTION, REPLICA IDENTITY, replication slot, row filter, column list, pg_createsubscriber, bidirectional replication, subscription stuck, WAL growing, updates not replicating, how to replicate one table, zero downtime upgrade

Use when setting up a hot-standby replica, choosing synchronous_commit durability, or debugging replica lag and query cancellation. Prevents disk filling from a physical slot without max_slot_wal_keep_size, primary stalls when a synchronous standby goes down, and standby query cancellation from conflicting vacuum. Covers pg_basebackup clone, primary_conninfo + standby.signal, hot_standby, physical replication slots, WAL archiving, synchronous_commit modes, synchronous_standby_names quorum, pg_promote, cascading replication, pg_stat_replication monitoring, hot_standby_feedback. Keywords: streaming replication, hot standby, pg_basebackup, replication slot, synchronous_commit, synchronous_standby_names, pg_promote, standby.signal, pg_stat_replication, replica lag, max_slot_wal_keep_size, query canceled on standby, disk full replication, how to set up a replica, failover

Use when splitting a large table by time or category, automating partition creation, or debugging why a query scans every partition. Prevents queries that scan all partitions from missing the partition key in WHERE (no pruning), unique constraints rejected for omitting the partition key, and long ATTACH locks without a pre-matching CHECK constraint. Covers declarative partitioning RANGE / LIST / HASH, partition pruning (plan-time + runtime v11+), partition-wise join/aggregate, DEFAULT partition, ATTACH / DETACH CONCURRENTLY (v14+), sub-partitioning, pg_partman automation. Keywords: partitioning, PARTITION BY RANGE, LIST, HASH, partition pruning, partition-wise join, DEFAULT partition, ATTACH PARTITION, DETACH CONCURRENTLY, pg_partman, sub-partitioning, query scans all partitions, partition is slow, how to split a big table, time-series table

Use when working with 3D geometry, volumetric calculations, or raster (gridded) data in PostGIS. Prevents calling 3D functions without postgis_sfcgal installed (function not found), bloating tables with in-db rasters that should be out-db, and choosing raster where vector fits. Covers postgis_sfcgal 3D predicates (ST_3DIntersects, ST_3DDistance, ST_Volume), ST_Extrude, Z-coordinate handling, postgis_raster type (ST_Value, ST_Band, ST_Clip), in-db vs out-db storage, raster vs vector decision, postgis_topology overview. Keywords: PostGIS 3D, postgis_sfcgal, ST_3DDistance, ST_3DIntersects, ST_Extrude, ST_Volume, raster, postgis_raster, ST_Value, ST_Clip, postgis_topology, function does not exist sfcgal, 3D query, raster vs vector, gridded data, volumetric

Use when storing embeddings for semantic search, picking hnsw vs ivfflat, or debugging why a vector query does a seqscan. Prevents index opclass not matching the query distance operator (index unused), building ivfflat on an empty table (poor recall), and exceeding the indexable dimension limit. Covers CREATE EXTENSION vector, vector / halfvec / sparsevec / bit types, distance operators (<->, <=>, <#>, <+>), ivfflat vs hnsw decision, operator classes, dimension limits, ORDER BY ... LIMIT k query pattern, ef_search tuning, hybrid search with tsvector. Keywords: pgvector, vector, embedding, hnsw, ivfflat, cosine distance, <=>, <->, similarity search, semantic search, vector_cosine_ops, dimension limit, hybrid search, vector query is slow, embedding seqscan, which vector index, RAG retrieval

Use when querying spatial data, picking geometry vs geography, or speeding up "find points near X" queries with PostGIS. Prevents losing the GiST index by using ST_Distance in WHERE (use ST_DWithin), seqscan from missing spatial index, and wrong results from mixing SRIDs without ST_Transform. Covers CREATE EXTENSION postgis, geometry vs geography, SRID (4326 / 3857 / projected), GiST spatial index, spatial predicates (ST_Intersects / ST_Within / ST_DWithin / ST_Distance), ST_Transform, ST_Buffer, constructors, measurement functions. Keywords: PostGIS, geometry, geography, SRID, 4326, 3857, GiST, ST_Intersects, ST_DWithin, ST_Distance, ST_Transform, ST_Contains, spatial index, spatial query is slow, find points near, distance in meters, my postgis query does seqscan, wrong distance results

Use when a query is slow, picking which index type to create, or auditing a table for missing and redundant indexes. Prevents missing FK-column indexes (slow joins and cascade deletes), expression-index mismatch (index never used), redundant indexes bloating writes, and reaching for B-tree where GIN/GiST/BRIN fits. Covers B-tree / GIN / GiST / SP-GiST / BRIN / Hash decision tree, partial index, expression index, multicolumn column-ordering, INCLUDE covering columns (v11+), index-only scans, CREATE INDEX CONCURRENTLY. Keywords: index, B-tree, GIN, GiST, SP-GiST, BRIN, Hash, partial index, expression index, covering index, INCLUDE, multicolumn index, CREATE INDEX CONCURRENTLY, query is slow, which index should I use, missing index on foreign key, index not being used, too many indexes

Use when building search over text columns, ranking results by relevance, or choosing between full-text search and trigram fuzzy matching. Prevents to_tsquery syntax errors on raw user input (use websearch_to_tsquery), seqscan from missing GIN index, and config mismatch between index expression and query. Covers tsvector / tsquery, to_tsvector, plainto_tsquery / phraseto_tsquery / websearch_to_tsquery, @@ match, ts_rank / ts_rank_cd, ts_headline, GIN vs GiST index choice, generated tsvector column (v12+), setweight, FTS vs pg_trgm decision. Keywords: full text search, tsvector, tsquery, to_tsvector, websearch_to_tsquery, ts_rank, GIN, ts_headline, setweight, text search, search is slow, how to do search in postgres, fuzzy search, relevance ranking, search returns nothing

Use when storing list-valued columns, modelling time/number intervals, or preventing overlapping bookings with exclusion constraints. Prevents seqscan on range-overlap queries from missing GiST index, CSV-in-text instead of native array, and off-by-one errors from inclusive vs exclusive range bounds. Covers array literals + operators (= ANY, @>, &&, ||), unnest WITH ORDINALITY, array_agg, range types (int4range, tstzrange, daterange), range operators (@>, &&, -|-), multirange (v14+), GiST exclusion constraints for non-overlap. Keywords: array, ARRAY, ANY, unnest, array_agg, range type, int4range, tstzrange, daterange, multirange, range overlap, exclusion constraint, GiST, EXCLUDE USING gist, overlapping bookings, how to store a list, prevent double booking, range query is slow

Use when computing per-row dynamic subqueries, top-N-per-group results, or expanding JSON arrays per row in a JOIN clause. Prevents losing outer rows when LATERAL subquery returns empty (use LEFT JOIN LATERAL ON true), and reaching for LATERAL when a simple JOIN suffices (less optimizer-friendly). Covers LATERAL semantics, INNER vs LEFT JOIN LATERAL, correlated subquery equivalence, top-N-per-group pattern, LATERAL with set-returning functions, LATERAL vs scalar subquery decision tree. Keywords: LATERAL, CROSS JOIN LATERAL, LEFT JOIN LATERAL, top N per group, per row subquery, correlated subquery, generate_series LATERAL, unnest in JOIN, missing rows when joining, why do I lose rows in lateral, jsonb_array_elements per row, polygons vertices LATERAL, lateral subquery empty

Use when computing running totals, rank within partition, top-N-per-group via window, or comparing rows to neighbours. Prevents LAST_VALUE returning current row (wrong default frame), filtering on window result in WHERE (illegal, must wrap), and reaching for GROUP BY when window suffices. Covers OVER clause, PARTITION BY, frame clauses (ROWS/RANGE/GROUPS v11+), EXCLUDE (v11+), named windows, ranking (RANK/DENSE_RANK/ROW_NUMBER/NTILE), offset (LAG/LEAD/FIRST_VALUE/LAST_VALUE/NTH_VALUE), window vs aggregate decision tree. Keywords: window function, OVER, PARTITION BY, frame clause, ROWS BETWEEN, RANGE BETWEEN, GROUPS, LAG, LEAD, RANK, DENSE_RANK, ROW_NUMBER, NTILE, named window, running total, top N per group, why is LAST_VALUE returning current row, can I use window in WHERE

Use when writing readable multi-stage queries, traversing parent/child trees, or constructing data-modifying CTEs that return both before and after states. Prevents pre-v12 fence-reliance breaking under v12+ inlining (NOT MATERIALIZED default), infinite-loop recursive CTEs without termination, and reaching for stored procedures when a recursive CTE suffices. Covers non-recursive CTE, RECURSIVE CTE (UNION ALL vs UNION, termination, SEARCH/CYCLE v14+), MATERIALIZED vs NOT MATERIALIZED (v12+), data-modifying CTE (INSERT/UPDATE/DELETE in WITH), tree-walk + graph patterns. Keywords: CTE, WITH, WITH RECURSIVE, MATERIALIZED, NOT MATERIALIZED, SEARCH, CYCLE, data-modifying CTE, tree walk, graph traversal, recursive query, my recursive CTE never returns, query inlined unexpectedly, how to traverse parent_id, fence broken v12, ERROR aggregate functions are not allowed in WITH RECURSIVE, infinite loop CTE, out of memory recursive

Use when authoring CREATE TABLE / ALTER TABLE / INSERT / UPDATE / DELETE statements, picking IDENTITY vs SERIAL, or learning the RETURNING clause. Prevents shipping SERIAL columns (deprecated, breaks GENERATED ALWAYS guarantees), forgetting RETURNING on DML, and missing GENERATED columns for derived data. Covers CREATE TABLE grammar, IDENTITY columns (v10+), SERIAL deprecation, GENERATED AS STORED (v12+), RETURNING on INSERT/UPDATE/DELETE/MERGE (v17+ for MERGE), DEFAULT semantics, ALTER TABLE basics, DROP/TRUNCATE, transactional DDL. Keywords: CREATE TABLE, ALTER TABLE, INSERT, UPDATE, DELETE, RETURNING, IDENTITY, GENERATED AS IDENTITY, SERIAL, GENERATED AS STORED, DEFAULT, TRUNCATE, RESTART IDENTITY, transactional DDL, my serial sequence is broken, how to get inserted id back, why is generated column not working

Use when querying JSON columns, picking the right GIN opclass, or extracting values from jsonb payloads. Prevents slow JSONB queries from missing GIN index, wrong opclass choice (jsonb_path_ops cannot answer key-existence queries), and using json type where jsonb belongs. Covers ->, ->>, #>, #>>, @>, <@, ?, ?&, ?|, jsonb_set, jsonb_path_query, jsonb_path_match (v12+), JSON_TABLE (v17+), GIN jsonb_ops vs jsonb_path_ops decision tree, JSONB containment query pattern. Keywords: jsonb, json, JSONB, GIN, jsonb_ops, jsonb_path_ops, jsonpath, jsonb_path_query, JSON_TABLE, containment, ->>, @>, extract json key, query nested json, jsonb is slow, missing index on jsonb, how to query json field, why does my jsonb query do seqscan, json vs jsonb which one, jsonb_set update key

Use when writing idempotent inserts, multi-action data sync, or migrating from MySQL INSERT ... ON DUPLICATE KEY UPDATE. Prevents 23505 unique_violation when ON CONFLICT misconfigured, non-deterministic MERGE source race, and using MERGE when simpler UPSERT suffices. Covers INSERT ... ON CONFLICT (v9.5+) conflict_target rules, DO NOTHING vs DO UPDATE, EXCLUDED pseudo-table, MERGE statement (v15+), MERGE-RETURNING (v17+), merge_action(), UPSERT vs MERGE decision tree. Keywords: ON CONFLICT, UPSERT, MERGE, EXCLUDED, DO UPDATE, DO NOTHING, merge_action, RETURNING, idempotent insert, INSERT ON DUPLICATE KEY UPDATE, unique violation 23505, how to do upsert in postgres, when to use merge instead of upsert, MERGE cardinality violation, ERROR there is no unique or exclusion constraint matching the ON CONFLICT specification, ERROR MERGE command cannot affect row a second time

Use when starting a new PostgreSQL database, picking naming conventions, deciding multi-schema layout, or hardening search_path against SECURITY DEFINER injection. Prevents using SERIAL where IDENTITY is required (v10+), shipping a quoted-identifier mess ("camelCase" forces quotes everywhere), leaving public schema writable, and SECURITY DEFINER search_path hijack. Covers snake_case naming, IDENTITY vs SERIAL (v10+), multi-schema layout patterns, search_path semantics + injection risks, v15 public-schema default lockdown, multi-tenant via schema vs RLS decision tree. Keywords: schema design, naming convention, IDENTITY, GENERATED AS IDENTITY, SERIAL deprecated, search_path, SECURITY DEFINER, public schema, snake_case, multi-tenant, where should I put my tables, can I use camelCase, why does serial cause sequence issues, search path hijack, what schema layout, public schema permission denied, function ignoring search_path, identity vs serial which one

Use when designing PostgreSQL permission models, picking which role attributes to grant, or auditing existing privileges before a security review. Prevents over-granting SUPERUSER, leaving public schema writable, forgetting ALTER DEFAULT PRIVILEGES for future tables, and confusion between role attributes and predefined roles. Covers role attributes (LOGIN, SUPERUSER, REPLICATION, BYPASSRLS, CREATEDB, CREATEROLE, INHERIT), GRANT/REVOKE matrix, ALTER DEFAULT PRIVILEGES, predefined roles (pg_read_all_data, pg_write_all_data, pg_monitor, pg_use_reserved_connections v16+, pg_maintain v17+), role membership, SET ROLE. Keywords: GRANT, REVOKE, role, predefined role, BYPASSRLS, SUPERUSER, ALTER DEFAULT PRIVILEGES, pg_read_all_data, pg_maintain, permission denied, who can see this table, how to set up read-only user, where do default privileges come from

Use when implementing multi-tenant isolation, Supabase auth-based row access, or any per-row authorization in PostgreSQL. Prevents forgetting WITH CHECK on UPDATE (silent data leak via update-target), missing FORCE ROW LEVEL SECURITY (owner bypasses policies), and confusing PERMISSIVE vs RESTRICTIVE evaluation order. Covers ENABLE / FORCE ROW LEVEL SECURITY, CREATE POLICY (USING vs WITH CHECK semantics), PERMISSIVE vs RESTRICTIVE combinators, policy-evaluation order per command, role-per-policy decomposition, BYPASSRLS attribute, Supabase auth.uid() / auth.jwt() patterns. Keywords: ROW LEVEL SECURITY, RLS, CREATE POLICY, USING, WITH CHECK, PERMISSIVE, RESTRICTIVE, FORCE ROW LEVEL SECURITY, BYPASSRLS, auth.uid, auth.jwt, Supabase RLS, multi-tenant, row not visible, user sees other tenant data, why can owner bypass my policy, how to write a policy, RLS not working, policy not applied, ERROR new row violates row-level security policy, ERROR permission denied for table

Use when designing for PostgreSQL concurrency, understanding why a query sees old rows, or debugging "table is bloated" / "where did the rows go" symptoms. Prevents misunderstanding MVCC visibility, snapshot isolation, hint-bit first-read cost, and the transactional DDL guarantee. Covers MVCC tuple visibility, snapshot isolation, process model (postmaster, backends, autovacuum, walwriter, checkpointer), shared buffers, WAL durability, visibility map, hint bits, FSM, transactional DDL. Keywords: MVCC, snapshot isolation, xmin, xmax, ctid, hint bits, visibility map, WAL, shared_buffers, autovacuum, transactional DDL, table is bloated, query sees old data, where did the rows go, why is my select slow after insert, what is postgres doing

Use when navigating PostgreSQL's SQL surface, picking the right system catalog, or learning psql meta-commands. Prevents grabbing information_schema when pg_catalog is faster, or hunting for system info without knowing the catalog map. Covers DDL/DML/DCL/TCL grammar overview, psql meta-commands, pg_catalog vs information_schema decision tree, system catalog map. Keywords: pg_catalog, information_schema, psql, meta-commands, system catalogs, DDL, DML, DCL, TCL, MERGE, ON CONFLICT, where is my table info, how to list users, how to inspect schema, what does \d do

Use when targeting PostgreSQL 15, 16, or 17 and needing to know which feature is available where, or migrating across versions. Prevents writing queries that fail on older supported versions, missing v17 features that solve real problems, and confusion about which MERGE / RETURNING / JSON_TABLE behavior applies. Covers per-version feature delta (v15 MERGE/security_invoker/row filters/public lockdown, v16 logical-from-standby/COPY DEFAULT/role membership rewrite, v17 incremental backup/MERGE-RETURNING/JSON_TABLE/transaction_timeout/pg_createsubscriber). Keywords: PostgreSQL 15, PostgreSQL 16, PostgreSQL 17, MERGE, RETURNING, JSON_TABLE, incremental backup, pg_createsubscriber, transaction_timeout, COPY DEFAULT, security_invoker, logical replication from standby, which feature is in which version, can I use this on v15, how to detect version, what's new in postgres 17, syntax error on old version, feature unavailable, ERROR function does not exist, missing GUC