Menu
SkillsMP has collected 38 skills from Impertio-Studio/PostgreSQL-Claude-Skill-Package. Open a skill to review its source and details.
Showing 38 of 38 collected skills.
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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,…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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,…
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…
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…
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…
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…
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…
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…
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…
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…