PostgreSQL Indexes That Help Most Projects, and Those That Slow Writes

PostgreSQL indexes are often added too late, after a query becomes slow, or too early, when a schema is still mostly theoretical. Both patterns create problems. Missing indexes turn common reads into table scans. Excessive indexes increase write latency, storage use, WAL volume, vacuum work, and cache pressure.

The practical question is not “Should this column be indexed?” It is “Does this index match a frequent access pattern closely enough to justify its cost on every insert, update, and delete?” That framing separates indexes most projects need from indexes that only make the database heavier.

The write cost is real

An index is not metadata. It is another data structure PostgreSQL must maintain.

When a row is inserted, every relevant index must receive a new entry. When an indexed column changes, the old index entry and the new one must be handled. When rows are deleted or updated, cleanup eventually has to deal with dead tuples. On busy systems, too many indexes can make the write path slower even when no read query benefits from them.

The cost is not only CPU. Indexes also affect:

• Disk usage

• WAL generation

• Replication throughput

• Cache residency

• Autovacuum workload

• Migration time when indexes are created or rebuilt

• Locking and operational risk during schema changes

The safest index is not the one that exists “just in case.” It is the one attached to a query pattern that actually runs often enough to pay for its maintenance cost.

This is why production indexing should start from query shape, not from column names.

B-tree indexes: the default for a reason

For most application workloads, the standard B-tree index is the first useful tool. It supports equality lookups, range filters, ordered scans, and many common join patterns.

Typical candidates are foreign keys, natural lookup keys, high-selectivity filters, and columns used in common pagination or retrieval paths.

CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders (customer_id);

CREATE INDEX CONCURRENTLY idx_users_email
ON users (email);

A B-tree index on customer_id helps queries such as:

SELECT id, status, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

But the simple customer_id index may not be enough if the query always orders by created_at. PostgreSQL can use the index to find rows for the customer, then still sort them. For small result sets that may be fine. For large customer histories, the sort can become the expensive part.

That is where composite indexes matter.

Composite indexes: order matters more than people expect

A composite index is useful when the same query filters or orders by multiple columns in a predictable sequence. The column order is not arbitrary.

For a query like this:

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

A better index may be:

CREATE INDEX CONCURRENTLY idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

This index supports the equality filters first, then the ordering. It can help PostgreSQL avoid scanning unrelated rows and may reduce or remove the need for a separate sort step.

The common mistake is creating several single-column indexes and expecting PostgreSQL to combine them into the same result as a well-designed composite index. PostgreSQL can combine indexes in some cases, but that is not the same as having a single index that matches the access path, especially when ordering and LIMIT are involved.

A useful rule of thumb:

1. Put stable equality predicates first.

2. Put range predicates after equality predicates.

3. Put ordering columns where they match the query’s ORDER BY.

4. Avoid adding columns that do not narrow, order, or satisfy the query.

Composite indexes become harmful when they are created for every possible column combination. They are strongest when they represent a few high-volume query paths.

Partial indexes: index the rows that matter

Many tables have rows that are rarely queried in the same way. For example, an application may frequently fetch open invoices, active subscriptions, pending jobs, or non-deleted records. Indexing the entire table for that access pattern can waste space and write effort.

A partial index limits the index to rows matching a condition.

CREATE INDEX CONCURRENTLY idx_invoices_open_by_account
ON invoices (account_id, due_date)
WHERE status = 'open';

This supports queries such as:

SELECT id, due_date, amount
FROM invoices
WHERE account_id = 1001
  AND status = 'open'
ORDER BY due_date ASC;

The benefit is operationally clear: closed invoices do not occupy this index, and updates to unrelated rows do not maintain it unless they enter or leave the indexed condition.

Partial indexes are especially useful for:

• Soft-delete patterns, such as deleted_at IS NULL

• Active-only records

• Pending or retryable background jobs

• Sparse boolean or status values where one subset is queried far more often

• Multi-tenant tables where active records dominate read paths

They are less useful when the predicate changes frequently or when queries are inconsistent. A partial index only helps when the query condition matches the index predicate closely enough for the planner to use it.

Unique indexes: correctness, not only performance

Unique indexes are often discussed as performance tools, but their primary role is data integrity. If a rule must be true in the database, enforce it in the database.

CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_unique
ON users (lower(email));

CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_unique

ON users (lower(email));

CREATE UNIQUE INDEX CONCURRENTLY idx_users_active_email_unique
ON users (lower(email))
WHERE deleted_at IS NULL;

This allows historical soft-deleted users to keep their old email values while preventing duplicate active accounts.

Unique indexes do add write overhead, and they can fail writes that violate the rule. That is the point. Use them for real business invariants, not as a casual optimization.

Indexes for ORDER BY: avoiding expensive sorts

An index can serve ordering only when its column order and direction fit the query. This matters for feeds, dashboards, audit logs, job queues, and pagination.

Consider:

SELECT id, title, published_at
FROM articles
WHERE tenant_id = 7
  AND status = 'published'
ORDER BY published_at DESC
LIMIT 30;

A useful index is:

CREATE INDEX CONCURRENTLY idx_articles_tenant_status_published_at
ON articles (tenant_id, status, published_at DESC);

This is different from indexing only published_at. The query first needs rows for one tenant and one status, then it needs them in descending publish order. The index should reflect that path.

This also explains why offset pagination becomes expensive at scale. Even with a good index, large offsets require PostgreSQL to walk past many rows before returning the page. Keyset pagination usually fits indexes better:

SELECT id, title, published_at
FROM articles
WHERE tenant_id = 7
  AND status = 'published'
  AND published_at < '2026-04-01 12:00:00'
ORDER BY published_at DESC
LIMIT 30;

The matching index can continue scanning from the previous boundary instead of repeatedly skipping an increasing number of rows.

Index types and when they usually pay off

The table is not a ranking. It is a reminder that each index should be tied to one or more observable query patterns.

Indexes that usually slow projects down

Some indexes look reasonable in schema reviews but rarely pay for themselves in production.

Indexing every foreign key without checking access patterns

Foreign keys often need indexes, especially when they are used in joins, deletes from parent tables, or common child lookups. But the reason is access pattern and constraint maintenance, not a blanket rule. In high-write systems, verify that the index supports real queries or parent-row operations.

Indexing low-selectivity columns alone

A standalone index on status, type, is_active, or is_deleted is often weak when the table contains large groups with the same value. It may still help in some distributions, but partial or composite indexes usually produce a cleaner access path.

Poor pattern:

CREATE INDEX idx_orders_status
ON orders (status);

Often better:

CREATE INDEX CONCURRENTLY idx_orders_pending_created_at
ON orders (created_at)
WHERE status = 'pending';

Duplicating prefixes

If you already have an index on (tenant_id, status, created_at), a separate index on (tenant_id) may or may not be needed. PostgreSQL can use the leftmost prefix of a B-tree composite index in many cases. Keeping both indexes only makes sense if the narrower index is materially better for important queries or maintenance.

Indexing columns that change constantly

Indexes on frequently updated columns can be expensive. If the column also has poor selectivity or weak query value, it becomes a write tax. Background job tables, counters, and mutable state columns deserve extra caution.

How to choose indexes in a real project

A practical indexing process is simple, but it requires discipline.

Start with the queries, not the schema. Identify the endpoints, jobs, reports, and admin screens that matter. Look at the WHERE, JOIN, ORDER BY, and LIMIT clauses together.

Then inspect actual execution plans with realistic data. A query that works on a development database with ten thousand rows may behave differently on a production table with years of history. The goal is not to force index usage. The goal is to understand whether the planner has a cheap path.

Use this checklist:

• Does the index match a frequent or latency-sensitive query?

• Does it support filtering, ordering, uniqueness, or all three?

• Is the leading column selective or required for tenant isolation?

• Does a wider existing index already cover the same path?

• Will the indexed columns be updated frequently?

• Can a partial index cover the hot subset instead of the whole table?

• Is the index needed for correctness, as with uniqueness?

• Can it be created safely with CONCURRENTLY in production?

For write-heavy systems, index review should be part of performance work, not just schema design. Removing unused or duplicate indexes can be as important as adding missing ones.

What most projects should adopt first

Most PostgreSQL applications benefit from a small, intentional index set:

• B-tree indexes for common joins and selective lookups

• Composite indexes for stable query paths that combine filters and ordering

• Unique indexes for business rules that must survive concurrency

• Partial indexes for hot subsets such as active, open, pending, or non-deleted rows

• ORDER BY-aware indexes for feeds, dashboards, and keyset pagination

That is enough for many systems. Specialized index types may be useful for full-text search, arrays, JSONB, geospatial data, or similarity search, but they should solve a specific problem. They are not a substitute for getting core B-tree indexing right.

For engineers who work with PostgreSQL schema design, query planning, and production data systems regularly, the PostgreSQL Architect certification is the most relevant DevCerts track to review.

Conclusion

PostgreSQL indexing is a production trade-off, not a decoration on the schema. The indexes that help most projects are attached to real query shapes: selective B-tree lookups, carefully ordered composite indexes, partial indexes for hot subsets, unique indexes for integrity, and indexes that match ORDER BY with LIMIT.

The indexes that slow projects down are usually speculative, duplicated, poorly ordered, or attached to columns that change often without improving important reads. Treat every index as a small piece of infrastructure with a maintenance cost. Add it when it protects correctness or gives the planner a better path for a query that matters. Remove it when it only makes writes heavier.