MindMap — Database (canvas)

+−Fit⛶

6. Practice

5. Postgres Features

4. Performance & Operations

3. Writing Data & Concurrency

2. Combining Data

1. Fundamentals

PostgreSQL Queries

PostgreSQL Queries

Overview

• Purpose: Hub for PostgreSQL query notes — interview prep and day-to-day work
• Approach: Every note uses one shared e-commerce dataset from E-commerce Practice Queries, so examples can be pasted into a scratch database and run as-is
• Verified Against: PostgreSQL 17 — features from newer/older versions are labelled (e.g. “PG 15+”)
• Theory Lives In: Backend/Databases Concepts — ACID, Database Indexing, Concurrency Control, Database Normalisation, Database Replication, Database Partition
• Mind Map: Backend/Database/MindMap.canvas

Quick Setup

docker run --rm -d --name pg-practice -e POSTGRES_PASSWORD=x -p 55432:5432 postgres:17
docker exec -it pg-practice psql -U postgres
# paste the "Sample Schema" and "Seed Data" blocks from E-commerce Practice Queries

Recommended Study Order

1. Fundamentals

1. DDL Data Types and Constraints — CREATE/ALTER/DROP, identity vs serial, text vs varchar, numeric, timestamptz, uuid, jsonb, arrays, PK/FK/UNIQUE/CHECK
2. SELECT Fundamentals — logical processing order, WHERE, ORDER BY, OFFSET vs keyset pagination, DISTINCT ON, NULL / three-valued logic, CASE
3. Aggregation and GROUP BY — GROUP BY, HAVING, FILTER, GROUPING SETS / ROLLUP / CUBE, string_agg / array_agg, fan-out

2. Combining Data

4. Joins — inner/outer/cross/self joins, ON vs WHERE, semi/anti joins, NOT IN pitfall, LATERAL
5. Subqueries and CTEs — correlated subqueries, EXISTS/IN/ANY/ALL, WITH, recursive CTEs, MATERIALIZED
6. Window Functions — ROW_NUMBER/RANK/DENSE_RANK, LAG/LEAD, running totals, ROWS vs RANGE, top-N, gaps & islands

3. Writing Data and Concurrency

7. Inserts Updates Upserts and MERGE — RETURNING, ON CONFLICT, UPDATE … FROM, DELETE … USING, MERGE, COPY
8. Transactions and Locking — BEGIN/SAVEPOINT, isolation levels, FOR UPDATE, SKIP LOCKED job queue, advisory locks

4. Performance and Operations

9. Indexes and Query Performance — B-tree/partial/expression/INCLUDE/GIN, CONCURRENTLY, EXPLAIN ANALYZE, anti-patterns
10. Admin and Inspection Queries — pg_stat_activity, killing queries, locks, pg_stat_statements, sizes, vacuum health

5. Postgres Features

11. JSONB and Arrays — ->, ->>, @>, ?, JSONPath, SQL/JSON (PG 17), GIN indexing, array operators

12. Views Functions and Triggers — views, materialized views + REFRESH CONCURRENTLY, SQL/PL/pgSQL functions, procedures, triggers

13. UUID Versions — v1–v8, why v4 hurts B-tree PKs (measured), v7 for keys, uuidv7() (PG 18), v7 function for PG 17

6. Practice

14. E-commerce Practice Queries — schema, seed data, 20 graded questions (easy → hard) with solutions

Note Map

#	Note	Group	Key Interview Topics
1	DDL Data Types and Constraints	Fundamentals	serial vs identity, FK ON DELETE, NULL in CHECK/UNIQUE
2	SELECT Fundamentals	Fundamentals	Processing order, keyset pagination, NULL logic
3	Aggregation and GROUP BY	Fundamentals	WHERE vs HAVING, FILTER, ROLLUP
4	Joins	Combining	LEFT JOIN filters, NOT IN vs NOT EXISTS, LATERAL
5	Subqueries and CTEs	Combining	Correlated subqueries, recursive org chart
6	Window Functions	Combining	Top-N per group, running totals, LAG
7	Inserts Updates Upserts and MERGE	Writing	UPSERT, MERGE, bulk load
8	Transactions and Locking	Writing	Isolation levels, SKIP LOCKED, lost updates
9	Indexes and Query Performance	Performance	Why isn’t my index used, EXPLAIN
10	Admin and Inspection Queries	Performance	Find & kill blockers, slow query log
11	JSONB and Arrays	Features	json vs jsonb, GIN
12	Views Functions and Triggers	Features	Matview refresh, trigger use cases
13	UUID Versions	Features	UUIDv4 vs v7 as PK, bigint vs UUID
14	E-commerce Practice Queries	Practice	All of the above

Postgres vs Standard SQL Cheat Sheet

Feature	Postgres	Standard / Other DBs
Auto IDs	GENERATED … AS IDENTITY (or legacy serial)	Identity (std), AUTO_INCREMENT (MySQL)
Return Written Rows	RETURNING on INSERT/UPDATE/DELETE/MERGE	OUTPUT (SQL Server), none in MySQL
Upsert	ON CONFLICT DO UPDATE	MERGE (std), ON DUPLICATE KEY (MySQL)
First Row per Group	DISTINCT ON	ROW_NUMBER()
Case-insensitive LIKE	ILIKE	lower(x) LIKE …
Conditional Aggregate	FILTER (WHERE …) (std, MySQL lacks it)	sum(CASE …)
Casting	x::int	CAST(x AS int)
NULL-safe Equality	IS [NOT] DISTINCT FROM	<=> (MySQL)
Transactional DDL	Yes	No in MySQL/Oracle
Arrays / JSONB	First-class types with indexes	Limited

Summary

• Start: Load the practice schema, then work through the notes in order 1 → 13
• Interview Focus: NULL logic, joins vs anti-joins, window functions, CTEs, UPSERT, isolation & locking, index anti-patterns
• Real Work Focus: Keyset pagination, RETURNING, SKIP LOCKED, CREATE INDEX CONCURRENTLY, EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements

Link to original

DDL Data Types and Constraints

DDL Data Types and Constraints

Overview

• DDL (Data Definition Language): Defines structure — CREATE, ALTER, DROP, TRUNCATE
• DML (Data Manipulation Language): Works with rows — SELECT, INSERT, UPDATE, DELETE, MERGE (see Inserts Updates Upserts and MERGE)
• Postgres Twist: DDL is transactional in Postgres — CREATE/ALTER/DROP can be rolled back (unlike MySQL/Oracle)
• Sample Data: Examples use the schema from E-commerce Practice Queries

DDL Commands

CREATE TABLE

CREATE TABLE IF NOT EXISTS coupons (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code        text NOT NULL UNIQUE,
    percent_off numeric(5,2) NOT NULL CHECK (percent_off > 0 AND percent_off <= 100),
    valid_from  timestamptz NOT NULL DEFAULT now(),
    valid_to    timestamptz,
    created_by  bigint REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT valid_range CHECK (valid_to IS NULL OR valid_to > valid_from)
);
 
-- Copy structure (columns, defaults, constraints, indexes) without data
CREATE TABLE coupons_archive (LIKE coupons INCLUDING ALL);
 
-- Create from a query (no constraints/indexes copied)
CREATE TABLE top_products AS
SELECT id, name, price FROM products WHERE price > 500;

ALTER TABLE

ALTER TABLE coupons ADD COLUMN max_uses int;
ALTER TABLE coupons ALTER COLUMN max_uses SET DEFAULT 100;
ALTER TABLE coupons ALTER COLUMN max_uses SET NOT NULL;     -- fails if NULLs exist
ALTER TABLE coupons RENAME COLUMN max_uses TO usage_limit;
ALTER TABLE coupons ALTER COLUMN usage_limit TYPE bigint;    -- may rewrite the table
ALTER TABLE coupons ADD CONSTRAINT usage_positive CHECK (usage_limit > 0);
ALTER TABLE coupons DROP CONSTRAINT usage_positive;
ALTER TABLE coupons DROP COLUMN usage_limit;
ALTER TABLE top_products RENAME TO expensive_products;

DROP and TRUNCATE

DROP TABLE IF EXISTS expensive_products;
DROP TABLE IF EXISTS coupons_archive CASCADE;   -- CASCADE also drops dependent views / FKs
TRUNCATE coupons RESTART IDENTITY;              -- fast delete-all, resets identity counter

Command	Removes	Speed	Fires DELETE triggers	Rollback in PG
DELETE FROM t	Rows (optionally filtered)	Slow (row by row, leaves dead tuples)	Yes	Yes
TRUNCATE t	All rows	Very fast (new empty file)	No	Yes
DROP TABLE t	Table + data + indexes	Fast	No	Yes

Transactional DDL

BEGIN;
ALTER TABLE coupons ADD COLUMN notes text;
-- deploy script failed half way? everything undoes cleanly
ROLLBACK;

Safe Migrations on Big Tables

• Adding a Column with a Constant DEFAULT: Instant since PG 11 (no table rewrite)
• Adding NOT NULL: Scans the table under an ACCESS EXCLUSIVE lock — add a CHECK (col IS NOT NULL) NOT VALID, VALIDATE it, then SET NOT NULL (PG 12+ uses the validated check to skip the scan)
• Adding a FK / CHECK: Use NOT VALID then VALIDATE CONSTRAINT to avoid a long blocking scan
• Changing a Column Type: Usually rewrites the table — plan downtime or do add-column + backfill + swap
• Lock Timeout: SET lock_timeout = '3s'; before DDL so a migration doesn’t queue behind a long query and block everyone

ALTER TABLE coupons ADD CONSTRAINT coupons_creator_fk_v2
    FOREIGN KEY (created_by) REFERENCES users(id) NOT VALID;   -- instant, checks new rows only
ALTER TABLE coupons VALIDATE CONSTRAINT coupons_creator_fk_v2;  -- scans, but allows reads/writes
ALTER TABLE coupons DROP CONSTRAINT coupons_creator_fk_v2;

Data Types

Integers and IDs

Type	Size	Range	Use
smallint	2 B	±32K	Small enums, counters
integer / int	4 B	±2.1 billion	Most counts
bigint	8 B	±9.2 × 10¹⁸	IDs of anything that grows
numeric(p,s)	Variable	Exact, up to 131072 digits	Money, quantities that must be exact
real / double precision	4 / 8 B	Approximate (IEEE 754)	Measurements, science — never money

SELECT 0.1::float8 + 0.2::float8 = 0.3 AS float_equal,     -- false
       0.1::numeric + 0.2::numeric = 0.3 AS numeric_equal;  -- true

serial vs IDENTITY

• serial / bigserial: Old Postgres shorthand — creates a sequence + DEFAULT nextval(...). Not SQL standard; the sequence is a separate object with its own permissions
• GENERATED ALWAYS AS IDENTITY: SQL-standard, PG 10+. Sequence is owned by the column; manual inserts of id are rejected unless you say OVERRIDING SYSTEM VALUE
• GENERATED BY DEFAULT AS IDENTITY: Like serial — allows explicit values (handy for data imports)
• Recommendation: Use bigint GENERATED ALWAYS AS IDENTITY for new tables

CREATE TABLE id_demo (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    note text
);
INSERT INTO id_demo (note) VALUES ('auto id');                        -- id = 1
INSERT INTO id_demo (id, note) OVERRIDING SYSTEM VALUE VALUES (100, 'forced');
-- INSERT INTO id_demo (id, note) VALUES (5, 'x');
--   ERROR: cannot insert a non-DEFAULT value into column "id"
SELECT * FROM id_demo;

UUID

• Pros: Globally unique, can be generated by clients, no ID guessing
• Cons: 16 bytes vs 8; random v4 UUIDs scatter inserts across the B-tree (page splits, poor cache locality)
• Generation: gen_random_uuid() built in since PG 13 (v4). PG 18 adds uuidv7() — time-ordered, index-friendly

CREATE TABLE sessions (
    id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at timestamptz NOT NULL
);
INSERT INTO sessions (user_id, expires_at) VALUES (1, now() + interval '7 days') RETURNING id;

Text: text vs varchar(n) vs char(n)

• text: Unlimited length; the idiomatic choice in Postgres
• varchar(n): Same storage and speed as text, plus a length check. Changing n later needs ALTER
• char(n): Blank-padded to n — almost never what you want (fine for fixed codes like ISO country char(2))
• Performance: No difference between text and varchar in Postgres — prefer text + a CHECK (length(col) <= n) if you need a limit

Dates and Times

Type	Stores	Use
timestamptz	An absolute instant (stored as UTC, shown in session TimeZone)	Almost everything: created_at, events
timestamp	Wall-clock date + time with no zone	Rare — e.g. “store opens 09:00 local”
date	Calendar day	Birthdays, billing dates
interval	A duration	now() - interval '30 days'

SET timezone = 'UTC';
SELECT now()                                         AS now_utc,
       now() AT TIME ZONE 'Asia/Kolkata'             AS ist_wall_clock,
       '2025-03-31'::date + 1                        AS next_day,
       date_trunc('month', timestamptz '2025-03-18 10:00+00') AS month_start,
       age(timestamptz '2025-07-15', timestamptz '2025-01-10') AS age_between,
       extract(dow FROM date '2025-07-15')           AS day_of_week;   -- 0 = Sunday

Boolean, JSONB, Arrays, Enums

• boolean: true / false / NULL
• jsonb: Binary JSON, indexable — see JSONB and Arrays. Avoid plain json unless you need key order / duplicate keys preserved
• Arrays: text[], int[] — great for tags; avoid for relationships you need FKs on
• Enum: CREATE TYPE ... AS ENUM — compact and type-safe, but removing/renaming values is awkward. A CHECK (status IN (...)) or a lookup table is easier to evolve

CREATE TYPE ticket_priority AS ENUM ('low', 'medium', 'high');
SELECT 'high'::ticket_priority > 'low'::ticket_priority AS ordered_by_declaration;  -- true

Constraints

Constraint Types

Constraint	Guarantees	Notes
PRIMARY KEY	Unique + NOT NULL; one per table	Creates a unique B-tree index
FOREIGN KEY	Value exists in parent table	Does not auto-create an index on the child column
UNIQUE	No duplicates	NULLs are distinct by default (many NULLs allowed); UNIQUE NULLS NOT DISTINCT in PG 15+
CHECK	Boolean expression is not false	Passes when the expression is NULL!
NOT NULL	Column has a value	Cheapest constraint, use liberally
DEFAULT	Value when omitted	Not a constraint, but part of column definition
EXCLUDE	No two rows “overlap”	PG-specific, e.g. no overlapping bookings

Foreign Key ON DELETE Actions

Action	When parent row is deleted	Typical use
NO ACTION (default)	Error if children exist (checked at end of statement)	Safe default
RESTRICT	Error immediately, cannot be deferred	orders.user_id — never lose orders
CASCADE	Delete children too	order_items.order_id — items die with the order
SET NULL	Set child FK to NULL	users.referred_by — keep user, forget referrer
SET DEFAULT	Set child FK to its default	Rare

-- RESTRICT in action: user 1 has orders
-- DELETE FROM users WHERE id = 1;
--   ERROR: update or delete on table "users" violates foreign key constraint "orders_user_id_fkey"
 
-- CASCADE in action: deleting an order removes its items
BEGIN;
DELETE FROM orders WHERE id = 5;
SELECT count(*) AS items_left_for_order_5 FROM order_items WHERE order_id = 5;   -- 0
ROLLBACK;

UNIQUE, CHECK and NULL Gotchas

CREATE TABLE nulls_demo (
    a int UNIQUE,
    b int UNIQUE NULLS NOT DISTINCT,    -- PG 15+
    c int CHECK (c > 0)
);
INSERT INTO nulls_demo (a, b, c) VALUES (NULL, NULL, NULL);   -- ok: CHECK on NULL passes
INSERT INTO nulls_demo (a, b)    VALUES (NULL, 1);             -- ok: a allows many NULLs
-- INSERT INTO nulls_demo (b) VALUES (NULL);
--   ERROR: duplicate key value violates unique constraint "nulls_demo_b_key"
SELECT count(*) FROM nulls_demo;   -- 2

Composite and Partial Uniqueness

-- One active coupon per code, but many expired ones allowed
CREATE UNIQUE INDEX one_live_coupon_per_code ON coupons (code) WHERE valid_to IS NULL;
 
-- Composite PK on a join table
CREATE TABLE product_tags (
    product_id bigint REFERENCES products(id) ON DELETE CASCADE,
    tag        text,
    PRIMARY KEY (product_id, tag)
);

EXCLUDE Constraint (Postgres-only)

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_bookings (
    room   int NOT NULL,
    during tstzrange NOT NULL,
    EXCLUDE USING gist (room WITH =, during WITH &&)   -- same room AND overlapping time = rejected
);
INSERT INTO room_bookings VALUES (1, '[2025-01-01 10:00+00, 2025-01-01 11:00+00)');
INSERT INTO room_bookings VALUES (1, '[2025-01-01 11:00+00, 2025-01-01 12:00+00)');   -- ok, touches
-- INSERT INTO room_bookings VALUES (1, '[2025-01-01 10:30+00, 2025-01-01 11:30+00)');
--   ERROR: conflicting key value violates exclusion constraint

Generated Columns

ALTER TABLE order_items
    ADD COLUMN line_total numeric(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED;
SELECT order_id, product_id, line_total FROM order_items WHERE order_id = 1;
ALTER TABLE order_items DROP COLUMN line_total;

Inspecting Structure

-- psql: \d products   \dt   \di   \d+ orders
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'products'
ORDER BY ordinal_position;
 
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;

Interview Questions

• “serial vs identity?”: Identity is SQL standard, owned by the column, ALWAYS blocks accidental manual IDs
• “text or varchar(255)?”: Same performance in Postgres; use text unless a length rule is a real business rule
• “Why did my CHECK let a NULL through?”: CHECK only rejects false; NULL is “unknown” → add NOT NULL
• “Does a FK create an index?”: No — index the child column yourself or deletes on the parent scan the child table
• “DELETE vs TRUNCATE?”: TRUNCATE is O(1), skips row triggers, takes an ACCESS EXCLUSIVE lock

Summary

• DDL: CREATE / ALTER / DROP / TRUNCATE, and all of it is transactional in Postgres
• Types: bigint identity IDs, numeric for money, text over varchar, timestamptz over timestamp, jsonb over json
• Constraints: Push data rules into the database — PK, FK with the right ON DELETE, UNIQUE, CHECK, NOT NULL
• Gotchas: NULL passes CHECK, UNIQUE allows many NULLs, FKs are not indexed automatically
• Migrations: NOT VALID + VALIDATE, lock_timeout, and avoid table rewrites on hot tables

Link to original

SELECT Fundamentals

SELECT Fundamentals

Overview

• SELECT: Reads rows; everything else in querying (joins, aggregates, windows) builds on it
• Key Ideas: Filtering (WHERE), sorting (ORDER BY), paging (LIMIT/OFFSET vs keyset), de-duplication (DISTINCT / DISTINCT ON), NULL logic, CASE
• Must Know for Interviews: The logical processing order — it explains most “why can’t I use this alias here?” errors
• Sample Data: E-commerce Practice Queries

Logical Query Processing Order

Written Order vs Evaluated Order

Written:    SELECT → FROM → WHERE → GROUP BY → HAVING → WINDOW → ORDER BY → LIMIT
 
Evaluated:  1. FROM / JOIN      build the row set
            2. WHERE            filter rows        (no aggregates, no SELECT aliases)
            3. GROUP BY         form groups
            4. HAVING           filter groups      (aggregates allowed)
            5. SELECT           compute expressions, then window functions
            6. DISTINCT         remove duplicates
            7. ORDER BY         sort               (SELECT aliases allowed)
            8. LIMIT / OFFSET   cut

Consequences

• Aliases in WHERE: Not allowed — WHERE runs before SELECT
• Aliases in ORDER BY: Allowed — ORDER BY runs after SELECT
• Postgres Extension: Postgres also accepts output aliases / ordinal positions in GROUP BY (GROUP BY 1) — not standard SQL
• Window Functions in WHERE: Not allowed — wrap in a subquery/CTE (see Window Functions)
• Physical Plan: The planner may execute in a different order (e.g. push filters into joins); the logical order only defines the result

-- Alias works in ORDER BY…
SELECT name, price * 1.18 AS price_with_tax
FROM products
ORDER BY price_with_tax DESC
LIMIT 3;
 
-- …but not in WHERE: repeat the expression or use a subquery
-- SELECT name, price * 1.18 AS price_with_tax FROM products WHERE price_with_tax > 100;
--   ERROR: column "price_with_tax" does not exist
SELECT *
FROM (SELECT name, price * 1.18 AS price_with_tax FROM products) t
WHERE price_with_tax > 1000;

WHERE: Filtering

Operators

Operator	Example	Notes
Comparison	price >= 100	<> and != are the same
BETWEEN	price BETWEEN 50 AND 100	Inclusive both ends — careful with timestamps
IN	status IN ('paid','shipped')	Readable OR-list
LIKE / ILIKE	name ILIKE '%phone%'	ILIKE = case-insensitive (Postgres)
Regex	sku ~ '^ACC-\d+$'	~ match, ~* case-insensitive, !~ not match
IS [NOT] NULL	country IS NULL	Never = NULL
IS [NOT] DISTINCT FROM	a IS DISTINCT FROM b	NULL-safe equality
ANY / ALL	id = ANY (ARRAY[1,2,3])	Great for passing arrays from app code

SELECT sku, name, price
FROM products
WHERE is_active
  AND (name ILIKE '%laptop%' OR sku ~ '^PHN-')
  AND price BETWEEN 500 AND 2500
ORDER BY price;

Date Ranges: Use Half-Open Intervals

-- Bad:  created_at BETWEEN '2025-03-01' AND '2025-03-31'   → misses 31 Mar after 00:00
-- Good: >= start AND < next start
SELECT id, created_at
FROM orders
WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01'
ORDER BY created_at;

ORDER BY

• Direction: ASC (default) / DESC
• NULL Placement: Postgres puts NULLs last for ASC and first for DESC (treats NULL as largest). Override with NULLS FIRST / NULLS LAST
• Determinism: Without a unique tie-breaker, row order among equal keys is undefined — always add id when paginating
• No ORDER BY = No Order: A table has no inherent order; results can change between runs

SELECT full_name, country
FROM users
ORDER BY country DESC NULLS LAST, full_name;

LIMIT / OFFSET vs Keyset Pagination

OFFSET Pagination

-- Page 3, 5 per page
SELECT id, created_at, status
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 5 OFFSET 10;
 
-- Standard-SQL spelling (same thing), plus WITH TIES support
SELECT id, created_at FROM orders ORDER BY created_at DESC OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;

Keyset (Seek / Cursor) Pagination

-- Client remembers the last row of the previous page: (created_at, id) = ('2025-05-01 12:00+00', 9)
SELECT id, created_at, status
FROM orders
WHERE (created_at, id) < ('2025-05-01 12:00+00', 9)   -- row-value comparison
ORDER BY created_at DESC, id DESC
LIMIT 5;
-- Backed by an index on (created_at, id) this is a single index range scan, whatever the page number

Aspect	OFFSET	Keyset
Deep Pages	Slow — must read and throw away OFFSET rows	Constant time with an index
Concurrent Inserts	Rows shift → duplicates / skipped rows	Stable
Jump to Page N	Yes	No (next/prev only)
Requirements	Any ORDER BY	Unique, indexed sort key (add id as tie-breaker)
Best For	Admin UIs, small tables	Infinite scroll, APIs, exports

DISTINCT and DISTINCT ON

DISTINCT

SELECT DISTINCT country FROM users ORDER BY country;   -- NULL appears once
SELECT count(DISTINCT user_id) AS customers_with_orders FROM orders;

DISTINCT ON (Postgres-only)

• What: Keep the first row per group, where “first” is defined by ORDER BY
• Rule: ORDER BY must start with the DISTINCT ON expressions
• Classic Use: “Latest X per Y” without a window function

-- Most expensive product in each category
SELECT DISTINCT ON (category_id) category_id, name, price
FROM products
ORDER BY category_id, price DESC, id;

NULL Handling

Three-Valued Logic

• Values: TRUE, FALSE, UNKNOWN (NULL)
• Comparisons with NULL: NULL = NULL, NULL <> 1, NULL > 0 are all NULL, not true/false
• WHERE Keeps Only TRUE: Rows where the condition is NULL are dropped — this is how rows “disappear”

a	b	a AND b	a OR b	NOT a
TRUE	NULL	NULL	TRUE	FALSE
FALSE	NULL	FALSE	NULL	TRUE
NULL	NULL	NULL	NULL	NULL

SELECT NULL = NULL                 AS eq,            -- NULL
       NULL IS NULL                AS is_null,       -- true
       NULL IS DISTINCT FROM NULL  AS distinct_from, -- false
       1    IS DISTINCT FROM NULL  AS one_vs_null,   -- true
       TRUE OR NULL                AS t_or_n,        -- true
       FALSE AND NULL              AS f_and_n;       -- false
 
-- Classic trap: "everyone not from IN" silently drops Hiro (country IS NULL)
SELECT count(*) FROM users WHERE country <> 'IN';                              -- 4
SELECT count(*) FROM users WHERE country IS DISTINCT FROM 'IN';                -- 5

NULL Functions

Function	Returns	Example
COALESCE(a, b, …)	First non-NULL argument	COALESCE(country, 'unknown')
NULLIF(a, b)	NULL if a = b, else a	total / NULLIF(count, 0) avoids divide-by-zero
IS [NOT] NULL	Boolean	WHERE referred_by IS NULL
count(col) vs count(*)	Non-NULL values vs all rows	count(country) = 7, count(*) = 8

SELECT full_name,
       COALESCE(country, 'unknown') AS country,
       NULLIF(country, 'IN')        AS non_indian_country
FROM users
ORDER BY id;
 
SELECT count(*) AS all_rows, count(country) AS with_country, count(DISTINCT country) AS countries
FROM users;

NULLs in Aggregates and Sorting

• Aggregates Ignore NULL: sum, avg, min, max, count(col) skip NULLs; sum of zero rows is NULL, not 0
• Grouping: All NULLs fall into one group
• Arithmetic: 5 + NULL is NULL; 'a' || NULL is NULL (use concat() which skips NULLs)

SELECT 'a' || NULL               AS concat_op,   -- NULL
       concat('a', NULL, 'b')    AS concat_fn,   -- 'ab'
       (SELECT sum(price) FROM products WHERE false) AS sum_of_nothing;  -- NULL

CASE Expressions

-- Searched CASE
SELECT name, price,
       CASE
           WHEN price >= 1000 THEN 'premium'
           WHEN price >= 100  THEN 'mid'
           ELSE 'budget'
       END AS price_band
FROM products
ORDER BY price DESC;
 
-- Simple CASE
SELECT id, status,
       CASE status WHEN 'cancelled' THEN 0 WHEN 'pending' THEN 1 ELSE 2 END AS status_rank
FROM orders
ORDER BY status_rank, id
LIMIT 5;
 
-- CASE in ORDER BY: custom sort order
SELECT id, status FROM orders
ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'paid' THEN 2 WHEN 'shipped' THEN 3 ELSE 4 END, id;

• Missing ELSE: Returns NULL
• Evaluation: First matching WHEN wins; branches are evaluated lazily

Handy Postgres Extras

SELECT upper(name), length(name), left(sku, 3) AS prefix,
       split_part(sku, '-', 2) AS sku_number,
       round(price * 0.9, 2) AS discounted,
       price::int AS price_int,            -- :: is Postgres cast syntax (CAST(x AS int) is standard)
       format('%s costs %s', name, price) AS label
FROM products
WHERE id <= 3;
 
SELECT * FROM generate_series(date '2025-01-01', date '2025-03-01', interval '1 month') AS month;

Interview Questions

• “Why does WHERE x <> 5 miss rows?”: Rows with x IS NULL evaluate to unknown and are filtered out
• “Why can’t I use a column alias in WHERE?”: WHERE is evaluated before SELECT
• “OFFSET pagination is slow on page 10,000 — fix it?”: Keyset pagination with a composite index on the sort key + id
• “Latest row per group?”: DISTINCT ON in Postgres; ROW_NUMBER() portable
• “COALESCE vs NULLIF?”: COALESCE replaces NULL with a value; NULLIF turns a value into NULL

Summary

• Processing Order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
• Filtering: Use half-open date ranges, ILIKE/regex for text, = ANY(array) for lists
• Pagination: OFFSET is simple but degrades; keyset is stable and constant-time
• DISTINCT ON: Postgres shortcut for “first row per group”
• NULLs: Three-valued logic — use IS NULL, IS DISTINCT FROM, COALESCE, NULLIF, and remember aggregates skip NULLs

Link to original

Aggregation and GROUP BY

Aggregation and GROUP BY

Overview

• Aggregation: Collapse many rows into one per group — count, sum, avg, min, max, string_agg, array_agg, bool_and, …
• GROUP BY: Defines the groups; HAVING filters groups after aggregation
• Postgres Extras: FILTER (WHERE …) clause, GROUPING SETS / ROLLUP / CUBE, ordered-set aggregates (percentile_cont, mode)
• Sample Data: E-commerce Practice Queries

GROUP BY Basics

Rules

• Every SELECT Column: Must be in GROUP BY or inside an aggregate
• Functional Dependency (PG): If you group by a table’s primary key, you can select any other column of that table
• Grouping by Expressions: GROUP BY date_trunc('month', created_at) or by position GROUP BY 1
• Aggregates Skip NULLs: Except count(*)

SELECT status,
       count(*)               AS orders,
       min(created_at)::date  AS first_order,
       max(created_at)::date  AS last_order
FROM orders
GROUP BY status
ORDER BY orders DESC;
 
-- Grouping by PK lets us select u.full_name / u.email without listing them
SELECT u.id, u.full_name, u.email, count(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id
ORDER BY orders DESC, u.id;

WHERE vs HAVING

Aspect	WHERE	HAVING
Runs	Before grouping	After grouping
Filters	Rows	Groups
Aggregates Allowed	No	Yes
Performance	Reduces rows early — put non-aggregate filters here	Only for aggregate conditions

-- Customers with 2+ non-cancelled orders and over 1000 spent
SELECT o.user_id, count(DISTINCT o.id) AS orders, sum(oi.quantity * oi.unit_price) AS spent
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'                       -- row filter
GROUP BY o.user_id
HAVING count(DISTINCT o.id) >= 2                    -- group filter
   AND sum(oi.quantity * oi.unit_price) > 1000
ORDER BY spent DESC;

Common Aggregate Functions

Function	Returns	Notes
count(*)	Rows in group	Counts NULL rows too
count(col)	Non-NULL values	count(DISTINCT col) for unique values
sum / avg	Total / mean	NULL for empty input; avg(int) returns numeric
min / max	Extremes	Work on text, dates too
bool_and / bool_or	All / any true	every() is the standard alias of bool_and
string_agg(expr, sep ORDER BY …)	Concatenated text	Order inside the call
array_agg(expr ORDER BY …)	Array	Includes NULLs
jsonb_agg / jsonb_object_agg	JSON array / object	Build API payloads in SQL
percentile_cont(0.5) WITHIN GROUP (ORDER BY x)	Median (interpolated)	Ordered-set aggregate
mode() WITHIN GROUP (ORDER BY x)	Most frequent value

SELECT c.name                                               AS category,
       count(*)                                             AS products,
       round(avg(p.price), 2)                               AS avg_price,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY p.price) AS median_price,
       bool_and(p.stock > 0)                                AS all_in_stock,
       string_agg(p.name, ', ' ORDER BY p.price DESC)       AS products_by_price,
       array_agg(p.sku ORDER BY p.sku)                      AS skus
FROM products p
JOIN categories c ON c.id = p.category_id
GROUP BY c.name
ORDER BY c.name;

Nesting Rows as JSON

-- One row per order with its items as a JSON array — handy for APIs
SELECT o.id,
       o.status,
       jsonb_agg(jsonb_build_object('sku', p.sku, 'qty', oi.quantity) ORDER BY p.sku) AS items
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE o.id IN (1, 8)
GROUP BY o.id
ORDER BY o.id;

FILTER Clause (Conditional Aggregation)

Why

• Pivot-style Reports: Many conditional counts/sums in one pass over the data
• Cleaner Than CASE: count(*) FILTER (WHERE …) vs sum(CASE WHEN … THEN 1 ELSE 0 END)
• Standard SQL: Part of SQL:2003, supported in Postgres since 9.4 (MySQL lacks it)

SELECT date_trunc('month', created_at)::date                AS month,
       count(*)                                              AS all_orders,
       count(*) FILTER (WHERE status = 'delivered')          AS delivered,
       count(*) FILTER (WHERE status IN ('pending','paid'))  AS open,
       count(*) FILTER (WHERE status = 'cancelled')          AS cancelled,
       -- the portable equivalent:
       sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_case
FROM orders
GROUP BY 1
ORDER BY 1;
 
-- Payment method mix per order status
SELECT o.status,
       sum(p.amount) FILTER (WHERE p.method = 'card')   AS card,
       sum(p.amount) FILTER (WHERE p.method = 'upi')    AS upi,
       sum(p.amount) FILTER (WHERE p.method = 'wallet') AS wallet
FROM orders o
JOIN payments p ON p.order_id = o.id AND p.status = 'succeeded'
GROUP BY o.status
ORDER BY o.status;

GROUPING SETS, ROLLUP and CUBE

Concept

• GROUPING SETS: Several GROUP BYs in one query, results stacked (like UNION ALL of each grouping, but one scan)
• ROLLUP (a, b): Hierarchical subtotals → (a, b), (a), () — e.g. year → month → grand total
• CUBE (a, b): Every combination → (a, b), (a), (b), ()
• GROUPING(col): Returns 1 when col is rolled up in that row — distinguishes a subtotal NULL from a real NULL

Clause	Groupings for (country, status)	Rows Produced
GROUP BY country, status	(country, status)	Detail only
ROLLUP (country, status)	(country, status), (country), ()	Detail + per-country + total
CUBE (country, status)	(country, status), (country), (status), ()	All subtotals
GROUPING SETS ((country), (status))	(country), (status)	Only what you list

-- Revenue by country and status with subtotals and a grand total
SELECT CASE WHEN GROUPING(u.country) = 1 THEN 'ALL' ELSE COALESCE(u.country, '??') END AS country,
       CASE WHEN GROUPING(o.status)  = 1 THEN 'ALL' ELSE o.status END                  AS status,
       sum(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN users u        ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY ROLLUP (u.country, o.status)
ORDER BY GROUPING(u.country), u.country, GROUPING(o.status), o.status;

-- Two independent breakdowns in one query
SELECT method, status, count(*) AS payments, sum(amount) AS amount
FROM payments
GROUP BY GROUPING SETS ((method), (status), ())
ORDER BY method NULLS LAST, status NULLS LAST;

-- Every combination of method × status
SELECT method, status, count(*)
FROM payments
GROUP BY CUBE (method, status)
ORDER BY method NULLS LAST, status NULLS LAST;

string_agg and array_agg in Practice

-- Comma-separated product list per order
SELECT oi.order_id, string_agg(p.name, ', ' ORDER BY p.name) AS products
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY oi.order_id
HAVING count(*) > 1
ORDER BY oi.order_id;
 
-- array_agg + unnest round trip; DISTINCT inside the aggregate
SELECT user_id,
       array_agg(DISTINCT status ORDER BY status) AS statuses,
       array_length(array_agg(id), 1)             AS order_count
FROM orders
GROUP BY user_id
ORDER BY user_id;

Common Mistakes

Fan-out Double Counting

• Problem: Joining orders → order_items and orders → payments multiplies rows (items × payments), inflating sums
• Fix: Aggregate each child table in its own subquery/CTE, then join the per-order results

-- WRONG: order 8 has 3 items and 2 payments → 6 joined rows → payments counted 3×
SELECT o.id, sum(p.amount) AS paid_wrong
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN payments p     ON p.order_id = o.id AND p.status = 'succeeded'
WHERE o.id = 8
GROUP BY o.id;                                      -- 3399.00
 
-- RIGHT: pre-aggregate
SELECT o.id, i.due, p.paid
FROM orders o
JOIN (SELECT order_id, sum(quantity * unit_price) AS due FROM order_items GROUP BY order_id) i ON i.order_id = o.id
JOIN (SELECT order_id, sum(amount) AS paid FROM payments WHERE status = 'succeeded' GROUP BY order_id) p ON p.order_id = o.id
WHERE o.id = 8;                                     -- 1133.00 / 1133.00

Other Gotchas

• count(*) with LEFT JOIN: Counts the NULL-extended row as 1 — use count(child.id)
• sum Returns NULL on No Rows: Wrap with COALESCE(sum(x), 0)
• avg of Integers: Returns numeric in Postgres (good), but integer division 5/2 = 2 elsewhere in expressions
• Aggregates in WHERE: Not allowed — use HAVING or a subquery

Interview Questions

• “WHERE vs HAVING?”: Row filter before grouping vs group filter after aggregation
• “Count orders per status in one row?”: count(*) FILTER (WHERE status = …) (or sum(CASE …) for portability)
• “Subtotals and grand total?”: ROLLUP; use GROUPING() to label total rows
• “Why is my revenue doubled after adding a join?”: Fan-out — pre-aggregate per table
• “Median in Postgres?”: percentile_cont(0.5) WITHIN GROUP (ORDER BY x)

Summary

• GROUP BY + Aggregates: One output row per group; group by the PK to select dependent columns freely
• HAVING: Filters on aggregate results; keep row filters in WHERE
• FILTER: Clean conditional aggregation — the go-to for pivot reports
• GROUPING SETS / ROLLUP / CUBE: Multiple levels of subtotals in one scan; GROUPING() marks subtotal rows
• Pitfalls: Fan-out double counting, count(*) on outer joins, sum() of nothing is NULL

Link to original

Joins

Joins

Overview

• Join: Combine rows from two tables on a condition — the core of relational querying
• Types: INNER, LEFT/RIGHT/FULL OUTER, CROSS, self join, plus the patterns semi-join (EXISTS) and anti-join (NOT EXISTS)
• Postgres Extras: LATERAL joins, USING, NATURAL (avoid), FULL JOIN support (MySQL lacks it)
• Physical Algorithms: Nested Loop, Hash Join, Merge Join — chosen by the planner, see Indexes and Query Performance and Query Planner and Optimiser
• Sample Data: E-commerce Practice Queries

Join Types

Visual Summary

A = users, B = orders (joined on users.id = orders.user_id)
 
INNER JOIN        → only matching pairs                    (users with orders)
LEFT JOIN         → all of A + matches, NULLs if none     (every user, orders or not)
RIGHT JOIN        → all of B + matches                     (rarely used; flip to LEFT)
FULL OUTER JOIN   → all of A and all of B                  (reconciliation)
CROSS JOIN        → every A × every B                      (combinations, calendars)
SEMI JOIN         → A rows that HAVE a match, no dup rows  (WHERE EXISTS)
ANTI JOIN         → A rows that have NO match              (WHERE NOT EXISTS)

Join	Unmatched Left Rows	Unmatched Right Rows	Can Duplicate Left Rows
INNER	Dropped	Dropped	Yes (one per match)
LEFT	Kept (NULLs on right)	Dropped	Yes
RIGHT	Dropped	Kept	Yes
FULL	Kept	Kept	Yes
CROSS	n/a	n/a	Yes (m × n rows)
Semi (EXISTS)	Dropped	n/a	No
Anti (NOT EXISTS)	Only these kept	n/a	No

INNER JOIN

SELECT o.id AS order_id, u.full_name, o.status
FROM orders o
JOIN users u ON u.id = o.user_id          -- JOIN = INNER JOIN
WHERE o.status = 'shipped'
ORDER BY o.id;

LEFT JOIN

-- Every user with their order count (0 for none)
SELECT u.full_name, count(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.full_name
ORDER BY orders DESC, u.full_name;

ON vs WHERE in an Outer Join

• Condition in ON: Filters the right table before matching — left rows are still kept
• Condition in WHERE: Filters the result — a WHERE right.col = … turns the LEFT JOIN into an INNER JOIN
• Rule of Thumb: Conditions on the optional (right) table go in ON

-- Users with their DELIVERED order count — keeps users with zero
SELECT u.full_name, count(o.id) AS delivered_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'delivered'
GROUP BY u.id, u.full_name
ORDER BY u.id;                                   -- 8 rows
 
-- Same filter in WHERE silently drops Gina and Hiro (and anyone without a delivered order)
SELECT u.full_name, count(o.id) AS delivered_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'delivered'
GROUP BY u.id, u.full_name
ORDER BY u.id;                                   -- 6 rows

FULL OUTER JOIN

-- Reconcile orders vs successful payments: show mismatches from both sides
SELECT COALESCE(o.id, p.order_id) AS order_id, o.status, p.amount
FROM (SELECT * FROM orders WHERE status IN ('paid','shipped','delivered')) o
FULL JOIN (SELECT order_id, sum(amount) AS amount FROM payments
           WHERE status = 'succeeded' GROUP BY order_id) p
       ON p.order_id = o.id
WHERE o.id IS NULL OR p.order_id IS NULL;        -- empty = books are balanced

CROSS JOIN

-- Every user × every month → a grid to LEFT JOIN facts onto (zero-filled report)
SELECT u.full_name, m.month::date, count(o.id) AS orders
FROM users u
CROSS JOIN generate_series(date '2025-01-01', date '2025-03-01', interval '1 month') AS m(month)
LEFT JOIN orders o ON o.user_id = u.id
                  AND o.created_at >= m.month AND o.created_at < m.month + interval '1 month'
WHERE u.id <= 2
GROUP BY u.full_name, m.month
ORDER BY u.full_name, m.month;

USING and NATURAL

• USING (col): Shorthand when both sides share a column name; outputs the column once
• NATURAL JOIN: Joins on all same-named columns — fragile (adding created_at to both tables silently changes the join). Avoid

SELECT order_id, product_id, quantity, payments.amount
FROM order_items
JOIN payments USING (order_id)
WHERE order_id = 1;          -- note the payment amount repeats on every item row (fan-out)

Self Joins

• What: Join a table to itself with two aliases
• Uses: Hierarchies (employee → manager), referrals, comparing rows within a table (pairs, duplicates)

-- Employee with their manager (LEFT so the CEO appears)
SELECT e.name AS employee, m.name AS manager, e.department
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;
 
-- Employees who earn more than their manager
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;          -- none here; try UPDATE employees SET salary = 100000 WHERE name = 'Leo'
 
-- Pairs of products in the same category (a.id < b.id avoids mirror duplicates and self-pairs)
SELECT a.name, b.name, a.category_id
FROM products a
JOIN products b ON a.category_id = b.category_id AND a.id < b.id
ORDER BY a.category_id, a.name;

• Deep Hierarchies: A self join goes one level per join — use a recursive CTE for arbitrary depth (Subqueries and CTEs)

Semi Joins and Anti Joins

Semi Join: EXISTS / IN

-- Users who have at least one delivered order (one row per user, no DISTINCT needed)
SELECT u.full_name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'delivered')
ORDER BY u.full_name;
 
-- Same with IN
SELECT full_name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'delivered')
ORDER BY full_name;

• Why not JOIN + DISTINCT?: A join multiplies rows (Asha has 2 delivered orders), forcing a DISTINCT sort/hash. Semi-joins stop at the first match

Anti Join: NOT EXISTS vs LEFT JOIN … IS NULL vs NOT IN

-- 1. NOT EXISTS (preferred)
SELECT u.full_name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
 
-- 2. LEFT JOIN … IS NULL (equivalent, Postgres plans the same Hash Anti Join)
SELECT u.full_name FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

The NOT IN NULL Pitfall

• Problem: x NOT IN (subquery) is x <> v1 AND x <> v2 AND …. If any v is NULL, every comparison with it is unknown → the whole predicate is never true → zero rows
• Also Slower: Postgres can’t turn NOT IN into an anti-join (because of those NULL semantics); it uses a hashed SubPlan or, if the subquery is too big for work_mem, a per-row scan
• Fix: Always use NOT EXISTS (or ensure the column is NOT NULL)

-- "Users who never referred anyone": referred_by contains NULLs
SELECT full_name FROM users
WHERE id NOT IN (SELECT referred_by FROM users);                 -- 0 rows (!!)
 
SELECT full_name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM users r WHERE r.referred_by = u.id)
ORDER BY u.id;                                                   -- 5 rows: Chen, Eva, Farid, Gina, Hiro
 
-- NOT IN works only if you strip NULLs yourself
SELECT full_name FROM users
WHERE id NOT IN (SELECT referred_by FROM users WHERE referred_by IS NOT NULL)
ORDER BY id;

Pattern	Handles NULLs	Plan	Verdict
NOT EXISTS	Correct	Hash/Merge Anti Join	Use this
LEFT JOIN … IS NULL	Correct	Anti Join	Fine
NOT IN (subquery)	Breaks on any NULL	Hashed SubPlan / slow	Avoid
EXCEPT	Treats NULLs as equal	Set operation	Good for whole-row diffs

LATERAL Joins

Concept

• What: A subquery in FROM that can reference columns of tables to its left — like a for each row loop
• Use Cases: Top-N per group, calling set-returning functions per row, reusing computed expressions
• LEFT JOIN LATERAL … ON true: Keep left rows even when the lateral subquery returns nothing

-- Latest 2 orders for every user (users with none still shown)
SELECT u.full_name, recent.id AS order_id, recent.created_at::date
FROM users u
LEFT JOIN LATERAL (
    SELECT o.id, o.created_at
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY o.created_at DESC
    LIMIT 2
) recent ON true
ORDER BY u.id, recent.created_at DESC;

-- Most expensive item in each order
SELECT o.id, top_item.name, top_item.unit_price
FROM orders o
CROSS JOIN LATERAL (
    SELECT p.name, oi.unit_price
    FROM order_items oi JOIN products p ON p.id = oi.product_id
    WHERE oi.order_id = o.id
    ORDER BY oi.unit_price DESC
    LIMIT 1
) top_item
ORDER BY o.id
LIMIT 5;
 
-- Expand an array column into rows per product (implicit LATERAL for functions)
SELECT p.sku, t.tag
FROM products p, unnest(p.tags) AS t(tag)
WHERE p.category_id = 4;

Top-N per Group Method	Strength	Weakness
LATERAL + LIMIT	Fast with index on (group, sort) — reads only N rows per group	Needs a driving table of groups
ROW_NUMBER() window	Portable, one pass	Sorts every row of every group
DISTINCT ON	Shortest for N = 1	N = 1 only, Postgres-only

Join Performance Notes

• Index the FK Side: orders(user_id), order_items(product_id) — Postgres doesn’t do it for you
• Join Algorithms: Nested Loop (small outer + indexed inner), Hash Join (large unsorted sets, equality only), Merge Join (both sides sorted)
• Filter Early: Push filters into ON / WHERE of the smaller side; pre-aggregate before joining to avoid fan-out (Aggregation and GROUP BY)
• Many Joins: Above join_collapse_limit (8) tables the planner stops exhaustively reordering joins

EXPLAIN
SELECT u.full_name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Interview Questions

• “LEFT JOIN but rows still disappear?”: A WHERE filter on the right table discards the NULL-extended rows — move it into ON
• “NOT IN vs NOT EXISTS?”: NOT IN returns nothing if the subquery has a NULL; NOT EXISTS is NULL-safe and plans as an anti-join
• “Find employees earning more than their manager”: Self join on manager_id
• “Top 3 orders per customer”: LATERAL + LIMIT, or ROW_NUMBER() (Window Functions)
• “Why does my count double after a join?”: One-to-many fan-out; aggregate before joining or count(DISTINCT …)

Summary

• INNER / LEFT / FULL / CROSS: Choose by which unmatched rows you need to keep
• ON vs WHERE: Right-table filters belong in ON for outer joins
• Self Join: Same table twice with aliases — hierarchies and row comparisons
• Semi / Anti Joins: EXISTS / NOT EXISTS; never NOT IN against a nullable column
• LATERAL: Per-row subqueries — the Postgres tool for top-N per group and per-row function calls

Link to original

Subqueries and CTEs

Subqueries and CTEs

Overview

• Subquery: A SELECT nested inside another statement — in WHERE, FROM, SELECT, or HAVING
• CTE (Common Table Expression): A named subquery defined with WITH, readable top-to-bottom
• Recursive CTE: WITH RECURSIVE — walks trees and graphs (org charts, category trees, bill of materials)
• Postgres 12+: CTEs are inlined by default; control with MATERIALIZED / NOT MATERIALIZED
• Sample Data: E-commerce Practice Queries

Subquery Types

Type	Returns	Where Used	Example
Scalar	One value (1 row × 1 col)	SELECT, WHERE	price > (SELECT avg(price) FROM products)
Row	One row, many columns	Row comparison	(a, b) = (SELECT x, y …)
Table / Derived	A relation	FROM	FROM (SELECT …) t
Correlated	Re-evaluated per outer row	WHERE, SELECT	References outer alias
Existence	Boolean	EXISTS, IN, ANY, ALL	Semi / anti joins

Scalar Subquery

-- Products priced above the average active product
SELECT name, price
FROM products
WHERE price > (SELECT avg(price) FROM products WHERE is_active)
ORDER BY price DESC;
-- A scalar subquery returning >1 row raises: ERROR: more than one row returned by a subquery used as an expression

Derived Table (Subquery in FROM)

SELECT status, round(avg(total), 2) AS avg_order_value
FROM (
    SELECT o.id, o.status, sum(oi.quantity * oi.unit_price) AS total
    FROM orders o JOIN order_items oi ON oi.order_id = o.id
    GROUP BY o.id
) per_order                                  -- alias required
GROUP BY status
ORDER BY avg_order_value DESC;

Correlated Subquery

• What: The inner query references a column of the outer query, so it is logically run once per outer row
• Performance: Postgres often decorrelates EXISTS/IN into joins; correlated scalar subqueries in SELECT usually run as a SubPlan per row — fine for small outputs, slow for millions
• Rewrite Options: Join to a pre-aggregated derived table, or LATERAL

-- Each product with how many times it was ordered (correlated scalar in SELECT)
SELECT p.name,
       (SELECT count(*) FROM order_items oi WHERE oi.product_id = p.id) AS times_ordered
FROM products p
ORDER BY times_ordered DESC, p.name
LIMIT 5;
 
-- Products priced above their category's average (correlated in WHERE)
SELECT p.name, p.category_id, p.price
FROM products p
WHERE p.price > (SELECT avg(p2.price) FROM products p2 WHERE p2.category_id = p.category_id)
ORDER BY p.category_id;

EXISTS, IN, ANY, ALL

Comparison

Operator	Meaning	NULL Behaviour
EXISTS (sub)	Sub returns ≥ 1 row	Never NULL — true/false only
x IN (sub)	x equals some value	NULL if no match and sub has a NULL
x NOT IN (sub)	x equals no value	Never true if sub has a NULL — see Joins
x = ANY (sub / array)	Same as IN	Works with arrays: id = ANY ($1::int[])
x > ALL (sub)	Greater than every value	True for an empty sub
x > ANY (sub)	Greater than at least one	False for an empty sub

-- EXISTS: categories that have at least one out-of-stock product
SELECT c.name FROM categories c
WHERE EXISTS (SELECT 1 FROM products p WHERE p.category_id = c.id AND p.stock = 0);
 
-- IN with a list from a subquery
SELECT full_name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'cancelled');
 
-- = ANY with an array (what drivers send for "WHERE id IN (…)" lists)
SELECT id, name FROM products WHERE id = ANY (ARRAY[1, 3, 5]);
 
-- > ALL: products more expensive than every book
SELECT name, price FROM products
WHERE price > ALL (SELECT price FROM products WHERE category_id = 8)
  AND category_id <> 8
ORDER BY price
LIMIT 3;

CTEs (WITH)

Why Use CTEs

• Readability: Name each step; read the query top-down like a pipeline
• Reuse: Reference the same CTE several times in the main query
• Recursion: Only CTEs can recurse
• Data-Modifying CTEs: INSERT/UPDATE/DELETE … RETURNING inside WITH — chain writes in one statement (see Inserts Updates Upserts and MERGE)

WITH order_totals AS (
    SELECT order_id, sum(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
), customer_value AS (
    SELECT o.user_id, sum(t.total) AS lifetime_value, count(*) AS orders
    FROM orders o
    JOIN order_totals t ON t.order_id = o.id
    WHERE o.status IN ('paid', 'shipped', 'delivered')
    GROUP BY o.user_id
)
SELECT u.full_name, cv.orders, cv.lifetime_value,
       round(cv.lifetime_value / cv.orders, 2) AS avg_order_value
FROM customer_value cv
JOIN users u ON u.id = cv.user_id
WHERE cv.lifetime_value > (SELECT avg(lifetime_value) FROM customer_value)   -- CTE reused
ORDER BY cv.lifetime_value DESC;

MATERIALIZED vs NOT MATERIALIZED (PG 12+)

• Before PG 12: Every CTE was an optimization fence — computed once, stored, outer filters could not be pushed in
• PG 12+ Default: A non-recursive, side-effect-free CTE referenced once is inlined like a subquery; referenced twice or more it is materialized
• MATERIALIZED: Force compute-once (useful to stop the planner re-running an expensive CTE, or as a deliberate fence)
• NOT MATERIALIZED: Force inlining even when referenced multiple times (lets WHERE id = … use an index inside)

Option	Computed	Outer Filters Pushed Down	Use When
Default (1 reference)	Inlined	Yes	Normal case
Default (2+ references)	Once	No	Expensive CTE reused
MATERIALIZED	Once	No	Expensive volatile work, or planner misestimates
NOT MATERIALIZED	Per reference	Yes	Cheap CTE + selective outer filter

-- Inlined: plan is just "Seq Scan on products, Filter: (id = 3)" — the filter reaches the table,
-- so on a large table it becomes an Index Scan using products_pkey
EXPLAIN (COSTS OFF)
WITH p AS NOT MATERIALIZED (SELECT * FROM products)
SELECT * FROM p WHERE id = 3;
 
-- Fenced: plan shows "CTE Scan on p, Filter: (id = 3)" over a full scan of products — no index possible
EXPLAIN (COSTS OFF)
WITH p AS MATERIALIZED (SELECT * FROM products)
SELECT * FROM p WHERE id = 3;

Recursive CTEs

Structure

WITH RECURSIVE name AS (
    anchor query                -- starting rows (e.g. the root)
  UNION ALL
    recursive query             -- joins the table to "name" (rows from the previous step)
)
SELECT … FROM name;
 
Execution: run anchor → working table; repeat recursive part on the working table
           until it returns no rows; UNION ALL all iterations together

• UNION vs UNION ALL: UNION removes duplicates each iteration (prevents cycles on graphs, but costs a hash); UNION ALL is faster for trees
• Cycle Safety: Track a path array and stop when a node repeats, or use PG 14’s CYCLE clause
• Depth Limit: Add a depth column and WHERE depth < 20 as a safety net

Example: Org Chart (Top-Down)

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 1 AS level, name::text AS chain
    FROM employees
    WHERE manager_id IS NULL                         -- anchor: the CEO
  UNION ALL
    SELECT e.id, e.name, e.manager_id, org.level + 1, org.chain || ' > ' || e.name
    FROM employees e
    JOIN org ON e.manager_id = org.id                -- step: direct reports of the previous level
)
SELECT repeat('    ', level - 1) || name AS org_chart, level, chain
FROM org
ORDER BY chain;

Example: Everyone Under a Manager (Subtree) and Total Salary

WITH RECURSIVE reports AS (
    SELECT id, name, salary FROM employees WHERE name = 'Raj'
  UNION ALL
    SELECT e.id, e.name, e.salary
    FROM employees e JOIN reports r ON e.manager_id = r.id
)
SELECT count(*) - 1 AS people_under_raj, sum(salary) AS team_cost_incl_raj
FROM reports;

Example: Path to Root (Bottom-Up) for a Category

WITH RECURSIVE ancestors AS (
    SELECT id, name, parent_id, 0 AS up
    FROM categories WHERE name = 'Laptops'
  UNION ALL
    SELECT c.id, c.name, c.parent_id, a.up + 1
    FROM categories c JOIN ancestors a ON c.id = a.parent_id
)
SELECT string_agg(name, ' > ' ORDER BY up DESC) AS breadcrumb FROM ancestors;
-- Electronics > Computers > Laptops

Example: Category Tree with Cycle Protection (PG 14+ SEARCH / CYCLE)

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
  UNION ALL
    SELECT c.id, c.name, c.parent_id FROM categories c JOIN tree t ON c.parent_id = t.id
) SEARCH DEPTH FIRST BY name SET ord
  CYCLE id SET is_cycle USING path
SELECT name, parent_id, path FROM tree WHERE NOT is_cycle ORDER BY ord;

Example: Generate a Series Without generate_series

WITH RECURSIVE days AS (
    SELECT date '2025-01-01' AS d
  UNION ALL
    SELECT d + 1 FROM days WHERE d < date '2025-01-07'
)
SELECT d, to_char(d, 'Dy') AS weekday FROM days;

Subquery vs CTE vs Join vs LATERAL

Need	Best Tool
Filter by existence	EXISTS / NOT EXISTS
Readable multi-step logic	CTEs
Reuse an intermediate result	CTE (materialized once if referenced twice)
Per-row top-N / function call	LATERAL (Joins)
Hierarchy / graph traversal	WITH RECURSIVE
Value compared to an aggregate	Scalar subquery or window function (Window Functions)

Interview Questions

• “Correlated vs non-correlated subquery?”: Correlated references the outer row and is logically re-run per row; non-correlated runs once
• “Are CTEs an optimization fence in Postgres?”: Not since PG 12 unless referenced multiple times or marked MATERIALIZED
• “Print an org chart / find all reports of X”: Recursive CTE with anchor + UNION ALL step
• “How do you stop infinite recursion?”: UNION instead of UNION ALL, a visited-path array, the CYCLE clause, or a depth limit
• “IN vs EXISTS?”: Same plan for positive checks in modern Postgres; NOT IN differs dangerously from NOT EXISTS with NULLs

Summary

• Subqueries: Scalar, row, derived table, correlated; EXISTS/IN/ANY/ALL for set membership
• CTEs: Named steps for readability and reuse; inlined by default since PG 12
• MATERIALIZED / NOT MATERIALIZED: Explicit control over fencing vs inlining
• Recursive CTEs: Anchor + recursive step + UNION ALL; guard against cycles and runaway depth
• Choose the Right Tool: Joins for combining, EXISTS for filtering, CTEs for structure, LATERAL for per-row work

Link to original

Window Functions

Window Functions

Overview

• Window Function: Computes a value across a set of related rows while keeping every row (unlike GROUP BY, which collapses them)
• Syntax: function(...) OVER (PARTITION BY … ORDER BY … frame)
• Families: Ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE), offset (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE), aggregate (sum, avg, count … OVER)
• Evaluated: After WHERE/GROUP BY/HAVING, before ORDER BY/LIMIT — so you can’t filter on them in WHERE (see SELECT Fundamentals)
• Sample Data: E-commerce Practice Queries

Anatomy of OVER()

sum(amount) OVER (
    PARTITION BY user_id           -- split rows into independent groups (optional)
    ORDER BY created_at            -- order inside each partition (needed for ranking/LAG/running totals)
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- frame: which rows feed the aggregate
)

Part	Purpose	If Omitted
PARTITION BY	Restart the calculation per group	Whole result is one partition
ORDER BY	Defines sequence for rank/offset/running calcs	Rows unordered; aggregate covers whole partition
Frame	Subset of the partition for aggregates/*_VALUE	RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY present, else whole partition
WINDOW w AS (…)	Name a window to reuse	Repeat the definition

-- GROUP BY collapses; a window keeps every row and adds the group value
SELECT name, department, salary,
       round(avg(salary) OVER (PARTITION BY department))          AS dept_avg,
       round(salary - avg(salary) OVER (PARTITION BY department)) AS diff_from_avg,
       round(100 * salary / sum(salary) OVER (), 1)            AS pct_of_payroll
FROM employees
ORDER BY department, salary DESC;

Ranking Functions

ROW_NUMBER vs RANK vs DENSE_RANK

Function	Ties	Gaps After Ties	Salaries 150, 150, 120
ROW_NUMBER()	Arbitrary unique numbers	n/a	1, 2, 3
RANK()	Same rank	Yes	1, 1, 3
DENSE_RANK()	Same rank	No	1, 1, 2
PERCENT_RANK()	(rank − 1) / (rows − 1)	—	0, 0, 1
NTILE(n)	Splits into n near-equal buckets	—	1, 1, 2 (n = 2)

SELECT name, department, salary,
       ROW_NUMBER() OVER w AS row_num,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS dense_rnk,
       NTILE(2)     OVER w AS half
FROM employees
WHERE department = 'Engineering'
WINDOW w AS (ORDER BY salary DESC)
ORDER BY salary DESC, name;

Top-N per Group

-- Top 2 earners per department (window must be filtered in an outer query)
SELECT department, name, salary
FROM (
    SELECT department, name, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk <= 2
ORDER BY department, rnk, name;

• ROW_NUMBER: Exactly N rows per group, ties broken arbitrarily (add a tie-breaker column)
• RANK / DENSE_RANK: May return more than N rows when ties exist
• Alternatives: DISTINCT ON for N = 1, LATERAL … LIMIT N for large tables with an index (see Joins)

Classic: Nth Highest Salary

-- 2nd highest distinct salary overall (NULL if it doesn't exist)
SELECT (SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET 1 LIMIT 1) AS second_highest;
 
-- With DENSE_RANK — generalises to "Nth per department"
SELECT department, name, salary
FROM (SELECT *, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS r FROM employees) t
WHERE r = 2;

Deduplication

-- Keep only the latest payment attempt per order; ROW_NUMBER marks the duplicates
SELECT order_id, id AS payment_id, status, paid_at
FROM (
    SELECT p.*, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY paid_at DESC, id DESC) AS rn
    FROM payments p
) t
WHERE rn = 1 AND order_id IN (3, 8)
ORDER BY order_id;
 
-- Delete duplicates pattern (here: would delete earlier attempts — rolled back)
BEGIN;
DELETE FROM payments
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY paid_at DESC) AS rn
        FROM payments
    ) t WHERE rn > 1
);
ROLLBACK;

Offset Functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE

Function	Returns	Default If Missing
LAG(x, n, default)	Value from n rows before	NULL (or default)
LEAD(x, n, default)	Value from n rows after	NULL (or default)
FIRST_VALUE(x)	First row of the frame	—
LAST_VALUE(x)	Last row of the frame (gotcha below)	—
NTH_VALUE(x, n)	n-th row of the frame	NULL

-- Each customer's orders with previous/next order date and gap in days
SELECT user_id, id, created_at::date AS order_date,
       LAG(created_at::date)  OVER w                        AS prev_order,
       LEAD(created_at::date) OVER w                        AS next_order,
       created_at::date - LAG(created_at::date) OVER w      AS days_since_prev,
       FIRST_VALUE(created_at::date) OVER w                 AS first_order
FROM orders
WHERE user_id IN (1, 2)
WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
ORDER BY user_id, created_at;

The LAST_VALUE Gotcha

• Problem: With ORDER BY, the default frame ends at the current row, so LAST_VALUE returns the current row’s value
• Fix: Extend the frame to UNBOUNDED FOLLOWING (or use FIRST_VALUE with reversed order)

SELECT name, salary,
       LAST_VALUE(name) OVER (ORDER BY salary)                    AS wrong_lowest_paid_is_me,
       LAST_VALUE(name) OVER (ORDER BY salary
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_paid
FROM employees
WHERE department = 'Sales'
ORDER BY salary;

Aggregate Windows: Running Totals and Moving Averages

WITH daily AS (
    SELECT o.created_at::date AS day, sum(oi.quantity * oi.unit_price) AS revenue
    FROM orders o JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status IN ('paid', 'shipped', 'delivered')
    GROUP BY 1
)
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), 2) AS moving_avg_last_3,
       sum(revenue) OVER (PARTITION BY date_trunc('month', day) ORDER BY day) AS month_to_date,
       round(100 * revenue / sum(revenue) OVER (), 1)                         AS pct_of_total
FROM daily
ORDER BY day;

Frames: ROWS vs RANGE vs GROUPS

Frame Modes

Mode	Frame Measured In	Ties (Peers)	Example
ROWS	Physical rows	Each row separately	ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = last 3 rows
RANGE	Value distance on the ORDER BY key	Peers included together	RANGE BETWEEN interval '7 days' PRECEDING AND CURRENT ROW = last 7 days
GROUPS	Peer groups	Groups of ties	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW

Frame Bounds

• UNBOUNDED PRECEDING / FOLLOWING: Partition start / end
• n PRECEDING / FOLLOWING: Offset (rows, value, or groups depending on mode)
• CURRENT ROW: In RANGE/GROUPS this means “the current row and its peers”
• EXCLUDE CURRENT ROW / TIES / GROUP: Postgres 11+ frame exclusion

ROWS vs RANGE With Ties

-- Engineering has two 150000 salaries: RANGE treats them as peers and adds both at once
SELECT name, salary,
       sum(salary) OVER (ORDER BY salary ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_running,
       sum(salary) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_running
FROM employees
WHERE department = 'Engineering'
ORDER BY salary, name;

• Takeaway: The default frame is RANGE, so a “running total” over a non-unique key jumps on ties. Use ROWS (or add a unique tie-breaker to ORDER BY) for a strict row-by-row running sum

RANGE With an Interval: Rolling Time Windows

-- Revenue in the 30 days up to and including each order (calendar-based, not row-based)
SELECT o.id, o.created_at::date,
       sum(t.total) OVER (ORDER BY o.created_at
                          RANGE BETWEEN interval '30 days' PRECEDING AND CURRENT ROW) AS rolling_30d
FROM orders o
JOIN (SELECT order_id, sum(quantity * unit_price) AS total FROM order_items GROUP BY order_id) t
  ON t.order_id = o.id
WHERE o.status <> 'cancelled'
ORDER BY o.created_at;

Gaps and Islands

• Problem: Group consecutive runs (e.g. consecutive months with an order, login streaks)
• Trick: value − ROW_NUMBER() is constant within a consecutive run

-- Streaks of consecutive months in which Asha placed an order
WITH months AS (
    SELECT DISTINCT date_trunc('month', created_at)::date AS m
    FROM orders WHERE user_id = 1
), grp AS (
    SELECT m,
           m - (ROW_NUMBER() OVER (ORDER BY m) * interval '1 month') AS island
    FROM months
)
SELECT min(m) AS streak_start, max(m) AS streak_end, count(*) AS months
FROM grp
GROUP BY island
ORDER BY streak_start;

Filtering on Window Results

-- ERROR: window functions are not allowed in WHERE
-- SELECT * FROM employees WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 3;
 
-- Wrap it (CTE or subquery)
WITH ranked AS (
    SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees
)
SELECT name, salary FROM ranked WHERE rn <= 3;

• PG 15+ Note: For outer filters like WHERE rn <= N on ROW_NUMBER/RANK/count, the planner adds a “Run Condition” and stops evaluating the window early, so the wrapper is efficient

Interview Questions

• “ROW_NUMBER vs RANK vs DENSE_RANK?”: Unique numbers vs gaps after ties vs no gaps
• “Top 3 per category?”: Ranking window in a subquery, filter rn <= 3 outside
• “Running total?”: sum(x) OVER (ORDER BY …); use a ROWS frame if the key has ties
• “Month-over-month growth?”: LAG(revenue) OVER (ORDER BY month); divide with NULLIF(…, 0)
• “Why does LAST_VALUE return the current row?”: Default frame ends at the current row — widen it
• “Window vs GROUP BY?”: Window keeps detail rows; GROUP BY collapses them

Summary

• OVER(): PARTITION BY groups, ORDER BY sequences, frame selects rows for aggregates
• Ranking: ROW_NUMBER (unique), RANK (gaps), DENSE_RANK (no gaps), NTILE (buckets)
• Offsets: LAG/LEAD for previous/next comparisons; widen the frame for LAST_VALUE
• Frames: ROWS counts rows, RANGE measures values (peers together, intervals), GROUPS counts tie-groups
• Patterns: Top-N per group, deduplication, running totals, moving averages, gaps-and-islands

Link to original

Inserts Updates Upserts and MERGE

Inserts Updates Upserts and MERGE

Overview

• Writes: INSERT, UPDATE, DELETE, MERGE, COPY
• Postgres Superpowers: RETURNING on every write, ON CONFLICT (UPSERT), UPDATE … FROM, DELETE … USING, data-modifying CTEs, MERGE (PG 15+, RETURNING on MERGE in PG 17+)
• MVCC Reminder: An UPDATE writes a new row version and marks the old one dead; DELETE marks it dead — VACUUM cleans up later
• Sample Data: E-commerce Practice Queries — all examples run inside BEGIN … ROLLBACK so the data stays unchanged

INSERT

Forms

BEGIN;
 
-- Single row
INSERT INTO users (email, full_name, country)
VALUES ('ivan@example.com', 'Ivan Petrov', 'RS');
 
-- Multi-row (one round trip, much faster than many single inserts)
INSERT INTO categories (name, parent_id) VALUES
  ('Tablets', 1),
  ('Garden',  NULL);
 
-- From a query
CREATE TEMP TABLE vip_users AS SELECT id, email FROM users WHERE false;   -- empty copy
INSERT INTO vip_users (id, email)
SELECT u.id, u.email
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'delivered');
 
-- All defaults
CREATE TEMP TABLE audit_ping (id int GENERATED ALWAYS AS IDENTITY, at timestamptz DEFAULT now());
INSERT INTO audit_ping DEFAULT VALUES;
 
SELECT count(*) AS vip_count FROM vip_users;
ROLLBACK;

RETURNING

• What: Return columns of the inserted/updated/deleted rows — no second SELECT round trip
• Typical Use: Get generated IDs, defaults, computed values
• Works On: INSERT, UPDATE, DELETE, and MERGE (PG 17+)

BEGIN;
INSERT INTO orders (user_id) VALUES (7)
RETURNING id, status, created_at;                -- status shows the DEFAULT 'pending'
ROLLBACK;

UPSERT: INSERT … ON CONFLICT

Syntax

INSERT INTO t (cols) VALUES (…)
ON CONFLICT (conflict_target)          -- unique columns / ON CONSTRAINT name / partial index predicate
DO NOTHING                             -- skip the row
| DO UPDATE SET col = EXCLUDED.col     -- EXCLUDED = the row you tried to insert
        [WHERE condition]              -- only update when this holds

• Requires: A unique index or constraint matching the conflict target
• Atomic: Safe under concurrency — no “check then insert” race
• EXCLUDED: Pseudo-table holding the proposed row; the existing row is referenced by the table name
• Limitation: The same key can’t appear twice in one INSERT … ON CONFLICT DO UPDATE statement (error: “command cannot affect row a second time”)

BEGIN;
 
-- DO NOTHING: idempotent insert (e.g. replaying events)
INSERT INTO users (email, full_name) VALUES ('asha@example.com', 'Asha Duplicate')
ON CONFLICT (email) DO NOTHING
RETURNING id;                                    -- returns 0 rows: nothing inserted
 
-- DO UPDATE: set price from a feed, only if it actually changed
INSERT INTO products (sku, name, category_id, price)
VALUES ('LAP-001', 'Air Laptop 13', 3, 949.00),
       ('LAP-003', 'Air Laptop 15', 3, 1299.00)
ON CONFLICT (sku) DO UPDATE
   SET price = EXCLUDED.price
 WHERE products.price IS DISTINCT FROM EXCLUDED.price
RETURNING sku, price, (xmax = 0) AS inserted;
 
-- Counter pattern: increment or create
CREATE TEMP TABLE page_views (path text PRIMARY KEY, views bigint NOT NULL);
INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (path) DO UPDATE SET views = page_views.views + 1;
INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (path) DO UPDATE SET views = page_views.views + 1;
SELECT * FROM page_views;                        -- /home | 2
 
ROLLBACK;

Approach	Race-Safe	Notes
SELECT then INSERT/UPDATE in app	No	Two requests both see “missing” → duplicate error
INSERT … ON CONFLICT	Yes	Postgres-native; needs unique index
MERGE	Not fully	Can still raise unique violations under concurrent inserts; use ON CONFLICT for pure upserts

UPDATE

Basic and Computed

BEGIN;
UPDATE products
SET price = round(price * 0.9, 2),
    stock = stock + 10
WHERE category_id = 8
RETURNING sku, price, stock;
ROLLBACK;

UPDATE … FROM (Join in an Update)

• What: Update rows using values from other tables
• Gotcha: If the FROM produces several matches for one target row, Postgres picks one arbitrarily — aggregate first

BEGIN;
-- Mark orders as 'paid' when they have a successful payment covering the total
UPDATE orders o
SET    status = 'paid'
FROM  (SELECT order_id, sum(amount) AS paid FROM payments WHERE status = 'succeeded' GROUP BY order_id) p,
      (SELECT order_id, sum(quantity * unit_price) AS due FROM order_items GROUP BY order_id) d
WHERE  p.order_id = o.id
  AND  d.order_id = o.id
  AND  o.status = 'pending'
  AND  p.paid >= d.due
RETURNING o.id, o.status;                        -- 0 rows: pending orders are unpaid in the seed
ROLLBACK;

BEGIN;
-- Recalculate stock from a delivery batch
CREATE TEMP TABLE delivery (sku text, qty int);
INSERT INTO delivery VALUES ('ACC-003', 25), ('LAP-002', 3);
 
UPDATE products p
SET    stock = p.stock + d.qty
FROM   delivery d
WHERE  d.sku = p.sku
RETURNING p.sku, p.stock;
ROLLBACK;

UPDATE with a Subquery (Portable)

BEGIN;
UPDATE users u
SET country = 'XX'
WHERE country IS NULL
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)
RETURNING id, full_name, country;
ROLLBACK;

DELETE

DELETE … USING (Join in a Delete)

BEGIN;
-- Delete failed payments for delivered orders
DELETE FROM payments p
USING orders o
WHERE o.id = p.order_id
  AND o.status = 'delivered'
  AND p.status = 'failed'
RETURNING p.id, p.order_id, p.amount;
ROLLBACK;

Batch Deletes on Big Tables

• Problem: One huge DELETE holds locks long, bloats the table, and generates a WAL spike
• Fix: Delete in batches by primary key, commit between batches; or partition by time and DROP/DETACH PARTITION (see Database Partition)

BEGIN;
-- One batch of at most 1000 rows; loop this from a script until 0 rows are affected
DELETE FROM payments
WHERE id IN (
    SELECT id FROM payments
    WHERE status = 'failed' AND paid_at < now() - interval '90 days'
    ORDER BY id
    LIMIT 1000
);
ROLLBACK;

MERGE (PG 15+)

Concept

• What: SQL-standard “synchronise target with source” — one statement with WHEN MATCHED / WHEN NOT MATCHED branches
• Actions: UPDATE, DELETE, INSERT, DO NOTHING
• PG 17+: WHEN NOT MATCHED BY SOURCE (target rows missing from source) and RETURNING with merge_action()
• vs ON CONFLICT: MERGE can delete and handle several conditions, but is not race-proof for inserts; ON CONFLICT is the safer pure upsert

BEGIN;
CREATE TEMP TABLE supplier_feed (sku text PRIMARY KEY, price numeric(10,2), stock int, discontinued boolean);
INSERT INTO supplier_feed VALUES
  ('ACC-001', 24.00, 300, false),   -- update
  ('ACC-003', 45.00,   0, true),    -- discontinued → deactivate
  ('ACC-009', 15.00, 500, false);   -- new product → insert
 
MERGE INTO products p
USING supplier_feed f ON f.sku = p.sku
WHEN MATCHED AND f.discontinued THEN
    UPDATE SET is_active = false, stock = 0
WHEN MATCHED THEN
    UPDATE SET price = f.price, stock = f.stock
WHEN NOT MATCHED AND NOT f.discontinued THEN
    INSERT (sku, name, category_id, price, stock)
    VALUES (f.sku, 'New item ' || f.sku, 4, f.price, f.stock)
RETURNING merge_action(), p.sku, p.price, p.stock, p.is_active;   -- PG 17+
 
ROLLBACK;

Data-Modifying CTEs

• What: INSERT/UPDATE/DELETE … RETURNING inside WITH, chained in one atomic statement
• Snapshot Rule: All sub-statements see the same snapshot — they can’t see each other’s changes except through RETURNING
• Classic Uses: Archive-then-delete, create parent + children, audit logging

BEGIN;
CREATE TEMP TABLE payments_archive (LIKE payments);
 
-- Move failed payments to an archive table in one statement
WITH moved AS (
    DELETE FROM payments
    WHERE status = 'failed'
    RETURNING *
)
INSERT INTO payments_archive
SELECT * FROM moved
RETURNING id, order_id, status;
 
ROLLBACK;

Bulk Loading with COPY

COPY vs INSERT

Method	Speed	Notes
Single-row INSERT	Slowest	One round trip + one commit per row if autocommit
Multi-row INSERT	Good	Batch 500–1000 rows per statement
COPY	Fastest	Streams rows, minimal parsing overhead
COPY into UNLOGGED/staging + INSERT … SELECT	Fastest for ETL	Validate/transform in SQL, then merge

-- Export a query to stdout as CSV (psql prints it)
COPY (SELECT sku, name, price FROM products WHERE category_id = 8 ORDER BY sku)
TO STDOUT WITH (FORMAT csv, HEADER);

# Server-side COPY reads files on the DB server (needs superuser / pg_read_server_files)
#   COPY products_staging FROM '/var/lib/postgresql/products.csv' WITH (FORMAT csv, HEADER);
# Client-side \copy (psql) reads a file on YOUR machine — usually what you want
psql -c "\copy products_staging FROM 'products.csv' WITH (FORMAT csv, HEADER)"

Fast Bulk Load Checklist

• Load into a Staging Table: Then INSERT … SELECT … ON CONFLICT into the real table
• Drop / Defer Indexes: Create indexes after loading big batches
• Large Transaction: One commit per batch, not per row
• ANALYZE Afterwards: So the planner knows the new data distribution
• COPY FREEZE: For a table created/truncated in the same transaction, avoids later vacuum work
• PG 17: COPY … ON_ERROR ignore skips bad rows instead of failing the whole load

Interview Questions

• “How do you do an upsert in Postgres?”: INSERT … ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col
• “MERGE vs ON CONFLICT?”: MERGE handles insert/update/delete branches from a source; ON CONFLICT is the concurrency-safe upsert
• “Get the generated ID after insert?”: RETURNING id
• “Update using another table?”: UPDATE … FROM; make sure each target row matches at most one source row
• “Delete millions of rows safely?”: Batches by PK with commits, or drop a time partition
• “Fastest way to load 10M rows?”: COPY into a staging table, build indexes after, ANALYZE

Summary

• INSERT: Multi-row VALUES, INSERT … SELECT, RETURNING for generated values
• UPSERT: ON CONFLICT DO NOTHING / DO UPDATE with EXCLUDED, race-safe given a unique index
• UPDATE / DELETE: FROM / USING for joins; aggregate the source to avoid ambiguous matches
• MERGE: Standard multi-branch sync (PG 15+), RETURNING + NOT MATCHED BY SOURCE in PG 17
• Bulk: COPY / \copy, staging tables, batch deletes, ANALYZE after loads

Link to original

Transactions and Locking

Transactions and Locking

Overview

• Transaction: A group of statements that commit or roll back as one unit — the “A” in ACID
• From SQL: BEGIN / COMMIT / ROLLBACK, SAVEPOINT, isolation levels, explicit row locks (FOR UPDATE, SKIP LOCKED), advisory locks
• Postgres Model: MVCC — readers never block writers and writers never block readers; only writers on the same row wait for each other
• Theory Notes: Concurrency Control, Shared vs Exclusive Lock, Two-phase Locking, Deadlock, Phantom Reads, Serializable vs Non-Repeatable Isolation Level
• Sample Data: E-commerce Practice Queries

BEGIN, COMMIT, ROLLBACK

Basics

• Autocommit: Without BEGIN, every statement is its own transaction
• BEGIN: Starts a transaction block (START TRANSACTION is the standard spelling)
• COMMIT: Makes changes durable and visible to others
• ROLLBACK: Discards every change since BEGIN
• Aborted State: After any error inside a block, Postgres rejects further statements (current transaction is aborted) until ROLLBACK (or rollback to a savepoint)

-- Transfer stock between two products atomically
BEGIN;
UPDATE products SET stock = stock - 5 WHERE sku = 'ACC-001';
UPDATE products SET stock = stock + 5 WHERE sku = 'ACC-003';
SELECT sku, stock FROM products WHERE sku IN ('ACC-001', 'ACC-003') ORDER BY sku;
ROLLBACK;   -- COMMIT in real life

SAVEPOINT

• What: A named point inside a transaction you can roll back to without losing earlier work
• Use: Try a risky step, recover from an expected error, continue
• Cost: Each savepoint creates a subtransaction — thousands in one transaction hurt performance (subtrans SLRU overflow)

BEGIN;
INSERT INTO categories (name) VALUES ('Toys');
 
SAVEPOINT before_dupe;
INSERT INTO categories (name) VALUES ('Books');      -- violates UNIQUE(name) → error, txn now aborted
ROLLBACK TO SAVEPOINT before_dupe;                   -- recovered: 'Toys' insert still alive
 
INSERT INTO categories (name) VALUES ('Garden');
SELECT name FROM categories WHERE name IN ('Toys', 'Garden', 'Books') ORDER BY name;
RELEASE SAVEPOINT before_dupe;
ROLLBACK;

Isolation Levels

Read Phenomena

• Dirty Read: See another transaction’s uncommitted data
• Non-Repeatable Read: Re-reading a row gives a different value (someone committed an update)
• Phantom Read: Re-running a range query returns new/missing rows
• Serialization Anomaly: Result impossible under any serial order (e.g. write skew)

Postgres Behaviour

Level	Dirty	Non-Repeatable	Phantom	Anomaly	Postgres Notes
READ UNCOMMITTED	No	Possible	Possible	Possible	Treated as READ COMMITTED
READ COMMITTED (default)	No	Possible	Possible	Possible	New snapshot per statement
REPEATABLE READ	No	No	No (stricter than standard)	Possible	One snapshot per transaction; concurrent update → error 40001
SERIALIZABLE	No	No	No	No	SSI — detects dangerous patterns, aborts one txn with 40001

Setting the Level

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders;      -- snapshot taken at the first query
-- other sessions' commits are invisible from here on
SELECT count(*) FROM orders;      -- same answer, guaranteed
COMMIT;
 
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;   -- must be the first statement in the txn
SHOW transaction_isolation;
COMMIT;
 
-- Read-only reporting transaction that can't fail with serialization errors
BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT sum(amount) FROM payments WHERE status = 'succeeded';
COMMIT;

Lost Update and How to Prevent It

READ COMMITTED, two sessions both run "read stock → subtract in app → write":
 
Session A: SELECT stock FROM products WHERE id = 5;         -- 10
Session B: SELECT stock FROM products WHERE id = 5;         -- 10
Session A: UPDATE products SET stock = 9 WHERE id = 5;  COMMIT;
Session B: UPDATE products SET stock = 9 WHERE id = 5;  COMMIT;   -- A's sale is lost (should be 8)

Fix	How	Trade-off
Atomic UPDATE	SET stock = stock - 1	Best when logic fits in SQL
SELECT … FOR UPDATE	Lock the row before reading	Pessimistic; others wait
Optimistic Version Column	UPDATE … WHERE id = $1 AND version = $2 → 0 rows = retry	No locks held; app must retry
REPEATABLE READ / SERIALIZABLE	DB raises 40001 on conflict	App must retry the whole txn

BEGIN;
-- Atomic decrement with a guard: 0 rows updated = out of stock
UPDATE products SET stock = stock - 1
WHERE id = 5 AND stock > 0
RETURNING stock;                                  -- USB-C Hub has 0 → returns nothing
ROLLBACK;

Explicit Row Locks

Lock Strengths

Clause	Blocks	Use
FOR UPDATE	Other FOR UPDATE/SHARE, UPDATE, DELETE of the row	You will update/delete the row
FOR NO KEY UPDATE	Same, but allows FOR KEY SHARE (FK checks)	Updating non-key columns (what plain UPDATE takes)
FOR SHARE	Writers, but not other sharers	Ensure the row doesn’t change while you read
FOR KEY SHARE	Only deletes / key changes	Taken by FK checks on the parent
NOWAIT	—	Error immediately instead of waiting
SKIP LOCKED	—	Silently skip locked rows (queues!)

BEGIN;
-- Pessimistic: lock the order and its items' products before changing them
SELECT o.id, o.status
FROM orders o
WHERE o.id = 10
FOR UPDATE;                                      -- other txns trying to lock/update order 10 now wait
 
UPDATE orders SET status = 'paid' WHERE id = 10;
ROLLBACK;
 
-- Lock only one table of a join
BEGIN;
SELECT p.id, p.stock
FROM products p JOIN order_items oi ON oi.product_id = p.id
WHERE oi.order_id = 10
FOR UPDATE OF p NOWAIT;                          -- error 55P03 if someone else holds it
ROLLBACK;

Job Queue With FOR UPDATE SKIP LOCKED

• Pattern: Many workers poll the same table; each grabs a different unlocked job — no double processing, no blocking
• Why It Works: SKIP LOCKED ignores rows already locked by other workers’ open transactions
• Index: A partial index on pending jobs keeps the poll cheap

CREATE TABLE IF NOT EXISTS jobs (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kind        text NOT NULL,
    payload     jsonb NOT NULL DEFAULT '{}',
    status      text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'running', 'done', 'failed')),
    attempts    int NOT NULL DEFAULT 0,
    run_after   timestamptz NOT NULL DEFAULT now(),
    locked_by   text,
    created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS jobs_queued_idx ON jobs (run_after, id) WHERE status = 'queued';
 
INSERT INTO jobs (kind, payload)
SELECT 'send_receipt', jsonb_build_object('order_id', id)
FROM orders WHERE status = 'delivered';

-- Worker loop body (each worker runs this; claim + mark in one statement)
BEGIN;
WITH next_job AS (
    SELECT id
    FROM jobs
    WHERE status = 'queued' AND run_after <= now()
    ORDER BY run_after, id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'running', attempts = attempts + 1, locked_by = 'worker-1'
FROM next_job
WHERE j.id = next_job.id
RETURNING j.id, j.kind, j.payload;
 
-- … do the work in the app …
UPDATE jobs SET status = 'done' WHERE locked_by = 'worker-1' AND status = 'running';
COMMIT;
 
SELECT status, count(*) FROM jobs GROUP BY status ORDER BY status;

• Two Workers at Once: Worker 2 running the same claim while worker 1’s transaction is open gets the next job, not a wait (verified with two concurrent sessions: A claimed job 2, B immediately got job 3)
• Crash Safety: If you claim and process inside one transaction, a crash rolls back to queued. If you commit the claim first (short transactions), add a timeout sweeper that resets stale running jobs
• At Scale: Fine for thousands of jobs/sec; beyond that, or for fan-out, consider a dedicated broker

Advisory Locks

Concept

• What: Application-defined locks on an arbitrary bigint key — Postgres just tracks who holds the number
• Session-level: pg_advisory_lock(key) — held until unlocked or the session ends (careful with connection pools — see Database Connection Pooling)
• Transaction-level: pg_advisory_xact_lock(key) — released automatically at commit/rollback (safer)
• Try Variants: pg_try_advisory_lock(key) returns false instead of waiting

Use Case	Example
Single-runner cron	Only one instance of a nightly job runs
Serialize per entity	One balance recalculation per user at a time
Migrations	Tools like Flyway/Rails take an advisory lock so two deploys don’t migrate together

-- Only one process may rebuild the report for user 42 at a time
BEGIN;
SELECT pg_advisory_xact_lock(hashtext('rebuild_report'), 42);   -- two-int form: (namespace, id)
-- … rebuild …
COMMIT;                                                         -- lock released
 
-- Cron guard: skip if another instance is already running
SELECT pg_try_advisory_lock(777) AS got_lock;     -- true for the first caller, false for others
SELECT pg_advisory_unlock(777);
 
-- See who holds advisory locks
SELECT locktype, classid, objid, pid, granted FROM pg_locks WHERE locktype = 'advisory';

Table Locks and DDL

• Every Statement Takes a Table Lock: SELECT → ACCESS SHARE, UPDATE/DELETE/INSERT → ROW EXCLUSIVE, most ALTER TABLE / DROP / TRUNCATE → ACCESS EXCLUSIVE
• The Queue Problem: An ALTER TABLE waiting behind a long SELECT blocks every later query on the table — always SET lock_timeout for migrations
• Explicit: LOCK TABLE t IN SHARE ROW EXCLUSIVE MODE; (rarely needed)

BEGIN;
SET LOCAL lock_timeout = '2s';           -- give up instead of stalling the app
SET LOCAL statement_timeout = '30s';
ALTER TABLE products ADD COLUMN IF NOT EXISTS weight_g int;
ROLLBACK;

Deadlocks

• Cause: A locks row 1 then wants row 2; B locks row 2 then wants row 1
• Postgres Response: After deadlock_timeout (1s) it detects the cycle and aborts one transaction with error 40P01
• Prevention: Lock rows in a consistent order (e.g. ORDER BY id … FOR UPDATE), keep transactions short — see Deadlock

BEGIN;
-- Lock all rows you need up front, in id order
SELECT id FROM products WHERE id IN (7, 3, 9) ORDER BY id FOR UPDATE;
ROLLBACK;

Retry Logic for 40001 / 40P01

for attempt in 1..5:
    try:
        BEGIN ISOLATION LEVEL SERIALIZABLE
        ... statements ...
        COMMIT
        break
    except SQLSTATE in ('40001' serialization_failure, '40P01' deadlock_detected):
        ROLLBACK
        sleep(random backoff)

Interview Questions

• “Default isolation level in Postgres?”: READ COMMITTED (per-statement snapshot)
• “How does Postgres avoid readers blocking writers?”: MVCC — readers see a snapshot of committed row versions
• “Build a job queue in Postgres?”: SELECT … FOR UPDATE SKIP LOCKED LIMIT 1 inside a transaction
• “Prevent lost updates?”: Atomic UPDATE … SET x = x - 1, FOR UPDATE, optimistic versioning, or SERIALIZABLE + retry
• “What is an advisory lock?”: An app-defined mutex keyed by a number, session- or transaction-scoped
• “Why did my migration take the site down?”: ACCESS EXCLUSIVE lock queued behind a long query — use lock_timeout

Summary

• Transactions: BEGIN/COMMIT/ROLLBACK; errors abort the block; SAVEPOINT for partial recovery
• Isolation: READ COMMITTED default; REPEATABLE READ = transaction snapshot; SERIALIZABLE = full safety + retries
• Row Locks: FOR UPDATE / FOR SHARE / NOWAIT / SKIP LOCKED — SKIP LOCKED powers job queues
• Advisory Locks: Application-level mutexes; prefer the xact variant with poolers
• Operational Safety: lock_timeout for DDL, consistent lock ordering, retry on 40001/40P01

Link to original

Indexes and Query Performance

Indexes and Query Performance

Overview

• Goal: Write queries the planner can serve from an index, and read EXPLAIN output to prove it
• Index Theory Lives Elsewhere: Database Indexing, Normal Index VS Composite Index, Key vs Non-Key Column Indexes, Index Scan vs Index Only Scan, Sequential vs index vs bitmap index scan, Query Planner and Optimiser, B-Tree
• This Note: The SQL side — CREATE INDEX variants, CONCURRENTLY, EXPLAIN ANALYZE, and the query patterns that silently defeat indexes
• Verified: All plans below were captured from PostgreSQL 17 on the 200k-row table created in the next section

Lab Table

-- 200k synthetic events (deterministic, ~1 second to build)
DROP TABLE IF EXISTS events;
CREATE TABLE events (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    int NOT NULL,
    kind       text NOT NULL,             -- view 79%, click 15%, purchase 5%, refund 1%
    email      text NOT NULL,
    amount     numeric(10,2),
    props      jsonb NOT NULL,
    created_at timestamptz NOT NULL
);
INSERT INTO events (user_id, kind, email, amount, props, created_at)
SELECT (g * 7919) % 10000 + 1,
       CASE WHEN g % 97 = 0 THEN 'refund' WHEN g % 20 = 0 THEN 'purchase'
            WHEN g % 5 = 0 THEN 'click' ELSE 'view' END,
       'User' || g || '@Example.com',
       CASE WHEN g % 20 = 0 THEN (g % 500) + 0.99 END,
       jsonb_build_object('device', (ARRAY['ios','android','web'])[1 + g % 3], 'campaign', 'c' || (g % 50)),
       timestamptz '2025-01-01 00:00+00' + g * interval '1 minute'
FROM generate_series(1, 200000) AS g;
VACUUM ANALYZE events;    -- fresh statistics + visibility map (needed for index-only scans)

Reading EXPLAIN

Variants

Command	Runs Query	Shows
EXPLAIN	No	Estimated plan + costs
EXPLAIN ANALYZE	Yes (wrap writes in BEGIN … ROLLBACK)	Actual rows, time, loops
EXPLAIN (ANALYZE, BUFFERS)	Yes	+ pages hit in cache / read from disk (default with ANALYZE in PG 18)
EXPLAIN (COSTS OFF)	No	Plan shape only — handy for notes/tests
EXPLAIN (FORMAT JSON)	No	Machine-readable, paste into visualisers (explain.dalibo.com)

How to Read a Node

Index Scan using events_user_created_idx on events  (cost=0.42..21.30 rows=20 width=16) (actual time=0.010..0.015 rows=5 loops=1)
│                                                    │     │      │       │     │               │                     │       │
│                                                    │     │      │       │     │               │                     │       └ times this node ran (multiply!)
│                                                    │     │      │       │     │               │                     └ rows actually returned (per loop)
│                                                    │     │      │       │     │               └ ms to first row .. ms to last row (per loop)
│                                                    │     │      │       │     └ avg row width in bytes
│                                                    │     │      │       └ ESTIMATED rows
│                                                    │     │      └ total cost (arbitrary units)
│                                                    │     └ startup cost
└ node type: how rows are produced

• Read Inside-Out: The most indented node runs first and feeds its parent
• Estimate vs Actual: rows=20 estimated vs rows=20000 actual → stale stats or correlated columns → ANALYZE, extended statistics
• Rows Removed by Filter: Large number = the index didn’t cover that condition
• loops: Inner side of a Nested Loop runs once per outer row — actual time × loops is the real cost
• Buffers shared read: Pages fetched from disk/OS cache; hit = Postgres buffer cache

Common Node Types

Node	Meaning	Good When
Seq Scan	Read whole table	Small table or most rows needed
Index Scan	Walk index, fetch each heap row	Few rows, or ordered output needed
Index Only Scan	Answer from index alone (Heap Fetches: 0 ideal)	Covering index + well-vacuumed table
Bitmap Index/Heap Scan	Collect TIDs, then read heap pages in order	Medium selectivity, OR / AND of indexes
Nested Loop	For each outer row, probe inner	Small outer, indexed inner
Hash Join	Build hash of one side, probe with other	Big unsorted equi-joins
Merge Join	Merge two sorted inputs	Both already sorted
Sort / Incremental Sort	Sort rows (Sort Method: external merge = spilled to disk)	Raise work_mem or add an index on the order
HashAggregate / GroupAggregate	GROUP BY via hash or sorted input
Gather / Parallel Seq Scan	Parallel workers	Large scans

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, sum(oi.quantity * oi.unit_price)
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = 1
GROUP BY o.id;

CREATE INDEX Variants

B-tree (Default)

• Supports: =, <, <=, >, >=, BETWEEN, IN, IS NULL, ORDER BY, prefix LIKE 'abc%' (needs text_pattern_ops or C collation)
• Before: Seq Scan … Rows Removed by Filter: 199980

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM events WHERE user_id = 42;
 
CREATE INDEX events_user_id_idx ON events (user_id);
 
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM events WHERE user_id = 42;

-- before
Seq Scan on events (actual rows=20 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 199980
-- after
Bitmap Heap Scan on events (actual rows=20 loops=1)
  Recheck Cond: (user_id = 42)
  Heap Blocks: exact=20
  ->  Bitmap Index Scan on events_user_id_idx (actual rows=20 loops=1)
        Index Cond: (user_id = 42)

Composite Index and Column Order

• Leftmost Prefix Rule: Index on (a, b) serves WHERE a = … and WHERE a = … AND b …, but not WHERE b = … alone (efficiently)
• Order Matters: Equality columns first, then the range / ORDER BY column
• Removes Sorts: WHERE user_id = ? ORDER BY created_at DESC LIMIT n becomes a short index walk

-- Single-column index: needs a Sort on top
EXPLAIN (COSTS OFF)
SELECT id, created_at FROM events WHERE user_id = 42 ORDER BY created_at DESC LIMIT 5;
 
CREATE INDEX events_user_created_idx ON events (user_id, created_at DESC);
 
-- Composite: rows come out of the index already ordered — no Sort node
EXPLAIN (COSTS OFF)
SELECT id, created_at FROM events WHERE user_id = 42 ORDER BY created_at DESC LIMIT 5;

-- before                                        -- after
Limit                                            Limit
  ->  Sort                                         ->  Index Scan using events_user_created_idx on events
        Sort Key: created_at DESC                        Index Cond: (user_id = 42)
        ->  Bitmap Heap Scan on events
              ->  Bitmap Index Scan on events_user_id_idx

Covering Index (INCLUDE)

• What: Store extra non-key columns in the index leaf so the query never touches the table → Index Only Scan
• INCLUDE vs Adding to Key: Included columns don’t affect ordering/uniqueness and keep the upper tree small
• Needs Vacuum: Index-only scans check the visibility map; un-vacuumed pages show up as Heap Fetches

CREATE INDEX events_user_created_cov ON events (user_id, created_at DESC) INCLUDE (kind, amount);
VACUUM events;
 
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT created_at, kind, amount FROM events WHERE user_id = 42 ORDER BY created_at DESC LIMIT 5;

Limit (actual rows=5 loops=1)
  ->  Index Only Scan using events_user_created_cov on events (actual rows=5 loops=1)
        Index Cond: (user_id = 42)
        Heap Fetches: 0

Partial Index

• What: Index only rows matching a WHERE — smaller, faster, cheaper to maintain
• Use: Hot subsets (status = 'queued', deleted_at IS NULL, rare kinds), partial uniqueness
• Rule: The query’s WHERE must imply the index predicate

CREATE INDEX events_refunds_idx ON events (created_at) WHERE kind = 'refund';
 
EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE kind = 'refund' AND created_at >= '2025-03-01';
 
SELECT pg_size_pretty(pg_relation_size('events_refunds_idx')) AS partial_idx,     -- 64 kB
       pg_size_pretty(pg_relation_size('events_user_id_idx')) AS full_idx;        -- ~1.5 MB

Index Scan using events_refunds_idx on events
  Index Cond: (created_at >= '2025-03-01 00:00:00+00'::timestamp with time zone)

Expression Index

• What: Index the result of an expression; the query must use the exact same expression

EXPLAIN (COSTS OFF) SELECT * FROM events WHERE lower(email) = 'user42@example.com';   -- Parallel Seq Scan
 
CREATE INDEX events_lower_email_idx ON events (lower(email));
ANALYZE events;                         -- expression indexes get their own statistics
 
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE lower(email) = 'user42@example.com';   -- Index Scan

Index Scan using events_lower_email_idx on events
  Index Cond: (lower(email) = 'user42@example.com'::text)

GIN for JSONB, Arrays and Full-Text

Operator Class	Supports	Size
jsonb_ops (default)	@>, ?, `?	, ?&, @?, @@`
jsonb_path_ops	@>, @?, @@ only	Smaller, faster for containment
array_ops	@>, <@, &&, = on arrays
gin_trgm_ops (pg_trgm)	LIKE '%x%', ILIKE, ~, similarity

CREATE INDEX events_props_gin ON events USING gin (props jsonb_path_ops);
 
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events WHERE props @> '{"campaign": "c7"}';

Aggregate
  ->  Bitmap Heap Scan on events
        Recheck Cond: (props @> '{"campaign": "c7"}'::jsonb)
        ->  Bitmap Index Scan on events_props_gin
              Index Cond: (props @> '{"campaign": "c7"}'::jsonb)

• More JSONB Indexing: See JSONB and Arrays

Other Index Types

Type	Best For
Hash	Pure equality on big keys (WAL-logged since PG 10); B-tree usually just as good
GiST	Ranges, geometry, exclusion constraints, nearest-neighbour
SP-GiST	Partitioned spaces — IPs, quad-trees, text prefixes
BRIN	Huge append-only tables where values correlate with physical order (timestamps) — tiny index

UNIQUE and CONCURRENTLY

• Plain CREATE INDEX: Blocks writes (SHARE lock) for the whole build — OK in dev, dangerous on hot tables
• CREATE INDEX CONCURRENTLY: Doesn’t block writes; takes longer (two table scans), can’t run inside a transaction block
• If It Fails: Leaves an INVALID index — drop it and retry (DROP INDEX CONCURRENTLY)
• REINDEX CONCURRENTLY: Rebuild a bloated index without blocking (PG 12+)

CREATE INDEX CONCURRENTLY IF NOT EXISTS events_kind_idx ON events (kind);
 
-- Find invalid leftovers from failed concurrent builds
SELECT indexrelid::regclass AS index_name FROM pg_index WHERE NOT indisvalid;

Index Anti-Patterns

1. Function or Cast on the Indexed Column

CREATE INDEX events_created_idx ON events (created_at);
 
-- Bad: cast on the column → Filter over the entire index, no Index Cond
EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE created_at::date = '2025-02-01';
 
-- Good: half-open range on the raw column → Index Cond
EXPLAIN (COSTS OFF) SELECT count(*) FROM events
WHERE created_at >= '2025-02-01' AND created_at < '2025-02-02';

-- bad                                                        -- good
Parallel Index Only Scan using events_created_idx              Index Only Scan using events_created_idx
  Filter: ((created_at)::date = '2025-02-01'::date)              Index Cond: ((created_at >= …) AND (created_at < …))

• Same for: lower(col), date_trunc(…, col), col + 1 = 5, coalesce(col, 0) = 0 — rewrite or add an expression index

2. Implicit Casts / Type Mismatch

-- user_id is int; a numeric literal casts the COLUMN to numeric → index unusable
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE user_id = 42.0;
-- Filter: ((user_id)::numeric = 42.0)   ← Parallel Seq Scan
 
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE user_id = 42;      -- index used

• Real-World Causes: ORMs binding bigint vs numeric, comparing varchar to text[], joining int to bigint across tables (fine) vs int to text (not fine), UUIDs stored as text

3. Leading Wildcard LIKE

-- B-tree can serve a prefix match only with text_pattern_ops (or C collation)
CREATE INDEX events_email_pattern_idx ON events (email text_pattern_ops);
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE email LIKE 'User4211%';   -- Index Scan (range on prefix)
 
-- Leading % can't use a B-tree at all → Seq Scan
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE email LIKE '%4211@%';
 
-- Fix: trigram GIN index
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX events_email_trgm_idx ON events USING gin (email gin_trgm_ops);
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE email LIKE '%4211@%';     -- Bitmap Index Scan on events_email_trgm_idx

4. OR Across Columns

• Problem: WHERE a = 1 OR b = 2 needs an index on both columns (combined with BitmapOr); if one side is unindexed the whole query falls back to a Seq Scan
• Fixes: Index both columns, rewrite as UNION, or use IN (…) / = ANY for same-column ORs

-- amount has no index → Parallel Seq Scan for the whole OR
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE user_id = 42 OR amount > 498;
 
-- Both sides indexed → BitmapOr of two index scans
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE user_id = 42 OR email = 'User1@Example.com';
 
-- UNION rewrite (each branch uses its own best plan)
EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE user_id = 42
UNION
SELECT * FROM events WHERE email = 'User1@Example.com';

5. Low Selectivity

• Rule of Thumb: If a predicate matches more than ~5–20% of the table, a Seq Scan is cheaper than random index lookups — the planner is right to ignore your index

EXPLAIN (COSTS OFF) SELECT * FROM events WHERE kind = 'view';     -- 79% of rows → Seq Scan
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE kind = 'refund';   -- 1% → Index Scan using events_refunds_idx (the partial index)

6. Other Classics

Anti-pattern	Why It Hurts	Fix
Unindexed FK columns	Joins scan; DELETE on parent scans child	Index child FK columns
SELECT *	Prevents index-only scans, more I/O	Select needed columns
NOT IN (subquery)	No anti-join, NULL bug	NOT EXISTS (Joins)
Deep OFFSET	Reads and discards rows	Keyset pagination (SELECT Fundamentals)
Too many indexes	Every write updates every index; blocks HOT updates	Drop unused (pg_stat_user_indexes.idx_scan = 0)
Stale statistics	Bad row estimates → bad plans	ANALYZE; tune autovacuum
count(*) on huge tables for UI	Full scan under MVCC	Estimate from pg_class.reltuples

Planner Statistics Tools

-- What the planner believes about a column
SELECT attname, n_distinct, null_frac, most_common_vals
FROM pg_stats
WHERE tablename = 'events' AND attname IN ('kind', 'user_id');
 
-- Correlated columns: teach the planner they move together
CREATE STATISTICS IF NOT EXISTS events_kind_amount (dependencies) ON kind, amount FROM events;
ANALYZE events;
 
-- Session-level experiments (never in production config)
SET enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE kind = 'view' LIMIT 1;
RESET enable_seqscan;

Interview Questions

• “Index exists but isn’t used — why?”: Function/cast on the column, type mismatch, leading wildcard, low selectivity, stale stats, OR with an unindexed column
• “EXPLAIN vs EXPLAIN ANALYZE?”: Estimate only vs actually execute and report real rows/time (careful with writes)
• “Composite index column order?”: Equality filters first, then range/sort column; leftmost prefix rule
• “What is a covering index?”: Index containing every column the query needs (INCLUDE) → index-only scan
• “Add an index to a busy production table?”: CREATE INDEX CONCURRENTLY, outside a transaction, check for INVALID afterwards
• “Index a JSONB column?”: GIN (jsonb_path_ops for @>), or a B-tree expression index on (data->>'key')

Summary

• EXPLAIN ANALYZE + BUFFERS: Compare estimated vs actual rows, watch Rows Removed by Filter, loops and Sort spills
• Index Toolkit: B-tree, composite, INCLUDE covering, partial, expression, GIN (jsonb/trgm), BRIN for time series
• Production: CONCURRENTLY for create/reindex/drop; clean up INVALID indexes
• Anti-patterns: Functions/casts on columns, implicit type casts, LIKE '%x', mixed-column ORs, low selectivity
• Balance: Every index speeds reads but slows writes — measure with pg_stat_user_indexes (Admin and Inspection Queries)

Link to original

Admin and Inspection Queries

Admin and Inspection Queries

Overview

• Purpose: Copy-paste SQL for “what is the database doing right now?” — sessions, slow queries, locks, sizes, index usage, vacuum health
• Main Sources: pg_stat_activity, pg_locks, pg_stat_statements, pg_stat_user_tables, pg_stat_user_indexes, size functions
• Permissions: Superuser or the pg_monitor role sees everything; others see only their own sessions’ query text
• Related: Indexes and Query Performance, Transactions and Locking, Database Connection Pooling, Replication Postgres Example

Sessions: pg_stat_activity

Key Columns

Column	Meaning
pid	Backend process ID — used to cancel/terminate
state	active, idle, idle in transaction, idle in transaction (aborted)
query	Current (or last, if idle) statement text
query_start / xact_start	When the statement / transaction began
wait_event_type / wait_event	What it is waiting on (Lock, IO, Client, LWLock)
backend_type	client backend, autovacuum worker, walsender, …

-- What's running right now, longest first
SELECT pid,
       usename,
       application_name,
       client_addr,
       state,
       now() - query_start                AS running_for,
       wait_event_type || ':' || wait_event AS waiting_on,
       left(query, 80)                    AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND pid <> pg_backend_pid()
ORDER BY query_start NULLS LAST;

-- Connection count by state (are we near max_connections?)
SELECT state, count(*) FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state ORDER BY count(*) DESC;
 
SELECT current_setting('max_connections')::int AS max_conn,
       count(*)                                AS used
FROM pg_stat_activity;

-- Long-running queries (> 5 minutes) and old open transactions
SELECT pid, state, now() - query_start AS query_age, now() - xact_start AS xact_age, left(query, 80)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND (now() - query_start > interval '5 minutes'
       OR (state LIKE 'idle in transaction%' AND now() - xact_start > interval '5 minutes'));

• Idle in Transaction: The worst state — holds locks and blocks vacuum from cleaning dead rows. Set idle_in_transaction_session_timeout

Cancelling and Killing Queries

Function	Effect	Use
pg_cancel_backend(pid)	Cancels the current query; session stays	First try — gentle (like Ctrl-C)
pg_terminate_backend(pid)	Kills the whole session/connection	Stuck idle-in-transaction, cancel didn’t work
pg_terminate_backend(pid, timeout_ms)	Waits for it to die (PG 14+)	Scripts

-- Cancel one query (replace 12345 with a pid from pg_stat_activity)
-- SELECT pg_cancel_backend(12345);
-- SELECT pg_terminate_backend(12345);
 
-- Kill all sessions idle in a transaction for more than 10 minutes
SELECT pid, pg_terminate_backend(pid) AS terminated
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '10 minutes'
  AND pid <> pg_backend_pid();
 
-- Prevent runaway queries in the first place
SET statement_timeout = '30s';                        -- per session; or ALTER ROLE app SET …
SET idle_in_transaction_session_timeout = '60s';
SET lock_timeout = '5s';
RESET ALL;

Locks and Blocking

Who Is Blocking Whom

-- The one query to remember: blocked sessions and the pid(s) blocking them
SELECT blocked.pid                     AS blocked_pid,
       left(blocked.query, 60)         AS blocked_query,
       now() - blocked.query_start     AS waiting_for,
       blocking.pid                    AS blocking_pid,
       blocking.state                  AS blocking_state,
       left(blocking.query, 60)        AS blocking_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid
ORDER BY waiting_for DESC;

-- Raw lock table: which relations are locked, in which mode, granted or waiting
SELECT l.pid,
       l.locktype,
       l.relation::regclass AS relation,
       l.mode,
       l.granted,
       a.state,
       left(a.query, 60)    AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation IS NOT NULL
  AND a.pid <> pg_backend_pid()
ORDER BY l.granted, l.pid;

• granted = false: That session is waiting
• Lock Tree: A waiting ALTER TABLE (AccessExclusiveLock) makes everything behind it wait too — see Transactions and Locking
• Deadlocks: Logged automatically; count with SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();

pg_stat_statements: Slowest and Most Expensive Queries

Setup

# postgresql.conf (requires restart)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Top Queries

-- Top 10 by TOTAL time — what is the database spending its life on
SELECT round(total_exec_time::numeric, 1)                      AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2)                       AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       rows,
       left(query, 80)                                         AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Slowest on average (called at least 10 times), plus cache hit ratio per query
SELECT calls,
       round(mean_exec_time::numeric, 2)   AS mean_ms,
       round(max_exec_time::numeric, 2)    AS max_ms,
       round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct,
       left(query, 80)                     AS query
FROM pg_stat_statements
WHERE calls >= 10
ORDER BY mean_exec_time DESC
LIMIT 10;
 
-- Reset counters after a deploy / tuning change
SELECT pg_stat_statements_reset();

• Normalised Text: Literals become $1, $2 so the same query shape aggregates together
• Total vs Mean: Optimise total time first — a 2 ms query called 10M times beats a 5 s report run twice

Sizes

-- Database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
 
-- Biggest tables: heap, indexes, TOAST, total
SELECT c.relname                                         AS table,
       pg_size_pretty(pg_relation_size(c.oid))           AS heap,
       pg_size_pretty(pg_indexes_size(c.oid))            AS indexes,
       pg_size_pretty(pg_total_relation_size(c.oid))     AS total,
       c.reltuples::bigint                               AS est_rows
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 10;
 
-- Biggest indexes
SELECT indexrelid::regclass AS index, relid::regclass AS table,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

Function	Measures
pg_relation_size(t)	Main heap only
pg_table_size(t)	Heap + TOAST + FSM/VM
pg_indexes_size(t)	All indexes of the table
pg_total_relation_size(t)	Everything
pg_database_size(db)	Whole database

Fast Row Count Estimate

-- count(*) scans the whole table under MVCC; the planner's estimate is instant
SELECT reltuples::bigint AS estimated_rows FROM pg_class WHERE oid = 'orders'::regclass;

Index Usage

-- Unused indexes (idx_scan = 0 since stats reset) — candidates to drop, except PK/UNIQUE
SELECT s.relname AS table, s.indexrelname AS index,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;
 
-- Tables read mostly by sequential scans (maybe missing an index)
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       round(seq_tup_read::numeric / NULLIF(seq_scan, 0)) AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;
 
-- Invalid indexes left by failed CREATE INDEX CONCURRENTLY
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

Vacuum, Bloat and Health

-- Dead rows and last (auto)vacuum/analyze per table
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,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
 
-- Transaction ID wraparound risk (alert well before 2 billion)
SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_to_wraparound
FROM pg_database
ORDER BY xid_age DESC;
 
-- Buffer cache hit ratio (OLTP should be > 99%)
SELECT round(100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database;
 
-- Is autovacuum running right now?
SELECT pid, now() - xact_start AS running_for, query
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';

Settings and Server Info

SELECT version();
SELECT pg_postmaster_start_time(), now() - pg_postmaster_start_time() AS uptime;
 
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('max_connections', 'shared_buffers', 'work_mem', 'maintenance_work_mem',
               'effective_cache_size', 'random_page_cost', 'statement_timeout',
               'idle_in_transaction_session_timeout', 'autovacuum')
ORDER BY name;
 
-- Settings changed from defaults
SELECT name, setting, source FROM pg_settings WHERE source NOT IN ('default', 'override') ORDER BY name;

Replication Quick Checks

-- On the primary: connected standbys and lag
SELECT application_name, client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes,
       replay_lag
FROM pg_stat_replication;
 
-- On a standby
SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp() AS replay_delay;

• More: Database Replication, Synchronous vs Asynchronous Replication

psql Meta-Commands Cheat Sheet

Command	Shows
\l	Databases
\dt / \dt+	Tables (with sizes)
\d table	Columns, indexes, constraints, triggers
\di+	Indexes with sizes
\dv / \dm / \df	Views / matviews / functions
\du	Roles
\x auto	Expanded output for wide rows
\timing on	Show query time
\watch 2	Re-run the last query every 2 seconds
\e	Edit the query in $EDITOR

Interview Questions

• “A query is stuck — what do you do?”: Find it in pg_stat_activity, check pg_blocking_pids, pg_cancel_backend then pg_terminate_backend
• “How do you find the slowest queries?”: pg_stat_statements ordered by total_exec_time and mean_exec_time
• “Table is huge but has few rows?”: Bloat — check n_dead_tup, autovacuum history, long transactions holding back cleanup
• “cancel vs terminate?”: Cancel stops the statement; terminate kills the connection
• “How big is this table with its indexes?”: pg_total_relation_size

Summary

• Activity: pg_stat_activity for running/idle sessions; watch idle in transaction
• Control: pg_cancel_backend → pg_terminate_backend; prevent with timeouts
• Locks: pg_blocking_pids() + pg_locks to find the blocker
• Workload: pg_stat_statements — optimise by total time
• Housekeeping: Sizes, unused indexes, dead tuples, XID age, cache hit ratio

Link to original

JSONB and Arrays

JSONB and Arrays

Overview

• jsonb: Binary, decomposed JSON — indexable, deduplicated keys, no key order preserved. Prefer it over json (text, re-parsed on every access)
• Arrays: Any type can be an array (text[], int[]); 1-based indexing
• When to Use: Sparse/variable attributes, external payloads, tags. Not for data you join, constrain with FKs, or update field-by-field at high frequency
• Sample Data: products.attributes jsonb and products.tags text[] in E-commerce Practice Queries

JSONB Operators

Access and Extraction

Operator	Returns	Example	Result
→	jsonb	attributes -> 'specs'	{"cpu": "M4"}
→>	text	attributes ->> 'brand'	Zen
#>	jsonb at path	attributes #> '{specs,cpu}'	"M4"
#>>	text at path	attributes #>> '{specs,cpu}'	M4
[ ] (PG 14+)	jsonb subscript	attributes['specs']['cpu']	"M4"

SELECT sku,
       attributes -> 'brand'               AS brand_json,     -- "Zen" (jsonb, quoted)
       attributes ->> 'brand'              AS brand_text,     -- Zen  (text)
       (attributes ->> 'ram_gb')::int      AS ram_gb,         -- cast text → int for math
       attributes #>> '{specs,cpu}'        AS cpu,
       attributes['color']                 AS color_subscript
FROM products
WHERE category_id = 3;

• → vs →>: Compare ->> results to text, -> results to jsonb. attributes -> 'brand' = 'Zen' fails (needs '"Zen"'::jsonb)
• Missing Key: Returns NULL, no error

Containment and Existence

Operator	Meaning	GIN-Indexable
@>	Left contains right	Yes (both opclasses)
<@	Left contained in right	No (neither opclass)
?	Top-level key/array element exists	Yes (jsonb_ops only)
?|	Any of these keys exist	Yes (jsonb_ops only)
?&	All of these keys exist	Yes (jsonb_ops only)
@?	JSONPath returns any item	Yes
@@	JSONPath predicate is true	Yes

-- Containment: products whose attributes include brand = Clicky AND wireless = true
SELECT sku, name FROM products
WHERE attributes @> '{"brand": "Clicky", "wireless": true}';
 
-- Nested containment
SELECT sku FROM products WHERE attributes @> '{"specs": {"cpu": "M4"}}';
 
-- Key existence
SELECT sku FROM products WHERE attributes ? 'author';                 -- books
SELECT sku FROM products WHERE attributes ?| ARRAY['author', 'switch'];
SELECT sku FROM products WHERE attributes ?& ARRAY['brand', 'color', 'storage_gb'];

• psql / JDBC Gotcha: ? clashes with JDBC placeholders — use jsonb_exists(col, 'key') or ?? escaping in drivers

SQL/JSON Path (PG 12+)

-- Products with more than 16 GB RAM, via a JSONPath predicate (@@) and query (@?)
SELECT sku FROM products WHERE attributes @@ '$.ram_gb > 16';
SELECT sku FROM products WHERE attributes @? '$ ? (@.storage_gb >= 256)';
 
-- Extract with jsonb_path_query / _first
SELECT sku, jsonb_path_query_first(attributes, '$.specs.cpu') AS cpu
FROM products WHERE attributes ? 'specs';
 
-- Variables in paths
SELECT sku FROM products
WHERE jsonb_path_exists(attributes, '$.pages ? (@ > $min)', '{"min": 300}');

PG 17 SQL/JSON Functions

SELECT JSON_VALUE(attributes, '$.brand')                    AS brand,
       JSON_VALUE(attributes, '$.ram_gb' RETURNING int)     AS ram,
       JSON_EXISTS(attributes, '$.specs.cpu')               AS has_cpu,
       JSON_QUERY(attributes, '$.specs')                    AS specs
FROM products
WHERE category_id = 3;
 
-- JSON_TABLE: turn JSON into rows/columns
SELECT jt.*
FROM JSON_TABLE(
    '[{"sku":"A","qty":2},{"sku":"B","qty":5}]'::jsonb, '$[*]'
    COLUMNS (sku text PATH '$.sku', qty int PATH '$.qty')
) AS jt;

Modifying JSONB

Operation	Syntax
Merge / overwrite keys	col || '{"k": "v"}' (shallow)
Remove key	col - 'k', col - ARRAY['a','b']
Remove nested path	col #- '{specs,cpu}'
Set nested value	jsonb_set(col, '{specs,cpu}', '"M5"', create_missing => true)
Insert into array	jsonb_insert(col, '{list,0}', '"x"')
Subscript assignment (PG 14+)	UPDATE t SET col['k'] = '"v"'

BEGIN;
UPDATE products
SET attributes = attributes || '{"warranty_years": 2}'          -- add/overwrite key
WHERE category_id = 3;
 
UPDATE products
SET attributes = jsonb_set(attributes, '{specs,gpu}', '"10-core"', true)
WHERE sku = 'LAP-002';
 
UPDATE products SET attributes['color'] = '"space grey"' WHERE sku = 'LAP-002';
 
UPDATE products SET attributes = attributes - 'warranty_years' WHERE sku = 'LAP-001';
 
SELECT sku, attributes FROM products WHERE category_id = 3 ORDER BY sku;
ROLLBACK;

• Whole-Value Rewrite: Any change rewrites the entire jsonb value (and row version) — big documents updated often = bloat and WAL churn

Building and Unpacking JSON

-- Rows → JSON
SELECT jsonb_build_object('id', id, 'name', full_name, 'country', country) AS user_json
FROM users WHERE id <= 2;
 
SELECT jsonb_agg(jsonb_build_object('sku', sku, 'price', price) ORDER BY price DESC) AS catalog
FROM products WHERE category_id = 5;
 
SELECT to_jsonb(u) - 'created_at' AS whole_row FROM users u WHERE id = 1;
 
-- JSON → rows
SELECT p.sku, kv.key, kv.value
FROM products p, jsonb_each(p.attributes) AS kv
WHERE p.sku = 'ACC-002';
 
SELECT * FROM jsonb_to_record('{"sku":"X-1","price":9.5,"stock":3}') AS r(sku text, price numeric, stock int);
 
SELECT jsonb_array_elements_text('["a","b","c"]') AS item;
 
-- Which keys are used across all products, and how often
SELECT key, count(*) FROM products, jsonb_object_keys(attributes) AS key
GROUP BY key ORDER BY count(*) DESC, key;

Indexing JSONB

Options

Index	Serves	Notes
GIN (jsonb_ops)	@>, ?, `?	, ?&, @?, @@`
GIN (jsonb_path_ops)	@>, @?, @@	~2–3× smaller, faster containment; no key-exists
B-tree expression	=, range, ORDER BY on one extracted field	((attributes ->> 'brand'))
Partial	Combine with a predicate	WHERE attributes ? 'author'

CREATE INDEX products_attr_gin       ON products USING gin (attributes);                  -- jsonb_ops
CREATE INDEX products_attr_path_gin  ON products USING gin (attributes jsonb_path_ops);
CREATE INDEX products_brand_idx      ON products ((attributes ->> 'brand'));              -- note double parens
CREATE INDEX products_ram_idx        ON products (((attributes ->> 'ram_gb')::int))
    WHERE attributes ? 'ram_gb';
 
-- The query must match the indexed expression exactly
SET enable_seqscan = off;    -- tiny table: force the planner to show index usage
EXPLAIN (COSTS OFF) SELECT sku FROM products WHERE attributes @> '{"brand": "Zen"}';
EXPLAIN (COSTS OFF) SELECT sku FROM products WHERE attributes ->> 'brand' = 'Zen';
EXPLAIN (COSTS OFF) SELECT sku FROM products WHERE attributes ? 'author';
RESET enable_seqscan;

• Containment vs Extraction: attributes @> '{"brand":"Zen"}' uses GIN; attributes ->> 'brand' = 'Zen' needs the B-tree expression index — pick one query style and index for it
• Statistics: The planner has poor stats inside jsonb; a frequently filtered field may deserve a real column (or a generated column)

-- Promote a hot JSON field to a real, indexable, typed column
ALTER TABLE products
    ADD COLUMN brand text GENERATED ALWAYS AS (attributes ->> 'brand') STORED;
CREATE INDEX products_brand_col_idx ON products (brand);
SELECT brand, count(*) FROM products GROUP BY brand ORDER BY brand;

Arrays

Operators and Functions

Operator / Function	Meaning	Example
arr[1]	Element (1-based)	tags[1]
arr[1:2]	Slice	tags[1:2]
= ANY(arr)	Element in array	'sql' = ANY(tags)
@>	Contains all	tags @> '{laptop,pro}'
<@	Is contained by	tags <@ '{book,sql,systems}'
&&	Overlaps (any in common)	tags && '{phone,ios}'
||	Concatenate / append	tags || 'sale'
array_length(arr, 1) / cardinality(arr)	Length
unnest(arr)	Array → rows
array_agg(x)	Rows → array
array_remove / array_append / array_position	Edit / search

SELECT sku, tags, tags[1] AS first_tag, cardinality(tags) AS n_tags
FROM products
WHERE tags && ARRAY['phone', 'book']           -- any overlap
ORDER BY sku;
 
SELECT sku FROM products WHERE tags @> ARRAY['laptop', 'pro'];
SELECT sku FROM products WHERE 'kitchen' = ANY (tags);
 
-- Tag cloud
SELECT tag, count(*) FROM products, unnest(tags) AS tag
GROUP BY tag HAVING count(*) > 1 ORDER BY count(*) DESC, tag;
 
BEGIN;
UPDATE products SET tags = array_append(tags, 'sale') WHERE category_id = 8;
UPDATE products SET tags = array_remove(tags, 'sql')  WHERE sku = 'BOK-001';
SELECT sku, tags FROM products WHERE category_id = 8 ORDER BY sku;
ROLLBACK;
 
-- GIN index for @>, <@, &&
CREATE INDEX products_tags_gin ON products USING gin (tags);

• = ANY(tags) and GIN: 'x' = ANY(tags) can’t use a GIN index — write tags @> ARRAY['x'] instead
• unnest WITH ORDINALITY: Get the position too — unnest(tags) WITH ORDINALITY AS t(tag, pos)

JSONB vs Columns vs Arrays

Need	Use
Fixed, queried, constrained fields	Real columns
Variable per-row attributes, read mostly	jsonb (+ GIN)
Small list of scalar labels	Array (+ GIN)
Many-to-many with integrity	Join table with FKs
Raw webhook payload for audit	jsonb (maybe no index)

Interview Questions

• “json vs jsonb?”: jsonb is binary, indexable, faster to query; json keeps exact text (whitespace, key order, duplicates)
• ”→ vs →>?”: -> returns jsonb, ->> returns text
• “Index a JSONB column?”: GIN for containment/key-exists; expression B-tree for one field equality/range
• “When not to use JSONB?”: Fields you join on, need FKs/constraints for, or update constantly
• “Find rows whose tags include X?”: tags @> ARRAY['X'] with a GIN index

Summary

• Access: -> jsonb, ->> text, #>/#>> paths, subscripts in PG 14+
• Query: @> containment, ? key existence, JSONPath @?/@@, SQL/JSON functions in PG 17
• Modify: ||, -, #-, jsonb_set, subscript assignment — each rewrites the whole value
• Index: GIN jsonb_ops vs jsonb_path_ops; expression or generated-column B-trees for hot fields
• Arrays: @>, &&, ANY, unnest, array_agg; GIN for containment/overlap

Link to original

Views Functions and Triggers

Views Functions and Triggers

Overview

• View: A stored query that behaves like a table; no data stored, re-run on every read
• Materialized View: A stored query result — fast reads, stale until REFRESH
• Function: Reusable logic in SQL or PL/pgSQL; Procedure (PG 11+) can COMMIT inside
• Trigger: Function that runs automatically on INSERT/UPDATE/DELETE/TRUNCATE
• Sample Data: E-commerce Practice Queries

Views

Creating and Using

CREATE OR REPLACE VIEW order_summary AS
SELECT o.id          AS order_id,
       o.user_id,
       u.full_name,
       o.status,
       o.created_at,
       sum(oi.quantity * oi.unit_price) AS total,
       count(*)                         AS line_items
FROM orders o
JOIN users u        ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, u.full_name;
 
SELECT full_name, count(*) AS orders, sum(total) AS spend
FROM order_summary
WHERE status <> 'cancelled'
GROUP BY full_name
ORDER BY spend DESC;

Properties

• Always Fresh: The query runs each time; the planner inlines the view and pushes filters into it
• Abstraction / Security: Expose a subset of columns/rows; grant on the view, not the base table
• CREATE OR REPLACE: Can add columns at the end, but can’t drop/rename/retype — use DROP VIEW + CREATE
• Dependencies: Base tables can’t drop a column a view uses (DROP … CASCADE drops the view too)

Updatable Views and CHECK OPTION

• Auto-updatable: Simple views (one table, no aggregates/DISTINCT/GROUP BY/LIMIT) accept INSERT/UPDATE/DELETE
• WITH CHECK OPTION: Rejects writes that would produce rows invisible through the view
• security_invoker (PG 15+): Run with the caller’s permissions/RLS instead of the view owner’s

CREATE OR REPLACE VIEW active_products AS
SELECT id, sku, name, price, stock, is_active
FROM products
WHERE is_active
WITH CHECK OPTION;
 
BEGIN;
UPDATE active_products SET price = price + 1 WHERE sku = 'ACC-001';    -- writes through to products
-- UPDATE active_products SET is_active = false WHERE sku = 'ACC-001';
--   ERROR: new row violates check option for view "active_products"
ROLLBACK;

Materialized Views

Creating and Refreshing

CREATE MATERIALIZED VIEW IF NOT EXISTS monthly_revenue AS
SELECT date_trunc('month', o.created_at)::date AS month,
       count(DISTINCT o.id)                    AS orders,
       sum(oi.quantity * oi.unit_price)        AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped', 'delivered')
GROUP BY 1
WITH DATA;
 
-- REFRESH ... CONCURRENTLY requires a UNIQUE index on the matview
CREATE UNIQUE INDEX IF NOT EXISTS monthly_revenue_month_idx ON monthly_revenue (month);
 
SELECT * FROM monthly_revenue ORDER BY month;
 
REFRESH MATERIALIZED VIEW monthly_revenue;                -- blocks readers while rebuilding
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;   -- readers keep seeing old data; diff-applies changes

REFRESH vs REFRESH CONCURRENTLY

Aspect	REFRESH	REFRESH CONCURRENTLY
Lock	ACCESS EXCLUSIVE — blocks SELECT	EXCLUSIVE — SELECT still works
Requirement	None	A UNIQUE index on plain columns; matview already populated
Speed	Faster (truncate + insert)	Slower (computes diff, updates changed rows)
Use When	Off-hours / nobody reading	Dashboards read it continuously

View vs Materialized View vs Table

Aspect	View	Materialized View	Summary Table + Triggers
Freshness	Always current	As of last refresh	Real-time
Read Speed	Same as underlying query	Fast (indexed)	Fast
Write Cost	None	Refresh cost (whole query)	Per-write trigger cost
Indexes	No (uses base table’s)	Yes	Yes
Scheduling	n/a	pg_cron / app job	n/a

Functions

SQL Functions

• LANGUAGE sql: Body is plain SQL; simple ones can be inlined into the calling query
• Volatility: IMMUTABLE (same input → same output forever; required for expression indexes), STABLE (consistent within a statement, may read tables), VOLATILE (default; random(), writes)
• PARALLEL SAFE: Mark it so parallel plans can use it
• SQL-standard Body (PG 14+): BEGIN ATOMIC … END — parsed at creation, dependencies tracked

CREATE OR REPLACE FUNCTION order_total(p_order_id bigint)
RETURNS numeric
LANGUAGE sql STABLE PARALLEL SAFE
AS $$
    SELECT COALESCE(sum(quantity * unit_price), 0)
    FROM order_items
    WHERE order_id = p_order_id
$$;
 
SELECT id, status, order_total(id) FROM orders WHERE user_id = 1 ORDER BY id;
 
-- Set-returning function: behaves like a parameterised view
CREATE OR REPLACE FUNCTION user_orders(p_user_id bigint)
RETURNS TABLE (order_id bigint, status text, total numeric)
LANGUAGE sql STABLE
AS $$
    SELECT o.id, o.status, order_total(o.id)
    FROM orders o
    WHERE o.user_id = p_user_id
    ORDER BY o.created_at
$$;
 
SELECT * FROM user_orders(2);

PL/pgSQL Functions

• Use When: You need variables, IF, loops, exceptions, dynamic SQL
• RAISE: NOTICE for debugging, EXCEPTION to abort with an error
• Performance: Each statement inside is planned (and cached); avoid row-by-row loops when one set-based query works

CREATE OR REPLACE FUNCTION apply_discount(p_sku text, p_percent numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
    v_new_price numeric;
BEGIN
    IF p_percent <= 0 OR p_percent >= 90 THEN
        RAISE EXCEPTION 'discount % out of range', p_percent USING ERRCODE = 'check_violation';
    END IF;
 
    UPDATE products
    SET price = round(price * (1 - p_percent / 100), 2)
    WHERE sku = p_sku
    RETURNING price INTO v_new_price;
 
    IF NOT FOUND THEN
        RAISE EXCEPTION 'unknown sku %', p_sku;
    END IF;
 
    RAISE NOTICE 'new price for % is %', p_sku, v_new_price;
    RETURN v_new_price;
END;
$$;
 
BEGIN;
SELECT apply_discount('LAP-001', 10);          -- 899.10
-- SELECT apply_discount('LAP-001', 95);       -- ERROR: discount 95 out of range
ROLLBACK;

Procedures

-- Procedures are CALLed and may COMMIT between batches (not allowed in functions)
CREATE OR REPLACE PROCEDURE archive_failed_payments(p_batch int DEFAULT 1000)
LANGUAGE plpgsql
AS $$
DECLARE
    v_rows int;
BEGIN
    LOOP
        DELETE FROM payments
        WHERE id IN (SELECT id FROM payments WHERE status = 'failed' LIMIT p_batch);
        GET DIAGNOSTICS v_rows = ROW_COUNT;
        EXIT WHEN v_rows = 0;
        COMMIT;                                    -- release locks / WAL per batch
    END LOOP;
END;
$$;
-- CALL archive_failed_payments(500);   -- must run OUTSIDE an explicit BEGIN block:
--   inside one it fails with ERROR: invalid transaction termination

Triggers

Concepts

Option	Choices	Notes
Timing	BEFORE, AFTER, INSTEAD OF (views)	BEFORE can modify NEW or skip the row (return NULL)
Event	INSERT, UPDATE [OF cols], DELETE, TRUNCATE
Level	FOR EACH ROW, FOR EACH STATEMENT	Statement triggers can use transition tables (REFERENCING NEW TABLE AS …)
Condition	WHEN (OLD.x IS DISTINCT FROM NEW.x)	Skip calling the function when nothing relevant changed
Variables	NEW, OLD, TG_OP, TG_TABLE_NAME	NEW is NULL for DELETE, OLD for INSERT

Example 1: Maintain updated_at

ALTER TABLE products ADD COLUMN IF NOT EXISTS updated_at timestamptz NOT NULL DEFAULT now();
 
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;                                    -- BEFORE trigger: return the (modified) row
END;
$$;
 
CREATE OR REPLACE TRIGGER products_set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)                -- skip no-op updates
EXECUTE FUNCTION set_updated_at();

Example 2: Audit Log

CREATE TABLE IF NOT EXISTS audit_log (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name text NOT NULL,
    op         text NOT NULL,
    row_id     bigint,
    old_row    jsonb,
    new_row    jsonb,
    changed_by text NOT NULL DEFAULT current_user,
    changed_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, op, row_id, old_row, new_row)
    VALUES (TG_TABLE_NAME,
            TG_OP,
            COALESCE(NEW.id, OLD.id),
            CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
            CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
    RETURN NULL;                                   -- AFTER trigger: return value ignored
END;
$$;
 
CREATE OR REPLACE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_trigger();
 
BEGIN;
UPDATE orders SET status = 'cancelled' WHERE id = 15;
UPDATE products SET stock = stock - 1 WHERE sku = 'BOK-001';
 
SELECT op, row_id, old_row ->> 'status' AS old_status, new_row ->> 'status' AS new_status
FROM audit_log;
SELECT sku, updated_at > created_at AS touched FROM products WHERE sku = 'BOK-001';
ROLLBACK;

Example 3: Enforce a Business Rule

-- Prevent changing items of an order that has already shipped
CREATE OR REPLACE FUNCTION forbid_shipped_item_changes()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM orders
               WHERE id = COALESCE(NEW.order_id, OLD.order_id)
                 AND status IN ('shipped', 'delivered')) THEN
        RAISE EXCEPTION 'order % is already %', COALESCE(NEW.order_id, OLD.order_id), 'shipped/delivered';
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$;
 
CREATE OR REPLACE TRIGGER order_items_guard
BEFORE INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION forbid_shipped_item_changes();
 
-- DELETE FROM order_items WHERE order_id = 1;
--   ERROR: order 1 is already shipped/delivered
BEGIN;
DELETE FROM order_items WHERE order_id = 10 RETURNING *;     -- pending order: allowed
ROLLBACK;

Trigger Pitfalls

• Hidden Logic: Behaviour invisible to app developers — document triggers and keep them small
• Performance: Row-level triggers fire per row; bulk loads slow down dramatically (use statement-level + transition tables, or disable during loads)
• Recursion: A trigger updating its own table can re-fire itself — guard with WHEN or pg_trigger_depth()
• Ordering: Multiple triggers on the same event fire in alphabetical name order
• Not Fired By: TRUNCATE doesn’t fire row-level DELETE triggers; COPY does fire row triggers

Inspecting Objects

-- psql: \dv  \dm  \df  \dS+ products (shows triggers)
SELECT viewname FROM pg_views WHERE schemaname = 'public';
SELECT matviewname, ispopulated FROM pg_matviews;
SELECT tgname, tgrelid::regclass AS on_table FROM pg_trigger WHERE NOT tgisinternal ORDER BY tgname;
SELECT proname, prokind FROM pg_proc WHERE pronamespace = 'public'::regnamespace ORDER BY proname;

Interview Questions

• “View vs materialized view?”: View re-runs the query; matview stores results and must be refreshed
• “Refresh a matview without blocking readers?”: REFRESH MATERIALIZED VIEW CONCURRENTLY + a unique index
• “Function vs procedure?”: Functions return values and run inside the caller’s transaction; procedures are CALLed and can COMMIT
• “IMMUTABLE vs STABLE vs VOLATILE?”: Determines caching and index eligibility — only IMMUTABLE functions can be used in index expressions
• “BEFORE vs AFTER trigger?”: BEFORE can change/skip the row; AFTER sees the final row and is used for side effects like audit logs
• “Why are triggers controversial?”: Hidden side effects, harder debugging, per-row overhead

Summary

• Views: Saved queries, always fresh, can be updatable with WITH CHECK OPTION
• Materialized Views: Cached results, indexable; REFRESH … CONCURRENTLY needs a unique index
• Functions: SQL for simple/inlinable logic, PL/pgSQL for control flow; set volatility correctly
• Procedures: CALL, can commit in loops — batch maintenance jobs
• Triggers: updated_at, audit logs, invariants — keep them small, documented and set-based where possible

Link to original

E-commerce Practice Queries

E-commerce Practice Queries

Overview

• Purpose: One realistic schema + seed data used by every note in PostgreSQL Queries, plus 20 graded practice queries (easy → hard) with solutions
• Domain: Small e-commerce shop — users, categories, products, orders, order_items, payments, plus an employees table for org-chart / salary questions
• How to Use: Load the schema and seed once, try each question yourself, then expand the solution
• Verified: Every block in this note was run against PostgreSQL 17

Spin Up a Scratch Database

docker run --rm -d --name pg-practice -e POSTGRES_PASSWORD=x -p 55432:5432 postgres:17
docker exec -it pg-practice psql -U postgres
-- paste the schema + seed blocks below

Entity Relationships

users 1──* orders 1──* order_items *──1 products *──1 categories (self-ref tree)
  │            └──* payments
  └── referred_by → users (self-reference)
 
employees.manager_id → employees (self-reference, org chart)

Sample Schema

DROP TABLE IF EXISTS payments, order_items, orders, products, categories, users, employees CASCADE;
 
CREATE TABLE users (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text NOT NULL UNIQUE,
    full_name   text NOT NULL,
    country     char(2),                                   -- NULL = unknown
    referred_by bigint REFERENCES users(id) ON DELETE SET NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE categories (
    id        int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      text NOT NULL UNIQUE,
    parent_id int REFERENCES categories(id) ON DELETE RESTRICT
);
 
CREATE TABLE products (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku         text NOT NULL UNIQUE,
    name        text NOT NULL,
    category_id int NOT NULL REFERENCES categories(id),
    price       numeric(10,2) NOT NULL CHECK (price >= 0),
    stock       int NOT NULL DEFAULT 0 CHECK (stock >= 0),
    attributes  jsonb NOT NULL DEFAULT '{}',
    tags        text[] NOT NULL DEFAULT '{}',
    is_active   boolean NOT NULL DEFAULT true,
    created_at  timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE orders (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    bigint NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    status     text NOT NULL DEFAULT 'pending'
               CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    created_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE order_items (
    order_id   bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id bigint NOT NULL REFERENCES products(id),
    quantity   int NOT NULL CHECK (quantity > 0),
    unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0),   -- price at time of purchase
    PRIMARY KEY (order_id, product_id)
);
 
CREATE TABLE payments (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    amount   numeric(10,2) NOT NULL CHECK (amount > 0),
    method   text NOT NULL CHECK (method IN ('card', 'upi', 'wallet')),
    status   text NOT NULL CHECK (status IN ('succeeded', 'failed', 'refunded')),
    paid_at  timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE employees (
    id         int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL,
    manager_id int REFERENCES employees(id),
    department text NOT NULL,
    salary     numeric(10,2) NOT NULL,
    hired_on   date NOT NULL
);
 
-- Postgres does NOT auto-index foreign key columns — add the ones you join/filter on
CREATE INDEX ON orders (user_id, created_at);
CREATE INDEX ON order_items (product_id);
CREATE INDEX ON payments (order_id);
CREATE INDEX ON products (category_id);

Seed Data

• Deterministic IDs: Rows are inserted in order into fresh tables, so identity IDs are 1, 2, 3, …
• Time Zone: Timestamps are written in UTC; run SET timezone = 'UTC'; so month/day results match

SET timezone = 'UTC';
 
INSERT INTO categories (name, parent_id) VALUES
  ('Electronics', NULL),   -- 1
  ('Computers',   1),      -- 2
  ('Laptops',     2),      -- 3
  ('Accessories', 2),      -- 4
  ('Phones',      1),      -- 5
  ('Home',        NULL),   -- 6
  ('Kitchen',     6),      -- 7
  ('Books',       NULL);   -- 8
 
INSERT INTO users (email, full_name, country, referred_by, created_at) VALUES
  ('asha@example.com',  'Asha Rao',    'IN', NULL, '2025-01-05 12:00+00'),  -- 1
  ('ben@example.com',   'Ben Carter',  'US', 1,    '2025-01-20 12:00+00'),  -- 2
  ('chen@example.com',  'Chen Wei',    'SG', 1,    '2025-02-11 12:00+00'),  -- 3
  ('diya@example.com',  'Diya Shah',   'IN', 2,    '2025-03-02 12:00+00'),  -- 4
  ('eva@example.com',   'Eva Muller',  'DE', NULL, '2025-03-15 12:00+00'),  -- 5
  ('farid@example.com', 'Farid Khan',  'IN', 4,    '2025-04-01 12:00+00'),  -- 6
  ('gina@example.com',  'Gina Lopez',  'US', NULL, '2025-05-09 12:00+00'),  -- 7 (never orders)
  ('hiro@example.com',  'Hiro Tanaka', NULL, NULL, '2025-06-21 12:00+00');  -- 8 (never orders, no country)
 
INSERT INTO products (sku, name, category_id, price, stock, attributes, tags, is_active) VALUES
  ('LAP-001', 'Air Laptop 13',  3,  999.00,  15, '{"brand":"Zen","ram_gb":16,"color":"silver"}',                    '{laptop,ultrabook}', true),
  ('LAP-002', 'Pro Laptop 16',  3, 2199.00,   5, '{"brand":"Zen","ram_gb":32,"color":"grey","specs":{"cpu":"M4"}}', '{laptop,pro}',       true),
  ('ACC-001', 'Wireless Mouse', 4,   25.00, 200, '{"brand":"Clicky","wireless":true}',                              '{mouse,wireless}',   true),
  ('ACC-002', 'Mechanical Keyboard', 4, 89.00, 60, '{"brand":"Clicky","wireless":false,"switch":"brown"}',          '{keyboard}',         true),
  ('ACC-003', 'USB-C Hub',      4,   45.00,   0, '{"brand":"Portly"}',                                              '{usb,hub}',          true),
  ('PHN-001', 'Pixel Phone',    5,  699.00,  30, '{"brand":"Gogo","color":"black","storage_gb":128}',               '{phone,android}',    true),
  ('PHN-002', 'Fruit Phone',    5,  999.00,  25, '{"brand":"Fruit","color":"white","storage_gb":256}',              '{phone,ios}',        true),
  ('KIT-001', 'Espresso Maker', 7,  349.00,  10, '{"brand":"Brew","color":"red"}',                                  '{coffee,kitchen}',   true),
  ('KIT-002', 'Chef Knife',     7,   59.00,  40, '{"brand":"Sharp"}',                                               '{knife,kitchen}',    true),
  ('BOK-001', 'SQL Performance Explained', 8, 35.00, 100, '{"author":"Winand","pages":200}',                        '{book,sql}',         true),
  ('BOK-002', 'Designing Data-Intensive Apps', 8, 45.00, 80, '{"author":"Kleppmann","pages":600}',                  '{book,systems}',     true),
  ('ACC-004', 'Laptop Stand',   4,   39.00,  50, '{"brand":"Portly","color":"silver"}',                             '{stand}',            false);
 
INSERT INTO orders (user_id, status, created_at) VALUES
  (1, 'delivered', '2025-01-10 12:00+00'),  -- 1
  (1, 'delivered', '2025-02-14 12:00+00'),  -- 2
  (2, 'delivered', '2025-02-20 12:00+00'),  -- 3
  (3, 'shipped',   '2025-03-05 12:00+00'),  -- 4
  (2, 'cancelled', '2025-03-18 12:00+00'),  -- 5
  (4, 'delivered', '2025-03-25 12:00+00'),  -- 6
  (1, 'paid',      '2025-04-02 12:00+00'),  -- 7
  (5, 'delivered', '2025-04-15 12:00+00'),  -- 8
  (3, 'delivered', '2025-05-01 12:00+00'),  -- 9
  (6, 'pending',   '2025-05-20 12:00+00'),  -- 10
  (4, 'shipped',   '2025-06-03 12:00+00'),  -- 11
  (5, 'paid',      '2025-06-10 12:00+00'),  -- 12
  (2, 'delivered', '2025-06-28 12:00+00'),  -- 13
  (6, 'delivered', '2025-07-04 12:00+00'),  -- 14
  (1, 'pending',   '2025-07-15 12:00+00');  -- 15
 
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1, 999.00), (1, 3, 1, 25.00),
  (2, 10, 2, 35.00), (2, 11, 1, 45.00),
  (3, 6, 1, 699.00), (3, 3, 2, 25.00),
  (4, 2, 1, 2199.00),
  (5, 7, 1, 999.00),
  (6, 8, 1, 349.00), (6, 9, 2, 59.00),
  (7, 4, 1, 89.00),  (7, 3, 1, 22.50),           -- discounted mouse
  (8, 1, 1, 999.00), (8, 4, 1, 89.00), (8, 5, 1, 45.00),
  (9, 10, 1, 35.00),
  (10, 6, 1, 699.00),
  (11, 11, 2, 45.00), (11, 10, 1, 35.00),
  (12, 7, 1, 999.00), (12, 3, 1, 25.00),
  (13, 8, 1, 349.00),
  (14, 9, 1, 59.00), (14, 10, 1, 35.00),
  (15, 2, 1, 2199.00);
 
INSERT INTO payments (order_id, amount, method, status, paid_at) VALUES
  (1,  1024.00, 'card',   'succeeded', '2025-01-10 12:05+00'),
  (2,   115.00, 'upi',    'succeeded', '2025-02-14 12:05+00'),
  (3,   749.00, 'card',   'failed',    '2025-02-20 12:01+00'),
  (3,   749.00, 'card',   'succeeded', '2025-02-20 12:04+00'),
  (4,  2199.00, 'card',   'succeeded', '2025-03-05 12:05+00'),
  (5,   999.00, 'wallet', 'refunded',  '2025-03-18 12:05+00'),
  (6,   467.00, 'upi',    'succeeded', '2025-03-25 12:05+00'),
  (7,   111.50, 'card',   'succeeded', '2025-04-02 12:05+00'),
  (8,  1000.00, 'card',   'succeeded', '2025-04-15 12:05+00'),
  (8,   133.00, 'wallet', 'succeeded', '2025-04-15 12:06+00'),   -- split payment
  (9,    35.00, 'upi',    'succeeded', '2025-05-01 12:05+00'),
  (11,  125.00, 'card',   'succeeded', '2025-06-03 12:05+00'),
  (12, 1024.00, 'wallet', 'succeeded', '2025-06-10 12:05+00'),
  (13,  349.00, 'card',   'succeeded', '2025-06-28 12:05+00'),
  (14,   94.00, 'upi',    'succeeded', '2025-07-04 12:05+00');
 
INSERT INTO employees (name, manager_id, department, salary, hired_on) VALUES
  ('Maya',  NULL, 'Exec',        300000, '2019-01-01'),  -- 1 CEO
  ('Raj',   1,    'Engineering', 200000, '2019-06-01'),  -- 2
  ('Lena',  1,    'Sales',       180000, '2020-02-01'),  -- 3
  ('Omar',  2,    'Engineering', 150000, '2020-08-15'),  -- 4
  ('Priya', 2,    'Engineering', 150000, '2021-03-01'),  -- 5
  ('Tom',   4,    'Engineering', 120000, '2022-05-10'),  -- 6
  ('Zoe',   4,    'Engineering', 110000, '2023-01-09'),  -- 7
  ('Sam',   3,    'Sales',        90000, '2021-11-20'),  -- 8
  ('Ivy',   3,    'Sales',        95000, '2022-07-01'),  -- 9
  ('Leo',   8,    'Sales',        70000, '2024-02-12');  -- 10
 
ANALYZE;

Data Facts Worth Knowing

• Revenue Statuses: paid, shipped, delivered count as revenue; pending and cancelled do not
• Edge Cases Built In: users with no orders (Gina, Hiro), a NULL country (Hiro), an inactive never-ordered product (Laptop Stand), an out-of-stock product (USB-C Hub), a split payment (order 8), a failed retry (order 3), a discounted line (order 7), a 3-level referral chain (Farid → Diya → Ben → Asha)

Practice Queries

Easy

Q1. Active products under 100, cheapest first

SELECT sku, name, price
FROM products
WHERE is_active AND price < 100
ORDER BY price, name;

• Expected: 6 rows, Wireless Mouse (25.00) first, Mechanical Keyboard (89.00) last; Laptop Stand (39.00) is excluded because it is inactive

Q2. Number of users per country, showing unknown countries as ??

SELECT COALESCE(country, '??') AS country, count(*) AS users
FROM users
GROUP BY 1
ORDER BY users DESC, country;

• Expected: IN 3, US 2, then ??, DE, SG with 1 each
• Point: GROUP BY puts all NULLs in one group; COALESCE just relabels it

Q3. Number of orders in each status

SELECT status, count(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC, status;

• Expected: delivered 8, paid 2, pending 2, shipped 2, cancelled 1

Q4. Total value of every order

SELECT o.id, o.status, sum(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id            -- o.status allowed: it depends functionally on the PK
ORDER BY o.id;

• Expected: 15 rows; order 1 = 1024.00, order 8 = 1133.00, order 7 = 111.50

Q5. Stock value per category, only where it is above 5,000

SELECT c.name AS category, sum(p.price * p.stock) AS stock_value
FROM categories c
JOIN products p ON p.category_id = c.id
WHERE p.is_active
GROUP BY c.name
HAVING sum(p.price * p.stock) > 5000
ORDER BY stock_value DESC;

• Expected: Phones 45945.00, Laptops 25980.00, Accessories 10340.00, Books 7100.00, Kitchen 5850.00
• Point: WHERE filters rows before grouping; HAVING filters groups after

Medium

Q6. Users who have never placed an order

SELECT u.id, u.full_name
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)
ORDER BY u.id;

• Expected: Gina Lopez, Hiro Tanaka
• Point: Anti-join. Prefer NOT EXISTS over NOT IN (see Joins for the NULL trap)

Q7. Products that have never been ordered

SELECT p.sku, p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;

• Expected: ACC-004 Laptop Stand
• Point: LEFT JOIN … IS NULL is the other anti-join form; Postgres plans both as a Hash Anti Join

Q8. Monthly revenue and order count (revenue statuses only)

SELECT date_trunc('month', o.created_at)::date AS month,
       count(DISTINCT o.id)                    AS orders,
       sum(oi.quantity * oi.unit_price)        AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped', 'delivered')
GROUP BY 1
ORDER BY 1;

• Expected: 7 months; Jan 1024.00, Feb 864.00, Mar 2666.00, Apr 1244.50, May 35.00, Jun 1498.00, Jul 94.00

Q9. Customer summary: orders, lifetime spend, last order (include users with no orders)

WITH order_totals AS (               -- aggregate items per order FIRST to avoid fan-out
    SELECT order_id, sum(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT u.full_name,
       count(o.id)                                   AS orders,
       COALESCE(sum(ot.total) FILTER (WHERE o.status IN ('paid','shipped','delivered')), 0) AS lifetime_spend,
       max(o.created_at)::date                       AS last_order
FROM users u
LEFT JOIN orders o        ON o.user_id = u.id
LEFT JOIN order_totals ot ON ot.order_id = o.id
GROUP BY u.id, u.full_name
ORDER BY lifetime_spend DESC, u.full_name;

• Expected: Chen Wei 2234.00 first, then Eva Muller 2157.00; Asha has 4 orders but only 1250.50 counted (one is pending); Gina and Hiro show 0 orders and 0 spend
• Point: count(o.id) (not count(*)) returns 0 for users with no orders; COALESCE turns the NULL sum into 0

Q10. Top 3 products by revenue, keeping ties

SELECT p.name, sum(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id AND o.status IN ('paid','shipped','delivered')
JOIN products p ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY revenue DESC
FETCH FIRST 3 ROWS WITH TIES;

• Expected: Pro Laptop 16 2199.00, Air Laptop 13 1998.00, Fruit Phone 999.00
• Point: WITH TIES adds any extra rows whose revenue equals the 3rd row’s value; LIMIT 3 would cut ties arbitrarily

Q11. Customers who bought both a laptop and a phone (non-cancelled orders)

SELECT u.full_name
FROM users u
JOIN orders o       ON o.user_id = u.id AND o.status <> 'cancelled'
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
JOIN categories c   ON c.id = p.category_id
WHERE c.name IN ('Laptops', 'Phones')
GROUP BY u.id, u.full_name
HAVING count(DISTINCT c.name) = 2;

• Expected: Eva Muller
• Point: “Has both X and Y” = filter to X or Y, group, HAVING count(DISTINCT …) = 2 (relational division)

Q12. Payment reconciliation: non-cancelled orders whose successful payments do not match the order total

WITH totals AS (
    SELECT order_id, sum(quantity * unit_price) AS due
    FROM order_items GROUP BY order_id
), paid AS (
    SELECT order_id, sum(amount) AS paid
    FROM payments WHERE status = 'succeeded' GROUP BY order_id
)
SELECT o.id, o.status, t.due, COALESCE(p.paid, 0) AS paid
FROM orders o
JOIN totals t    ON t.order_id = o.id
LEFT JOIN paid p ON p.order_id = o.id
WHERE o.status <> 'cancelled'
  AND t.due IS DISTINCT FROM p.paid
ORDER BY o.id;

• Expected: orders 10 and 15 (pending, unpaid). Order 8’s split payment sums correctly, order 3’s failed attempt is ignored
• Point: IS DISTINCT FROM is NULL-safe <> — a plain t.due <> p.paid would silently drop the unpaid orders

Hard

Q13. Each customer’s most recent order (one row per customer)

SELECT DISTINCT ON (o.user_id)
       o.user_id, u.full_name, o.id AS order_id, o.status, o.created_at::date
FROM orders o
JOIN users u ON u.id = o.user_id
ORDER BY o.user_id, o.created_at DESC;

• Expected: 6 rows, e.g. Asha → order 15, Ben → order 13
• Point: Postgres-only DISTINCT ON keeps the first row per key according to ORDER BY; portable version uses ROW_NUMBER() (Q14)

Q14. Top 2 best-selling products (by units) in each category

WITH units AS (
    SELECT p.category_id, p.name, sum(oi.quantity) AS units
    FROM order_items oi
    JOIN orders o   ON o.id = oi.order_id AND o.status <> 'cancelled'
    JOIN products p ON p.id = oi.product_id
    GROUP BY p.category_id, p.id, p.name
), ranked AS (
    SELECT c.name AS category, u.name, u.units,
           ROW_NUMBER() OVER (PARTITION BY u.category_id ORDER BY u.units DESC, u.name) AS rn
    FROM units u
    JOIN categories c ON c.id = u.category_id
)
SELECT category, name, units
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;

• Expected: 10 rows, e.g. Accessories → Wireless Mouse (5), Mechanical Keyboard (2)
• Point: Window functions run after WHERE, so filter on rn in an outer query. Use RANK() instead of ROW_NUMBER() to keep ties (both laptops sold 2 units here)

Q15. Monthly revenue with running total and month-over-month change

WITH monthly AS (
    SELECT date_trunc('month', o.created_at)::date AS month,
           sum(oi.quantity * oi.unit_price)        AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status IN ('paid', 'shipped', 'delivered')
    GROUP BY 1
)
SELECT month,
       revenue,
       sum(revenue) OVER w                                   AS running_total,
       lag(revenue) OVER w                                   AS prev_month,
       round(100.0 * (revenue - lag(revenue) OVER w)
             / NULLIF(lag(revenue) OVER w, 0), 1)            AS mom_pct
FROM monthly
WINDOW w AS (ORDER BY month)
ORDER BY month;

• Expected: running total ends at 7425.50; January’s mom_pct is NULL (no previous month)
• Point: NULLIF(x, 0) avoids division-by-zero errors

Q16. Category tree with full path and revenue rolled up to every ancestor

WITH RECURSIVE tree AS (
    SELECT id, name, name::text AS path, ARRAY[id] AS path_ids, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL
  UNION ALL
    SELECT c.id, c.name, t.path || ' > ' || c.name, t.path_ids || c.id, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
), cat_revenue AS (
    SELECT p.category_id, sum(oi.quantity * oi.unit_price) AS revenue
    FROM order_items oi
    JOIN orders o   ON o.id = oi.order_id AND o.status IN ('paid','shipped','delivered')
    JOIN products p ON p.id = oi.product_id
    GROUP BY p.category_id
)
SELECT t.path,
       t.depth,
       COALESCE(own.revenue, 0)                    AS own_revenue,
       (SELECT COALESCE(sum(cr.revenue), 0)
          FROM tree d
          JOIN cat_revenue cr ON cr.category_id = d.id
         WHERE t.id = ANY (d.path_ids))            AS subtree_revenue
FROM tree t
LEFT JOIN cat_revenue own ON own.category_id = t.id
ORDER BY t.path;

• Expected: Electronics subtree = 6240.50 (Laptops 4197.00 + Accessories 345.50 + Phones 1698.00); Computers = 4542.50; Home = 875.00
• Point: Carry an array of ancestor IDs (path_ids) down the recursion; “is in my subtree” becomes my_id = ANY(descendant.path_ids)

Q17. For every user, their root referrer and how many hops away it is

WITH RECURSIVE chain AS (
    SELECT id AS user_id, id AS ancestor_id, 0 AS depth
    FROM users
  UNION ALL
    SELECT c.user_id, u.referred_by, c.depth + 1
    FROM chain c
    JOIN users u ON u.id = c.ancestor_id
    WHERE u.referred_by IS NOT NULL
)
SELECT DISTINCT ON (c.user_id)
       u.full_name, r.full_name AS root_referrer, c.depth AS hops
FROM chain c
JOIN users u ON u.id = c.user_id
JOIN users r ON r.id = c.ancestor_id
ORDER BY c.user_id, c.depth DESC;

• Expected: Farid Khan → Asha Rao, 3 hops; users nobody referred are their own root with 0 hops

Q18. Days between consecutive orders per customer, and each customer’s average gap

WITH gaps AS (
    SELECT o.user_id,
           o.id,
           o.created_at::date AS order_date,
           o.created_at::date
             - lag(o.created_at::date) OVER (PARTITION BY o.user_id ORDER BY o.created_at) AS days_since_prev
    FROM orders o
    WHERE o.status <> 'cancelled'
)
SELECT u.full_name,
       count(*)                          AS orders,
       round(avg(days_since_prev), 1)    AS avg_gap_days   -- avg() ignores the NULL first gap
FROM gaps g
JOIN users u ON u.id = g.user_id
GROUP BY u.id, u.full_name
HAVING count(*) > 1
ORDER BY avg_gap_days;

• Expected: Farid Khan has the shortest average gap (45.0 days), Ben Carter the longest (128.0)
• Point: date - date returns an integer number of days in Postgres

Q19. Pareto analysis: each customer’s share of revenue, cumulative share and tier

WITH spend AS (
    SELECT o.user_id, sum(oi.quantity * oi.unit_price) AS total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status IN ('paid', 'shipped', 'delivered')
    GROUP BY o.user_id
)
SELECT u.full_name,
       s.total,
       round(100 * s.total / sum(s.total) OVER (), 1)                         AS pct_of_revenue,
       round(100 * sum(s.total) OVER (ORDER BY s.total DESC
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                 / sum(s.total) OVER (), 1)                                   AS cumulative_pct,
       CASE NTILE(3) OVER (ORDER BY s.total DESC)
            WHEN 1 THEN 'gold' WHEN 2 THEN 'silver' ELSE 'bronze' END       AS tier
FROM spend s
JOIN users u ON u.id = s.user_id
ORDER BY s.total DESC;

• Expected: Chen Wei and Eva Muller together make up 59.1% of revenue (gold)
• Point: sum(x) OVER () = grand total on every row; ROWS frame makes the cumulative sum strictly row-by-row even with ties

Q20. Writes: restock via UPSERT, then place an order atomically

• Part A: Restock from a supplier feed — existing SKUs add stock, new SKUs are inserted

BEGIN;
 
INSERT INTO products (sku, name, category_id, price, stock)
VALUES ('ACC-001', 'Wireless Mouse', 4, 25.00, 50),
       ('ACC-005', 'Webcam HD',      4, 59.00, 20)
ON CONFLICT (sku) DO UPDATE
   SET stock = products.stock + EXCLUDED.stock
RETURNING id, sku, stock, (xmax = 0) AS inserted;   -- xmax = 0 → row was inserted, not updated
 
ROLLBACK;   -- keep the practice data unchanged

• Expected: ACC-001 stock 250, inserted = f; ACC-005 gets id 14 (not 13), inserted = t

• Gotcha: ON CONFLICT still consumes an identity/sequence value for every proposed row, so gaps in IDs are normal

• Part B: Gina buys 2 mice and 1 chef knife — lock the product rows, create the order and items, decrement stock, all in one statement inside a transaction

BEGIN;
 
WITH cart (product_id, quantity) AS (
    VALUES (3::bigint, 2), (9::bigint, 1)
), locked AS (
    SELECT p.id, p.price, c.quantity
    FROM products p
    JOIN cart c ON c.product_id = p.id
    WHERE p.is_active
    FOR UPDATE OF p                         -- block concurrent buyers of the same rows
), new_order AS (
    INSERT INTO orders (user_id, status) VALUES (7, 'pending')
    RETURNING id
), items AS (
    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    SELECT n.id, l.id, l.quantity, l.price
    FROM new_order n CROSS JOIN locked l
    RETURNING product_id, quantity
)
UPDATE products p
SET    stock = p.stock - i.quantity         -- CHECK (stock >= 0) aborts the whole txn on oversell
FROM   items i
WHERE  p.id = i.product_id
RETURNING p.sku, p.stock AS remaining_stock;
 
ROLLBACK;   -- use COMMIT in real code

• Expected: ACC-001 → 198, KIT-002 → 39
• Point: All parts of a data-modifying CTE see the same snapshot and succeed or fail together; the CHECK constraint is the last line of defence against overselling

Interview Tips

• Clarify First: Ask how to treat cancelled/pending orders, ties, NULLs and users with zero rows before writing SQL
• Watch Fan-out: Joining two one-to-many tables then summing double-counts — aggregate in a CTE first (Q9, Q12)
• Say the Postgres-specific Bit: DISTINCT ON, FILTER, ON CONFLICT, RETURNING, IS DISTINCT FROM, FETCH … WITH TIES
• Check Edge Cases: Empty groups, NULL keys, division by zero, duplicate ranks

Summary

• Schema: Seven tables covering the common interview shapes — one-to-many, many-to-many, self-references and trees
• Easy: Filtering, sorting, GROUP BY/HAVING, NULL relabelling
• Medium: Anti-joins, fan-out-safe aggregation, relational division, NULL-safe comparisons
• Hard: DISTINCT ON, window ranking/running totals, recursive CTEs, UPSERT and atomic multi-table writes

Link to original

Concurrency Control

Concurrency Control

Overview

• Purpose: Manage simultaneous database access by multiple transactions
• Goal: Maintain data consistency while maximizing performance
• Challenge: Balance between isolation and concurrency
• Foundation: ACID properties compliance

Why Concurrency Control Matters

Without Concurrency Control

• Lost Updates: One transaction overwrites another’s changes
• Dirty Reads: Reading uncommitted data from other transactions
• Non-repeatable Reads: Same query returns different results within transaction
• Phantom Reads: New rows appear between repeated queries

Benefits of Proper Control

• Data Integrity: Prevents corruption and inconsistencies
• Predictable Behavior: Consistent transaction outcomes
• Performance: Optimal resource utilization
• Scalability: Support for multiple concurrent users

Core Concepts

Transaction Isolation Levels

• Read Uncommitted: No isolation, all phenomena possible
• Read Committed: Prevents dirty reads only
• Repeatable Read: Prevents dirty and non-repeatable reads
• Serializable: Full isolation, prevents all phenomena

Concurrency Control Methods

• Locking: Control access through locks (covered separately)
• Timestamps: Order transactions by timestamp
• Optimistic Control: Assume conflicts are rare, validate at commit
• Pessimistic Control: Assume conflicts likely, prevent proactively

Locking Mechanisms (High Level)

• Shared Locks: Multiple readers allowed
• Exclusive Locks: Single writer only
• Lock Granularity: Row, page, table level
• Lock Duration: When acquired and released
• Detailed coverage in separate notes

Timestamp-Based Control

Basic Concept

• Transaction Timestamp: Unique identifier for ordering
• Read/Write Timestamps: Track when data was accessed
• Rule: Conflicting operations must execute in timestamp order; an operation that would break that order aborts its transaction (transactions don’t have to finish in order)

Advantages

• No Deadlocks: Timestamp ordering prevents cycles
• No Waiting: Transactions either proceed or abort

Disadvantages

• More Aborts: Conflicts result in transaction restarts
• Overhead: Timestamp management cost
• Cascading Aborts: One abort can trigger others
• Starvation Possible: A transaction restarted with a new (younger) timestamp can keep getting aborted (wait-die / wound-wait avoid this by keeping the original timestamp)

Optimistic Concurrency Control

Three Phases

1. Read Phase: Transaction reads data and maintains local copies
2. Validation Phase: Check for conflicts with other transactions
3. Write Phase: Apply changes if validation succeeds

When to Use

• Low Conflict Rate: Few transactions modify same data
• Read-Heavy Workloads: More reads than writes
• Short Transactions: Quick operations with minimal overlap

Validation Criteria

• Read Set: Data read during transaction
• Write Set: Data modified during transaction
• Conflict Check: Ensure no overlapping read/write sets

Pessimistic Concurrency Control

Approach

• Assume Conflicts: Expect data contention
• Acquire Locks Early: Get locks before accessing data
• Hold Until Commit: Maintain locks through transaction

When to Use

• High Conflict Rate: Many transactions modify same data
• Write-Heavy Workloads: Frequent updates and modifications
• Long Transactions: Extended operations with high overlap

Trade-offs

• Advantages: Prevents conflicts, ensures consistency
• Disadvantages: Potential deadlocks, reduced concurrency

Multiversion Concurrency Control (MVCC)

Core Concept

• Multiple Versions: Keep multiple versions of same data
• Snapshot Isolation: Each transaction sees consistent snapshot
• No Read Locks: Readers don’t block writers

Implementation

• Version Numbers: Track data versions
• Visibility Rules: Determine which version transaction sees
• Garbage Collection: Clean up old versions

Advantages

• High Concurrency: Readers and writers don’t block each other
• Consistent Reads: Stable view throughout transaction
• No Read Locks: Reduced lock contention

Used By

• PostgreSQL: MVCC with snapshot isolation
• MySQL InnoDB: MVCC with repeatable read
• Oracle: MVCC with read consistency

Performance Considerations

Lock Granularity Impact

• Fine Granularity: Higher concurrency, more overhead
• Coarse Granularity: Lower concurrency, less overhead
• Adaptive: Escalate from row to table locks

Deadlock Prevention vs Detection

• Prevention: Order resources, timeout-based
• Detection: Wait-for graphs, victim selection
• Detailed coverage in separate deadlock notes

Index Locking

• Index Pages: Protect index structure modifications
• Key-Range Locks: Prevent phantom reads in ranges
• Next-Key Locking: Combination of record and gap locks

Common Patterns

Read-Modify-Write

• Problem: Lost update anomaly
• Solutions: SELECT FOR UPDATE, compare-and-swap
• Example: Inventory updates, account balances

Bulk Operations

• Challenge: Long-running transactions
• Strategies: Batch processing, checkpoints
• Consideration: Lock escalation thresholds

Distributed Transactions

• Two-Phase Commit: Coordinate across multiple databases
• Saga Pattern: Compensating transactions for failures
• Event Sourcing: Append-only, conflict-free approach

Monitoring and Tuning

Key Metrics

• Lock Wait Time: Time spent waiting for locks
• Deadlock Frequency: Rate of deadlock detection
• Transaction Throughput: Successful transactions per second
• Isolation Level Distribution: Usage patterns by level

Tuning Strategies

• Isolation Level: Use minimum required level
• Transaction Size: Keep transactions small and fast
• Access Patterns: Design to minimize conflicts
• Index Strategy: Proper indexing reduces lock duration

Best Practices

Application Design

• Short Transactions: Minimize lock hold time
• Consistent Ordering: Access resources in same order
• Retry Logic: Handle deadlocks and timeouts gracefully
• Read Replicas: Offload reads to reduce contention

Database Configuration

• Lock Timeout: Set appropriate timeout values
• Isolation Levels: Configure based on requirements
• Lock Escalation: Tune escalation thresholds
• Deadlock Detection: Configure detection intervals

Development Guidelines

• Explicit Locking: Use SELECT FOR UPDATE when needed
• Transaction Boundaries: Clear begin/commit patterns
• Error Handling: Proper rollback on failures
• Testing: Load testing with concurrent scenarios

Common Anti-Patterns

Avoid These

• Long-Running Transactions: Hold locks too long
• Unnecessary Locking: Over-conservative locking strategies
• Wrong Isolation Level: Using stricter than needed
• Implicit Transactions: Unclear transaction boundaries

Instead Do This

• Batch Processing: Break large operations into smaller chunks
• Optimistic Locking: Use version numbers for conflict detection
• Appropriate Isolation: Match level to business requirements
• Explicit Control: Clear transaction management

Related Topics

• Shared/Exclusive Locks: Detailed lock types and behavior
• Deadlock Management: Detection, prevention, and resolution
• Two-Phase Locking: Protocol for maintaining serializability
• Double Booking Problem: Real-world concurrency scenarios

Summary

• Goal: Balance consistency and performance in concurrent environments
• Methods: Locking, timestamps, optimistic/pessimistic control, MVCC
• Key Principle: Use minimum isolation level that meets requirements
• Best Practice: Design applications to minimize lock contention
• Monitoring: Track lock waits, deadlocks, and transaction performance

Link to original

Database Indexing

Database Indexing

• Indexing is a crucial technique used in databases to optimize data retrieval operations.

• It improves query performance by minimizing disk I/O operations, thus reducing the time it takes to locate and access data.

• Essentially, indexing allows the database management system (DBMS) to locate data more efficiently without having to scan the entire dataset.

• Indexes are organized data structures that allow quick searching based on key values.

• When an index is created for a database table, it maintains a sorted order of key values along with pointers to the actual data rows.

• This process significantly reduces the number of disk accesses required to fulfill a query.

Create a index on table

Load 1 Million random data

--employees table for the indexing lecture
--paste these commands into the postgres 
--start the docker instance
docker run --name pg -e POSTGRES_PASSWORD=postgres -d postgres
 
docker start pg
--run postgres command shell
docker exec -it pg psql -U postgres
--the command should switch to 
--postgres=#
-- paste these sql
create table employees( id serial primary key, name text);
 
create or replace function random_string(length integer) returns text as 
$$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
  length2 integer := (select trunc(random() * length + 1));
begin
  if length2 < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length2 loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;
 
 
insert into employees(name)(select random_string(10) from generate_series(0, 1000000));

create index employees_name on employees(name);

• When getting id, name it will not search using the index employees_name.
• LIKE can use a B-tree index, but only when the pattern is a constant anchored at the start ('abc%') — Postgres turns it into a range scan (>= 'abc' AND < 'abd')
• Leading wildcard ('%abc%', '%abc'): a B-tree can’t help → Seq Scan. Use a pg_trgm GIN index instead
• Locale Gotcha: In a non-C locale (e.g. en_US.UTF-8, the usual default), a default index is not used even for 'abc%'. Create it with text_pattern_ops or COLLATE "C"
• text_pattern_ops Trade-off: Serves LIKE 'abc%' and =, but not <, >, ORDER BY — keep a normal index too if you need those
• ILIKE: B-tree only if the pattern starts with non-letters. Otherwise use pg_trgm

-- prefix search in a non-C locale
create index employees_name_pattern on employees(name text_pattern_ops);
-- or: create index employees_name_c on employees(name collate "C");
 
-- contains / ILIKE search
create extension pg_trgm;
create index employees_name_trgm on employees using gin (name gin_trgm_ops);

Verified on PostgreSQL 17.8 (en_US.UTF-8, 500k rows) — see Postgres docs: Index Types, Operator Classes, pg_trgm

Query	Index	Plan
LIKE 'abc%'	default btree	Seq Scan
LIKE 'abc%'	text_pattern_ops	Index Scan (~>=~ 'abc' AND ~<~ 'abd')
LIKE 'abc%'	COLLATE "C"	Index Only Scan — 3.9 ms vs 25.9 ms seq
< 'abc' ORDER BY	text_pattern_ops only	Seq Scan + Sort
LIKE '%abc%'	any btree	Seq Scan
LIKE '%abc1%', ILIKE '%ABC1%'	pg_trgm GIN	Bitmap Index Scan
ILIKE 'abc%'	btree COLLATE "C"	Seq Scan

Link to original

UUID Versions

UUID Versions

Overview

• UUID: 128-bit identifier, written as 32 hex digits in 5 groups 8-4-4-4-12
• Why: Generate unique IDs anywhere (app, client, any DB node) without asking a central sequence
• Standard: RFC 9562 (2024) replaced RFC 4122 and added v6, v7, v8
• Key Interview Point: For database primary keys, v4 (random) hurts B-tree indexes. v7 (time-ordered) fixes this
• Related: Database Indexing, B+Tree, Indexes and Query Performance, DDL Data Types and Constraints

Anatomy

01a1d36c-3100-7293-a238-4c9ce305d4a4
              ^    ^
              |    └─ variant (first hex digit 8, 9, a or b = RFC variant)
              └────── version (13th hex digit) → 7

• Version: Tells you how the rest of the bits were made
• Variant: Always 10xx bits for standard UUIDs
• Special Values: Nil 00000000-0000-0000-0000-000000000000, Max ffffffff-ffff-ffff-ffff-ffffffffffff

All Versions

Version	Made From	Sortable by Time	Deterministic	Use Today?
v1	Timestamp (100 ns since 1582) + MAC address	No (time bytes stored low-first)	No	Avoid — leaks MAC & time
v2	DCE Security (v1 + POSIX UID/GID)	No	No	Practically never
v3	MD5(namespace + name)	No	Yes	Prefer v5
v4	122 random bits	No	No	Yes — when order/time must not leak
v5	SHA-1(namespace + name)	No	Yes	Yes — stable IDs from names
v6	v1 with time bytes reordered	Yes	No	Only for v1 legacy migration
v7	48-bit Unix ms timestamp + random	Yes	No	Yes — default for DB keys
v8	Custom / vendor-defined layout	Depends	Depends	Special cases

v4 — Random

• Bits: 122 random + 6 fixed (version/variant)
• Collisions: Need ~2.7 × 10¹⁸ UUIDs for a 50% chance of one duplicate — ~1 billion/sec for ~85 years
• Downside: Random order → every insert lands on a random index page

v5 / v3 — Name-Based

• Same Input → Same UUID: uuid5(NAMESPACE_URL, "https://x.com/a") always gives the same value
• Use Case: Idempotent imports, dedupe keys, mapping external IDs to UUIDs
• Note: Not secret — anyone with the name can compute it

v7 — Time-Ordered

| 48 bits unix_ts_ms | 4 ver | 12 bits rand_a | 2 var | 62 bits rand_b |

• Sorted by Creation Time: New values are (almost) always bigger than old ones
• Within One Millisecond: Postgres 18 puts a sub-millisecond fraction in rand_a, so values from one backend are strictly increasing
• Across Servers: Only roughly ordered (clock skew), which is still good enough for index locality
• Bonus: Creation time can be extracted from the ID

Why v4 Hurts Primary Keys (B-Tree)

• Sequential keys (bigint, v7): Every insert goes to the rightmost leaf page. When it fills, Postgres does a “rightmost split” and leaves the old page ~90% full. Hot pages stay in cache
• Random keys (v4): Every insert goes to a random leaf page → 50/50 page splits all over the index, pages ~50–70% full, the whole index must be in memory, many more dirty pages → more WAL (full-page images after each checkpoint) and more I/O
• MySQL InnoDB is worse: The table is the PK index (clustered), so random keys fragment the table rows themselves, not just an index

Verified on PostgreSQL 17.8 (3M inserts, shared_buffers = 16MB)

PK Type	Insert Time	WAL Written	PK Index Size	Leaf Density	Leaf Fragmentation
bigint identity	7.8 s	405 MB	64 MB	90%	0%
uuid v4	24.2 s	491 MB	123 MB	66%	50%
uuid v7	16.7 s	446 MB	90 MB	90%	0%

• v4 vs v7: 37% bigger index, half the leaf pages out of order, ~45% slower inserts
• v7 vs bigint: Index is bigger only because a UUID is 16 bytes vs 8. The layout is just as clean
• Caveat: On PG 17, v7 came from a PL/pgSQL function (below), so part of its extra time is function overhead. PG 18’s built-in uuidv7() is C. The gap to v4 grows when the index no longer fits in RAM
• Measured With: pgstatindex() from pgstattuple, and pg_wal_lsn_diff() before/after each insert

Storage

Stored As	Bytes
uuid type	16
text / varchar(36)	37–40
bigint	8

• Rule: Always use the native uuid type (Postgres) or BINARY(16) (MySQL), never strings

PostgreSQL

Function	Version	Since
gen_random_uuid()	v4	PG 13 (core)
uuidv4()	v4 (alias)	PG 18
uuidv7() / uuidv7(interval)	v7 (optional time shift)	PG 18
uuid_extract_version(u)	Read the version	PG 17
uuid_extract_timestamp(u)	Read creation time	PG 17 (v1), PG 18 (v1 + v7)
uuid-ossp extension: uuid_generate_v1(), _v3(), _v5()	v1/v3/v5	Extension

-- PG 18+
CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  created_at timestamptz NOT NULL DEFAULT now()
);
SELECT uuid_extract_timestamp(id) FROM orders LIMIT 1;  -- creation time from the key

v7 on PostgreSQL 17 and Older (verified on 17.8)

CREATE FUNCTION uuid_v7() RETURNS uuid LANGUAGE plpgsql VOLATILE AS $$
DECLARE us bigint := (extract(epoch FROM clock_timestamp()) * 1000000)::bigint;
        r  text   := replace(gen_random_uuid()::text, '-', '');
BEGIN
  -- 48-bit ms | '7' | 12-bit sub-ms fraction | variant + random (from the v4)
  RETURN (lpad(to_hex(us / 1000), 12, '0') || '7'
          || lpad(to_hex(((us % 1000) * 4096 / 1000)::int), 3, '0')
          || substr(r, 17, 16))::uuid;
END $$;
 
SELECT uuid_v7(), uuid_extract_version(uuid_v7());   -- → 7

• Or generate v7 in the app (most languages have a library) and insert it

MySQL

• UUID(): Returns v1
• Store: BINARY(16) with UUID_TO_BIN(UUID(), 1) — the 1 (swap flag) moves the time bits to the front so v1 values become time-ordered
• Read Back: BIN_TO_UUID(id, 1)
• Better: Generate v7 in the app and store as BINARY(16)

Choosing an ID

Situation	Choose
Internal IDs, single database	bigint GENERATED ALWAYS AS IDENTITY (smallest, fastest)
IDs made in the app / client / multiple DBs, or exposed in URLs	UUID v7
Must not reveal creation time or order	UUID v4
Same input must always map to same ID	UUID v5
Legacy system with v1	Keep, or migrate to v6/v7

• Common Pattern: bigint PK internally + a uuid (v4 or v7) public ID column with a UNIQUE index for URLs/APIs

Security Notes

• Not Enumerable: UUIDs stop /orders/1, /orders/2 guessing — but that is not access control. Always check authorization
• v7 Leaks Time: Anyone can read when a row was created from its ID
• v1 Leaks MAC Address: Identifies the machine that created it
• Not a Secret Token: Don’t use UUIDs as session tokens, password-reset tokens or API keys. Use a CSPRNG with ≥128 bits (e.g. crypto.randomBytes(32)); the RFC doesn’t require v4 to come from a secure random source

Likely Interview Questions

• Why not use UUIDv4 as a PK? Random inserts → page splits, ~50% fragmented and bigger index, more cache misses and WAL
• How does v7 fix it? Timestamp prefix → new keys append at the right end of the B-tree like a sequence
• UUID vs bigint? UUID: global uniqueness, generate anywhere, non-guessable. bigint: half the size, fastest, needs a central sequence, enumerable
• Can two UUIDs collide? Theoretically yes; for v4 practically never (2.7 × 10¹⁸ for a 50% chance)
• Same UUID every time for the same email? v5 with a namespace

Summary

• v1: time + MAC (avoid) · v3/v5: hash of a name, deterministic · v4: random · v6: sortable v1 · v7: Unix-ms time + random, sortable · v8: custom
• Database Keys: Prefer bigint identity or UUID v7. Avoid v4 as a clustered/B-tree PK at scale
• Measured: v4 PK index 37% bigger, 50% fragmented, ~45% slower inserts than v7 on PG 17.8
• Postgres: gen_random_uuid() (v4), uuidv7() from PG 18, function above for PG 17
• Storage: Native uuid / BINARY(16), never text
• Security: v7 reveals creation time; no UUID is a substitute for a secure random token

Link to original

theory

theory

Notes in this canvas

• PostgreSQL Queries
• DDL Data Types and Constraints
• SELECT Fundamentals
• Aggregation and GROUP BY
• Joins
• Subqueries and CTEs
• Window Functions
• Inserts Updates Upserts and MERGE
• Transactions and Locking
• Indexes and Query Performance
• Admin and Inspection Queries
• JSONB and Arrays
• Views Functions and Triggers
• E-commerce Practice Queries
• Concurrency Control
• Database Indexing
• UUID Versions