Stats
Actions
Tags
'%20stop-opacity%3D'0.16'%2F%3E%3Cstop%20offset%3D'1'%20stop-color%3D'rgb(200%2C90%2C60)'%20stop-opacity%3D'0.03'%2F%3E%3C%2FlinearGradient%3E%3C%2Fdefs%3E%3Crect%20width%3D'320'%20height%3D'200'%20fill%3D'url(%23g)'%2F%3E%3Ccircle%20cx%3D'250'%20cy%3D'56'%20r%3D'92'%20fill%3D'rgb(200%2C90%2C60)'%20fill-opacity%3D'0.06'%2F%3E%3Ccircle%20cx%3D'64'%20cy%3D'172'%20r%3D'58'%20fill%3D'rgb(200%2C90%2C60)'%20fill-opacity%3D'0.05'%2F%3E%3C%2Fsvg%3E)
From agi-super-team
Designs, optimizes, and maintains production PostgreSQL databases covering indexing, query tuning, partitioning, replication, backup/recovery, VACUUM operations, and connection pooling.
How this skill is triggered — by the user, by Claude, or both
Slash command
/agi-super-team:postgresql-database-engineeringThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
A comprehensive skill for professional PostgreSQL database engineering, covering everything from query optimization and indexing strategies to high availability, replication, and production database management. This skill enables you to design, optimize, and maintain high-performance PostgreSQL databases at scale.
A comprehensive skill for professional PostgreSQL database engineering, covering everything from query optimization and indexing strategies to high availability, replication, and production database management. This skill enables you to design, optimize, and maintain high-performance PostgreSQL databases at scale.
Use this skill when:
PostgreSQL uses a process-based architecture with several key components:
PostgreSQL's foundational concurrency mechanism:
Key Implications:
PostgreSQL supports four isolation levels:
Choosing Isolation:
PostgreSQL offers multiple index types for different use cases:
PostgreSQL's query planner determines execution strategies:
Planner Components:
Key Statistics:
n_distinct: Number of distinct values (for selectivity)correlation: Physical row ordering correlationmost_common_vals: MCV list for skewed distributionshistogram_bounds: Value distribution histogramUnderstanding EXPLAIN:
Table partitioning for managing large datasets:
Partition Pruning:
Partition-Wise Operations:
PostgreSQL replication options:
Synchronous vs Asynchronous:
Managing database connections efficiently:
Pooling Modes:
Critical maintenance operations:
Key configuration parameters:
shared_buffers: 25% of RAM (start point)
effective_cache_size: 50-75% of RAM
work_mem: Per-operation memory (sort, hash)
maintenance_work_mem: VACUUM, CREATE INDEX memory
checkpoint_timeout: How often to checkpoint
max_wal_size: WAL size before checkpoint
checkpoint_completion_target: Spread checkpoint I/O
wal_buffers: WAL write buffer size
random_page_cost: Relative cost of random I/O
effective_io_concurrency: Concurrent I/O operations
default_statistics_target: Histogram detail level
max_connections: Maximum client connections
connection_limit: Per-database/user limits
Decision Matrix:
| Query Pattern | Index Type | Reason |
|---|---|---|
WHERE id = 5 | B-tree | Equality lookup |
WHERE created_at > '2024-01-01' | B-tree | Range query |
ORDER BY name | B-tree | Sorting support |
WHERE tags @> ARRAY['sql'] | GIN | Array containment |
WHERE data->>'status' = 'active' | GIN (jsonb_path_ops) | JSONB query |
WHERE to_tsvector(content) @@ query | GIN | Full-text search |
WHERE location <-> point(0,0) | GiST | Nearest neighbor |
WHERE timestamp BETWEEN ... (large table) | BRIN | Sequential time-series |
WHERE ip_address << '192.168.0.0/16' | GiST or SP-GiST | IP range query |
Multi-column indexes for complex queries:
Column Ordering Rules:
Example:
-- Query: WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY created_at
-- Optimal index: (status, created_at)
CREATE INDEX idx_users_status_created ON users(status, created_at);
Index subset of rows:
Benefits:
Use Cases:
WHERE deleted_at IS NULLWHERE created_at > NOW() - INTERVAL '90 days'WHERE status IN ('pending', 'processing')Index computed values:
Examples:
-- Case-insensitive search
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Date truncation
CREATE INDEX idx_events_date ON events(DATE(created_at));
-- JSONB field
CREATE INDEX idx_data_status ON documents((data->>'status'));
Include non-key columns for index-only scans:
CREATE INDEX idx_users_email_include
ON users(email)
INCLUDE (first_name, last_name, created_at);
Benefit: Query satisfied entirely from index, no table lookup
Monitoring Index Usage:
-- Unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
Detecting Bloat:
-- Index bloat estimation
SELECT schemaname, tablename, indexname,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
Understanding query execution:
-- Basic EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- EXPLAIN ANALYZE (actually runs query)
EXPLAIN ANALYZE SELECT * FROM users WHERE created_at > '2024-01-01';
-- Detailed output
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';
Key Metrics:
Problem: One query per row in a loop Solution: JOIN or batch queries
Problem: Fetches unnecessary columns Solution: Select only needed columns
Problem: Index not used due to type mismatch Solution: Ensure query types match column types
Problem: WHERE UPPER(email) = '[email protected]'
Solution: Use expression index or compare correctly
Problem: WHERE status = 'A' OR status = 'B'
Solution: Use IN: WHERE status IN ('A', 'B')
Join Types:
Nested Loop
Hash Join
Merge Join
Join Order Matters:
SET join_collapse_limitTechniques:
Materialized Views:
Levels:
Time-series example:
-- Create partitioned table
CREATE TABLE events (
id BIGSERIAL,
event_type TEXT NOT NULL,
user_id INTEGER NOT NULL,
data JSONB,
created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);
-- Create partitions
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- Default partition for data outside ranges
CREATE TABLE events_default PARTITION OF events DEFAULT;
-- Indexes on partitions
CREATE INDEX idx_events_2024_01_user ON events_2024_01(user_id);
CREATE INDEX idx_events_2024_02_user ON events_2024_02(user_id);
Automated partition management:
-- Function to create monthly partitions
CREATE OR REPLACE FUNCTION create_monthly_partition(
base_table TEXT,
partition_date DATE
) RETURNS VOID AS $$
DECLARE
partition_name TEXT;
start_date DATE;
end_date DATE;
BEGIN
partition_name := base_table || '_' || TO_CHAR(partition_date, 'YYYY_MM');
start_date := DATE_TRUNC('month', partition_date);
end_date := start_date + INTERVAL '1 month';
EXECUTE format(
'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
FOR VALUES FROM (%L) TO (%L)',
partition_name, base_table, start_date, end_date
);
-- Create indexes
EXECUTE format(
'CREATE INDEX IF NOT EXISTS %I ON %I(user_id)',
'idx_' || partition_name || '_user', partition_name
);
END;
$$ LANGUAGE plpgsql;
Dropping old partitions:
-- Detach partition (fast, non-blocking)
ALTER TABLE events DETACH PARTITION events_2023_01;
-- Drop detached partition
DROP TABLE events_2023_01;
-- Or archive before dropping
CREATE TABLE archive.events_2023_01 AS SELECT * FROM events_2023_01;
DROP TABLE events_2023_01;
Primary server configuration (postgresql.conf):
# Replication settings
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
synchronous_commit = on # or off for async
synchronous_standby_names = 'standby1,standby2' # for sync replication
Create replication user:
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secure_password';
pg_hba.conf on primary:
# Allow replication connections
host replication replicator standby_ip/32 md5
Standby server setup:
# Stop standby PostgreSQL
systemctl stop postgresql
# Remove old data directory
rm -rf /var/lib/postgresql/14/main
# Base backup from primary
pg_basebackup -h primary_host -D /var/lib/postgresql/14/main \
-U replicator -P -v -R -X stream -C -S standby1
# Start standby
systemctl start postgresql
Standby configuration (created by -R flag):
# standby.signal file created automatically
# postgresql.auto.conf contains:
primary_conninfo = 'host=primary_host port=5432 user=replicator password=secure_password'
primary_slot_name = 'standby1'
On primary:
-- Check replication status
SELECT client_addr, state, sync_state, replay_lag
FROM pg_stat_replication;
-- Check replication slots
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;
On standby:
-- Check replication lag
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;
-- Check recovery status
SELECT pg_is_in_recovery();
Promoting standby to primary:
# Trigger failover
pg_ctl promote -D /var/lib/postgresql/14/main
# Or using SQL
SELECT pg_promote();
Controlled switchover:
# 1. Stop writes on primary
# 2. Wait for standby to catch up
# 3. Promote standby
# 4. Reconfigure old primary as new standby
On publisher (source):
-- Create publication
CREATE PUBLICATION my_publication FOR TABLE users, orders;
-- Or all tables
CREATE PUBLICATION all_tables FOR ALL TABLES;
On subscriber (destination):
-- Create subscription
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=publisher_host dbname=mydb user=replicator password=pass'
PUBLICATION my_publication;
-- Monitor subscription
SELECT * FROM pg_stat_subscription;
pg_basebackup:
# Full physical backup
pg_basebackup -h localhost -U postgres -D /backup/base \
-F tar -z -P -v
# With WAL files for point-in-time recovery
pg_basebackup -h localhost -U postgres -D /backup/base \
-X stream -F tar -z -P
Continuous archiving (WAL archiving):
# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/wal/%f'
pg_dump:
# Single database
pg_dump -h localhost -U postgres -F c -b -v -f mydb.dump mydb
# All databases
pg_dumpall -h localhost -U postgres -f all_databases.sql
# Specific tables
pg_dump -h localhost -U postgres -t users -t orders -F c -f tables.dump mydb
# Schema only
pg_dump -h localhost -U postgres --schema-only -F c -f schema.dump mydb
pg_restore:
# Restore database
pg_restore -h localhost -U postgres -d mydb -v mydb.dump
# Parallel restore
pg_restore -h localhost -U postgres -d mydb -j 4 -v mydb.dump
# Restore specific tables
pg_restore -h localhost -U postgres -d mydb -t users -v mydb.dump
Setup:
Recovery:
# 1. Restore base backup
tar -xzf base.tar.gz -C /var/lib/postgresql/14/main
# 2. Create recovery.signal file
touch /var/lib/postgresql/14/main/recovery.signal
# 3. Configure recovery target (postgresql.conf or postgresql.auto.conf)
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2024-01-15 14:30:00'
# Or: recovery_target_name = 'before_disaster'
# Or: recovery_target_lsn = '0/3000000'
# 4. Start PostgreSQL
systemctl start postgresql
3-2-1 Rule:
Backup Schedule:
Testing Backups:
Database Health:
Query Performance:
System Resources:
Connection stats:
SELECT count(*) as total_connections,
count(*) FILTER (WHERE state = 'active') as active,
count(*) FILTER (WHERE state = 'idle') as idle,
count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_transaction
FROM pg_stat_activity;
Cache hit ratio:
SELECT sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;
Table bloat:
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
n_dead_tup,
n_live_tup,
round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
Long-running queries:
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;
Lock monitoring:
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
AND blocking_locks.granted;
Installation:
CREATE EXTENSION pg_stat_statements;
Configuration (postgresql.conf):
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
Top queries by total time:
SELECT query,
calls,
total_exec_time,
mean_exec_time,
max_exec_time,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
Top queries by average time:
SELECT query,
calls,
mean_exec_time,
total_exec_time
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 20;
Normalization:
Data Types:
Constraints:
Zero-Downtime Migrations:
Add new column
ALTER TABLE users ADD COLUMN email_verified BOOLEAN;
Backfill data (in batches)
UPDATE users SET email_verified = false
WHERE email_verified IS NULL
LIMIT 10000;
Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;
Index Creation:
CREATE INDEX CONCURRENTLY in productionpg_stat_progress_create_indexLarge Table Modifications:
pg_repack for table rewritesAuthentication:
Authorization:
Network Security:
Audit Logging:
Daily:
Weekly:
Monthly:
Quarterly:
Configuration:
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_setup_cost = 1000
parallel_tuple_cost = 0.1
min_parallel_table_scan_size = 8MB
Forcing parallel execution:
SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table;
When parallelism helps:
Stored procedures:
CREATE OR REPLACE PROCEDURE update_user_statistics()
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE users SET
order_count = (SELECT COUNT(*) FROM orders WHERE user_id = users.id),
last_order_date = (SELECT MAX(created_at) FROM orders WHERE user_id = users.id);
COMMIT;
END;
$$;
Functions with proper error handling:
CREATE OR REPLACE FUNCTION create_user(
p_email TEXT,
p_name TEXT
) RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
v_user_id INTEGER;
BEGIN
INSERT INTO users (email, name)
VALUES (p_email, p_name)
RETURNING id INTO v_user_id;
RETURN v_user_id;
EXCEPTION
WHEN unique_violation THEN
RAISE EXCEPTION 'Email already exists: %', p_email;
WHEN OTHERS THEN
RAISE EXCEPTION 'Error creating user: %', SQLERRM;
END;
$$;
Access external data sources:
-- Install postgres_fdw
CREATE EXTENSION postgres_fdw;
-- Create server
CREATE SERVER remote_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote_host', dbname 'remote_database', port '5432');
-- Create user mapping
CREATE USER MAPPING FOR current_user
SERVER remote_db
OPTIONS (user 'remote_user', password 'remote_password');
-- Import foreign schema
IMPORT FOREIGN SCHEMA public
FROM SERVER remote_db
INTO local_schema;
-- Query foreign table
SELECT * FROM local_schema.remote_table;
Indexing JSONB:
-- GIN index for containment queries
CREATE INDEX idx_data_gin ON documents USING GIN (data);
-- Expression index for specific field
CREATE INDEX idx_data_status ON documents ((data->>'status'));
-- GIN index with jsonb_path_ops (smaller, faster for @> queries)
CREATE INDEX idx_data_path_ops ON documents USING GIN (data jsonb_path_ops);
Efficient JSONB queries:
-- Containment query (uses GIN index)
SELECT * FROM documents WHERE data @> '{"status": "active"}';
-- Existence query
SELECT * FROM documents WHERE data ? 'email';
-- Path query
SELECT * FROM documents WHERE data->'user'->>'email' = '[email protected]';
-- Array operations
SELECT * FROM documents WHERE data->'tags' @> '["sql", "postgres"]';
Basic setup:
-- Add tsvector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;
-- Generate search vector
UPDATE articles SET search_vector =
to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''));
-- Create GIN index
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
-- Trigger for automatic updates
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);
Search queries:
-- Basic search
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & database') query
WHERE search_vector @@ query
ORDER BY rank DESC;
-- Phrase search
SELECT title FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'database engineering');
-- Search with highlighting
SELECT title,
ts_headline('english', content, query) AS snippet
FROM articles, to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query;
Problem: Slow Queries
ANALYZE table_nameProblem: High CPU Usage
Problem: Connection Exhaustion
pg_stat_activityProblem: Autovacuum Not Keeping Up
Problem: Replication Lag
Problem: Transaction ID Wraparound
Find missing indexes on foreign keys:
SELECT c.conrelid::regclass AS table,
c.confrelid::regclass AS referenced_table,
string_agg(a.attname, ', ') AS foreign_key_columns
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid
AND c.conkey[1:array_length(c.conkey, 1)]
OPERATOR(pg_catalog.@>) i.indkey[0:array_length(c.conkey, 1) - 1]
)
GROUP BY c.conrelid, c.confrelid, c.conname;
Identify blocking queries:
SELECT activity.pid,
activity.usename,
activity.query,
blocking.pid AS blocking_id,
blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
Skill Version: 1.0.0 Last Updated: October 2025 Skill Category: Database Engineering, Performance Optimization, Data Architecture Compatible With: PostgreSQL 12+, 13, 14, 15, 16 Prerequisites: SQL knowledge, basic database concepts, Linux command line
npx claudepluginhub aaaaqwq/agi-super-team --plugin agi-super-teamOptimizes PostgreSQL with advanced features like CTEs/window functions/JSONB, extensions (pgvector/PostGIS/TimescaleDB), replication, partitioning, VACUUM tuning, and PgBouncer pooling.
Optimizes PostgreSQL queries, configures replication, and implements advanced features like JSONB, extensions, VACUUM tuning, and performance monitoring with EXPLAIN analysis.
Optimizes PostgreSQL queries, configures replication, and implements advanced features. Uses EXPLAIN ANALYZE, JSONB operations, VACUUM tuning, and pg_stat views for performance monitoring.