Offensive Security Researcher & Quantum Cryptography Analyst

How to Create Indexes in PostgreSQL

Abdul Wahab Junaid
How to Create Indexes in PostgreSQL

When I first started working with PostgreSQL, I treated indexes as an afterthought. My queries worked fine on small tables, so why bother? Then one day I ran a query against a table with two million rows and watched it take almost eight seconds to return a handful of results. That was the moment I actually sat down and learned how indexing works in PostgreSQL — and it changed how I design every table I build now.

In this guide, I’m going to walk you through everything I wish someone had explained to me when I started: what indexes actually are, how to create them, which index type to pick for which job, and the mistakes that quietly kill performance instead of helping it.

What Is an Index, Really?

Think of an index the same way you’d think of the index at the back of a textbook. If you want to find every page that mentions “mitochondria,” you don’t flip through all 400 pages — you check the index, find the page numbers, and jump straight there. A database index does exactly this for your tables.

Without an index, PostgreSQL has to perform a sequential scan — reading every single row in a table to check whether it matches your query’s condition. That’s fine for a table with 500 rows. It’s a disaster for a table with 50 million.

An index is a separate data structure, stored alongside your table, that keeps track of column values and where the corresponding rows live. When PostgreSQL sees a query that can use an index, it uses that structure to jump directly to the matching rows instead of scanning everything.

The trade-off — and this is important — is that indexes aren’t free. They take up disk space, and every time you insert, update, or delete a row, PostgreSQL also has to update the index. So indexing isn’t “more is always better.” It’s a balance.

The Basic Syntax

Here’s the fundamental syntax for creating an index in PostgreSQL:

CREATE INDEX index_name ON table_name (column_name);

Let’s say I have a table of customers:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(150),
    email VARCHAR(150),
    country VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

If I frequently search customers by email, I’d create an index like this:

CREATE INDEX idx_customers_email ON customers (email);

Now, when I run:

SELECT * FROM customers WHERE email = 'jane@example.com';

PostgreSQL can use idx_customers_email to locate that row almost instantly, instead of scanning the entire table.

Naming Convention I Use

I like to follow a consistent naming pattern: idx_<table>_<column>. It keeps things predictable when I’m scrolling through \di output in psql or checking a schema six months later and trying to remember why I created a particular index.

Creating Indexes on Multiple Columns

Sometimes a query filters on more than one column at once. In that case, a composite (multi-column) index can help:

CREATE INDEX idx_customers_country_created ON customers (country, created_at);

This is useful for a query like:

SELECT * FROM customers
WHERE country = 'Pakistan' AND created_at > '2025-01-01';

One thing that trips people up: column order matters in composite indexes. PostgreSQL can efficiently use a composite index if your query filters on the leftmost column(s) first. So idx_customers_country_created helps a query filtering on country alone, or country + created_at together — but it won’t help much for a query that filters on created_at alone.

Unique Indexes

If you want to enforce that a column (or combination of columns) never has duplicate values, use a unique index:

CREATE UNIQUE INDEX idx_customers_email_unique ON customers (email);

This does two things at once: it speeds up lookups on email, and it prevents PostgreSQL from allowing two rows with the same email. In fact, when you declare a column as PRIMARY KEY or UNIQUE in your table definition, PostgreSQL automatically creates a unique index behind the scenes — you don’t need to create one manually in that case.

Partial Indexes

This is one of my favorite PostgreSQL features, and it’s something a lot of beginners never discover. A partial index only indexes rows that match a specific condition:

CREATE INDEX idx_customers_active ON customers (email)
WHERE country = 'Pakistan';

I use partial indexes a lot when a table has a status column and most queries only care about “active” or “pending” rows. Instead of indexing the entire table, I index only the subset that actually gets queried often. This keeps the index smaller, faster to maintain, and cheaper on disk space.

A very common real-world use case:

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

If 95% of your orders are “completed” and only 5% are “pending,” but your dashboard constantly queries pending orders, this partial index is dramatically more efficient than indexing the whole table.

Expression Indexes

PostgreSQL also lets you index the result of an expression or function, not just a raw column. This is incredibly useful for case-insensitive searches:

CREATE INDEX idx_customers_lower_email ON customers (LOWER(email));

Now a query like this can actually use the index:

SELECT * FROM customers WHERE LOWER(email) = 'jane@example.com';

Without the expression index, PostgreSQL would have to compute LOWER(email) for every row during a sequential scan, defeating the purpose of indexing entirely.

Index Types in PostgreSQL

PostgreSQL supports several index types, and picking the right one matters as much as creating an index in the first place.

B-tree (Default)

CREATE INDEX idx_name ON table_name (column_name);

B-tree is the default and handles the vast majority of use cases: equality (=), range queries (<, >, BETWEEN), and sorting (ORDER BY). If you don’t specify a type, this is what you get.

Hash

CREATE INDEX idx_name ON table_name USING HASH (column_name);

Hash indexes are optimized purely for equality comparisons (=). They used to be riskier before PostgreSQL 10 (not crash-safe), but modern versions have fixed that. Still, in practice, B-tree is almost always the safer default choice unless you have a very specific reason to use hash.

GIN (Generalized Inverted Index)

CREATE INDEX idx_name ON table_name USING GIN (column_name);

GIN indexes shine with composite values — arrays, JSONB columns, and full-text search. If I’m indexing a JSONB column or building a full-text search feature, GIN is my go-to.

CREATE INDEX idx_products_tags ON products USING GIN (tags);

GiST (Generalized Search Tree)

CREATE INDEX idx_name ON table_name USING GIST (column_name);

GiST is common for geometric data types and range types — think PostGIS spatial queries or tsrange overlap checks.

BRIN (Block Range Index)

CREATE INDEX idx_name ON table_name USING BRIN (column_name);

BRIN indexes are lightweight and work well on huge tables where data is naturally ordered — like a created_at timestamp column on a table that only ever grows chronologically. They’re much smaller than B-tree indexes but less precise, trading a bit of lookup speed for a massive reduction in storage.

Creating Indexes Without Locking the Table

Here’s something that bit me early on: by default, CREATE INDEX takes a lock on the table that blocks writes while the index is being built. On a small table, that’s instant and unnoticeable. On a production table with millions of rows, that lock can hold up your application for minutes.

The fix is CONCURRENTLY:

CREATE INDEX CONCURRENTLY idx_customers_email ON customers (email);

This builds the index without blocking writes to the table. It takes longer overall and can’t run inside a transaction block, but it’s the safe way to add indexes to live production tables. I never run a plain CREATE INDEX on a production table anymore — CONCURRENTLY is now just muscle memory for me.

One caveat: if CREATE INDEX CONCURRENTLY fails partway through, it can leave behind an invalid index. You can check for these with:

SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indisvalid = false;

And clean them up with a plain DROP INDEX.

Verifying That Your Index Is Actually Being Used

Creating an index doesn’t guarantee PostgreSQL will use it. The query planner decides based on cost estimates, and sometimes a sequential scan really is cheaper — especially on small tables. To check, I always run EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email = 'jane@example.com';

If the index is being used, you’ll see Index Scan or Bitmap Index Scan in the output. If you see Seq Scan on a large table where you expected an index scan, something’s off — maybe the column has low selectivity (too many duplicate values), the table statistics are stale, or the query is structured in a way that prevents index usage (like wrapping the column in a function without an expression index).

Common Use Cases I Rely On

Troubleshooting Tips

My index exists, but the query is still slow. Run EXPLAIN ANALYZE first. If it’s doing a sequential scan, check whether your table statistics are outdated by running ANALYZE table_name;. PostgreSQL’s planner relies on statistics to estimate costs, and stale statistics can lead to bad decisions.

Too many indexes are slowing down my writes. Every index adds overhead to INSERT, UPDATE, and DELETE operations. If a table gets heavy write traffic, audit your indexes with:

SELECT indexrelid::regclass AS index, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

This shows indexes that have never been used — strong candidates for removal.

My composite index isn’t helping. Double check column order. Reorder so the most selective and most frequently filtered column comes first.

Index bloat after many updates/deletes. Over time, especially with heavy update traffic, indexes can bloat. Running REINDEX (or REINDEX CONCURRENTLY in PostgreSQL 12+) rebuilds them cleanly.

REINDEX INDEX CONCURRENTLY idx_customers_email;

Best Practices I Follow

  1. Index for your actual query patterns, not hypothetical ones. Look at your slow query log before adding indexes blindly.
  2. Use CONCURRENTLY in production, always.
  3. Don’t over-index write-heavy tables. Every index has a maintenance cost.
  4. Use partial indexes when queries consistently filter on a subset of data.
  5. Use expression indexes for functions you apply in WHERE clauses regularly (like LOWER()).
  6. Monitor unused indexes periodically and drop what you don’t need.
  7. Run EXPLAIN ANALYZE before and after adding an index to confirm it’s actually helping.
  8. Keep composite index column order aligned with your most common query filters.

Dropping an Index

If an index turns out to be unnecessary:

DROP INDEX idx_customers_email;

Or, safely on a production table:

DROP INDEX CONCURRENTLY idx_customers_email;

Understanding Index Storage and Cost

It’s worth spending a moment on what actually happens on disk when you create an index. PostgreSQL stores each index as its own separate physical structure, distinct from the table’s heap (the actual row data). A B-tree index on a single integer column of a 10-million-row table can easily add hundreds of megabytes to your database size. Multiply that by several indexes on the same table, and you can end up with an index footprint larger than the table itself.

This matters for a few practical reasons:

I usually check total index size per table with a query like this:

SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = 'customers';

This gives me a quick sanity check on whether a table’s indexing strategy has gotten out of hand.

Multi-Column Index Order — A Deeper Look

I touched on column order earlier, but it deserves more attention because it’s genuinely one of the most misunderstood aspects of indexing. Think of a composite B-tree index as a phone book sorted first by last name, then by first name. You can efficiently look up “everyone with last name Khan,” or “everyone with last name Khan and first name Ali.” But you cannot efficiently look up “everyone with first name Ali” without scanning the whole phone book, because the phone book isn’t organized that way.

The same logic applies to CREATE INDEX idx_orders_status_date ON orders (status, order_date). This index helps:

SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM orders WHERE status = 'pending' AND order_date > '2026-01-01';

But it does not meaningfully help:

SELECT * FROM orders WHERE order_date > '2026-01-01';

If you frequently query by order_date alone as well, you’d need a second, separate index just on order_date — a composite index doesn’t substitute for both access patterns at once.

Monitoring Index Health Over Time

Indexes aren’t a “set it and forget it” feature. I periodically review index health using PostgreSQL’s built-in statistics views. Beyond checking for unused indexes, I also look for bloated ones — indexes that have grown much larger than they should be due to heavy update/delete churn.

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

An index with a very low idx_scan count relative to how long it’s existed is a strong candidate for removal — it’s costing you write overhead and disk space without earning its keep on reads. I run this kind of audit roughly every few months on any table that sees heavy write traffic, since the query patterns an application actually uses tend to drift over time as features get added and removed.

Does PostgreSQL automatically index primary keys? Yes. Declaring a column as PRIMARY KEY automatically creates a unique B-tree index on it.

Does PostgreSQL automatically index foreign keys? No. You need to create that index manually if you’re joining or filtering on the foreign key column often.

How many indexes is too many on one table? There’s no fixed number — it depends on your read/write ratio. A read-heavy reporting table can carry many indexes comfortably. A write-heavy transactional table should stay lean, often five or fewer meaningful indexes.

Can I create an index on a JSONB column? Yes, using a GIN index: CREATE INDEX idx_data_gin ON table_name USING GIN (data); This lets you efficiently query JSONB with operators like @>.

Will an index help LIKE queries? A standard B-tree index helps with LIKE 'prefix%' patterns but not with LIKE '%suffix' or LIKE '%middle%'. For those, you’ll want a GIN index with the pg_trgm extension for trigram matching.

What’s the difference between CREATE INDEX and CREATE UNIQUE INDEX? A regular index speeds up lookups but allows duplicate values. A unique index does the same while also enforcing that no two rows share the same value.

Wrapping Up

Indexing in PostgreSQL isn’t something you set up once and forget. It’s an ongoing conversation between your query patterns and your table structure. Start by watching how your application actually queries data, index the columns that matter, verify with EXPLAIN ANALYZE, and periodically clean up what you no longer need. Once this becomes a habit, you’ll stop seeing those eight-second queries entirely — and start trusting your database to do what it’s actually built to do: return data fast, even at scale.

Exit mobile version