PostgreSQL Notes — Indexes

In databases, creating indexes can significantly improve query performance when dealing with large amounts of data. This article explores indexes in PostgreSQL. While using the standard CREATE INDEX syntax is sufficient for most everyday development scenarios, PostgreSQL also offers many different index types to choose from.

Introduction

Recently, while working on a small personal project requiring full-text search and location search features, I realized just how convenient PostgreSQL is. It has built-in full-text search, powerful GIS support (via PostGIS), trigger functions, handy window functions, hstore, and more. So while implementing these features, I decided to take some notes. Since it’s just a fun personal side project, adding indexes to such tiny datasets felt a bit comical, so there won’t be any rigorous benchmarks here.

Table of Contents

• Fundamentals of Indexing
• How to Create Indexes in PostgreSQL
• Caveats When Using Indexes
• How to Maintain Indexes
• Index Types in PostgreSQL
  • B-Tree
  • GIN
  • GiST
  • BRIN
  • Bloom

Fundamentals of Indexing

The most crucial function of a database is querying data. When data is stored on disk, we have to consider how to handle I/O performance bottlenecks. Due to hardware limitations, reading from a disk takes significantly more time than reading directly from memory. In a database, if no index is created on a table, it defaults to using sequential scans to find data. When the data volume is small, there is no noticeable performance difference—in fact, it might even be faster than using an index. However, as the data grows, sequential scans can easily lead to serious performance issues.

How Indexes Solve Slow Sequential Scans

By creating an additional index table on disk, we can use it like a book’s catalog or table of contents. When querying, the database searches the given index table for the corresponding entries, which then point to the physical data addresses on the disk.

B-Tree

A B-Tree is a self-balancing tree data structure specifically optimized for disk storage. Unlike a standard binary tree, a B-Tree can have many branches per node, effectively reducing tree depth. A detailed explanation of B-Trees would take quite a bit of space, so we’ll skip the deep dive here.

Indexes in PostgreSQL

Creating an Index

In PostgreSQL, you can create an index using standard SQL syntax:

CREATE INDEX index_name ON table_name (column_name) USING method;

In PostgreSQL, if no specific method is specified, it defaults to B-Tree. You can index a single column or create a composite index across multiple columns at once:

CREATE INDEX index_name ON table_name (col1, col2);

"Index Scan using index_subscribers_on_email on subscribers  (cost=0.28..8.29 rows=1 width=76)"
"  Index Cond: ((email)::text = 'xxx@yahoo.co.jp'::text)"

"Seq Scan on subscribers  (cost=0.00..37.38 rows=1 width=76)"
"  Filter: ((email)::text = 'xxx@gamil.co.jp'::text)"

We can use EXPLAIN to inspect query performance. As shown above, using a B-Tree index successfully reduced the query cost and time.

Caveats:

• Running CREATE INDEX locks writes on the entire table, which can take a considerable amount of time if the table contains a lot of data.
• You can add CREATE INDEX CONCURRENTLY to avoid locking the table against concurrent writes. However, to guarantee index integrity, CONCURRENTLY takes longer to build the index.

When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it’s done. There are several caveats to be aware of when using this option — see Building Indexes Concurrently.

source

When to Use Indexes

To illustrate when the database actually chooses to use an index, let’s create an index on a table that contains only two rows:

CREATE INDEX index_users_on_email ON users (email);
EXPLAIN SELECT email FROM users WHERE email = 'xxx@gmail.com';

"Seq Scan on users  (cost=0.00..1.02 rows=1 width=32)"
"  Filter: ((email)::text = 'xxx@gmail.com'::text)"

For small datasets, even if an index exists, the database query planner will still opt for a sequential scan.

This is because the overhead of random I/O is higher than sequential reads. Therefore, when the dataset is small, creating an index won’t improve query performance and only wastes disk space.

Index Size

Let’s create an index on a table with around 1,300 rows:

CREATE INDEX index_subscribers_on_email on subscribers (email);
SELECT pg_relation_size('index_subscibers_on_email')/1024 || 'K' AS size;//80K

We can inspect the size of the relation using pg_relation_size.

• Dropping an index: DROP INDEX IF EXISTS index_name.

To ensure index integrity, PostgreSQL acquires an exclusive lock on the table during index creation and releases it only once finished. For tables with frequent reads and writes, this could potentially cause service interruptions. For tables with huge row counts, building an index can take minutes or even tens of minutes.

Maintaining Indexes

https://wiki.postgresql.org/wiki/Index_Maintenance

First, let’s delete a few rows from the database. In databases, the default delete operation does not immediately reclaim disk space; instead, it marks tuples as “dead” or deleted. For indexes, deleting rows does not reduce the index size.

DELETE FROM subscribers WHERE id >1350;
SELECT pg_relation_size('index_subscibers_on_email')/1024 || 'K' AS size; // 80K

At this point, we can rebuild the index using REINDEX: REINDEX INDEX index_name

REINDEX INDEX index_subscibers_on_email;
SELECT pg_relation_size('index_subscibers_on_email')/1024 || 'K' AS size; // 72K

Rebuilding the index removes unused index pages, and the index size decreases.

As we can see, for tables with frequent inserts and deletes, periodically rebuilding indexes can reduce index size. If you need to delete a large amount of data, it is best to rebuild indexes to keep their size in check; otherwise, disk space will be wasted.

However, REINDEX also locks the table against writes. If downtime or maintenance windows are not an option, you can use CREATE INDEX CONCURRENTLY to build an entirely new index, drop the old index, and rename the new one. This takes longer and requires more manual steps than REINDEX, but it ensures that the table is never locked against concurrent writes.

Partial Indexes

Sometimes we only frequently query rows matching specific criteria. In such cases, there is no need to index the entire table. By using CREATE INDEX index_name ON table_name WHERE ..., we can create a partial index targeting only the rows we care about.

Index Sorting

When creating an index without specifying an order, it defaults to ascending order (ASC). However, in certain scenarios, we may query data sorted in descending order (DESC) far more often—such as leaderboards or article publication dates. In these cases, creating an index with DESC is much more efficient:

CREATE INDEX index_articles_on_published_date ON articles (published_date DESC);

Reindexing

B-Tree index pages are only reused when an index page is completely emptied. Consequently, deleting values in a database won’t shrink the index table size. In our example, after starting with 1,400 rows and deleting 50, the index size remained unchanged:

DELETE FROM subscribers WHERE id > 1350;

SELECT pg_relation_size('index_subscibers_on_email')/1024 || 'K' AS size; // 80K

Rebuilding the index:

REINDEX INDEX index_subscibers_on_email;
SELECT pg_relation_size('index_subscibers_on_email')/1024 || 'K' AS size;
72K

This cleans up unused index entries, freeing up disk space and preventing dead entries from bloating the disk. However, keep in mind that REINDEX also takes a table lock. If your service cannot afford downtime, consider creating a new index concurrently, dropping the original index, and renaming the new one.

Index Types in PostgreSQL

Note: The following sections have not been benchmarked.

Now that we’ve covered the basics of PostgreSQL indexes and how to use them, let’s explore common index types in PostgreSQL. If no method is specified during index creation, PostgreSQL defaults to B-Tree.

B-Tree

B-Trees support the following comparison operators:

• <
• <=
• =
• >=
• >

GIN (Generalized Inverted Index)

GIN is designed for handling cases where the items to be indexed are composite values, and the queries to be handled by the index need to search for element values that appear within the composite items.

GIN is primarily used for full-text search or data types like arrays. As the name suggests, an inverted index uses values as index keys to look up the locations where they appear.

For example:

this is cat
this is an apple.
cat meows.
// build inverted index
"this": {(0, 0), (1,0)}
"is": {(0,1), (1,1)}
"an": {(1,2)}
"apple: {(1,3)}
"cat": {(0,2), (2,0)}
"meows": {(2,1)}

Using the inverted index table above, if we want to search for the keyword cat, we can find the matching occurrences directly from the “cat” key: it appears as the second word of the first sentence and the first word of the third sentence.

Use cases:

• Full-text search
• Arrays

Using GIN in PostgreSQL:

CREATE INDEX index_on_document on sentences using gin(document);

GiST (Generalized Search Tree)

GiST is actually more of a generalized indexing framework. You can use GiST to implement custom index schemes (such as B-Trees, R-Trees, etc.). It is less commonly used directly for standard data, but for spatial data structures, B-Trees struggle to speed up queries like “contains”, “adjacent to”, or “intersects”. Therefore, extensions like PostGIS use GiST to build indexes with superior performance for spatial queries.

Use cases:

• Custom index implementations
• Spatial/geometric data structures
• Data structures where B-Trees do not fit the query patterns

BRIN (Block Range Indexes)

Unlike B-Trees, which index every single row, BRIN stores summary information for a range of physical disk blocks (pages). While B-Trees still outperform BRIN in raw query speed, BRIN indexes are vastly smaller in size.

If your data is naturally ordered and frequently queried by range—such as log files, transaction orders within a time window, billing records, etc.—the sheer volume of data can cause a B-Tree index to consume a huge amount of disk space. Since these workloads rarely perform exact point lookups and instead process data in ranges or batches, BRIN provides good performance while saving massive amounts of storage space.

Use cases:

• Log analysis
• Transaction order processing
• Huge datasets frequently queried by range

Bloom

A Bloom Filter is designed to solve hash table space and query time constraints. The algorithm can determine whether an element might be in a set very quickly, but it has false positives. Therefore, PostgreSQL must perform a secondary check on potential matches.

CREATE INDEX bloom_index_event ON events USING bloom (startTime, endTime, name, gifts)
 WITH (length=80, startTime=2, endTime=2, name=4, gifts=2);

length specifies the length of each signature bit array (default is 80, maximum is 4096). The subsequent parameters define how many bits each column will be mapped to (minimum 2, maximum 4096).

Use cases:

• Exact match queries across multiple columns: SELECT * FROM events WHERE startTime=20171111 and endTime=20171231 and name=christmas and gifts="gift_special". A bloom filter can quickly rule out records that are definitely not in the set.

Summary

In most cases, B-Tree indexes can handle the vast majority of scenarios. However, PostgreSQL provides a rich set of index types, making data easier to query and index while giving developers greater flexibility. This is one of the main reasons I love PostgreSQL.

1. PostgreSQL offers several indexing algorithms, each suited for distinct use cases.
2. CREATE INDEX locks the table against writes, which can be problematic for large tables. CREATE INDEX CONCURRENTLY avoids write-locks, but takes longer to complete.
3. Frequent updates and deletes lead to index bloat. Use REINDEX periodically to reclaim disk space, but keep in mind that REINDEX locks the table. To avoid locking, you can create a new index concurrently and drop/rename the old one.
4. CREATE UNIQUE INDEX can enforce uniqueness constraints on values.
5. Indexes can use a WHERE clause (partial indexes) to target specific subsets of data.
6. Indexes can specify sorting orders (default is ASC) to optimize specific use cases (e.g., article publication dates).
7. Indexes are not a silver bullet. For small tables, the planner will still choose sequential scans because random I/O overhead exceeds sequential scan costs. For static and small datasets (e.g., states/counties, zip codes), adding indexes won’t improve query performance and only consumes unnecessary disk space.