Postgres Expert

Optimization patterns:

• Add btree indexes on WHERE/JOIN columns
• Use composite indexes with optimal column ordering (equality, sort, range)
• Create covering indexes to avoid table lookups
• Use partial indexes for filtered queries: ⁠CREATE INDEX idx ON table (col) WHERE active = true
• Run ⁠ANALYZE after bulk data changes
• Set ⁠random_page_cost = 1.1 for SSDs (default 4.0 is for HDDs)

JSONB Operations

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;

Advanced Indexing

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;

Partitioning

When to partition:

• Tables >100GB
• Time-series data with regular archival
• Regional data requiring physical separation
• Query patterns that filter on partition key

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

Connection Management

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

Autovacuum Tuning

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

Performance Configuration

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

Replication Monitoring

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

Useful Extensions

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

Code Review Checklist

Query Performance:

• EXPLAIN (ANALYZE, BUFFERS) shows efficient plans
• No sequential scans on large tables
• Composite indexes match query patterns
• Covering indexes avoid table lookups
• Partial indexes for filtered queries

JSONB:

• GIN indexes on JSONB columns
• jsonb_path_ops for containment-only queries
• Expression indexes for frequently queried paths
• Proper operators used (@>, ?, ->, ->>)

Schema:

• Partitioning for tables >100GB or time-series data
• Foreign keys with appropriate ON DELETE/UPDATE
• Appropriate data types (not TEXT for everything)
• NOT NULL where appropriate

Connections:

• PgBouncer configured for high-traffic apps
• Pool sized correctly (not too large)
• Connection cleanup in error paths
• No long-running idle transactions

Maintenance:

• Autovacuum tuned for table characteristics
• Indexes created with CONCURRENTLY in production
• ANALYZE run after bulk data changes
• Monitoring for transaction ID wraparound

Security:

• Parameterized queries (no SQL injection)
• Least-privilege user roles
• SSL/TLS for connections
• No credentials in code