PostgreSQL for Developers: Indexes, EXPLAIN ANALYZE, JSONB, and Tuning for Real Workloads

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Create a B-tree index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index — only indexes rows matching the condition
CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending';

-- Expression index — indexes a computed value
CREATE INDEX idx_users_lower_email ON users(lower(email));

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Only 0.1% of orders are 'pending' — index just those
CREATE INDEX idx_orders_pending_created ON orders(created_at)
  WHERE status = 'pending';

-- Only index non-deleted users
CREATE INDEX idx_users_active_email ON users(email)
  WHERE deleted_at IS NULL;

-- Unique constraint only for active (non-cancelled) subscriptions
CREATE UNIQUE INDEX idx_subscriptions_active_user
  ON subscriptions(user_id)
  WHERE status != 'cancelled';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- Create GIN index on JSONB column for containment queries
CREATE INDEX idx_events_data ON events USING GIN(data);

-- Now this query uses the index
SELECT * FROM events WHERE data @> '{"type": "login"}';

-- Full-text search GIN index
CREATE INDEX idx_articles_fts ON articles
  USING GIN(to_tsvector('english', title || ' ' || body));

SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || body)
   @@ to_tsquery('english', 'kubernetes & deployment');

1
2
3
4
5

-- Table has 500M rows of sensor data inserted chronologically
-- A BRIN index is tiny (pages instead of rows) and effective for range queries
CREATE INDEX idx_sensor_readings_time ON sensor_readings
  USING BRIN(recorded_at)
  WITH (pages_per_range = 128);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- Tables with high sequential scans (possible missing indexes)
SELECT
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  ROUND(seq_scan::numeric / NULLIF(seq_scan + idx_scan, 0) * 100, 1) AS seq_scan_pct
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_tup_read DESC
LIMIT 20;

-- Unused indexes (wasting write performance)
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(indexrelid) DESC;

-- Index sizes
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

1
2
3
4
5
6
7
8

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.email
ORDER BY order_count DESC
LIMIT 10;

1
2
3
4
5
6
7
8
9

-- Seq Scan on a large table
Seq Scan on orders  (cost=0..52100.00 rows=1000000 ...)

-- Rows estimate vs actual wildly off (stale statistics)
(cost=... rows=100)   -- estimated
(actual rows=85000)   -- actual — 850x off!

-- Very high loops count (N+1 query hiding in the plan)
Nested Loop  (... loops=50000)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Enable the extension (add to postgresql.conf: shared_preload_libraries = 'pg_stat_statements')
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries by total time
SELECT
  LEFT(query, 100) AS query_snippet,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries with high variance (sometimes fast, sometimes slow)
SELECT
  LEFT(query, 100) AS query_snippet,
  calls,
  ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
  ROUND(stddev_exec_time / NULLIF(mean_exec_time, 0), 2) AS cv
FROM pg_stat_statements
WHERE calls > 100
ORDER BY cv DESC
LIMIT 10;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- Create a table with JSONB
CREATE TABLE events (
  id         BIGSERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  user_id    BIGINT NOT NULL,
  type       TEXT NOT NULL,
  data       JSONB NOT NULL DEFAULT '{}'
);

-- Insert
INSERT INTO events (user_id, type, data) VALUES
  (42, 'login',   '{"ip": "1.2.3.4", "user_agent": "Mozilla/5.0"}'),
  (42, 'purchase', '{"amount": 49.99, "items": ["widget", "gadget"], "currency": "USD"}'),
  (99, 'login',   '{"ip": "5.6.7.8", "mfa": true}');

-- Access operators
SELECT data->'amount'          FROM events WHERE type = 'purchase';  -- returns JSON
SELECT data->>'ip'             FROM events WHERE type = 'login';     -- returns text
SELECT data#>>'{items,0}'      FROM events WHERE type = 'purchase';  -- nested path

-- Containment: does this JSON contain this subset?
SELECT * FROM events WHERE data @> '{"mfa": true}';
SELECT * FROM events WHERE data @> '{"items": ["widget"]}';

-- Key existence
SELECT * FROM events WHERE data ? 'mfa';
SELECT * FROM events WHERE data ?| ARRAY['mfa', 'ip'];  -- has either key
SELECT * FROM events WHERE data ?& ARRAY['ip', 'user_agent'];  -- has both keys

-- Update JSONB
UPDATE events
SET data = data || '{"processed": true}'    -- merge/overwrite key
WHERE type = 'purchase';

UPDATE events
SET data = data - 'ip'                      -- remove key
WHERE type = 'login' AND user_id = 99;

1
2
3
4
5
6
7
8
9

-- GIN index for containment and key existence queries
CREATE INDEX idx_events_data ON events USING GIN(data);

-- Specific expression index for a frequently queried key
CREATE INDEX idx_events_ip ON events((data->>'ip'));

-- Partial GIN index on a subset of rows
CREATE INDEX idx_events_login_data ON events USING GIN(data)
  WHERE type = 'login';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- A notifications table where different notification types have different data
CREATE TABLE notifications (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL,
  type        TEXT NOT NULL,   -- 'email', 'sms', 'push', 'webhook'
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  sent_at     TIMESTAMPTZ,
  payload     JSONB NOT NULL
);

-- Email notification
INSERT INTO notifications (user_id, type, payload) VALUES
  (42, 'email', '{"to": "user@example.com", "subject": "Your order shipped", "template": "order_shipped", "order_id": 9981}');

-- Push notification
INSERT INTO notifications (user_id, type, payload) VALUES
  (42, 'push', '{"device_token": "abc123", "title": "Order shipped!", "badge": 1}');

-- Query email notifications for a user, extract specific fields
SELECT
  id,
  created_at,
  payload->>'to'       AS recipient,
  payload->>'subject'  AS subject
FROM notifications
WHERE user_id = 42
  AND type = 'email'
  AND sent_at IS NULL;

-- Aggregate over JSONB: count notifications by type per user
SELECT
  user_id,
  type,
  COUNT(*) AS count,
  MAX(created_at) AS latest
FROM notifications
GROUP BY user_id, type
ORDER BY user_id, count DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- Build a JSON summary of a user's recent orders
SELECT
  u.id,
  u.email,
  jsonb_agg(
    jsonb_build_object(
      'order_id', o.id,
      'total',    o.total,
      'status',   o.status,
      'items',    o.item_count
    )
    ORDER BY o.created_at DESC
  ) AS recent_orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# postgresql.conf — values for a server with 8 GB RAM dedicated to Postgres

# Memory
shared_buffers = 2GB          # 25% of RAM — Postgres's main cache
effective_cache_size = 6GB    # 75% of RAM — tells planner how much OS cache exists
work_mem = 64MB               # Per sort/hash operation (can be many simultaneously!)
maintenance_work_mem = 512MB  # For VACUUM, CREATE INDEX, etc.
wal_buffers = 64MB            # WAL write buffer

# Parallelism
max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4   # Parallel query workers per query

# WAL / Durability
wal_compression = on          # Compress WAL — usually a win
checkpoint_timeout = 15min    # How often to checkpoint
max_wal_size = 4GB            # Before forcing a checkpoint
min_wal_size = 1GB

# Connections
max_connections = 100         # Use a connection pooler (PgBouncer) for high concurrency

# Query planner cost settings (tune for SSD)
random_page_cost = 1.1        # Default is 4.0 — SSD random I/O is near sequential
effective_io_concurrency = 200 # For SSD — number of concurrent I/O operations

# Logging (useful for development and slow query analysis)
log_min_duration_statement = 1000   # Log queries taking > 1 second
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0                  # Log all temp file creation

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- Set for a specific session running a heavy analytical query
SET work_mem = '512MB';
SELECT ... complex aggregation ...;
RESET work_mem;

-- Or in a transaction
BEGIN;
SET LOCAL work_mem = '256MB';
-- heavy query here
COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

# pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction       # transaction pooling — most efficient
max_client_conn = 1000        # clients can connect freely
default_pool_size = 20        # only 20 real backend connections
min_pool_size = 5
reserve_pool_size = 5

log_connections = 0
log_disconnections = 0

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- Check autovacuum activity
SELECT
  schemaname,
  tablename,
  n_dead_tup,
  n_live_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Tables with bloat (dead tuples as % of total)
-- If dead_pct is consistently > 10-20%, autovacuum isn't keeping up

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- High-write table: vacuum more aggressively
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- vacuum when 1% dead (default 20%)
  autovacuum_analyze_scale_factor = 0.005, -- analyze when 0.5% changed
  autovacuum_vacuum_cost_delay = 2         -- less throttling (default 20ms)
);

-- Large mostly-read table: back off autovacuum
ALTER TABLE historical_data SET (
  autovacuum_vacuum_scale_factor = 0.1,
  autovacuum_analyze_scale_factor = 0.05
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Insert or update (upsert)
INSERT INTO user_preferences (user_id, key, value, updated_at)
VALUES (42, 'theme', 'dark', NOW())
ON CONFLICT (user_id, key)
DO UPDATE SET
  value = EXCLUDED.value,
  updated_at = EXCLUDED.updated_at;

-- Insert or do nothing
INSERT INTO event_dedup (event_id, received_at)
VALUES ('evt_abc123', NOW())
ON CONFLICT (event_id) DO NOTHING;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- Running total of revenue
SELECT
  date_trunc('day', created_at) AS day,
  SUM(amount) AS daily_revenue,
  SUM(SUM(amount)) OVER (ORDER BY date_trunc('day', created_at)) AS running_total
FROM orders
WHERE created_at > NOW() - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

-- Rank users by spend within each country
SELECT
  user_id,
  country,
  total_spend,
  RANK() OVER (PARTITION BY country ORDER BY total_spend DESC) AS country_rank
FROM (
  SELECT u.id AS user_id, u.country, SUM(o.total) AS total_spend
  FROM users u JOIN orders o ON o.user_id = u.id
  GROUP BY u.id, u.country
) spend;

-- Find previous and next values (lead/lag)
SELECT
  order_id,
  created_at,
  LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_at,
  created_at - LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS days_since_last
FROM orders;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- Common Table Expression for readability
WITH recent_orders AS (
  SELECT * FROM orders
  WHERE created_at > NOW() - INTERVAL '7 days'
    AND status != 'cancelled'
),
high_value_users AS (
  SELECT user_id, SUM(total) AS week_spend
  FROM recent_orders
  GROUP BY user_id
  HAVING SUM(total) > 500
)
SELECT u.email, hvu.week_spend
FROM high_value_users hvu
JOIN users u ON u.id = hvu.user_id
ORDER BY hvu.week_spend DESC;

-- Recursive CTE: walk a category tree
WITH RECURSIVE category_tree AS (
  -- Base case: root categories
  SELECT id, name, parent_id, 0 AS depth, name::text AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive case: children
  SELECT c.id, c.name, c.parent_id, ct.depth + 1,
         ct.path || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT * FROM category_tree ORDER BY path;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- Instead of querying recent orders per user in application code (N+1),
-- use LATERAL to do it in one query
SELECT
  u.id,
  u.email,
  recent.orders
FROM users u
CROSS JOIN LATERAL (
  SELECT jsonb_agg(
    jsonb_build_object('id', o.id, 'total', o.total, 'status', o.status)
    ORDER BY o.created_at DESC
  ) AS orders
  FROM orders o
  WHERE o.user_id = u.id
  LIMIT 5
) recent
WHERE u.created_at > NOW() - INTERVAL '30 days';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- Active queries and wait events
SELECT
  pid,
  now() - query_start AS duration,
  state,
  wait_event_type,
  wait_event,
  LEFT(query, 80) AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < NOW() - INTERVAL '5 seconds'
ORDER BY duration DESC;

-- Lock contention
SELECT
  blocked.pid AS blocked_pid,
  blocked_activity.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid
JOIN pg_catalog.pg_locks blocking ON blocking.relation = blocked.relation
  AND blocking.locktype = blocked.locktype
  AND blocking.pid != blocked.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking.pid = blocking_activity.pid
WHERE NOT blocked.granted;

-- Table sizes with bloat estimate
SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
  pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) AS index_size,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;