PostgreSQL: indexes, query plans, and correct transactions

A database can return the correct result slowly, or return an incorrect business outcome very quickly. Performance and concurrency need separate attention. This article follows an illustrative order dashboard and inventory operation to show how query shape, indexing, and transaction design fit together.

The SQL examples assume PostgreSQL and simplified tables. Test changes with realistic data and the PostgreSQL version used by your application.

Begin with the query the product actually needs

An order dashboard often asks for the newest orders belonging to one customer. Start with that access pattern rather than adding indexes to every column.

SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 25;

The secondary order on id gives a deterministic order when timestamps tie. A candidate index is:

CREATE INDEX orders_customer_created_id_idx
ON orders (customer_id, created_at DESC, id DESC);

The customer equality narrows the search, and the remaining keys match the requested ordering. Multicolumn B-tree indexes are generally most effective when the leading columns fit the predicates; the exact plan depends on data distribution and planner choices. See PostgreSQL’s multicolumn index documentation.

An index occupies space and adds maintenance work to writes. The objective is to support important access patterns, not maximize the number of indexes. An index useful for one customer’s recent orders may be a poor fit for a report grouped across all customers.

Read a plan as a description of work

Use a representative query with EXPLAIN (ANALYZE, BUFFERS) in a suitable test environment. ANALYZE executes the statement, so do not casually run it on a production write. Compare estimated and actual row counts, inspect repeated loops, and examine where rows are filtered out.

Planner cost values are not milliseconds. Actual node times and row counts need to be interpreted with their loop counts, and parent timings include work beneath them. Buffer statistics help distinguish kinds of data access. The official EXPLAIN guide explains those fields in detail.

Suppose a query returns 25 rows but scans a huge portion of the table. Ask whether the predicate and ordering fit the index. If the planner expected only a few matches but encountered thousands, investigate statistics and data skew before forcing a plan. A sequential scan can be the right choice when much of the table is needed.

Pagination is also an access-pattern decision

Large offsets require skipping earlier rows before producing a page. For a forward-only feed, a cursor based on the last ordering values can express the next page directly:

SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 25;

Here $2 and $3 are the last timestamp and ID from the preceding page. Assume both ordering columns are non-null and preserve their exact values in the cursor. Reapply the customer authorization and filters on every request. A cursor is navigation state, not permission to access an order.

Keyset pagination does not create an immutable snapshot across requests. New rows, deletions, or changes to ordering fields can still affect what the user sees. Decide whether the product needs a live feed or a stable exported snapshot.

Make the invariant explicit

Now consider an inventory rule: available quantity must never become negative. A naive implementation reads the quantity, checks it in application code, then writes a decremented value. Two concurrent requests can both observe the same initial stock and both conclude that a purchase is allowed.

Express the condition and mutation together:

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

Validate that $1 is a positive integer, make product_id unique, and interpret zero returned rows as “not available or not found.” A check constraint on nonnegative quantity provides another layer of protection. If an order row must be created with this decrement, execute both operations in one transaction and roll back if either fails.

This example solves a single-row inventory condition. It does not automatically solve invariants spanning multiple products, warehouses, or external payment systems.

Know what isolation promises

PostgreSQL’s default Read Committed mode gives each statement a snapshot of committed data at statement start. Two statements within one transaction can therefore observe different committed states. Stronger isolation changes which anomalies are possible; Serializable can reject a transaction when necessary to preserve a serializable outcome. Applications must be prepared to retry the entire transaction after serialization failure. These guarantees are documented in PostgreSQL’s isolation reference.

Keep transaction retries separate from external side effects. Sending a notification inside a transaction that later retries may send it twice. A transactional outbox can record the intended notification alongside the order, with a separate delivery worker and duplicate-aware processing.

Validate performance and correctness separately

Use a realistic dataset to compare query plans and latency. Then run concurrent inventory requests against a small stock count and check the final invariant. One test answers whether the operation is efficient; the other answers whether it remains correct under contention.

A good database change comes with a reason: this index supports this access pattern, this transaction protects this invariant, and these measurements demonstrate the expected behavior. That reasoning is more durable than copying a list of optimization tips.