Postgresql

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

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

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

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)

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

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

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

-- 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, BUFFERS, FORMAT TEXT) SELECT ...;

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

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

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

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

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

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

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