PostgreSQL Indexes: Choose and Add Them Safely

When a query that used to be instant starts taking seconds, a missing or poorly chosen index is one of the first things to check. PostgreSQL indexes let the database find rows without reading the whole table, but they are not free: every index takes space and slows down writes. Adding one for every slow query leads to tables weighed down by indexes that are rarely used. This guide explains the main types of PostgreSQL indexes, how to choose columns and order, how to add an index without blocking a busy table and how to check that an index is actually helping.

What an index does and what it costs

The PostgreSQL documentation describes indexes as a common way to improve database performance, letting the server find and retrieve specific rows much faster than without an index, while warning that indexes also add overhead to the database system as a whole and should be used sensibly (PostgreSQL: Indexes).

The overhead comes from maintenance. Every insert, update or delete that touches an indexed column must also update the index. On a table that receives many writes, too many indexes slow every write and increase storage and backup size.

Types of PostgreSQL indexes

PostgreSQL offers several index types: B-tree, Hash, GiST, SP-GiST, GIN and BRIN, plus the bloom extension. The documentation says CREATE INDEX creates B-tree indexes by default, which fit the most common situations (PostgreSQL: Index types).

In practice:

• B-tree: the default, suited to equality and range comparisons and sorting on ordinary columns such as IDs, dates and status values.
• GIN: the documentation calls these inverted indexes, appropriate for values that contain multiple components, such as arrays. They are commonly used for full text search and JSONB.
• BRIN: block range indexes that store summaries of values in consecutive physical blocks, most effective for columns whose values are well-correlated with the physical order of rows, such as an append-only event table ordered by time.
• GiST and SP-GiST: frameworks for specialised data such as geometric and range types.

Start with B-tree unless you have a specific reason to use another type.

Choose columns from real queries

Indexes should follow how the application actually queries data. Look at the conditions in WHERE clauses, JOIN keys and ORDER BY columns of your slowest and most frequent queries. Our guide to API response time shows how to trace a slow endpoint down to the query responsible.

Columns that filter out most rows benefit most. An index on a column with only two values, such as a true/false flag, rarely helps on its own unless the query targets the rare value, in which case a partial index can be a better fit.

Multicolumn indexes and column order

A multicolumn index covers several columns in a defined order. The documentation explains that a multicolumn B-tree index can be used with conditions on any subset of its columns, but is most efficient when there are constraints on the leading, leftmost columns (PostgreSQL: Multicolumn indexes). It also advises that multicolumn indexes should be used sparingly, that a single-column index is sufficient in most situations and that indexes with more than three columns are unlikely to be helpful unless the table's usage is extremely stylised.

A useful rule is to put columns used for equality first and the column used for a range or sort last. An index on customer and created date, in that order, suits queries that fetch one customer's recent orders.

Partial and expression indexes

Two variations solve common problems efficiently:

• Partial indexes cover only rows that match a condition, such as open orders or rows not soft-deleted. They are smaller and cheaper to maintain.
• Expression indexes index the result of an expression, such as the lower-case form of an email address, so queries using that same expression can use the index.

Worked example: indexing an orders table

The table below is a hypothetical set of indexing decisions for an orders table behind a customer portal. It shows the reasoning from query to index, not tested settings.

Query patternExample conditionIndex decisionReasonCustomer's recent orderscustomer_id equals X, ordered by created_atB-tree on customer_id, created_atEquality column first, sort column secondOpen orders queuestatus is openPartial index on created_at where status is openSmall subset of rows; avoids indexing closed ordersLookup by emaillower(email) equals XExpression index on lower(email)Matches the query's expressionOrder line search in JSON attributesattributes contain a keyGIN index on the JSONB columnInverted index suits component valuesMonthly reporting on old orderscreated_at in a past monthConsider BRIN if rows are inserted in time orderSmall index for well-correlated data

Each decision should be confirmed with EXPLAIN before and after, and revisited if query patterns change.

Add indexes without blocking writes

On a busy production table, a normal index build can cause trouble. The CREATE INDEX documentation explains that a standard build locks out writes, but not reads, until it finishes, while the CONCURRENTLY option builds the index without taking locks that prevent concurrent inserts, updates or deletes (PostgreSQL: CREATE INDEX).

Concurrent builds have caveats. The documentation notes that they take longer, and that if a problem arises during the build, such as a deadlock or a uniqueness violation, the command fails but leaves behind an invalid index that is ignored for queries yet still adds update overhead. Check for invalid indexes after a failed build and drop or rebuild them.

Check that the index is used

Adding an index does not guarantee the planner will use it. Run EXPLAIN, or EXPLAIN ANALYZE in a safe environment, on the target query before and after, and confirm the plan changed and the time improved. If the index is not used, the table may be small, the condition may match too many rows or statistics may be out of date.

After a while in production, review index usage statistics and remove indexes that are never scanned. They cost writes and storage while providing nothing.

Indexes and replicas

If you send read traffic to physical replicas, remember that indexes built on the primary are replicated to them, so every index adds work on each copy of the data. Our guide to PostgreSQL read replicas covers how replicas fit into a performance plan alongside indexing.

Frequently Asked Questions

What is the default PostgreSQL index type?

B-tree. The documentation says CREATE INDEX creates B-tree indexes by default, and they fit the most common situations.

Can too many indexes slow down a database?

Yes. Every index must be updated on writes to its columns, which slows inserts and updates and increases storage. Remove indexes that are not used.

How do I add an index without locking the table?

Use CREATE INDEX CONCURRENTLY, which avoids blocking writes. It takes longer and can leave an invalid index if it fails, so check afterwards.

Does column order matter in a multicolumn index?

Yes. A multicolumn B-tree index is most efficient when queries constrain its leading columns, so order columns to match your most important queries.

Make slow queries fast again

We find the queries that matter, choose the right PostgreSQL indexes and roll changes out safely on live systems. See our custom app development services, or talk to us about PostgreSQL indexes.

About the Author

Manav Sharma is the founder of Insyrge and a CRM solutions architect. He works across Zoho, Salesforce, HubSpot, Odoo and more, designing the CRM setups, integrations and automations that keep sales and operations teams working from the same data.