KAHIBARO
Discord Login Register ☰
☰

11.8 Indexes

Table of Contents

Why Indexes Matter in PostgreSQL

Indexes are one of the most powerful tools to make your PostgreSQL queries fast. They can turn a query that takes seconds into one that runs in milliseconds. As a backend developer, you will use indexes all the time, especially when your tables grow large.

This chapter focuses on how indexes work in PostgreSQL, the main types you will use, and practical examples relevant to backend applications. It assumes you already know basic SQL and what a table, column, and query are.

Important: Indexes speed up reads but have a cost on writes and storage.
You should index carefully, not "everything."

How PostgreSQL Uses Indexes

Sequential Scan vs Index Scan

When PostgreSQL executes a query, it chooses a "plan" for how to find the needed rows. For example, for:

sql 
SELECT * FROM users WHERE email = 'alice@example.com';

PostgreSQL can:

  1. Sequential scan
    Read every row in users and check the email value.
    Works fine for small tables, becomes slow for large ones.
  2. Index scan
    Use an index on email to jump directly to matching rows, then fetch them from the table.

You can see the plan with EXPLAIN:

sql 
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

Typical outputs:

Plan typeDescription
Seq ScanSequential scan, reads the whole table.
Index ScanUses an index to find matching rows.
Index Only ScanUses an index and may skip touching the table if index has all needed data.

PostgreSQL decides automatically when to use an index based on statistics about the table. Creating an index does not force it to be used, but it makes index scans possible.

Creating and Dropping Indexes

Basic Index Creation

To create a simple index:

sql 
CREATE INDEX idx_users_email ON users (email);

Pattern:

sql 
CREATE INDEX index_name ON table_name (column1, column2, ...);

Guidelines:

Example: a backend with a users table:

sql 
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    username    TEXT NOT NULL UNIQUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Even though email and username are UNIQUE, PostgreSQL already creates indexes for those. You do not need to create them again.

You might still add an index on created_at for queries like:

sql 
SELECT * FROM users
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

Index:

sql 
CREATE INDEX idx_users_created_at ON users (created_at);

Dropping Indexes

If an index is not used, it wastes disk and slows writes. Drop it:

sql 
DROP INDEX idx_users_created_at;

Note:

sql 
  ALTER TABLE users DROP CONSTRAINT users_email_key;

which also drops the associated index.

Use \d table_name in psql to list indexes on a table:

sql 
\d users

You will see lines like:

text 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
    "idx_users_created_at" btree (created_at)

B-tree Indexes (The Default Type)

What B-tree Indexes Are Good For

By default PostgreSQL creates B-tree indexes. They are balanced tree structures that keep keys sorted and allow fast search.

B-tree indexes are ideal for:

Common backend examples:

Example index:

sql 
CREATE INDEX idx_orders_user_id_created_at
ON orders (user_id, created_at DESC);

This index can speed up queries like:

sql 
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

PostgreSQL can use the index to find all rows for user_id = 42 and already has them sorted by created_at DESC.

Rule: Start B-tree index columns with the most selective and most frequently used conditions.
For example WHERE user_id = ? AND created_at >= ?, index as (user_id, created_at), not (created_at, user_id).

Operator Support

B-tree indexes support many operators, including:

For most "normal" querying on numbers, timestamps, and text, B-tree is the default and correct choice.

Other Index Types: GiST, GIN, and Hash

PostgreSQL supports several index types. You select one with USING:

sql 
CREATE INDEX idx_name ON table_name USING index_type (column);

You will mostly use B-tree, but for certain patterns other types are better.

Hash Indexes

Hash indexes support equality comparison:

sql 
CREATE INDEX idx_users_email_hash
ON users USING hash (email);

Historically they had limitations. Modern PostgreSQL improved them, but B-tree usually works just as well or better, since B-tree also supports equality.

In practice for backend work:

GiST Indexes

GiST stands for "Generalized Search Tree". It is a flexible index type that supports:

Example: range type on a table of promotions:

sql 
CREATE TABLE promotions (
    id         BIGSERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    active_during TSRANGE NOT NULL  -- timestamp range
);

You can index the range:

sql 
CREATE INDEX idx_promotions_active_during
ON promotions USING gist (active_during);

Then queries like:

sql 
SELECT *
FROM promotions
WHERE active_during @> NOW();

can use the GiST index to find promotions whose active period contains the current time.

GiST indexes are useful when you use specialized data types that come with GiST operator classes.

GIN Indexes

GIN stands for "Generalized Inverted Index". It is designed for:

Example: tags as an array:

sql 
CREATE TABLE articles (
    id      BIGSERIAL PRIMARY KEY,
    title   TEXT NOT NULL,
    tags    TEXT[] NOT NULL
);

You want to query:

sql 
SELECT *
FROM articles
WHERE tags @> ARRAY['python'];  -- articles that include 'python' tag

Create a GIN index:

sql 
CREATE INDEX idx_articles_tags_gin
ON articles USING gin (tags);

Another common backend use case is indexing JSONB fields, which we will revisit in the JSON Data chapter. For example:

sql 
CREATE TABLE events (
    id        BIGSERIAL PRIMARY KEY,
    payload   JSONB NOT NULL
);
CREATE INDEX idx_events_payload_gin
ON events USING gin (payload);

Then queries like:

sql 
SELECT *
FROM events
WHERE payload ? 'user_id';  -- JSON key existence

can use the GIN index.

Guideline:

  • Use B-tree for simple columns with equality, ranges, ordering.
  • Use GIN for arrays, JSONB, and full-text search.
  • Use GiST for ranges and geometric or specialized data types.

Single-Column vs Multicolumn Indexes

Single-Column Indexes

A single-column index:

sql 
CREATE INDEX idx_users_email ON users (email);

This helps queries that filter or sort on only email:

sql 
SELECT * FROM users WHERE email = 'alice@example.com';

However, if your query is:

sql 
SELECT * FROM users
WHERE email = 'alice@example.com'
  AND is_active = TRUE;

PostgreSQL might use:

Multicolumn Indexes

Multicolumn indexes store combinations of columns in a single index:

sql 
CREATE INDEX idx_users_is_active_email
ON users (is_active, email);

This index helps when your queries filter using the leading column and possibly additional columns:

sql 
  WHERE is_active = TRUE
sql 
  WHERE is_active = TRUE AND email = 'alice@example.com';

However, it does not help if you filter only by email:

sql 
-- index on (is_active, email) is not very useful for this
WHERE email = 'alice@example.com';

Important rule:

In a multicolumn B-tree index (a, b, c):

  • PostgreSQL can efficiently use it for queries on:
    • a
    • a, b
    • a, b, c
  • It cannot fully use it for queries that only specify b or c without a.

Backend Example

Suppose you have an orders table:

sql 
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    status      TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Common queries in your API:

sql 
-- 1. List recent orders for a user
SELECT * FROM orders
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;
-- 2. List orders by status and date range
SELECT * FROM orders
WHERE status = $1
  AND created_at >= $2
  AND created_at < $3;

Possible indexes:

sql 
CREATE INDEX idx_orders_user_id_created_at
ON orders (user_id, created_at DESC);
CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at);

Each index is designed around the pattern of filters and ordering.

Do not blindly create multicolumn indexes with many columns. They are larger and more expensive to maintain. Focus on the real query patterns your application uses.

Covering Indexes and Index Only Scans

What Is a Covering Index?

A covering index is an index that contains all the columns a query needs. If all selected columns are inside the index, PostgreSQL may use an Index Only Scan, which avoids reading the table rows in many cases.

Example:

sql 
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    username    TEXT NOT NULL UNIQUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Add an index:

sql 
CREATE INDEX idx_users_email_created_at
ON users (email, created_at);

Now consider:

sql 
SELECT email, created_at
FROM users
WHERE email = 'alice@example.com';

The index already has email and created_at. PostgreSQL can:

You can check with:

sql 
EXPLAIN ANALYZE
SELECT email, created_at
FROM users
WHERE email = 'alice@example.com';

Look for Index Only Scan in the output.

Includes: Adding Non-key Columns

PostgreSQL supports INCLUDE to add additional columns to an index as non-key columns. This can make covering indexes without affecting ordering and size too much.

Example:

sql 
CREATE INDEX idx_orders_user_id_created_at_include_status
ON orders (user_id, created_at DESC)
INCLUDE (status);

Here:

This can help queries like:

sql 
SELECT user_id, created_at, status
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;

All needed columns are in the index, so PostgreSQL can use an Index Only Scan and avoid touching the table for most rows.

Partial Indexes

What Is a Partial Index?

A partial index is built on only a subset of rows that satisfy a condition. This can:

Pattern:

sql 
CREATE INDEX index_name
ON table_name (column1, column2, ...)
WHERE condition;

PostgreSQL will only use this index for queries where it can prove that the query condition implies the index condition.

Backend Example: Active Users

Suppose most users are inactive or soft deleted, and you always filter WHERE is_active = TRUE.

sql 
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE
);

Instead of indexing all rows:

sql 
CREATE INDEX idx_users_email ON users (email);

You can index only active users:

sql 
CREATE INDEX idx_users_email_active
ON users (email)
WHERE is_active = TRUE;

Now the index only contains active users, and queries like:

sql 
SELECT *
FROM users
WHERE email = 'alice@example.com'
  AND is_active = TRUE;

can use a smaller index.

But this query:

sql 
SELECT *
FROM users
WHERE email = 'alice@example.com';

might not use the partial index, because the query does not guarantee is_active = TRUE.

Rule: Partial indexes are only used if the query's WHERE clause logically implies the index WHERE condition.

Backend Example: Recent Data Only

Suppose you have a logs table that grows quickly, but your API only filters on recent logs, for example last 7 days:

sql 
CREATE TABLE logs (
    id          BIGSERIAL PRIMARY KEY,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    level       TEXT NOT NULL,
    message     TEXT NOT NULL
);

Your queries:

sql 
SELECT *
FROM logs
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 100;

You can create a partial index:

sql 
CREATE INDEX idx_logs_recent_created_at
ON logs (created_at DESC)
WHERE created_at >= NOW() - INTERVAL '30 days';

This indexes only logs from the last 30 days, which is where you expect most queries to focus. Old rows will not be in the index, but you probably do not query them often.

Expression Indexes

Indexing Computed Values

You do not have to index only plain columns. PostgreSQL lets you index expressions, for example LOWER(email).

Pattern:

sql 
CREATE INDEX index_name
ON table_name (expression);

This is useful when your queries use expressions in the WHERE clause.

Backend Example: Case-insensitive Email Search

Many applications want to treat emails as case insensitive:

sql 
SELECT *
FROM users
WHERE LOWER(email) = LOWER('Alice@example.com');

If you only index email:

sql 
CREATE INDEX idx_users_email ON users (email);

PostgreSQL might not use this index for the LOWER(email) query, because the expression changes the column.

Instead, create an expression index:

sql 
CREATE INDEX idx_users_lower_email
ON users ((LOWER(email)));

Note the double parentheses. Now queries that use LOWER(email) can use this index:

sql 
SELECT *
FROM users
WHERE LOWER(email) = LOWER('alice@example.com');

This is a very common technique for case-insensitive uniqueness and lookup. If you also want to enforce uniqueness in a case-insensitive way, you can use a unique index on the expression:

sql 
CREATE UNIQUE INDEX users_email_ci_unique
ON users ((LOWER(email)));

Now PostgreSQL will prevent inserting two users with emails that differ only by case.

Other Expression Examples

Some useful patterns:

sql 
  CREATE INDEX idx_orders_created_date
  ON orders ((created_at::date));

For queries like:

sql 
  SELECT COUNT(*)
  FROM orders
  WHERE created_at::date = CURRENT_DATE;
sql 
  CREATE INDEX idx_events_user_id
  ON events ((payload->>'user_id'));

For queries like:

sql 
  SELECT *
  FROM events
  WHERE payload->>'user_id' = '123';

Expression indexes are powerful but can be misused. Always make sure:

Practical Indexing Strategies for Backend Applications

What to Index

You should consider creating indexes for:

  1. Primary key and unique constraints
    PostgreSQL already does this automatically.
  2. Foreign keys
    If you have:
sql 
   CREATE TABLE orders (
       id       BIGSERIAL PRIMARY KEY,
       user_id  BIGINT NOT NULL REFERENCES users(id)
   );

It is usually a good idea to index the foreign key column:

sql 
   CREATE INDEX idx_orders_user_id ON orders (user_id);

This speeds up queries that get all orders for a user, and speeds up deletes/updates on the parent table when checking references.

  1. Columns used frequently in WHERE clauses
    For example:
    • WHERE email = ?
    • WHERE status = ?
    • WHERE created_at >= ?
  2. Columns used in JOIN conditions

For example:

sql 
   SELECT *
   FROM orders
   JOIN users ON orders.user_id = users.id;

Here indexing orders.user_id and having users.id as primary key helps.

  1. Columns used for sorting with filter

For example:

sql 
   SELECT *
   FROM orders
   WHERE user_id = ?
   ORDER BY created_at DESC
   LIMIT 20;

This motivates an index (user_id, created_at DESC).

What Not to Index

Avoid indexing:

Each index:

Using EXPLAIN to Check Index Usage

Use EXPLAIN to see whether a query uses an index:

sql 
EXPLAIN
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Look at the output:

For more accurate information use:

sql 
EXPLAIN ANALYZE
SELECT ...

This actually runs the query and shows real timings, which is very helpful in development and testing, but be careful with huge tables in production.

Recap

In PostgreSQL, indexes are crucial to making your backend queries fast and scalable:

In real backend projects you will often iterate:

  1. Write your API queries.
  2. Observe slow queries.
  3. Use EXPLAIN ANALYZE to see what is happening.
  4. Add or adjust indexes based on the query patterns.
  5. Remove unused indexes over time.

Understanding these patterns will let you design PostgreSQL schemas that remain fast as your application and its data grow.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3156 / 24h
Impressum / Imprint
Datenschutz / Data Policy