PostgreSQL Pro Playbook

01UPSERT — INSERT ... ON CONFLICT

Scenario: syncing user profiles from an external system — insert if new, update if the email already exists. No race conditions, one statement.

CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text UNIQUE NOT NULL,
  full_name  text,
  login_count int DEFAULT 0,
  updated_at timestamptz DEFAULT now()
);

INSERT INTO users (email, full_name)
VALUES ('ravi@corp.com', 'Ravi Kumar')
ON CONFLICT (email) DO UPDATE SET
  full_name   = EXCLUDED.full_name,
  login_count = users.login_count + 1,
  updated_at  = now()
RETURNING id, email, login_count;

02JSONB — schemaless data with real indexes

Scenario: product catalog where every category has different attributes. You want NoSQL flexibility with SQL joins and ACID.

CREATE TABLE products (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL,
  attrs jsonb NOT NULL DEFAULT '{}'
);
CREATE INDEX idx_products_attrs ON products USING gin (attrs);

INSERT INTO products (name, attrs) VALUES
('ThinkPad X1',  '{"type":"laptop","ram_gb":32,"tags":["business","14inch"]}'),
('Pixel 9',      '{"type":"phone","ram_gb":12,"tags":["android","5g"]}'),
('MacBook Air',  '{"type":"laptop","ram_gb":16,"tags":["m3","13inch"]}');

SELECT name, attrs->>'ram_gb' AS ram, attrs->'tags' AS tags
FROM products
WHERE attrs @> '{"type":"laptop"}'
  AND (attrs->>'ram_gb')::int >= 16;

03Top-N per group — ROW_NUMBER()

Scenario: "show the 2 highest-paid employees in each department" — the classic interview question, solved the pro way.

CREATE TABLE employees (id int, name text, dept text, salary int);
INSERT INTO employees VALUES
(1,'Asha','Eng',180000),(2,'Vikram','Eng',165000),
(3,'Meera','Eng',150000),(4,'John','Sales',120000),
(5,'Priya','Sales',140000),(6,'Dev','Sales',110000);

SELECT * FROM (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) t WHERE rn <= 2;

04Running totals & moving averages

Scenario: finance dashboard — cumulative revenue and a 3-day moving average, straight from SQL, no app-side loops.

CREATE TABLE daily_sales (day date, revenue numeric);
INSERT INTO daily_sales VALUES
('2026-06-01',1000),('2026-06-02',1500),('2026-06-03',800),
('2026-06-04',2000),('2026-06-05',1200);

SELECT day, revenue,
  SUM(revenue) OVER (ORDER BY day) AS running_total,
  ROUND(AVG(revenue) OVER (
    ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 0) AS ma_3day
FROM daily_sales;

05Recursive CTE — org charts, categories, BOMs

Scenario: walk an employee → manager tree of any depth in a single query. Works for category trees, folder structures, bill of materials.

CREATE TABLE org (id int PRIMARY KEY, name text, manager_id int);
INSERT INTO org VALUES
(1,'CEO',NULL),(2,'VP Eng',1),(3,'VP Sales',1),
(4,'Eng Mgr',2),(5,'Dev A',4),(6,'Dev B',4);

WITH RECURSIVE tree AS (
  SELECT id, name, manager_id, 0 AS depth, name::text AS path
  FROM org WHERE manager_id IS NULL
  UNION ALL
  SELECT o.id, o.name, o.manager_id, t.depth + 1, t.path || ' → ' || o.name
  FROM org o JOIN tree t ON o.manager_id = t.id
)
SELECT depth, path FROM tree ORDER BY path;

06LATERAL join — "for each row, run a subquery"

Scenario: for every customer, fetch their 2 most recent orders. LATERAL is a correlated subquery that can return multiple rows and columns.

CREATE TABLE customers (id int PRIMARY KEY, name text);
CREATE TABLE orders (id int, customer_id int, total numeric, placed_at date);
INSERT INTO customers VALUES (1,'Acme'),(2,'Globex');
INSERT INTO orders VALUES
(10,1,500,'2026-06-01'),(11,1,900,'2026-06-20'),(12,1,300,'2026-05-10'),
(13,2,1200,'2026-06-15');

SELECT c.name, o.id AS order_id, o.total, o.placed_at
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, total, placed_at
  FROM orders
  WHERE customer_id = c.id
  ORDER BY placed_at DESC LIMIT 2
) o;

07Full-text search — no Elasticsearch needed

Scenario: search articles with stemming ("running" matches "run"), ranking, and an index — built into Postgres.

CREATE TABLE articles (
  id    int PRIMARY KEY,
  title text,
  body  text,
  tsv   tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', body),  'B')
  ) STORED
);
CREATE INDEX idx_articles_tsv ON articles USING gin(tsv);

INSERT INTO articles VALUES
(1,'Index tuning guide','How indexes speed up queries',DEFAULT),
(2,'Vacuum internals','Why vacuuming keeps tables fast',DEFAULT),
(3,'Query speed tips','Indexing strategies for speedy queries',DEFAULT);

SELECT id, title, ts_rank(tsv, q) AS rank
FROM articles, websearch_to_tsquery('english', 'index speed') q
WHERE tsv @@ q
ORDER BY rank DESC;

08Partial indexes — index only what you query

Scenario: 50M orders, 99% are 'completed', your app only ever polls the pending ones. Index just those.

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';   -- indexes ~1% of rows

SELECT id, created_at FROM orders
WHERE status = 'pending' ORDER BY created_at LIMIT 10;

09Covering indexes — Index-Only Scans

Scenario: a hot lookup query hitting the table millions of times/day. Put every needed column in the index so Postgres never touches the heap.

CREATE INDEX idx_users_email_inc ON users (email) INCLUDE (full_name, login_count);

EXPLAIN ANALYZE
SELECT full_name, login_count FROM users WHERE email = 'ravi@corp.com';

10Keyset pagination — OFFSET is a trap

Scenario: infinite-scroll feed. OFFSET 100000 reads and throws away 100k rows every page. Keyset reads only what it returns.

SELECT id, title FROM posts
ORDER BY created_at DESC, id DESC
OFFSET 100000 LIMIT 20;
-- 480 ms, scans 100,020 rows

SELECT id, title FROM posts
WHERE (created_at, id) < ('2026-05-01 10:00', 88231)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 0.4 ms, scans 20 rows

11Job queue — FOR UPDATE SKIP LOCKED

Scenario: 20 workers pulling jobs from one table. Without SKIP LOCKED they all fight for the same row; with it, each worker instantly grabs a different job.

CREATE TABLE jobs (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb,
  status  text DEFAULT 'queued',
  run_at  timestamptz DEFAULT now()
);
CREATE INDEX ON jobs (run_at) WHERE status = 'queued';

UPDATE jobs SET status = 'running'
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'queued' AND run_at <= now()
  ORDER BY run_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING id, payload;

12DISTINCT ON — latest row per key

Scenario: sensor readings table — get the most recent reading per device. Postgres-only syntax, wonderfully terse.

CREATE TABLE readings (device_id int, temp numeric, at timestamptz);
INSERT INTO readings VALUES
(1, 21.5, '2026-07-03 09:00'),(1, 22.1, '2026-07-03 10:00'),
(2, 30.0, '2026-07-03 09:30'),(2, 29.4, '2026-07-03 09:45');

SELECT DISTINCT ON (device_id)
       device_id, temp, at
FROM readings
ORDER BY device_id, at DESC;

13FILTER clause — pivot tables in one pass

Scenario: one row per month with signups split by plan — a pivot report without CASE WHEN soup.

SELECT date_trunc('month', created_at)::date AS month,
  COUNT(*)                                    AS total,
  COUNT(*) FILTER (WHERE plan = 'free')     AS free,
  COUNT(*) FILTER (WHERE plan = 'pro')      AS pro,
  COUNT(*) FILTER (WHERE plan = 'enterprise') AS ent
FROM signups
GROUP BY 1 ORDER BY 1;

14generate_series — fill gaps in time series

Scenario: chart shows daily orders, but days with zero orders vanish from GROUP BY output. Generate the calendar, then LEFT JOIN.

SELECT d::date AS day, COALESCE(COUNT(o.id), 0) AS orders
FROM generate_series('2026-06-28'::date, '2026-07-02'::date, '1 day') d
LEFT JOIN orders o ON o.placed_at::date = d
GROUP BY d ORDER BY d;

15Declarative partitioning — big tables, small pain

Scenario: events table growing 50M rows/month. Partition by month: queries prune to one partition, old data drops instantly.

CREATE TABLE events (
  id   bigint GENERATED ALWAYS AS IDENTITY,
  type text,
  at   timestamptz NOT NULL
) PARTITION BY RANGE (at);

CREATE TABLE events_2026_06 PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE events_2026_07 PARTITION OF events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

EXPLAIN SELECT count(*) FROM events WHERE at >= '2026-07-01';

16Materialized views — precomputed dashboards

Scenario: a revenue-summary query joins 5 tables and takes 30s. The dashboard hits it every page load. Compute once, read instantly.

CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT date_trunc('month', placed_at)::date AS month,
       SUM(total) AS revenue, COUNT(*) AS orders
FROM orders GROUP BY 1;

CREATE UNIQUE INDEX ON mv_monthly_revenue (month);

-- refresh without blocking readers (needs the unique index):
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

17Row-Level Security — multi-tenancy in the DB

Scenario: SaaS app, many tenants in one table. One forgotten WHERE tenant_id = ? in app code leaks data across customers. RLS makes leaks impossible.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::int);

-- app sets tenant per connection/transaction:
SET app.tenant_id = '7';

SELECT id, tenant_id, amount FROM invoices; -- no WHERE clause!

18Arrays + unnest — tags without a join table

Scenario: posts have tags. For simple cases an array column with a GIN index beats a three-table many-to-many.

CREATE TABLE posts (id int PRIMARY KEY, title text, tags text[]);
CREATE INDEX ON posts USING gin(tags);
INSERT INTO posts VALUES
(1,'Tuning autovacuum', '{postgres,performance}'),
(2,'Intro to JSONB',    '{postgres,json}'),
(3,'React hooks',       '{javascript,react}');

SELECT title FROM posts WHERE tags @> '{postgres}';

SELECT tag, COUNT(*) FROM posts, unnest(tags) tag
GROUP BY tag ORDER BY 2 DESC;

19Generated columns — computed, stored, indexable

Scenario: you always search users by lowercased email and report on order line totals. Let the DB maintain the derived values.

CREATE TABLE line_items (
  qty        int NOT NULL,
  unit_price numeric NOT NULL,
  total      numeric GENERATED ALWAYS AS (qty * unit_price) STORED
);
INSERT INTO line_items (qty, unit_price) VALUES (3, 199.99), (2, 49.50);

20Audit log — triggers that never forget

Scenario: compliance asks "who changed this price and when?" A trigger writes every change to an append-only audit table, regardless of which app made the change.

CREATE TABLE audit_log (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tbl      text, op text,
  old_row  jsonb, new_row jsonb,
  by_user  text DEFAULT current_user,
  at       timestamptz DEFAULT now()
);

CREATE FUNCTION audit_trg() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (tbl, op, old_row, new_row)
  VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER products_audit
AFTER UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION audit_trg();

21EXPLAIN ANALYZE — read plans like a pro

Scenario: a query is slow. Before touching anything, see what the planner actually did — estimated vs actual rows is where the truth lives.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';

CREATE INDEX ON orders (customer_id, status);
ANALYZE orders;  -- refresh planner statistics

22pg_stat_statements — find the real problem queries

Scenario: "the database is slow." Don't guess — rank every query shape by total time consumed across all executions.

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;

SELECT substring(query, 1, 45) AS query,
       calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 1)  AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 3;

23Exclusion constraints — no double bookings, ever

Scenario: meeting-room booking. App-level overlap checks always race under concurrency. Let the database physically reject overlaps.

CREATE EXTENSION btree_gist;

CREATE TABLE bookings (
  room    int,
  during  tstzrange,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);

INSERT INTO bookings VALUES (1, '[2026-07-03 10:00, 2026-07-03 11:00)'); -- OK
INSERT INTO bookings VALUES (1, '[2026-07-03 10:30, 2026-07-03 11:30)'); -- ?

24Advisory locks — app-level mutexes in the DB

Scenario: 5 app servers run the same nightly billing job on cron. Only one should execute. No Zookeeper, no Redis — Postgres is the lock service.

-- each server tries; only one wins (non-blocking):
SELECT pg_try_advisory_lock(hashtext('billing-job')) AS acquired;

-- ... do the work if acquired = true, then:
SELECT pg_advisory_unlock(hashtext('billing-job'));

25LISTEN / NOTIFY — pub-sub without a broker

Scenario: invalidate app caches or wake up workers the instant a row changes — pushed by the database itself, no polling.

-- session A (worker):
LISTEN order_events;

-- trigger publishes on every new order:
CREATE FUNCTION notify_order() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('order_events',
    json_build_object('id', NEW.id, 'total', NEW.total)::text);
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER t_notify AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION notify_order();

26COPY — bulk load 100× faster than INSERT

Scenario: import a 10M-row CSV. Row-by-row INSERTs take an hour; COPY streams it in minutes.

-- server-side file, or from client via \copy in psql:
\copy sales(sold_at, sku, qty, amount) FROM 'sales.csv' WITH (FORMAT csv, HEADER);

-- export a query result just as easily:
\copy (SELECT * FROM mv_monthly_revenue) TO 'revenue.csv' WITH (FORMAT csv, HEADER);

27Soft delete — without breaking uniqueness

Scenario: "deleted" users must stay for audit, their emails must be reusable, and active-user queries must stay fast. Partial unique index solves all three.

ALTER TABLE users ADD COLUMN deleted_at timestamptz;

-- uniqueness only among the living:
CREATE UNIQUE INDEX uq_users_email_active ON users (email)
WHERE deleted_at IS NULL;

-- "delete":
UPDATE users SET deleted_at = now() WHERE id = 42;

-- same email can register again:
INSERT INTO users (email, full_name) VALUES ('ravi@corp.com', 'Ravi K'); -- OK ✅

28Transactions & isolation — pick the right guarantee

Scenario: transferring money between accounts. READ COMMITTED (the default) permits anomalies under concurrency that stricter levels prevent.

BEGIN ISOLATION LEVEL SERIALIZABLE;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- on conflict: ERROR 40001 serialization_failure → retry the transaction

29VACUUM & bloat — why tables get slow over time

Scenario: a heavily-updated table gets slower every week even though row count is flat. MVCC keeps old row versions ("dead tuples") until vacuum reclaims them.

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 3;

30Connection pooling — Postgres hates 1,000 connections

Scenario: traffic spike, app opens 800 connections, database melts at 40% idle CPU. Each Postgres connection is a full OS process — they're expensive even when idle.

SELECT state, count(*) FROM pg_stat_activity
GROUP BY state ORDER BY 2 DESC;

31NULL — three-valued logic bites everyone

Scenario: “find users NOT in the banned list” returns zero rows and nobody knows why. NULL is the culprit — SQL logic has three values: true, false, and unknown.

SELECT NULL = NULL;                      -- NULL  (not true!)
SELECT 5 <> NULL;                        -- NULL  (not true!)

-- the killer: NOT IN with a NULL in the list
SELECT * FROM users
WHERE id NOT IN (SELECT banned_id FROM bans);  -- bans has one NULL → 0 rows, always

-- the fixes
WHERE id IS DISTINCT FROM other_id       -- NULL-safe comparison
WHERE NOT EXISTS (SELECT 1 FROM bans b WHERE b.banned_id = users.id)
COALESCE(discount, 0)                     -- default a NULL
NULLIF(total, 0)                          -- avoid division by zero: x / NULLIF(y,0)

32timestamptz vs timestamp — always the former

Scenario: app servers in UTC, office in Pune, users worldwide. With plain timestamp, “9:00” means nothing — 9:00 where? Daylight-saving bugs ship twice a year, on schedule.

CREATE TABLE meetings (title text, starts_at timestamptz);
INSERT INTO meetings VALUES ('Standup', '2026-07-03 09:00:00+05:30'); -- stored as UTC instant

-- each client sees it in their own zone:
SET timezone = 'Asia/Kolkata';
SELECT starts_at FROM meetings;                       -- 2026-07-03 09:00:00+05:30
SET timezone = 'America/New_York';
SELECT starts_at FROM meetings;                       -- 2026-07-02 23:30:00-04

-- “what calendar day was that in Pune?” — be explicit:
SELECT (starts_at AT TIME ZONE 'Asia/Kolkata')::date;

33Zero-downtime migrations — DDL without an outage

Scenario: add an index to a 200M-row table on a live system. Plain CREATE INDEX blocks all writes for 20 minutes — that’s an outage with extra steps.

-- 1. Indexes: CONCURRENTLY builds without blocking writes (can't run in a transaction)
CREATE INDEX CONCURRENTLY idx_orders_email ON orders (email);

-- 2. Always set a lock timeout so migrations fail fast instead of queueing behind traffic
SET lock_timeout = '3s';

-- 3. NOT NULL on a big table, without the full-table-scan lock:
ALTER TABLE orders ADD CONSTRAINT orders_email_nn
  CHECK (email IS NOT NULL) NOT VALID;   -- instant: new rows only
ALTER TABLE orders VALIDATE CONSTRAINT orders_email_nn; -- scans with weak lock

-- 4. ADD COLUMN with a constant DEFAULT is instant since PG 11 — no rewrite:
ALTER TABLE orders ADD COLUMN source text DEFAULT 'web';

34Fast counts — COUNT(*) is honest but slow

Scenario: the admin dashboard shows “total users” and now takes 40 seconds, because exact COUNT(*) must visit every live row (MVCC — no maintained global counter exists).

-- 1. Planner estimate: instant, usually within ~1% on analyzed tables
SELECT reltuples::bigint AS estimate
FROM pg_class WHERE relname = 'users';

-- 2. Estimate for any filtered query — parse it from EXPLAIN:
EXPLAIN (FORMAT json) SELECT * FROM users WHERE country = 'IN';
-- → Plan.“Plan Rows”: 1,204,117

-- 3. Exact count, but only when someone truly needs it
SELECT count(*) FROM users;

35Find & kill duplicates — then lock the door

Scenario: a years-old table with no unique constraint on email. Find the dupes, keep the oldest row, delete the rest, and make recurrence impossible.

SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1;

DELETE FROM users WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at, id) rn
    FROM users) t
  WHERE rn > 1);

CREATE UNIQUE INDEX CONCURRENTLY uq_users_email ON users (email);

36Foreign keys — choose your ON DELETE story

Scenario: deleting a customer. What happens to their orders? That’s a business decision, and the FK clause is where you encode it.

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  total       numeric
);
-- Postgres does NOT index FK columns automatically. Do it yourself:
CREATE INDEX ON orders (customer_id);

37Constraining values — CHECK vs ENUM vs lookup table

Scenario: an order status column must only ever hold known values. Three tools, three trade-off profiles — veterans pick by how often the list changes.

-- 1. CHECK: simple, alterable
status text NOT NULL CHECK (status IN ('pending','paid','shipped'))

-- 2. ENUM: typed, ordered, compact
CREATE TYPE order_status AS ENUM ('pending','paid','shipped');

-- 3. Lookup table: values are data
CREATE TABLE statuses (code text PRIMARY KEY, label text, sort_order int);
status text NOT NULL REFERENCES statuses(code)

38Primary keys — identity vs UUIDv4 vs UUIDv7

Scenario: choosing PKs for a new system. Sequential bigints are fast but guessable and awkward across services; random UUIDs shred index locality. UUIDv7 threads the needle.

-- workhorse default:
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- distributed / client-generated / non-guessable (PG 18: uuidv7(); earlier: extension or app-side):
id uuid PRIMARY KEY DEFAULT uuidv7()

39EXISTS vs IN vs JOIN — say what you mean

Scenario: “customers who have at least one order.” Three phrasings, one intent — but JOIN multiplies rows, and NOT IN has a NULL trapdoor (#31).

-- ✅ EXISTS: stops at first match, never duplicates
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- ⚠️ JOIN: a customer with 500 orders appears 500 times
SELECT DISTINCT c.name FROM customers c JOIN orders o ON o.customer_id = c.id;

-- ✅ anti-join: customers with NO orders
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

40Fuzzy search — pg_trgm for typos and substrings

Scenario: users type “jhon smit” and expect to find John Smith. Full-text search (#07) matches words, not typos — trigram similarity handles misspellings and mid-word substrings.

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (full_name gin_trgm_ops);

-- leading-wildcard ILIKE now uses the index (impossible with btree):
SELECT full_name FROM users WHERE full_name ILIKE '%smit%';

-- typo-tolerant ranking:
SELECT full_name, similarity(full_name, 'jhon smit') AS score
FROM users
WHERE full_name % 'jhon smit'   -- % = "similar enough" operator
ORDER BY score DESC LIMIT 3;

41RETURNING + writable CTEs — atomic pipelines

Scenario: archive expired sessions — move rows from one table to another with no window where they exist in both or neither. One statement, fully atomic.

WITH moved AS (
  DELETE FROM sessions
  WHERE expires_at < now()
  RETURNING *
)
INSERT INTO sessions_archive SELECT * FROM moved
RETURNING id;

42Views as API contracts — refactor without fear

Scenario: three services and two BI tools query your tables directly. Every rename breaks someone. Put a view in between: the view is the promise, the tables are the implementation.

CREATE VIEW api_customers AS
SELECT id,
       full_name        AS name,
       email,
       created_at::date AS member_since
FROM customers
WHERE deleted_at IS NULL;      -- soft-delete filter (#27) baked in

-- later: rename the real column; repair the contract in the same transaction
BEGIN;
ALTER TABLE customers RENAME COLUMN full_name TO display_name;
CREATE OR REPLACE VIEW api_customers AS
SELECT id, display_name AS name, email, created_at::date AS member_since
FROM customers WHERE deleted_at IS NULL;
COMMIT;   -- consumers never saw a thing

43Batch updates in chunks — never one giant UPDATE

Scenario: backfill a new column on 80M rows. One UPDATE statement = hours-long transaction, gigabytes of WAL, replication lag, and a vacuum mountain. Chunk it.

-- repeat until 0 rows affected:
WITH batch AS (
  SELECT id FROM orders
  WHERE normalized_email IS NULL
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
)
UPDATE orders o SET normalized_email = lower(trim(email))
FROM batch WHERE o.id = batch.id;
-- commit, small sleep, repeat  (partial index on the WHERE keeps each pick instant, #08)

44Backups — pg_dump vs PITR, and the restore test

Scenario: someone runs DELETE FROM orders without a WHERE at 14:32. A nightly dump loses the whole day. Point-in-time recovery rewinds to 14:31.

-- logical dump: portable, per-database, great < ~100GB
pg_dump -Fc mydb > mydb.dump          # custom format: compressed, parallel-restorable
pg_restore -j 8 -d mydb_restored mydb.dump

-- physical + WAL archiving (PITR): base backup + every change since
pgbackrest --stanza=main backup       # or wal-g / barman
pgbackrest --stanza=main restore --type=time "--target=2026-07-03 14:31:00"

45Roles & least privilege — stop connecting as postgres

Scenario: the web app connects as a superuser “because it works”. One SQL injection now owns every database, can read every table, and can drop them too. Ten minutes of GRANTs fixes this forever.

-- app role: exactly CRUD, nothing else
CREATE ROLE app_rw LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA public TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;

-- future tables get the same grants automatically:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;

-- read-only role for BI, analysts, dashboards
CREATE ROLE app_ro LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA public TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;

46Extended statistics — when the planner multiplies wrong

Scenario: WHERE city = 'Pune' AND pincode = '411001' is estimated at 40 rows but returns 120,000 — the planner picked a nested loop and the query takes minutes. Why? It assumed the columns are independent.

CREATE STATISTICS stat_city_pin (dependencies)
  ON city, pincode FROM addresses;
ANALYZE addresses;

🎯Quiz — 200 questions, 5 at a time

Answer 5 questions per round — your progress and score are saved in this browser, so you can come back anytime and continue until all 200 are done. Score: 0 / 0Reset quiz