Postgres Expert

SELECT version();
SHOW server_version;

-- Critical PostgreSQL settings
SHOW shared_buffers;
SHOW effective_cache_size;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW max_connections;
SHOW wal_level;
SHOW checkpoint_completion_target;

-- Installed extensions
SELECT * FROM pg_extension;

-- Available extensions
SELECT * FROM pg_available_extensions WHERE installed_version IS NULL;

-- Connection and activity overview
SELECT datname, numbackends, xact_commit, xact_rollback FROM pg_stat_database;
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- Detailed execution analysis
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;

-- Track query performance over time
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 analysis
SELECT 
  datname,
  100.0 * blks_hit / (blks_hit + blks_read) as buffer_hit_ratio
FROM pg_stat_database 
WHERE blks_read > 0;

-- Check JSONB index usage
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM table WHERE jsonb_column @> '{"key": "value"}';

-- Monitor JSONB index effectiveness
SELECT 
  schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes 
WHERE indexname LIKE '%gin%';

-- Default jsonb_ops (supports more operators)
CREATE INDEX idx_jsonb_default ON api USING GIN (jdoc);

-- jsonb_path_ops (smaller, faster for containment)
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'));

-- Identify 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;

-- Find duplicate or redundant indexes
WITH index_columns AS (
  SELECT 
    schemaname, tablename, indexname,
    array_agg(attname ORDER BY attnum) as columns
  FROM pg_indexes i
  JOIN pg_attribute a ON a.attrelid = i.indexname::regclass
  WHERE a.attnum > 0
  GROUP BY schemaname, tablename, indexname
)
SELECT * FROM index_columns i1
JOIN index_columns i2 ON (
  i1.schemaname = i2.schemaname AND 
  i1.tablename = i2.tablename AND 
  i1.indexname < i2.indexname AND
  i1.columns <@ i2.columns
);

-- 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_jsonb ON products USING GIN (attributes);
CREATE INDEX idx_gin_fts ON articles USING GIN (to_tsvector('english', content));

-- GiST - geometric data, ranges, hierarchical data
CREATE INDEX idx_gist_location ON stores USING GiST (location);

-- BRIN - large sequential tables, time-series data
CREATE INDEX idx_brin_timestamp ON events USING BRIN (created_at);

-- Hash - equality only, smaller than B-tree
CREATE INDEX idx_hash ON lookup USING HASH (code);

-- Partial indexes - filtered subsets
CREATE INDEX idx_partial_active ON users (email) WHERE active = true;

-- Check partition pruning effectiveness
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM partitioned_table 
WHERE partition_key BETWEEN '2024-01-01' AND '2024-01-31';

-- Monitor partition sizes
SELECT 
  schemaname, tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE tablename LIKE 'measurement_%'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Range partitioning (time-series data)
CREATE TABLE measurement (
  id SERIAL,
  logdate DATE NOT NULL,
  data JSONB
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2024m01 PARTITION OF measurement
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- List partitioning (categorical data)
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,
  order_date DATE
) PARTITION BY HASH (customer_id);

CREATE TABLE orders_0 PARTITION OF orders
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);

-- Monitor current connections
SELECT 
  datname, state, count(*) as connections,
  max(now() - state_change) as max_idle_time
FROM pg_stat_activity 
GROUP BY datname, state
ORDER BY connections DESC;

-- Identify long-running connections
SELECT 
  pid, usename, datname, state,
  now() - state_change as idle_time,
  now() - query_start as query_runtime
FROM pg_stat_activity 
WHERE state != 'idle'
ORDER BY query_runtime DESC;

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = users.txt

# Pool modes
pool_mode = transaction  # Most efficient
# pool_mode = session    # For prepared statements
# pool_mode = statement  # Rarely needed

# Connection limits
max_client_conn = 200
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5

# Timeouts
server_lifetime = 3600
server_idle_timeout = 600

-- Monitor autovacuum effectiveness
SELECT 
  schemaname, tablename,
  n_tup_ins, n_tup_upd, n_tup_del, n_dead_tup,
  last_vacuum, last_autovacuum,
  last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Check vacuum progress
SELECT 
  datname, pid, phase,
  heap_blks_total, heap_blks_scanned, heap_blks_vacuumed
FROM pg_stat_progress_vacuum;

-- Monitor transaction age
SELECT 
  datname, age(datfrozenxid) as xid_age,
  2147483648 - age(datfrozenxid) as xids_remaining
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Global autovacuum settings
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;  -- Vacuum when 10% + threshold
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05; -- Analyze when 5% + threshold
ALTER SYSTEM SET autovacuum_max_workers = 3;
ALTER SYSTEM SET maintenance_work_mem = '1GB';

-- Per-table autovacuum tuning for high-churn tables
ALTER TABLE high_update_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_delay = 10
);

-- Disable autovacuum for bulk load tables
ALTER TABLE bulk_load_table SET (autovacuum_enabled = false);

-- Primary server replication status
SELECT 
  client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
  write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- Replication slot status
SELECT 
  slot_name, plugin, slot_type, database, active,
  restart_lsn, confirmed_flush_lsn,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as lag_size
FROM pg_replication_slots;

-- Standby server status (run on standby)
SELECT 
  pg_is_in_recovery() as is_standby,
  pg_last_wal_receive_lsn(),
  pg_last_wal_replay_lsn(),
  pg_last_xact_replay_timestamp();

-- Primary server setup (postgresql.conf)
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5
synchronous_commit = on
synchronous_standby_names = 'standby1,standby2'

-- Hot standby configuration
hot_standby = on
max_standby_streaming_delay = 30s
hot_standby_feedback = on

-- Essential extensions
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS uuid-ossp;
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- PostGIS for spatial data
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

-- Window functions for analytics
SELECT 
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) as running_total
FROM orders;

-- Common Table Expressions (CTEs) with recursion
WITH RECURSIVE employee_hierarchy AS (
  SELECT id, name, manager_id, 1 as level
  FROM employees WHERE manager_id IS NULL
  
  UNION ALL
  
  SELECT e.id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

-- UPSERT operations
INSERT INTO products (id, name, price)
VALUES (1, 'Widget', 10.00)
ON CONFLICT (id) 
DO UPDATE SET 
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  updated_at = CURRENT_TIMESTAMP;

-- Create tsvector column and GIN index
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || content);
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);

-- Trigger to maintain search_vector
CREATE OR REPLACE FUNCTION articles_search_trigger() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', NEW.title || ' ' || NEW.content);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_update 
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();

-- Full-text search query
SELECT *, ts_rank_cd(search_vector, query) as rank
FROM articles, to_tsquery('english', 'postgresql & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Core memory settings
shared_buffers = '4GB'                    -- 25% of RAM
effective_cache_size = '12GB'             -- 75% of RAM (OS cache + shared_buffers estimate)
work_mem = '256MB'                        -- Per sort/hash operation
maintenance_work_mem = '1GB'              -- VACUUM, CREATE INDEX operations
autovacuum_work_mem = '1GB'              -- Autovacuum operations

-- Connection memory
max_connections = 200                     -- Adjust based on connection pooling

-- WAL settings
max_wal_size = '4GB'                      -- Larger values reduce checkpoint frequency
min_wal_size = '1GB'                      -- Keep minimum WAL files
wal_compression = on                      -- Compress WAL records
wal_buffers = '64MB'                      -- WAL write buffer

-- Checkpoint settings
checkpoint_completion_target = 0.9        -- Spread checkpoints over 90% of interval
checkpoint_timeout = '15min'              -- Maximum time between checkpoints

-- Planner settings
random_page_cost = 1.1                    -- Lower for SSDs (default 4.0 for HDDs)
seq_page_cost = 1.0                       -- Sequential read cost
cpu_tuple_cost = 0.01                     -- CPU processing cost per tuple
cpu_index_tuple_cost = 0.005              -- CPU cost for index tuple processing

-- Enable key features
enable_hashjoin = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on                       -- Don't disable unless specific need