postgresql

name	postgresql
description	Use when writing complex PostgreSQL queries, diagnosing slow queries with EXPLAIN ANALYZE, designing indexes, handling concurrent writes, or planning safe schema migrations on large tables.

PostgreSQL Patterns

Advanced querying, indexing, and schema design for PostgreSQL 14+.

When to Activate

• Writing window functions, CTEs, or recursive queries
• Querying JSONB columns
• Designing indexes or diagnosing missing indexes
• Interpreting EXPLAIN ANALYZE output
• Handling concurrent writes (upsert, locking, transactions)
• Full-text search without Elasticsearch
• Planning schema migrations safely

---

Window Functions

Window functions compute values across rows related to the current row — without collapsing them like GROUP BY.

-- ROW_NUMBER: unique rank per partition
SELECT
  user_id,
  order_id,
  total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders;

-- Get each user's latest order
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;

-- RANK vs DENSE_RANK vs ROW_NUMBER
-- RANK:       1,2,2,4   (gaps after tie)
-- DENSE_RANK: 1,2,2,3   (no gaps)
-- ROW_NUMBER: 1,2,3,4   (always unique)

-- Running total
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM transactions;

-- Moving average (last 7 days)
SELECT
  date,
  value,
  AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM metrics;

-- LAG/LEAD: access previous/next row
SELECT
  date,
  revenue,
  LAG(revenue)  OVER (ORDER BY date) AS prev_revenue,
  LEAD(revenue) OVER (ORDER BY date) AS next_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS day_over_day
FROM daily_revenue;

-- NTILE: divide rows into buckets
SELECT user_id, spend,
  NTILE(4) OVER (ORDER BY spend DESC) AS quartile  -- 1=top 25%
FROM user_spend;

---

CTEs (Common Table Expressions)

-- Basic CTE — improves readability, reuse within query
WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE status = 'active' AND last_login > NOW() - INTERVAL '30 days'
),
user_orders AS (
  SELECT user_id, COUNT(*) AS order_count, SUM(total) AS lifetime_value
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
)
SELECT
  u.name,
  u.email,
  COALESCE(o.order_count, 0) AS orders,
  COALESCE(o.lifetime_value, 0) AS ltv
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id
ORDER BY o.lifetime_value DESC NULLS LAST;

-- Recursive CTE — hierarchies, trees, paths
WITH RECURSIVE category_tree AS (
  -- Anchor: start from roots
  SELECT id, name, parent_id, 0 AS depth, ARRAY[id] AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

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

-- Writable CTEs (INSERT/UPDATE/DELETE in CTE)
WITH deleted_sessions AS (
  DELETE FROM sessions
  WHERE expires_at < NOW()
  RETURNING user_id, session_id
)
INSERT INTO audit_log (user_id, action, metadata)
SELECT user_id, 'session_expired', jsonb_build_object('session_id', session_id)
FROM deleted_sessions;

---

JSONB

JSONB stores JSON as binary — indexable and queryable.

-- Schema
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type TEXT NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Query operators
SELECT payload->>'email'          FROM users;          -- text (->>' extracts as text)
SELECT payload->'address'         FROM users;          -- JSONB subtree
SELECT payload#>>'{address,city}' FROM users;          -- nested path as text
SELECT payload#>'{address}'       FROM users;          -- nested path as JSONB

-- Filter on JSONB fields
SELECT * FROM events WHERE payload->>'type' = 'purchase';
SELECT * FROM events WHERE (payload->>'amount')::numeric > 100;
SELECT * FROM events WHERE payload @> '{"status": "active"}';  -- contains
SELECT * FROM events WHERE payload ? 'discount_code';           -- key exists
SELECT * FROM events WHERE payload ?| ARRAY['tag1', 'tag2'];    -- any key exists
SELECT * FROM events WHERE payload ?& ARRAY['tag1', 'tag2'];    -- all keys exist

-- Update JSONB
UPDATE users
SET metadata = jsonb_set(metadata, '{last_login}', to_jsonb(NOW()))
WHERE id = '123';

-- Remove key
UPDATE users SET metadata = metadata - 'temp_token' WHERE id = '123';

-- Aggregate into JSONB
SELECT jsonb_agg(row_to_json(u)) FROM users u WHERE active;
SELECT jsonb_object_agg(key, value) FROM settings;

-- Unnest JSONB array
SELECT elem->>'name' FROM products, jsonb_array_elements(tags) AS elem;

---

Indexes

B-tree (default — equality and range)

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Composite: order matters — put equality columns first, range last
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);
-- This index helps: WHERE user_id = ? AND status = ? AND created_at > ?
-- This index helps: WHERE user_id = ? AND status = ?
-- This index doesn't help much: WHERE status = ? AND created_at > ?  (skipped user_id)

Partial index — index only matching rows

-- Index only active orders — much smaller than full index
CREATE INDEX idx_active_orders ON orders(user_id, created_at)
WHERE status = 'active';

-- Index only non-null values
CREATE INDEX idx_users_stripe_id ON users(stripe_customer_id)
WHERE stripe_customer_id IS NOT NULL;

Covering index — include extra columns to avoid heap fetches

-- Query: SELECT status, total FROM orders WHERE user_id = ?
-- Without INCLUDE: index lookup + heap fetch for status, total
-- With INCLUDE: index lookup only (index-only scan)
CREATE INDEX idx_orders_user_covering ON orders(user_id) INCLUDE (status, total);

GIN — full-text search and JSONB

-- JSONB containment queries (@>, ?)
CREATE INDEX idx_events_payload ON events USING GIN (payload);

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

Expression index

-- Query: WHERE lower(email) = ?
CREATE INDEX idx_users_email_lower ON users (lower(email));

-- Query: WHERE DATE(created_at) = ?
CREATE INDEX idx_orders_date ON orders (DATE(created_at));

---

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

Key things to look for:

-- GOOD: index scan
Index Scan using idx_orders_user_id on orders  (cost=0.43..8.45 rows=1)
  Index Cond: (user_id = '123')
  Actual Rows: 1, Loops: 1

-- BAD: sequential scan on large table
Seq Scan on orders  (cost=0.00..45000.00 rows=1000000)   ← missing index
  Filter: (user_id = '123')
  Rows Removed by Filter: 999999

-- BAD: nested loop with many iterations
Nested Loop  (rows=10000)
  -> Seq Scan on orders         ← no index on join column
  -> Index Scan using ...

-- Check Buffers output for cache hit ratio
Buffers: shared hit=95 read=5    ← 95% from cache (good)
Buffers: shared hit=10 read=990  ← mostly disk reads (bad — consider index or caching)

Workflow: run EXPLAIN ANALYZE, look for Seq Scan on large tables and Rows Removed by Filter ratios. Add index on the filter/join column, re-check.

---

Transactions and Locking

-- Explicit transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;   -- or ROLLBACK;

-- SELECT FOR UPDATE — lock rows to prevent concurrent modification
BEGIN;
SELECT * FROM inventory WHERE product_id = '123' FOR UPDATE;
-- Other transactions block here until we COMMIT
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = '123';
COMMIT;

-- SELECT FOR UPDATE SKIP LOCKED — skip locked rows (job queue pattern)
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Isolation levels
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- default
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;   -- no phantom reads within tx
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;      -- full serialization (slowest)

-- Advisory locks — application-level named locks
SELECT pg_advisory_lock(12345);       -- session lock
SELECT pg_advisory_xact_lock(12345);  -- transaction lock (auto-released on commit)

---

Upsert (INSERT ... ON CONFLICT)

-- Insert or ignore
INSERT INTO user_preferences (user_id, key, value)
VALUES ('123', 'theme', 'dark')
ON CONFLICT (user_id, key) DO NOTHING;

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

-- Conditional upsert — only update if new value is newer
ON CONFLICT (id) DO UPDATE SET
  value = EXCLUDED.value
WHERE user_preferences.updated_at < EXCLUDED.updated_at;

---

LATERAL Joins

LATERAL lets a subquery reference columns from tables to its left — like a correlated subquery but returning multiple rows.

-- Latest 3 orders per user
SELECT u.name, o.order_id, o.total
FROM users u
CROSS JOIN LATERAL (
  SELECT order_id, total
  FROM orders
  WHERE user_id = u.id          -- references u from outer query
  ORDER BY created_at DESC
  LIMIT 3
) o;

-- Useful with functions that return sets
SELECT u.id, tags.tag
FROM users u
CROSS JOIN LATERAL jsonb_array_elements_text(u.tags) AS tags(tag);

---

Full-Text Search

-- tsvector: preprocessed searchable document
-- tsquery: search query with operators

-- Create a generated column (auto-updated)
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))
  ) STORED;

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Search
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgres & indexing') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

-- Highlight matching terms
SELECT ts_headline('english', body, to_tsquery('postgres & indexing'),
  'StartSel=<mark>, StopSel=</mark>, MaxFragments=2'
) FROM articles;

-- Phrase search (words in order)
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'full text search');

---

Migration Strategy

-- Safe for large tables (doesn't lock):

-- 1. Add nullable column first (no default needed, no lock)
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMPTZ;

-- 2. Backfill in batches (avoid one giant UPDATE that locks)
UPDATE orders SET shipped_at = completed_at
WHERE id IN (SELECT id FROM orders WHERE shipped_at IS NULL LIMIT 10000);
-- Repeat until done, or use pg_cron / application loop

-- 3. Add constraint after backfill
ALTER TABLE orders ALTER COLUMN shipped_at SET NOT NULL;

-- Add index concurrently — no table lock
CREATE INDEX CONCURRENTLY idx_orders_shipped_at ON orders(shipped_at);

-- Drop index concurrently
DROP INDEX CONCURRENTLY idx_old_index;

-- Rename column (instant)
ALTER TABLE orders RENAME COLUMN old_name TO new_name;

---

Useful Functions

-- Date/time
NOW()                             -- current timestamp with timezone
CURRENT_DATE                      -- today as date
DATE_TRUNC('week', created_at)    -- truncate to week start
created_at + INTERVAL '7 days'    -- date arithmetic
EXTRACT(EPOCH FROM duration)      -- seconds as number

-- String
COALESCE(field, 'default')        -- first non-null
NULLIF(field, '')                 -- null if empty string
CONCAT_WS(', ', a, b, c)          -- join with separator, skips nulls
REGEXP_REPLACE(text, pattern, replacement, 'g')
LEFT(text, 100)                   -- first 100 chars

-- Array
ARRAY_AGG(id ORDER BY created_at) -- aggregate into array
UNNEST(tags)                      -- expand array to rows
array_length(tags, 1)             -- length of 1-dimensional array

-- UUID
gen_random_uuid()                 -- generate UUID v4 (pg 13+)

---

Red Flags

• Missing index on foreign key columns — PostgreSQL does not auto-index FK columns; every child(parent_id) that appears in a JOIN or WHERE needs a manual CREATE INDEX, or every lookup is a sequential scan
• CREATE INDEX without CONCURRENTLY on a live table — plain CREATE INDEX acquires a full table lock and blocks all writes for the duration; always use CREATE INDEX CONCURRENTLY in production migrations
• Single giant UPDATE to backfill a new column — UPDATE orders SET shipped_at = ... on millions of rows locks the table and blocks production traffic; backfill in batches of 10–50k rows via a loop or pg_cron
• EXPLAIN without ANALYZE and BUFFERS — EXPLAIN shows estimated costs only; EXPLAIN (ANALYZE, BUFFERS) shows actual row counts, actual time, and cache hit ratios — always use both flags when diagnosing performance
• SELECT * on large tables in joins — selecting all columns brings unnecessary data from disk and prevents index-only scans; always project only the columns you need
• N+1 queries in application code — fetching a list then querying for each row's related data in a loop is O(n) round trips; use a single JOIN or a single IN query with application-side grouping
• NOT IN with a subquery that can return NULLs — if the subquery returns any NULL, NOT IN returns no rows at all due to three-valued logic; use NOT EXISTS or LEFT JOIN ... WHERE right.id IS NULL instead

Checklist

• Foreign key columns have indexes (CREATE INDEX ON child(parent_id))
• Composite indexes put equality columns first, range columns last
• EXPLAIN ANALYZE run on any query returning > 10k rows
• Partial indexes used for queries with constant WHERE conditions
• Concurrent DDL (CREATE INDEX CONCURRENTLY, batched UPDATE) for large tables
• ON CONFLICT used for upsert instead of SELECT then INSERT/UPDATE
• FOR UPDATE SKIP LOCKED for queue patterns instead of application-level locking
• JSONB columns have GIN index when used with @> or ? operators
• Migrations add nullable column → backfill → add NOT NULL (never the reverse)