SQL Practical Cheatsheet — Complete Reference for Everyday Commands

• Which Engine This Cheatsheet Targets
• Basic CRUD
  • SELECT (Query)
  • INSERT
  • UPDATE
  • DELETE
• JOIN
• GROUP BY + Aggregate Functions
• Subquery
• CTE (Common Table Expression) — The King of Readability
• Window Functions
• Index Strategy
• Execution Plan (EXPLAIN)
• How to Read an Execution Plan
  • Read it inside-out
  • The four numbers in the parentheses
  • Estimated rows vs actual rows is signal number one
  • A Seq Scan on a large table with a selective filter
  • EXPLAIN ANALYZE really runs the query
• Why the Index You Created Is Not Being Used
  • 1. The leading-column rule for composite indexes
  • 2. A function or a cast wrapped around the indexed column
  • 3. Low selectivity, so a sequential scan really is the right plan
  • 4. Stale statistics
• Practical Pattern Collection
  • Pagination
  • Deduplication
  • Date-Related
  • Lock Considerations
• Keyset Pagination
  • Why OFFSET degrades as you go deeper
  • Rewriting it as a seek
  • How the plan differs
  • What keyset costs you
• Locks and Schema Changes — Where the Real Danger Is
  • What an UPDATE or DELETE without WHERE does
  • The real problem is the long transaction
  • Finding who is blocking
  • The rule that prevents deadlocks
  • Index builds — CREATE INDEX blocks writes
  • ALTER TABLE — the default is ACCESS EXCLUSIVE
  • The safe pattern for adding a constraint
• Failure Cases and Traps
  • 1. A query that was fast yesterday is slow today
  • 2. SELECT * in a join blows up the row width
  • 3. A NULL in a NOT IN subquery returns nothing at all
  • 4. Implicit type casts
  • 5. COUNT on a huge table
• When Not to Use This
• References
• Quiz

Which Engine This Cheatsheet Targets

Before copying anything out of a cheatsheet you need to know which engine it was written for. The examples here are written against PostgreSQL 18. The syntax gives it away: ON CONFLICT ... DO UPDATE, FULL OUTER JOIN, GROUP BY ROLLUP, INTERVAL '30 days', the created_at::date cast, || string concatenation, DATE_TRUNC, WITH RECURSIVE, and partial indexes with a WHERE clause are all PostgreSQL-family syntax.

The following features covered in the sections below are PostgreSQL features confirmed against the official PostgreSQL documentation.

• Partial indexes and expression indexes
• CREATE INDEX CONCURRENTLY
• The EXPLAIN ANALYZE output format and its field names
• Per-subcommand lock levels for ALTER TABLE, plus NOT VALID / VALIDATE CONSTRAINT
• The pg_blocking_pids() function

If you are on MySQL, those sections do not carry over. Execution plans in particular have a completely different output format — the EXPLAIN FORMAT=JSON example in this article is the evidence — and the "How to Read an Execution Plan" section below is entirely about PostgreSQL output. MySQL's lock levels and online DDL behavior differ too, so check the MySQL manual separately. The UPSERT and JOIN UPDATE / JOIN DELETE snippets already in this article list both engines side by side, so you can use those as they are.

Basic CRUD

SELECT (Query)

-- Basic query
SELECT * FROM users WHERE age >= 20 ORDER BY created_at DESC LIMIT 10;

-- Specific columns only
SELECT id, name, email FROM users WHERE status = 'active';

-- Alias
SELECT
    u.name AS user_name,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- DISTINCT (remove duplicates)
SELECT DISTINCT department FROM employees;

-- BETWEEN, IN, LIKE
SELECT * FROM products
WHERE price BETWEEN 10000 AND 50000
  AND category IN ('electronics', 'books')
  AND name LIKE '%Galaxy%';

-- NULL handling
SELECT name, COALESCE(phone, 'Not registered') AS phone
FROM users
WHERE email IS NOT NULL;

INSERT

-- Single insert
INSERT INTO users (name, email, age) VALUES ('Kim Youngju', 'yj@example.com', 30);

-- Multiple inserts
INSERT INTO users (name, email, age) VALUES
    ('Hong Gildong', 'hong@example.com', 25),
    ('Lee Sunsin', 'lee@example.com', 35),
    ('King Sejong', 'sejong@example.com', 45);

-- INSERT from SELECT (table copy)
INSERT INTO users_backup (name, email, age)
SELECT name, email, age FROM users WHERE status = 'active';

-- UPSERT (UPDATE if exists, INSERT if not)
-- PostgreSQL
INSERT INTO users (email, name, login_count)
VALUES ('yj@example.com', 'Kim Youngju', 1)
ON CONFLICT (email)
DO UPDATE SET
    login_count = users.login_count + 1,
    last_login = NOW();

-- MySQL
INSERT INTO users (email, name, login_count)
VALUES ('yj@example.com', 'Kim Youngju', 1)
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login = NOW();

UPDATE

-- Basic update
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01';

-- Update multiple columns at once
UPDATE products
SET price = price * 1.1,           -- 10% increase
    updated_at = NOW()
WHERE category = 'electronics';

-- JOIN UPDATE (update referencing another table)
-- PostgreSQL
UPDATE orders o
SET status = 'cancelled'
FROM users u
WHERE o.user_id = u.id
  AND u.status = 'banned';

-- MySQL
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.status = 'cancelled'
WHERE u.status = 'banned';

-- Conditional UPDATE with CASE
UPDATE employees
SET salary = CASE
    WHEN department = 'engineering' THEN salary * 1.15
    WHEN department = 'sales' THEN salary * 1.10
    ELSE salary * 1.05
END
WHERE hire_date < '2024-01-01';

-- Warning: UPDATE without WHERE modifies ALL rows!
-- Always verify with SELECT first!
SELECT * FROM users WHERE last_login < '2025-01-01';  -- Verify first
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01';

DELETE

-- Basic delete
DELETE FROM sessions WHERE expired_at < NOW();

-- JOIN DELETE
-- PostgreSQL
DELETE FROM orders
USING users
WHERE orders.user_id = users.id AND users.status = 'deleted';

-- MySQL
DELETE o FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'deleted';

-- TRUNCATE (delete all, fast, resets AUTO_INCREMENT)
TRUNCATE TABLE logs;

-- Soft delete pattern (recommended)
UPDATE users SET deleted_at = NOW() WHERE id = 123;
-- When querying:
SELECT * FROM users WHERE deleted_at IS NULL;

JOIN

-- INNER JOIN (only rows that exist in both)
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN (all from left + matching from right)
SELECT u.name, COALESCE(COUNT(o.id), 0) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- → Includes users with no orders (order_count = 0)

-- RIGHT JOIN (all from right + matching from left)
-- Rarely used, flipping LEFT JOIN is more readable

-- FULL OUTER JOIN (all from both sides)
SELECT u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN (all combinations, Cartesian product)
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;
-- 3 sizes x 4 colors = 12 combinations

-- SELF JOIN (join with itself)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

JOIN Diagram:

INNER JOIN:    A intersection B (intersection only)
LEFT JOIN:     A + (A intersection B)
RIGHT JOIN:    (A intersection B) + B
FULL OUTER:    A union B (union)

GROUP BY + Aggregate Functions

-- Basic aggregation
SELECT
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000000  -- Only departments with average salary above 50M
ORDER BY avg_salary DESC;

-- ROLLUP (subtotals + grand total)
SELECT
    COALESCE(department, '=== Total ===') AS department,
    COALESCE(position, '--- Subtotal ---') AS position,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY ROLLUP(department, position);

Subquery

-- WHERE subquery
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders
    WHERE amount > 1000000
);

-- FROM subquery (inline view)
SELECT dept_name, avg_salary
FROM (
    SELECT department AS dept_name, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) sub
WHERE avg_salary > 60000000;

-- EXISTS (check existence, faster than IN for large datasets)
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.status = 'completed'
);

-- Scalar subquery (in SELECT clause)
SELECT
    name,
    salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

CTE (Common Table Expression) — The King of Readability

-- Basic CTE
WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active' AND last_login > NOW() - INTERVAL '30 days'
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    au.name,
    au.email,
    COALESCE(uo.order_count, 0) AS orders,
    COALESCE(uo.total, 0) AS total_spent
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id
ORDER BY total_spent DESC;

-- Recursive CTE (org charts, category trees)
WITH RECURSIVE org_tree AS (
    -- Base case: CEO (manager_id is NULL)
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: traverse subordinates
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT REPEAT('  ', level - 1) || name AS org_chart, level
FROM org_tree
ORDER BY level, name;

Window Functions

-- ROW_NUMBER (sequential numbering)
SELECT
    name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_all,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_dept
FROM employees;

-- RANK vs DENSE_RANK
-- RANK: 1, 2, 2, 4 (ties at 2nd, then 4th)
-- DENSE_RANK: 1, 2, 2, 3 (ties at 2nd, then 3rd)

-- LAG / LEAD (reference previous/next row)
SELECT
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date) AS prev_day,
    revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY date))
        / LAG(revenue) OVER (ORDER BY date) * 100, 1
    ) AS change_pct
FROM daily_sales;

-- Running Total
SELECT
    date, amount,
    SUM(amount) OVER (ORDER BY date) AS running_total,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

-- NTILE (divide into quantiles)
SELECT
    name, salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
    -- 1=top 25%, 2=25~50%, 3=50~75%, 4=bottom 25%
FROM employees;

Index Strategy

-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Composite index order matters!
-- idx(a, b, c):
--   WHERE a = 1                    Used
--   WHERE a = 1 AND b = 2         Used
--   WHERE a = 1 AND b = 2 AND c = 3 Used
--   WHERE b = 2                    NOT used! (leading column missing)
--   WHERE a = 1 AND c = 3         Partially used (a only, b skipped)

-- Partial index (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Covering index (query resolved from index alone, no table access)
CREATE INDEX idx_covering ON orders(user_id, status, amount);
SELECT status, SUM(amount) FROM orders WHERE user_id = 123 GROUP BY status;
-- → Only reads from index! (no table I/O)

Execution Plan (EXPLAIN)

-- PostgreSQL
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.name;

-- How to read:
-- Seq Scan: Full table scan (need an index?)
-- Index Scan: Index used
-- Index Only Scan: Covering index
-- Nested Loop: JOIN for small datasets
-- Hash Join: JOIN for large datasets
-- Sort: ORDER BY (disk if memory exceeded)
-- Bitmap Heap Scan: Combining multiple indexes

-- MySQL
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';

How to Read an Execution Plan

The section above shows the command. What actually matters is reading the output.

Read it inside-out

A plan is a tree. The most deeply indented node runs first, and its result flows up to the node outside it. Reading top-down therefore shows you the order backwards. Start at the deepest node to see how rows are pulled out, then follow upward to see where they are filtered and how they are joined.

There is one property the documentation is explicit about. The cost of an upper-level node includes the cost of all its child nodes. So you cannot look at the top number alone and say "this node is expensive". You need the difference between parent and child to know what that node actually spent.

The four numbers in the parentheses

-- Example output: EXPLAIN (ANALYZE, BUFFERS)
 Hash Join  (cost=1082.00..3418.55 rows=4821 width=48) (actual time=8.412..41.903 rows=4795.00 loops=1)
   Hash Cond: (o.user_id = u.id)
   Buffers: shared hit=1204 read=812
   ->  Seq Scan on orders o  (cost=0.00..2015.00 rows=100000 width=24) (actual time=0.011..12.204 rows=100000.00 loops=1)
         Buffers: shared hit=200 read=815
   ->  Hash  (cost=1021.00..1021.00 rows=4880 width=32) (actual time=8.301..8.302 rows=4795.00 loops=1)
         Buckets: 8192  Batches: 1  Memory Usage: 384kB
         ->  Seq Scan on users u  (cost=0.00..1021.00 rows=4880 width=32) (actual time=0.019..7.104 rows=4795.00 loops=1)
               Filter: (status = 'active'::text)
               Rows Removed by Filter: 45205
               Buffers: shared hit=1004
 Planning Time: 0.312 ms
 Execution Time: 43.115 ms

Line by line.

• cost=1082.00..3418.55 — the first is the startup cost, the second the total cost. Startup cost is what is spent before output can begin; for a sort node, that includes finishing the sort. Total cost assumes the node is run to completion. The units are arbitrary units set by the planner's cost parameters, conventionally anchored on disk page fetches at 1.0. They are not milliseconds.
• rows=4821 — the estimated number of rows this node will emit, not the number it scans. It is what is left after filtering. The documentation calls this out separately as easy to misread.
• width=48 — the estimated average width, in bytes, of the rows this node emits.
• actual time=8.412..41.903 — real start and end times, in milliseconds of real time . cost is in arbitrary units, so the two will not line up. Do not try to compare them.
• loops=1 — how many times this node was executed. For nodes executed repeatedly, such as the inner side of a Nested Loop, actual time and rows are shown as averages per execution . Multiply by loops to get the total. Missing this is a classic trap: "the inner index scan is 0.003ms, so it is fine" is wrong when loops=10000 — that is 30ms.
• Rows Removed by Filter: 45205 — how many rows the filter threw away. It appears only when at least one scanned row was rejected. Reading 50,000 rows to keep 4,795 tells you whether an index on that filter would pay off.

Estimated rows vs actual rows is signal number one

The documentation says it directly: "The thing that's usually most important to look for is whether the estimated row counts are reasonably close to reality."

Every choice the planner makes rests on those estimates. When an estimate is badly wrong, every decision after it is wrong too. Expecting 100 rows and choosing a Nested Loop, then getting a million, means a million index lookups. Expecting a million and choosing a Hash Join, then getting ten, means you built a hash table for nothing.

So the first thing to do with a plan is compare the rows= estimate against the actual ... rows= measurement at each node. A factor of a few is fine; find the node where they diverge by an order of magnitude or more and you have found the start of the problem. Then ask why the estimate was wrong there. Usually the statistics are stale, the planner does not know about a correlation between columns, or the condition is wrapped in a function so its selectivity cannot be estimated.

A Seq Scan on a large table with a selective filter

The users scan above is exactly that shape: 50,000 rows read, 4,795 kept. At that selectivity an index has a real chance of winning. Flip it around — a condition that keeps 40,000 of 50,000 rows — and taking the index is a loss. Using an index means bouncing between index and table with random access, and if you are going to read most of the pages anyway, reading them sequentially is faster.

So do not create an index reflexively just because you saw a Seq Scan. There are two things to judge on: is the table actually large (look at the read block counts in Buffers ), and is the condition actually selective (look at the ratio of Rows Removed by Filter to the surviving rows ).

EXPLAIN ANALYZE really runs the query

Miss this and you cause an incident. In the documentation's own words, because EXPLAIN ANALYZE actually runs the query, any side effects happen as usual. The result rows are discarded, but an UPDATE, DELETE, or INSERT really does change your data.

To see the plan of a data-modifying query without changing anything, wrap it in a transaction and roll back. This is what the documentation recommends.

BEGIN;

EXPLAIN ANALYZE
UPDATE orders SET status = 'cancelled' WHERE created_at < '2025-01-01';

ROLLBACK;

If you ever look at plans in production, make this a habit. Plain EXPLAIN does not execute anything and only shows estimates — but then you have no measured row counts, so you cannot check signal number one.

Why the Index You Created Is Not Being Used

When an index from the index strategy section does not appear in the plan, the cause is almost always one of four things. Checking them in order is fastest.

1. The leading-column rule for composite indexes

This is already tabulated in the index section above. A three-column index is usable only from the left, contiguously. If the leading column is not in your conditions, that index drops out of the running. If the index name does not appear in the plan at all, suspect this first.

2. A function or a cast wrapped around the indexed column

The most common cause, and the hardest to spot.

-- The index is on the email column
CREATE INDEX idx_users_email ON users(email);

-- But the condition is on lower(email) -> the index cannot be used
SELECT * FROM users WHERE lower(email) = 'yj@example.com';

-- Same for dates. An index on created_at is useless once you cast it
SELECT * FROM orders WHERE created_at::date = '2026-08-16';

-- Fix 1) create an expression index
CREATE INDEX idx_users_email_lower ON users(lower(email));
-- From the docs: an index computed on upper(col) allows the clause
-- WHERE upper(col) = 'JIM' to use that index.

-- Fix 2) rewrite the condition as a range so the column is bare again
SELECT * FROM orders
WHERE created_at >= '2026-08-16' AND created_at < '2026-08-17';

The reason is simple. An index stores column values in sorted order. There is no guarantee that applying a function to those values preserves that order, so the index cannot be used. Index the result of the function itself — an expression index — and it becomes usable again.

3. Low selectivity, so a sequential scan really is the right plan

If 80% of rows have status = 'active', taking the index is a loss. That is not a bug; the planner judged correctly. It is the same story as the "Seq Scan on a large table" section above.

The card you can play here is a partial index — there is already an example in the index section. Indexing only the frequently used subset instead of the whole table makes the index smaller, and raises selectivity for queries that arrive with that condition.

4. Stale statistics

The planner chooses plans based on statistics about table contents. In the documentation's words, "It is important to have reasonably accurate statistics, otherwise poor choices of plans might degrade database performance." Those statistics are gathered by ANALYZE, which can also run as an optional step of VACUUM.

The autovacuum daemon issues ANALYZE automatically whenever a table's contents have changed sufficiently. But right after a bulk load, where the data changed all at once, plans get made from stale statistics while you wait for that automatic run.

-- Run it by hand right after a bulk load
ANALYZE orders;

-- Then check whether the plan changed
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

As a diagnosis order: if the index name never appears in the plan, look at 1 and 2; if the index is a candidate but is not chosen, look at 3 and 4. Confirming 4 is easy — run ANALYZE and see whether the plan changes.

Practical Pattern Collection

Pagination

-- OFFSET method (simple but slow for large datasets)
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 40;

-- Cursor-based (recommended for large datasets!)
SELECT * FROM posts
WHERE id < 12345  -- Last seen id
ORDER BY id DESC
LIMIT 20;

Deduplication

-- Find duplicate rows
SELECT email, COUNT(*) as cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Delete duplicates, keep only the oldest
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id) FROM users GROUP BY email
);

-- Or using CTE
WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

Date-Related

-- Today / this month / this year
SELECT * FROM orders WHERE created_at::date = CURRENT_DATE;
SELECT * FROM orders WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', NOW());

-- Daily stats for last 7 days
SELECT
    DATE(created_at) AS date,
    COUNT(*) AS orders,
    SUM(amount) AS revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY DATE(created_at)
ORDER BY date;

-- Hourly distribution
SELECT
    EXTRACT(HOUR FROM created_at) AS hour,
    COUNT(*) AS count
FROM orders
GROUP BY hour
ORDER BY hour;

Lock Considerations

-- SELECT FOR UPDATE (pessimistic locking)
BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE;  -- Other transactions wait
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- Optimistic locking (version column)
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- Only updates if version matches
-- Affected rows = 0 means someone else updated first!

Keyset Pagination

Explaining the pagination pattern above properly turns it into the section from this article you will reach for most often.

Why OFFSET degrades as you go deeper

One line from the documentation covers it: "The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient."

OFFSET 100000 does not skip 100,000 rows — it produces 100,000 rows and then throws them away. Page 1 needs 20 rows built; page 5001 needs 100,020. The cost grows linearly with the page number. A report that "the later pages of the list screen got slow" is almost always this.

Rewriting it as a seek

-- OFFSET method: page 5001 builds 100,020 rows and discards 100,000
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

-- Keyset (seek) method: continue from the values of the last row you saw
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-05-01 12:00:00', 84213)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- This index is what makes it worthwhile
CREATE INDEX idx_posts_created_id ON posts(created_at DESC, id DESC);

The key is the tie-breaker column . Ordering by created_at alone leaves rows with the same timestamp in an undefined order, so rows get skipped or duplicated at page boundaries. The documentation states this too: "When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows."

So put a unique column — usually the primary key — at the end of the sort key. That is what id is doing above. Row comparison (created_at, id) < (...) lets you compare both columns at once, and it matches the sort direction of the composite index.

How the plan differs

-- Example output: OFFSET method
 Limit  (cost=8421.55..8423.24 rows=20 width=48) (actual time=182.401..182.408 rows=20.00 loops=1)
   ->  Index Scan Backward using idx_posts_created_id on posts
         (cost=0.42..84210.33 rows=1000000 width=48)
         (actual time=0.028..170.552 rows=100020.00 loops=1)
 Execution Time: 182.443 ms

-- Example output: keyset method
 Limit  (cost=0.42..2.11 rows=20 width=48) (actual time=0.031..0.052 rows=20.00 loops=1)
   ->  Index Scan Backward using idx_posts_created_id on posts
         (cost=0.42..84210.33 rows=899980 width=48)
         (actual time=0.029..0.047 rows=20.00 loops=1)
         Index Cond: (ROW(created_at, id) < ROW('2026-05-01 12:00:00'::timestamp, 84213))
 Execution Time: 0.081 ms

Both plans use the same index. The difference is in the inner node's actual ... rows . The OFFSET version actually pulled up 100,020 rows; the keyset version pulled 20. The presence or absence of Index Cond is what makes that difference. When the condition is pushed into the index, it can jump straight to the starting point; without it, it has to count from the beginning.

What keyset costs you

It is not free. You cannot jump to an arbitrary page number. A "1, 2, 3 ... 5001" page-number UI is out; only "load more" or "next" works. Showing a total page count needs a separate COUNT query, which leads straight into a problem covered in the traps section below.

The decision rule is therefore simple: do users actually jump to deep pages? On most list screens nobody goes to page 5001. If so, keyset is right.

Locks and Schema Changes — Where the Real Danger Is

The lock section above only covers SELECT FOR UPDATE and optimistic locking. That is not where outages come from.

What an UPDATE or DELETE without WHERE does

Losing rows is not the only problem. UPDATE, DELETE, INSERT, and MERGE take a ROW EXCLUSIVE lock on the target table. That mode does not conflict with itself, so other writes proceed alongside. The problem is at the row level. An UPDATE without a WHERE takes a row lock on every row in the table, and every transaction that wants to touch those rows waits until yours ends. On a million-row table that is effectively the whole table stopping.

And ROW EXCLUSIVE conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE. So while that long UPDATE runs, a CREATE INDEX (SHARE lock) or most forms of ALTER TABLE (ACCESS EXCLUSIVE lock) cannot even start.

The real problem is the long transaction

Locks are released when the transaction ends. A five-minute transaction is a five-minute lock. And there is a second effect. For MVCC, PostgreSQL does not immediately remove the old version of a row on UPDATE or DELETE, because, as the documentation puts it, "the row version must not be deleted while it is still potentially visible to other transactions". If even one long-lived transaction is open, the dead rows piling up in the meantime cannot be cleaned, and tables and indexes keep bloating.

That makes several patterns dangerous: holding a connection open while a human thinks, calling an external API from inside a transaction, and batch jobs that process everything in one transaction.

Finding who is blocking

-- Sessions currently waiting, and the PIDs blocking them
SELECT
    a.pid,
    a.state,
    now() - a.xact_start AS xact_age,
    now() - a.query_start AS query_age,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocked_by,
    left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.backend_type = 'client backend'
ORDER BY xact_age DESC NULLS LAST;

pg_blocking_pids(integer) returns an array of the process IDs of the sessions blocking the given process from acquiring a lock, or an empty array if nothing is blocking it. It covers both a session that holds a conflicting lock (a hard block) and one that is waiting for a conflicting lock ahead of you in the queue (a soft block). The documentation warns that frequent calls can affect performance because the function needs brief exclusive access to the lock manager's shared state. This is not a function to call every second from a monitoring job.

Sorting by xact_age descending is deliberate. In most lock incidents the culprit is the oldest open transaction, and that transaction is usually not waiting on anything — it is sitting idle in transaction, doing nothing.

The rule that prevents deadlocks

The documentation's prescription is one sentence: "The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order."

There is a second half: "One should also ensure that the first lock acquired on an object in a transaction is the most restrictive mode that will be needed for that object." In other words, reading a row with a plain SELECT and upgrading the lock later when you UPDATE it is how deadlocks get made. Take it with SELECT ... FOR UPDATE from the start.

If you cannot verify this in advance, the documentation adds, handle deadlocks on the fly by retrying transactions that abort because of them. In practice you do both: fix the ordering, and retry the deadlocks that still happen.

Index builds — CREATE INDEX blocks writes

-- This takes a SHARE lock. Other transactions can read, but
-- INSERT / UPDATE / DELETE block until the index build finishes.
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- This takes a SHARE UPDATE EXCLUSIVE lock. It does not block writes.
CREATE INDEX CONCURRENTLY idx_orders_user_date ON orders(user_id, created_at DESC);

-- On failure an invalid index is left behind. psql reports it as INVALID under \d.
SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
FROM pg_index
WHERE NOT indisvalid;

-- The recommended recovery is to drop it and try again.
DROP INDEX CONCURRENTLY idx_orders_user_date;

CONCURRENTLY is not free. Per the documentation it must perform two scans of the table, and it must wait for all existing transactions that could potentially modify or use the index to terminate. So it takes considerably longer. It also cannot be performed within a transaction block, unlike a regular CREATE INDEX — a real obstacle when your migration tool wraps all DDL in a single transaction.

Know the failure behavior too. If a problem such as a deadlock or a uniqueness violation arises while scanning, the command fails but leaves behind an "invalid" index. That index is ignored for querying because it might be incomplete, yet it still consumes update overhead. In other words, writes get slower for no benefit. Check for them periodically with the query above, and when you find one, drop it and rebuild.

ALTER TABLE — the default is ACCESS EXCLUSIVE

This is the single most important line. The documentation says it outright: "An ACCESS EXCLUSIVE lock is acquired unless explicitly noted."

ACCESS EXCLUSIVE conflicts with every mode, including the ACCESS SHARE that a plain SELECT takes. So while an ALTER TABLE waits for its lock, every query that arrives behind it queues up. One DDL statement stopping an entire service is exactly this structure — one person stopping in a doorway and a line forming behind them.

These are the forms the documentation explicitly notes as taking a weaker lock.

• SET STATISTICS — SHARE UPDATE EXCLUSIVE
• Changing per-attribute options — SHARE UPDATE EXCLUSIVE
• Changing cluster options — SHARE UPDATE EXCLUSIVE
• VALIDATE CONSTRAINT — SHARE UPDATE EXCLUSIVE
• ADD FOREIGN KEY — SHARE ROW EXCLUSIVE (most forms of adding a table constraint need ACCESS EXCLUSIVE; this one is the exception)
• DISABLE / ENABLE TRIGGER — SHARE ROW EXCLUSIVE
• ATTACH PARTITION — SHARE UPDATE EXCLUSIVE on the parent table, plus ACCESS EXCLUSIVE on the table being attached and on the default partition

Whether the table gets rewritten is a separate question. Independently of the lock level, a rewrite means the lock is held for a long time.

• ADD COLUMN with DEFAULT — with a non-volatile default, the value is evaluated once and stored in the table's metadata, returned when existing rows are accessed. That makes the statement very fast even on large tables, with no rewrite required. Conversely, adding a column with a volatile default such as clock_timestamp(), a stored generated column, an identity column, or a column with a constrained domain type rewrites the entire table and its indexes.
• ALTER COLUMN TYPE — normally rewrites the entire table and its indexes. The exception is when the USING clause does not change the column contents and the old type is binary coercible to the new one. If the collation changes, an index rebuild is required because the sort order might differ; absent a collation change, text and varchar can be swapped without rebuilding indexes.
• SET NOT NULL — ordinarily scans the entire table to check. The scan is skipped if a valid CHECK constraint exists that proves no NULL can exist.

The safe pattern for adding a constraint

-- Bad: scanning the whole large table locks out every other update meanwhile
ALTER TABLE orders ADD CONSTRAINT orders_amount_positive CHECK (amount > 0);

-- Good, step 1: NOT VALID commits immediately (no table scan)
ALTER TABLE orders ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT VALID;

-- Good, step 2: validate later (takes only a SHARE UPDATE EXCLUSIVE lock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive;

The documentation explains the principle. Scanning a large table to verify new foreign-key, check, or not-null constraints can take a long time, and other updates to the table are locked out until the ALTER TABLE ADD CONSTRAINT command is committed. Reducing that impact is the main purpose of the NOT VALID option: with it, ADD CONSTRAINT does not scan the table and can be committed immediately.

Afterwards, VALIDATE CONSTRAINT verifies that existing rows satisfy the constraint. That validation step does not need to lock out concurrent updates, since the constraint is already being enforced for rows other transactions insert or update — only pre-existing rows need checking. Hence it acquires only a SHARE UPDATE EXCLUSIVE lock. If the constraint is a foreign key, a ROW SHARE lock is also required on the referenced table.

Failure Cases and Traps

1. A query that was fast yesterday is slow today

Symptom: neither the code nor the data volume changed much, yet response times jumped by several times.

Diagnosis order:

1. Capture the current plan. If you did not save the fast plan from yesterday, start saving them now.
2. Compare estimated rows against actual rows at each node. A large divergence means a statistics problem.
3. Run ANALYZE and take the plan again. If it returns to the old shape, the cause is confirmed.
4. If the plan is unchanged, look at whether the join order or scan method flipped. The data distribution may have crossed a threshold and the planner switched from a Nested Loop to a Hash Join, or the reverse. That is correct behavior; the real cause is usually a missing index or a condition that cannot use one.

For queries with bind parameters, a cached plan can be reused even though a different value would deserve a different plan. Substitute the parameter with a literal and run EXPLAIN to see immediately whether the plan differs.

2. SELECT * in a join blows up the row width

Symptom: the join returns few rows but the query is slow, and sorts spill to disk.

Diagnosis: look at width= in the plan. It is the estimated average byte size of the rows that node emits. Joining three tables and pulling everything with SELECT * drags along every column you do not need. Sort and hash nodes have to hold those rows in memory, so a wide row overflows work memory and starts using disk.

The fix: list only the columns you need. As a side effect this often makes a covering index possible — that is the Index Only Scan story from the index section.

3. A NULL in a NOT IN subquery returns nothing at all

The quietest and most dangerous trap. There is no error; you just get an empty result.

The cause, in the documentation's exact words: "Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the NOT IN construct will be null, not true. This is in accordance with SQL's normal rules for Boolean combinations of null values."

-- Dangerous: a single NULL in orders.user_id makes this return zero rows
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- Safe 1) rewrite with NOT EXISTS (recommended)
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Safe 2) if you must keep NOT IN, filter the NULLs out explicitly
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

NOT EXISTS only cares whether any rows are returned, so it does not trip over nulls. Reaching for NOT EXISTS by habit is the safer default.

4. Implicit type casts

Symptom: the column has an index, yet the plan shows a Seq Scan. The condition is simple.

Diagnosis: check whether a cast appears on the Filter: or Index Cond: line of the plan. If you see ::type — as in Filter: (status = 'active'::text) in the example plan above — a conversion happened. If the cast is on the column side, you are in the same situation as case 2 of "Why the Index You Created Is Not Being Used". If it is only on the constant side, it is usually fine.

The fix: make the parameter types your application sends match the column types. Sending a string to a numeric column, or a date to a timestamp column, are the common cases.

5. COUNT on a huge table

Symptom: the total-count display on a list screen makes the page slow.

The cause: an unqualified COUNT ultimately has to count the rows. Take the plan and you will see a sequential scan or a full index scan. That is why this problem shows up together with keyset pagination above.

Options:

1. Ask whether the total really needs to be shown. On most list screens nobody looks at an exact total; all you need is whether there is a next page. Fetch with LIMIT 21 and check whether a 21st row came back.
2. If an approximation is enough, use the planner's estimate — the top-level rows= in EXPLAIN output.
3. If you truly need the exact value and query it often, keep a separate counter table and maintain it. That is a schema design problem, not a query tuning problem.

When Not to Use This

The honest boundary for a cheatsheet.

• When you copy a snippet without reading the plan on your data. Every example here assumes particular table sizes and data distributions. On your data the same query takes a different plan. Running EXPLAIN ANALYZE once before you paste costs 30 seconds.
• When the answer is a schema change or a cache. If bad normalization forces a five-table join every time, denormalizing one column beats finding a cleverer query. If the same aggregate runs thousands of times a second, cache the result instead of fixing the query. Query tuning is the answer only when the query is the problem.
• Window functions and CTEs are readable, but not free. A window function may require a sort per partition, and if that sort exceeds work memory it spills to disk. CTEs likewise materialize intermediate results. Using them for readability is good, but keep "maybe it is slow because of the CTE" on your hypothesis list. The way to check is always the same: take the plan and see which node eats the time.
• When you carry one engine's syntax straight over to another. Exactly as described in the engine section above.

References

• Using EXPLAIN (PostgreSQL 18) — https://www.postgresql.org/docs/current/using-explain.html (verified 2026-08-16)
• Explicit Locking (PostgreSQL 18) — https://www.postgresql.org/docs/current/explicit-locking.html (verified 2026-08-16)
• ALTER TABLE (PostgreSQL 18) — https://www.postgresql.org/docs/current/sql-altertable.html (verified 2026-08-16)
• CREATE INDEX (PostgreSQL 18) — https://www.postgresql.org/docs/current/sql-createindex.html (verified 2026-08-16)
• LIMIT and OFFSET (PostgreSQL 18) — https://www.postgresql.org/docs/current/queries-limit.html (verified 2026-08-16)
• Routine Vacuuming (PostgreSQL 18) — https://www.postgresql.org/docs/current/routine-vacuuming.html (verified 2026-08-16)
• Subquery Expressions (PostgreSQL 18) — https://www.postgresql.org/docs/current/functions-subquery.html (verified 2026-08-16)
• System Information Functions (PostgreSQL 18) — https://www.postgresql.org/docs/current/functions-info.html (verified 2026-08-16)

Quiz