PostgreSQL query optimization, JSONB operations, advanced indexing strategies, partitioning, connection management, and database administration. Use for PostgreSQL-specific optimizations, performance tuning, replication setup, and PgBouncer configuration.
Deep PostgreSQL expertise covering query optimization, JSONB, advanced indexing, partitioning, and connection management.
Stop and recommend switching if the issue involves:
database-expertprisma-expertredis-expertsqlite-expertKey diagnostics:
-- Detailed execution analysis
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;
-- Top slow queries
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;
-- Buffer hit ratio (should be >99%)
SELECT
datname,
100.0 * blks_hit / (blks_hit + blks_read) as buffer_hit_ratio
FROM pg_stat_database
WHERE blks_read > 0;
Optimization patterns:
Index strategies:
-- Default jsonb_ops (supports more operators, larger)
CREATE INDEX idx_jsonb_default ON api USING GIN (jdoc);
-- jsonb_path_ops (smaller, faster for containment only)
CREATE INDEX idx_jsonb_path ON api USING GIN (jdoc jsonb_path_ops);
-- Expression indexes for specific paths
CREATE INDEX idx_jsonb_tags ON api USING GIN ((jdoc -> 'tags'));
CREATE INDEX idx_jsonb_company ON api USING BTREE ((jdoc ->> 'company'));
Query patterns:
-- Containment
SELECT * FROM api WHERE jdoc @> '{"company": "Acme"}';
-- Existence
SELECT * FROM api WHERE jdoc ? 'tags';
-- Path extraction
SELECT jdoc -> 'user' ->> 'name' as username FROM api;
Index types and when to use them:
-- B-tree (default) - equality, ranges, sorting
CREATE INDEX idx_btree ON orders (customer_id, order_date);
-- GIN - JSONB, arrays, full-text search
CREATE INDEX idx_gin ON products USING GIN (attributes);
-- GiST - geometric data, ranges
CREATE INDEX idx_gist ON stores USING GIST (location);
-- BRIN - large sequential tables, time-series (very small index)
CREATE INDEX idx_brin ON events USING BRIN (created_at);
-- Partial indexes - filtered subsets
CREATE INDEX idx_active_users ON users (email) WHERE active = true;
-- Expression indexes
CREATE INDEX idx_lower_email ON users (LOWER(email));
Index maintenance:
-- Find unused indexes
SELECT
schemaname, tablename, indexname, idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- Create indexes without blocking writes
CREATE INDEX CONCURRENTLY idx_name ON table (column);
-- Rebuild bloated index
REINDEX INDEX CONCURRENTLY idx_name;
When to partition:
Strategies:
-- Range partitioning (time-series)
CREATE TABLE measurement (
id SERIAL,
logdate DATE NOT NULL,
data JSONB
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_2024_01 PARTITION OF measurement
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
-- List partitioning (categorical)
CREATE TABLE sales (
id SERIAL,
region TEXT NOT NULL,
amount DECIMAL
) PARTITION BY LIST (region);
CREATE TABLE sales_north PARTITION OF sales
FOR VALUES IN ('north', 'northeast', 'northwest');
-- Hash partitioning (even distribution)
CREATE TABLE orders (
id SERIAL,
customer_id INTEGER NOT NULL
) PARTITION BY HASH (customer_id);
CREATE TABLE orders_0 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
Critical insight: PostgreSQL uses ~9MB per connection (process-based) vs MySQL's ~256KB (thread-based). Connection pooling is essential, not optional.
Monitor connections:
-- Current connection count by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
-- Long-running queries
SELECT pid, usename, state,
now() - query_start as runtime,
query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY runtime DESC;
PgBouncer configuration:
# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb
[pgbouncer]
listen_port = 6432
pool_mode = transaction # Most efficient
max_client_conn = 200
default_pool_size = 25
server_idle_timeout = 600
Pool sizing formula: (cores * 2) + effective_spindle_count
Monitor vacuum effectiveness:
SELECT
schemaname, tablename,
n_dead_tup, n_live_tup,
last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
Tuning:
-- Global settings
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;
ALTER SYSTEM SET maintenance_work_mem = '1GB';
-- Per-table tuning for high-churn tables
ALTER TABLE high_update_table SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_scale_factor = 0.02
);
Memory settings (for 16GB RAM server):
shared_buffers = '4GB' -- 25% of RAM
effective_cache_size = '12GB' -- 75% of RAM
work_mem = '256MB' -- Per sort/hash operation
maintenance_work_mem = '1GB' -- VACUUM, CREATE INDEX
max_connections = 200 -- Lower if using PgBouncer
WAL and checkpoints:
max_wal_size = '4GB' -- Reduce checkpoint frequency
min_wal_size = '1GB'
wal_compression = on
checkpoint_completion_target = 0.9 -- Spread checkpoints over 90%
Query planner:
random_page_cost = 1.1 -- For SSDs (default 4.0 for HDDs)
effective_io_concurrency = 200 -- For SSDs
-- Primary: replication status
SELECT
client_addr, state,
write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
-- Standby: check lag
SELECT
pg_is_in_recovery(),
pg_last_wal_replay_lsn(),
now() - pg_last_xact_replay_timestamp() as lag
;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- Query statistics
CREATE EXTENSION IF NOT EXISTS pgcrypto; -- Encryption
CREATE EXTENSION IF NOT EXISTS uuid-ossp; -- UUID generation
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- Fuzzy text search
CREATE EXTENSION IF NOT EXISTS btree_gin; -- GIN index support
Query Performance:
EXPLAIN (ANALYZE, BUFFERS) shows efficient plansJSONB:
jsonb_path_ops for containment-only queries@>, ?, ->, ->>)Schema:
NOT NULL where appropriateConnections:
Maintenance:
CONCURRENTLY in productionANALYZE run after bulk data changesSecurity: