Postgres for Application Developers: The Parts That Matter

The Heroku Dev Center guide gives a useful rule: an index is a way to retrieve a relatively small number of rows, and it is only worth it when the WHERE clause is selective. With that in mind, these are the index shapes application developers reach for most.

Three rules from the Heroku guide prevent most mistakes.

• Column order matters in a multi-column index. An index on (a, b) serves WHERE a = x AND b = y and WHERE a = x, but queries with WHERE b = y can't use an index on columns (a, b). Put the column you always filter on first.
• Match the sort order of paginated lists. An index such as published_at DESC NULLS LAST lets Postgres read rows already in order and stop after the limit, instead of reading everything and sorting.
• Build indexes on live tables concurrently. An ordinary CREATE INDEX blocks writes for the whole build, which can mean hours on a large table. The guide recommends CREATE INDEX CONCURRENTLY to avoid blocking writes during index creation. It takes longer but keeps the application running.

The same guide warns that testing index usage on a laptop with a small dataset is misleading. On a tiny table Postgres correctly decides that reading the whole table is cheaper than the random reads an index requires. Tune indexes against production data or a copy that matches it.

To find indexes you do not need, look for ones that are never scanned. Vikas Jindal's practical guide points to the pg_stat_user_indexes view and its idx_scan counter, and flags indexes with high writes and low reads as candidates to drop. An index with zero scans after weeks of normal traffic is pure write overhead.

EXPLAIN shows the plan the planner chose. EXPLAIN ANALYZE runs the query and adds what actually happened. The Postgres documentation on using EXPLAIN is the reference, but you can get most of the value from a few habits.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Kat Batuigas of Crunchy Data walks through the output in four parts: the tree structure of plan nodes, planning and execution times, estimated statistics, and actual run statistics. Read it this way.

1. Find the node with the most time. Each node reports actual time in milliseconds. Child nodes feed their parents, so the expensive work is often deep in the tree.
2. Compare estimated rows to actual rows. When the planner expected 10 rows and got 500,000, it probably chose the wrong join or scan type. Stale statistics are a common cause, and running ANALYZE on the table refreshes them.
3. Look for "Rows Removed by Filter". In Batuigas's example, a parallel sequential scan read more than two million rows to return one. That line tells you an index on the filtered column is missing.
4. Watch the loops count. A node with loops=5000 ran five thousand times. Multiply its time by its loops to see its true cost.

The thoughtbot guide adds a useful visual trick: redraw the plan as a tree of Sort, Hash Join, Seq Scan and Hash nodes so the structure is obvious before you look at numbers.

One warning applies to every write statement. Crunchy Data notes that EXPLAIN ANALYZE will actually run the query, so be careful with updates or deletes. Wrap those in a transaction and roll it back.

Isolation decides what one transaction can see of another that is running at the same time. The Postgres documentation lists the standard levels and the anomalies each allows, and notes that Read Committed is the default isolation level in PostgreSQL.

The default works for most code. It breaks on the read-then-write pattern: read a balance, check it in application code, then write a new balance. Two requests can read the same starting value and both write, and one update is lost.

There are two standard fixes.

Lock the row you are about to change. SELECT ... FOR UPDATE holds a lock on the selected rows until the transaction ends, so a second request waits. Nile's guide to isolation shows the classic job queue built this way, using SELECT job FROM job_queue FOR UPDATE SKIP LOCKED LIMIT 1 so that several workers can pull jobs without picking the same one.

Raise the isolation level and retry. Under Repeatable Read or Serializable, Postgres detects conflicts and aborts one transaction with a serialization error. The documentation is explicit that applications using this level must be prepared to retry transactions due to serialization failures, and that the retry should restart the whole transaction from the beginning. If your data access layer cannot retry a full transaction, use row locks instead.

A simpler option often beats both: make the update itself do the check. UPDATE accounts SET balance = balance - 100 WHERE id = 7 AND balance >= 100 is atomic, and the affected row count tells you whether it succeeded.

Heroku's PgBouncer guide describes what happens when a client connects without a pooler: the server forks a new system process to handle the connection. Each process holds memory, and every managed Postgres plan has a connection limit. Serverless functions and horizontally scaled web servers each open their own connections, so the limit arrives faster than most teams expect.

A connection pooler sits between the application and the database and shares a small number of real connections among many clients. ScaleGrid describes PgBouncer as a lightweight proxy between the PostgreSQL database and client applications that speaks the Postgres protocol, so the application connects to it like any database.

PgBouncer offers three pooling modes, and the choice changes what your code may do.

• Session pooling gives a client one server connection for as long as it stays connected. It supports every feature but saves the least.
• Transaction pooling gives a client a server connection only for the length of a transaction. It saves the most, and it is what most serverless setups use.
• Statement pooling disallows multi-statement transactions entirely.

The PgBouncer feature table lists what transaction pooling breaks: SET and RESET, LISTEN, SQL-level PREPARE, WITH HOLD cursors, and session-level advisory locks are marked "Never". If your code sets a session variable for row-level security or takes an advisory lock outside a transaction, it will behave unpredictably behind a transaction pooler. Use SET LOCAL inside a transaction and transaction-level advisory locks instead.

Application validation runs before the write and cannot see what a concurrent request is doing. Heroku's index guide uses Rails as the example: an application-level uniqueness check doesn't guarantee uniqueness because concurrent users can create invalid duplicate records. The fix is a unique constraint or unique index in the database.

The same reasoning applies to other rules.

• NOT NULL on every column that must have a value.
• Foreign keys so an order cannot point at a customer that does not exist. Index the referencing column too, since joins and deletes on the parent table look rows up by it.
• CHECK constraints for simple invariants such as quantity > 0.
• INSERT ... ON CONFLICT for upserts, which the transaction isolation documentation describes as guaranteed to either insert or update each proposed row under Read Committed.

Constraints are also documentation. A new developer reading the schema learns the rules of the domain without reading application code.

Use this list when a feature touches the database in a new way.

1. Every foreign key column has an index.
2. Every query on a hot path has been run through EXPLAIN (ANALYZE, BUFFERS) against realistic data.
3. Multi-column indexes put the always-filtered column first and match the sort order of paginated lists.
4. New indexes on existing tables are created with CONCURRENTLY.
5. Read-then-write logic either locks the row, does the check inside the UPDATE, or runs at a stricter isolation level with a full retry loop.
6. Uniqueness and required fields are enforced by constraints, not only by application validation.
7. The application connects through a pooler, and code that runs behind transaction pooling avoids session state.
8. Unused indexes are reviewed and dropped.

If your team keeps reusable engineering checklists like this one, Quanta Intelligence skills and playbooks are one way to package them so every developer and coding agent applies the same rules.