SQL Indexes, Query Plans, Transactions, and Isolation¶

A backend service can have correct SQL and still fail under production load: a query scans millions of rows, an index slows every write, a transaction reads a stale view, or two concurrent requests both pass an application-level check. Reliable data access requires understanding both the access path chosen by the planner and the concurrency rules enforced by the database.

This guide uses PostgreSQL 18 for concrete syntax and behavior. Indexing and transaction concepts transfer to other relational databases, but planner choices, index capabilities, lock behavior, and isolation implementations differ. Verify the equivalent behavior for the database and version you deploy.

1. Start with the workload, not an index¶

Indexes are additional data structures maintained alongside a table. They can make selective reads, joins, ordering, and some updates or deletes faster. They also consume storage and add work to inserts, updates, and deletes. An index is useful only when it supports a real query shape often enough to justify its lifecycle cost.

Consider a multi-tenant orders table:

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   bigint NOT NULL,
    customer_id bigint NOT NULL,
    status      text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    total_cents bigint NOT NULL CHECK (total_cents >= 0)
);

A common endpoint might execute this query:

SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

The useful questions are:

How many rows belong to one tenant?
How selective is the status predicate for that tenant?
Does the query need a stable ordering when timestamps tie?
How many rows are returned, and how many table pages must be visited?
How frequently are these rows inserted or updated?

A primary key index does not automatically make this query efficient. The database needs an access path that matches the filter and ordering.

2. Choose an index that matches the query¶

B-tree is the default for ordinary lookups¶

PostgreSQL creates B-tree indexes by default. They support equality and range comparisons, and can provide ordered output. They are a sensible starting point for identifiers, timestamps, status fields, and many join keys.

For the orders query above:

CREATE INDEX orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC, id DESC)
INCLUDE (total_cents);

The key columns reflect the query shape:

tenant_id and status are equality filters.
created_at DESC, id DESC provide the requested deterministic order.
total_cents is included as payload because the query returns it but does not search or sort by it.

The unique id tie-breaker matters. If multiple orders share the same timestamp, ordering by timestamp alone does not define a stable page boundary.

Composite index order matters¶

For a B-tree index on (a, b, c), equality constraints on leading columns and a range constraint on the first column without an equality condition are especially useful for limiting the scanned portion. Constraints on later columns may still be checked in the index, but do not always reduce how much of the index must be scanned.

A practical starting pattern is:

Put columns constrained by equality before the first range column.
Then consider range and ordering columns in the order required by the query.
Include payload columns only when a measured index-only scan benefit justifies their storage and write cost.

This is a workload-driven heuristic, not a universal column-order formula. PostgreSQL 18 can sometimes use B-tree skip scan when a leading column is unconstrained and has few distinct values. That optimization does not remove the need to design indexes around common predicates.

Partial indexes for a frequently queried subset¶

If a small subset of rows is queried frequently, a partial index can avoid indexing the rest:

CREATE INDEX orders_unpaid_recent_idx
ON orders (tenant_id, created_at DESC, id DESC)
WHERE status IN ('pending', 'payment_retry');

This can be smaller and cheaper to maintain than a full-table index when the predicate selects a genuinely small, stable portion of the workload.

The planner must be able to prove that the query predicate implies the partial-index predicate. PostgreSQL does not use a general theorem prover for arbitrary equivalent expressions, and a generic parameterized plan may not be able to prove a predicate such as status = $2. Verify prepared-statement behavior under the real plan mode; a partial index is not a guarantee that every parameterized version of a query will use it.

Partial indexes are also useful for enforcing uniqueness over a subset of rows:

CREATE UNIQUE INDEX one_active_subscription_per_customer
ON subscriptions (customer_id)
WHERE cancelled_at IS NULL;

This makes the invariant a database-enforced rule rather than a race-prone application check.

Expression indexes and sargable predicates¶

If an endpoint repeatedly performs case-insensitive lookup, an expression index can support the expression used by the query:

CREATE INDEX customers_lower_email_idx
ON customers (lower(email));

SELECT id
FROM customers
WHERE lower(email) = lower($1);

Functions and operators in PostgreSQL index expressions must be immutable: their result must depend only on their arguments, not on time, other tables, or changing external state.

Avoid wrapping an indexed timestamp in a function when a range predicate expresses the same business rule:

-- Often prevents a plain index on created_at from being used as a range condition.
WHERE date(created_at) = DATE '2026-10-31'

-- Usually a better shape for a B-tree index on created_at.
WHERE created_at >= TIMESTAMPTZ '2026-10-31 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-11-01 00:00:00+00'

Compute the range boundaries in the intended business time zone before binding them. The UTC example is only correct for a UTC calendar day.

Covering indexes and index-only scans¶

An index-only scan can avoid fetching table rows when all required values are present in the index and PostgreSQL can establish tuple visibility from the visibility map. It is not enough that the index contains the selected columns: recently modified heap pages may still require heap visits to check MVCC visibility.

The INCLUDE clause stores payload columns without making them search keys:

CREATE INDEX orders_recent_covering_idx
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (status, total_cents);

Be selective. Wide payload columns increase index size and write amplification. An index-only scan may not be a win on a frequently updated table whose heap pages are rarely all-visible.

3. Read a query plan before changing SQL¶

PostgreSQL chooses a plan using the query, table statistics, indexes, and cost settings. Use EXPLAIN to inspect the plan:

EXPLAIN
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

Use EXPLAIN ANALYZE to execute the query and compare estimates with observed execution:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

Read the plan from the leaf nodes upward. Child nodes produce rows consumed by their parent nodes. The following are common operators, not a ranking from good to bad:

Seq Scan: reads the table. This is often optimal for small tables or queries returning a large fraction of rows.
Index Scan: finds candidate tuple locations in an index and fetches rows from the heap.
Bitmap Index Scan / Bitmap Heap Scan: gathers tuple locations from one or more indexes, then visits heap pages in a more page-oriented order. It can be useful when many rows match.
Index Only Scan: can return values from the index when visibility information permits it.
Nested Loop: repeatedly probes the inner input for rows from the outer input. It can be excellent for a small outer result with a selective inner index.
Hash Join: builds a hash table from one input and probes it with the other. It is common for larger equality joins.
Merge Join: joins inputs ordered by the join keys; sorting or ordered indexes may be needed.
Sort: orders rows when no suitable ordering is already available. Check whether it spills to disk.
Aggregate / HashAggregate: computes grouped or global aggregates; inspect memory use and input cardinality.

A sequential scan is not automatically a performance bug. For example, reading most of a table through an index can require many scattered heap fetches and cost more than reading the table sequentially.

Understand the numbers¶

Plan field	How to interpret it
`cost=startup..total`	Planner cost units, not milliseconds
`rows`	Estimated output rows for the node
`width`	Estimated average output row width in bytes
`actual time`	Observed node time in milliseconds when ANALYZE is enabled
`actual rows`	Observed rows emitted by the node
`loops`	Number of node executions; actual time and rows are per-loop averages
`Buffers`	Buffer hits, reads, dirties, and writes for the node and its children

For nodes with multiple loops, multiply the per-loop time by loops to understand the node's cumulative time. Large differences between estimated and actual row counts are often more actionable than the absolute cost number: a bad cardinality estimate can cause the planner to choose a poor join order or join algorithm.

EXPLAIN ANALYZE executes the statement. For data-modifying statements, the changes and triggers run. A transaction followed by ROLLBACK can undo ordinary transactional table changes, but do not assume it reverses external effects performed by triggers or functions. Do not casually run modifying plans against production data.

The reported execution time does not include normal client network transfer, and instrumentation itself can add overhead. Test representative data volumes and parameter values, not only a tiny local fixture.

4. Keep planner statistics useful¶

The planner estimates how many rows each predicate and join will produce. PostgreSQL's ANALYZE collects approximate statistics used for these estimates. Autovacuum normally performs automatic analyze as data changes, but bulk loads, major distribution changes, or stale statistics may justify a manual analyze:

ANALYZE orders;

Inspect readable per-column statistics with pg_stats rather than querying the internal pg_statistic catalog directly:

SELECT
    attname,
    n_distinct,
    most_common_vals,
    most_common_freqs
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'orders';

A common estimation problem occurs when predicates on two columns are correlated. For example, some statuses may be disproportionately common for particular tenants. Single-column statistics cannot describe that relationship. PostgreSQL supports extended statistics:

CREATE STATISTICS orders_tenant_status_stats (dependencies, mcv)
ON tenant_id, status
FROM orders;

ANALYZE orders;

Do not create statistics objects for every column combination. Add them when plan evidence points to a correlation problem, then compare estimated and actual rows again.

For workload-level discovery, pg_stat_statements aggregates planning and execution statistics for normalized statements. It must be loaded through shared_preload_libraries (which requires a server restart), and the extension must be created in the database where its view is queried:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Use this to find high cumulative cost, then inspect individual query plans with representative parameters. High total time may come from a moderately slow query called very often; high mean time may point to a different class of problem. Protect query text and monitoring output because statements can contain sensitive literals depending on configuration and tooling.

5. Deploy indexes safely¶

A normal CREATE INDEX takes a lock that blocks writes to the table while the build runs, although reads can continue. For a production table where writes must continue, PostgreSQL supports:

CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC, id DESC);

Concurrent creation performs more work and takes longer. It cannot run inside a transaction block. If a concurrent build fails, it can leave an invalid index that is ignored for query planning but still consumes update overhead; inspect the index state and remove or rebuild the invalid artifact before assuming the index is usable.

A basic inspection query is:

SELECT
    indexrelid::regclass AS index_name,
    indisready,
    indisvalid
FROM pg_index
WHERE indexrelid = 'orders_tenant_status_created_idx'::regclass;

A successful DDL command is not the end of the review. Confirm the index is valid, check the intended query plan, monitor write latency and index size, and remove redundant or unused indexes through a reviewed migration.

6. Transactions: make invariants atomic¶

A transaction groups related database changes into one unit. Use database constraints for invariants the database can express, and use conditional writes to avoid application-level read-then-write races.

For inventory, this pattern is safer than first reading available into application memory and then writing a new value:

UPDATE inventory
SET available = available - $2
WHERE sku = $1
  AND $2 > 0
  AND available >= $2
RETURNING available;

Run it inside the same transaction as the order insert. The application must check that exactly one row was returned; zero rows means the SKU is absent, the quantity is invalid, or stock is insufficient. Do not insert the order when the conditional update matched no row.

BEGIN;

-- Execute the conditional UPDATE above using bound parameters.
-- If it returns no row, ROLLBACK and report the business conflict.

INSERT INTO orders (tenant_id, customer_id, status, total_cents)
VALUES ($3, $4, 'pending', $5);

COMMIT;

The check constraint available >= 0 should still exist on the inventory table as a last line of defense. The conditional update is the concurrency-safe decision point; the transaction makes the stock decrement and order creation atomic.

Never concatenate untrusted values into SQL. Use the database driver's parameter-binding API. The $1 notation above is PostgreSQL's positional parameter style for prepared statements.

7. MVCC and transaction isolation¶

PostgreSQL uses multiversion concurrency control (MVCC). A statement reads a snapshot of committed row versions rather than simply blocking behind every writer. This reduces read/write contention, but it does not mean every transaction sees the same snapshot or that every business invariant is automatically safe.

The isolation-level names are standardized, but implementations differ. PostgreSQL 18 behaves as follows:

Level	PostgreSQL behavior	Main consideration
Read Uncommitted	Behaves like Read Committed	PostgreSQL does not expose dirty reads
Read Committed (default)	Each statement gets a new snapshot	Two SELECTs in one transaction can see different committed data
Repeatable Read	Statements share a transaction snapshot; PostgreSQL also prevents phantoms	Serialization anomalies can still occur
Serializable	Successful transactions behave as if run one at a time	Transactions may be aborted and must be retried

Read Committed: statement-level snapshots¶

Under Read Committed, each statement sees data committed before that statement began, plus the transaction's own earlier writes. A later statement in the same transaction can see commits that were invisible to an earlier statement.

This is suitable for many ordinary CRUD operations. A single conditional UPDATE such as the inventory example is often safer than splitting the decision into a SELECT and a later UPDATE.

Repeatable Read: stable snapshot, not full serializability¶

Under PostgreSQL Repeatable Read, statements use a snapshot established by the transaction's first non-transaction-control statement. It prevents non-repeatable reads and, in PostgreSQL, phantom reads. However, it can still permit a serialization anomaly known as write skew.

Imagine two support engineers are on call, and the rule is that at least one must remain on call:

Transaction A reads that both engineers are on call.
Transaction B reads the same snapshot.
A turns engineer A off call.
B turns engineer B off call.
The transactions update different rows, so both can commit under snapshot-based Repeatable Read.
No engineer remains on call.

The individual row updates do not conflict, but the cross-row invariant is broken. A stable snapshot is not the same guarantee as serial execution.

Serializable: protect cross-row invariants¶

For logic whose correctness depends on a set of rows or predicates, Serializable can detect dangerous read/write dependency patterns and abort a transaction rather than allow a non-serializable result.

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT count(*)
FROM on_call
WHERE active;

-- If more than one engineer is active, deactivate this engineer.
UPDATE on_call
SET active = false
WHERE engineer_id = $1;

COMMIT;

This is illustrative transaction logic: the application must make the decision from the count returned by the query and only update when the invariant remains satisfied. If PostgreSQL reports SQLSTATE 40001 (serialization_failure), abort and retry the entire transaction from the beginning with a bounded retry policy and backoff. Do not retry only the final UPDATE using values read from an aborted snapshot.

Serializable does not remove the need for error handling. A transaction can fail at commit after its application logic has run. Do not perform irreversible external side effects before commit; use a transactional outbox or another durable hand-off when a successful database transaction must trigger work in another system.

8. Explicit locks, deadlocks, and transaction lifetime¶

MVCC does not eliminate locks. PostgreSQL uses table-level and row-level locks for conflicting operations and explicit coordination.

Use SELECT ... FOR UPDATE when a transaction must lock known rows before making a decision based on them:

BEGIN;

SELECT id, available
FROM inventory
WHERE sku = $1
FOR UPDATE;

-- Validate and update the locked row in this transaction.

COMMIT;

The lock is held until transaction end. Row locks block conflicting writers and lockers of the same rows; they do not block ordinary readers. Locking known rows is not a general substitute for Serializable isolation when the invariant concerns rows that may not exist yet or an entire predicate/range.

Deadlocks can happen when transactions acquire the same locks in different orders. For example, one transfer locks account A then B while another locks B then A. Reduce the risk by:

Acquiring multiple rows in a consistent, deterministic order across every code path.
Keeping transactions short and doing no user interaction or slow network calls while holding locks.
Locking only the rows needed for the invariant.
Setting sensible statement and lock timeouts for the service's latency budget.
Handling deadlock SQLSTATE 40P01 by retrying the whole transaction when the operation is safe to retry.

PostgreSQL detects deadlocks and aborts one participant; applications must not depend on which transaction is selected as the victim.

9. A practical diagnosis workflow¶

When a database-backed endpoint becomes slow or incorrect, work in this order:

Find the workload. Use application traces and pg_stat_statements to identify high-total-time and high-mean-time statements.
Capture representative parameters. Tenant size, status distribution, date range, and result limit can change the plan.
Inspect the plan. Compare estimated and actual rows, loops, buffer activity, sorts, join strategy, and rows removed by filters.
Check statistics. Run ANALYZE after major distribution changes; consider extended statistics for correlated predicates.
Change one thing. Rewrite a non-sargable predicate, add a targeted index, or change the query shape—not all at once.
Measure read and write effects. Check latency, throughput, index size, insert/update cost, and contention.
Review correctness under concurrency. Identify the invariant, choose constraints/conditional writes/row locks/Serializable appropriately, and test competing transactions.
Deploy safely. Use concurrent index creation where appropriate, verify index validity, and keep rollback steps ready.

The repository includes a PostgreSQL 18 smoke-test script for the index, planner, and conditional-update examples. CI runs it against a real PostgreSQL service; it intentionally does not assert one exact plan shape because plans depend on data distribution and server cost settings.

10. Interview questions¶

Q1. Why can a sequential scan be faster than an index scan?¶

Answer: If a query returns a large fraction of the table, an index scan can cause many scattered heap fetches. A sequential scan reads pages more efficiently. The planner compares estimated costs; an index is not always the cheapest path.

Q2. How do you design a composite index for a tenant-scoped feed?¶

Answer: Start from the actual WHERE and ORDER BY clauses. Put tenant and other equality predicates first, then the range/order keys in the required order, with a unique tie-breaker. Confirm the plan and write overhead using representative data. PostgreSQL 18 skip scan can help some queries with missing leading keys, but should not replace workload-based design.

Q3. What is the difference between Index Cond and Filter in a plan?¶

Answer: Index Cond is applied by the index access method to find candidate entries. Filter is applied after rows reach the plan node. A selective condition appearing only as a Filter may mean the index does not narrow the candidate set, although the best remedy depends on the whole plan.

Q4. Why might the planner ignore an index that exists?¶

Answer: The query may return too many rows, the table may be small, the predicate may not match the index operator/expression, statistics may be stale, or the index may not provide the needed ordering. The planner chooses an estimated cheapest plan, not an index merely because one exists.

Q5. What does a large estimated-versus-actual row mismatch suggest?¶

Answer: The planner's cardinality model may be wrong because statistics are stale, the data distribution is skewed, or columns are correlated. Refresh statistics and consider targeted extended statistics before forcing plan settings.

Q6. Why are too many indexes harmful?¶

Answer: Every relevant insert, delete, and many updates must maintain index structures. Indexes consume disk and cache, can increase write amplification, and may reduce opportunities for heap-only tuple updates. Keep indexes tied to measured query workloads.

Q7. Does Repeatable Read guarantee serializable execution in PostgreSQL?¶

Answer: No. It gives a stable transaction snapshot and prevents phantom reads in PostgreSQL, but serialization anomalies such as write skew remain possible. Use Serializable or explicit coordination for invariants that require serial behavior.

Q8. What should an application do after SQLSTATE 40001?¶

Answer: Roll back and retry the entire transaction from the beginning, with bounded retries and backoff. Reusing values from the failed snapshot can preserve the very anomaly the retry is meant to avoid.

Q9. Does SELECT FOR UPDATE lock every row that might later match a predicate?¶

Answer: No. It locks rows actually selected. It does not generally prevent a concurrent transaction from inserting a new row that would match the predicate. Use a constraint, a suitable serialization strategy, or another explicit design for range-wide invariants.

Q10. What is the production trade-off of CREATE INDEX CONCURRENTLY?¶

Answer: It allows ordinary writes to continue during the build, but performs more work, takes longer, cannot run inside a transaction block, and can leave an invalid index if it fails. Verify validity and monitor the extra I/O load.

11. References¶

The examples and PostgreSQL-specific behavior were checked against the PostgreSQL 18 documentation:

Index type	Typical fit	Important limitation
B-tree	Equality, ranges, ordered scans, common joins	Not automatically best for low-selectivity predicates
Hash	Equality comparisons	Does not support range ordering
GIN	Multi-component values such as arrays, JSONB containment, and full-text search	Larger/write-heavier in many workloads; does not provide ordinary ordered output
GiST / SP-GiST	Geometric, range, nearest-neighbour, and other operator-class-specific searches	Behavior depends on the operator class and whether the index is lossy
BRIN	Very large tables where values correlate with physical row order, such as append-heavy time-series data	Summarizes block ranges; less precise than a B-tree and may require rechecks