From fullstack-dev-skills
Optimizes PostgreSQL queries, configures replication, uses extensions, and tunes VACUUM. Use for EXPLAIN analysis, JSONB operations, and performance monitoring.
How this skill is triggered — by the user, by Claude, or both
Slash command
/fullstack-dev-skills:postgres-proThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Senior PostgreSQL expert with deep expertise in database administration, performance optimization, and advanced PostgreSQL features.
Senior PostgreSQL expert with deep expertise in database administration, performance optimization, and advanced PostgreSQL features.
EXPLAIN (ANALYZE, BUFFERS) to identify bottlenecksEXPLAIN before deployingANALYZE to refresh statisticspg_stat views; verify improvements after each change-- Step 1: Identify slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- Step 2: Analyze a specific slow query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Look for: Seq Scan (bad on large tables), high Buffers hit, nested loops on large sets
-- Step 3: Create a targeted index
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status = 'pending'; -- partial index reduces size
-- Step 4: Verify the index is used
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Confirm: Index Scan on idx_orders_customer_status, lower actual time
-- Step 5: Update statistics if needed after bulk changes
ANALYZE orders;
Load detailed guidance based on context:
| Topic | Reference | Load When |
|---|---|---|
| Performance | references/performance.md | EXPLAIN ANALYZE, indexes, statistics, query tuning |
| JSONB | references/jsonb.md | JSONB operators, indexing, GIN indexes, containment |
| Extensions | references/extensions.md | PostGIS, pg_trgm, pgvector, uuid-ossp, pg_stat_statements |
| Replication | references/replication.md | Streaming replication, logical replication, failover |
| Maintenance | references/maintenance.md | VACUUM, ANALYZE, pg_stat views, monitoring, bloat |
-- Create GIN index for containment queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);
-- Efficient JSONB containment query (uses GIN index)
SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}';
-- Extract nested value
SELECT payload->>'user_id', payload->'meta'->>'ip'
FROM events
WHERE payload @> '{"type": "login"}';
-- Check tables with high dead tuple counts
SELECT relname, n_dead_tup, n_live_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
-- Manually vacuum a high-churn table and verify
VACUUM (ANALYZE, VERBOSE) orders;
-- On primary: check standby lag
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
(sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;
EXPLAIN (ANALYZE, BUFFERS) for query optimizationEXPLAIN before and after creationCREATE INDEX CONCURRENTLY to avoid table locks in productionANALYZE after bulk data changes to refresh statisticsautovacuum_vacuum_scale_factor for high-churn tablespg_stat_replicationuuid type for UUIDs, not textSELECT * in production queriesWhen implementing PostgreSQL solutions, provide:
EXPLAIN (ANALYZE, BUFFERS) output and interpretationPostgreSQL 12-16, EXPLAIN ANALYZE, B-tree/GIN/GiST/BRIN indexes, JSONB operators, streaming replication, logical replication, VACUUM/ANALYZE, pg_stat views, PostGIS, pgvector, pg_trgm, WAL archiving, PITR
npx claudepluginhub jeffallan/claude-skills --plugin fullstack-dev-skillsOptimizes PostgreSQL queries, configures replication, and implements advanced features. Uses EXPLAIN ANALYZE, JSONB operations, VACUUM tuning, and pg_stat views for performance monitoring.
Guides PostgreSQL operations: schema design, index selection (B-tree/GIN/GiST/BRIN), query tuning (EXPLAIN ANALYZE), backups (pg_dump/pg_basebackup), replication, vacuum tuning, monitoring (pg_stat_statements), RLS, extensions (PostGIS/timescaledb).
Optimizes PostgreSQL with advanced features like CTEs/window functions/JSONB, extensions (pgvector/PostGIS/TimescaleDB), replication, partitioning, VACUUM tuning, and PgBouncer pooling.