Database Indexing – Part 4: Covering vs. Composite Indexes in PostgreSQL – Which One Should You Use?

In my previous articles, we explored covering indexes and composite indexes separately.

Both indexes can improve query performance, but how do they differ in terms of storage, INSERT, UPDATE, and SELECT performance?

To understand this, I tested both index types in PostgreSQL using two different datasets: one with unique values in the first indexed column and another with duplicate values.

In this article, we'll compare the results of these experiments and understand when to use covering and composite indexes in real-world applications.

Experiment 1: Covering vs. Composite Indexes with Unique Key Values

To compare covering and composite indexes, we'll perform a few experiments in PostgreSQL using 10 million records.

We'll use two separate tables with identical structures and data, so we can test both index types independently.

1. Creating the Tables

CREATE TABLE documents_covering ( document_id BIGSERIAL PRIMARY KEY, document_key VARCHAR(100), document_version VARCHAR(200) ); CREATE TABLE documents_composite ( document_id BIGSERIAL PRIMARY KEY, document_key VARCHAR(100), document_version VARCHAR(200) ); 

2. Inserting 10 Million Records

First, let's populate the covering-index table.

INSERT INTO documents_covering (document_key, document_version) SELECT 'DOC_' || LPAD(g::TEXT, 96, '0'), 'VERSION_' || LPAD(g::TEXT, 192, '0') FROM generate_series(1, 10000000) AS g; 

Now, copy the same records into the composite-index table.

INSERT INTO documents_composite (document_id, document_key, document_version) SELECT document_id, document_key, document_version FROM documents_covering; 

3. Creating the Indexes

Covering Index:

CREATE INDEX idx_documents_covering ON documents_covering (document_key) INCLUDE (document_version); 

Composite Index:

CREATE INDEX idx_documents_composite ON documents_composite (document_key, document_version); 

Finally, update the table statistics.

ANALYZE documents_covering; ANALYZE documents_composite; 

Both tables now contain the same 10 million records, with different index configurations.

We'll compare their storage usage, SELECT, INSERT, and UPDATE performance to understand how these indexes behave.

1. Storage Comparison

Let's start by comparing how much storage each index occupies after indexing 10 million records.

Query 1: Comparing Index Sizes

We can use PostgreSQL's pg_relation_size() function to check the storage occupied by each index.

SELECT 'Covering Index' AS index_type, pg_size_pretty(pg_relation_size('idx_documents_covering')) AS index_size, pg_relation_size('idx_documents_covering') AS size_bytes UNION ALL SELECT 'Composite Index', pg_size_pretty(pg_relation_size('idx_documents_composite')), pg_relation_size('idx_documents_composite'); 

Results:

Both indexes occupied exactly the same storage space: 3,625 MB (3,801,243,648 bytes).

Query 2: Comparing Index Pages

Next, let's check the number of pages occupied by each index using PostgreSQL's pg_class system catalog.

SELECT relname AS index_name, relpages AS total_pages FROM pg_class WHERE relname IN ( 'idx_documents_covering', 'idx_documents_composite' ); 

Results:

Why Are Their Sizes the Same?

PostgreSQL uses a B-tree structure that works similarly to a B+ tree.

In PostgreSQL, B-tree indexes contain two main types of pages:

• Leaf pages: Store the actual index entries containing indexed values and references to table rows.
• Internal pages: Store separator keys and pointers that help PostgreSQL navigate to the correct leaf pages.

You can read more about this in the PostgreSQL B-tree Implementation documentation.

PostgreSQL also mentions that typically more than 99% of B-tree pages are leaf pages.

Both covering and composite indexes store their indexed column values in leaf entries. The difference is in how PostgreSQL handles those columns in internal pages.

Covering Index

In a covering index, additional columns are included using INCLUDE.

These columns are stored in leaf entries for data retrieval, but they are not needed for searching or navigating the B-tree.

So, PostgreSQL completely excludes included columns from internal navigation entries.

Composite Index

In a composite index, all columns are key columns and can participate in searching and sorting.

However, PostgreSQL has an optimization called suffix truncation.

Suffix truncation removes unnecessary trailing key columns from internal B-tree entries.

For example, if the first indexed column is enough to distinguish two page ranges, PostgreSQL doesn't need to store the second column in the corresponding internal separator.

You can read about this in the PostgreSQL documentation on suffix truncation.

In our case, document_key contains unique values, so the second column is generally unnecessary for distinguishing page ranges.

Since both indexes store the same column values in their leaf entries, and their internal entries can also be similarly compact, their storage requirements can be very close.

In our experiment, both indexes occupied exactly 3,625 MB across 464,019 pages.

2. SELECT Performance Comparison

Next, let's compare how covering and composite indexes perform when retrieving records.

We'll select 6,500 records using document_key and retrieve both document_key and document_version.

We'll execute each query twice to compare the initial run and the warm-cache run.

Query 1: Covering Index

EXPLAIN (ANALYZE, BUFFERS) SELECT document_key, document_version FROM documents_covering WHERE document_key BETWEEN 'DOC_' || LPAD('100000'::TEXT, 96, '0') AND 'DOC_' || LPAD('106499'::TEXT, 96, '0'); 

Initial Run:

Warm-Cache Run:

Query 2: Composite Index

EXPLAIN (ANALYZE, BUFFERS) SELECT document_key, document_version FROM documents_composite WHERE document_key BETWEEN 'DOC_' || LPAD('100000'::TEXT, 96, '0') AND 'DOC_' || LPAD('106499'::TEXT, 96, '0'); 

Initial Run:

Warm-Cache Run:

Results Comparison

Observations

Both indexes used an Index Only Scan, meaning PostgreSQL retrieved the required column values directly from the indexes.

Both also reported 0 heap fetches, so PostgreSQL didn't need to visit the table rows to check their visibility.

During the initial runs, both indexes reported 301 shared-buffer reads. During the warm-cache runs, those became 301 shared-buffer hits, as the required index pages were already available in PostgreSQL's shared buffers.

The execution times were nearly identical, with small variations between runs.

For this SELECT query, neither index showed a clear performance advantage.

3. INSERT Performance Comparison

Next, let's compare INSERT performance between covering and composite indexes.

Both tables already contain 10 million records. We'll insert another 100,000 records into each table and measure the execution time using EXPLAIN (ANALYZE, BUFFERS).

Query 1: Covering Index

EXPLAIN (ANALYZE, BUFFERS) INSERT INTO documents_covering (document_id, document_key, document_version) SELECT g, 'DOC_' || LPAD(g::TEXT, 96, '0'), 'VERSION_' || LPAD(g::TEXT, 192, '0') FROM generate_series(10000001, 10100000) AS g; 

Execution Result:

Execution time: 4,814.892 ms

Query 2: Composite Index

EXPLAIN (ANALYZE, BUFFERS) INSERT INTO documents_composite (document_id, document_key, document_version) SELECT g, 'DOC_' || LPAD(g::TEXT, 96, '0'), 'VERSION_' || LPAD(g::TEXT, 192, '0') FROM generate_series(10000001, 10100000) AS g; 

Execution Result:

Execution time: 4,681.164 ms

Results Comparison

Observations

Both indexes showed similar INSERT performance, with the composite index completing around 134 ms faster in this run.

When inserting records, PostgreSQL needs to write the new rows to the table and create the corresponding entries in its indexes.

Both index types store the same column values in their leaf entries, so the amount of index maintenance can be similar.

These tests were performed after deleting and vacuuming previously inserted records, so existing free space and index maintenance may also affect the results.

We didn't observe a significant performance difference between covering and composite indexes in this INSERT test.

4. UPDATE Performance Comparison

Next, let's compare how covering and composite indexes handle updates.

We'll update document_version for 100,000 existing records in each table.

In the covering index, document_version is an included column, while in the composite index, it's a key column.

Query 1: Covering Index

EXPLAIN (ANALYZE, BUFFERS) UPDATE documents_covering SET document_version = 'UPDATED_' || LPAD(document_id::TEXT, 190, '0') WHERE document_id BETWEEN 1 AND 100000; 

Execution Result:

Execution time: 5,165.132 ms

Query 2: Composite Index

EXPLAIN (ANALYZE, BUFFERS) UPDATE documents_composite SET document_version = 'UPDATED_' || LPAD(document_id::TEXT, 190, '0') WHERE document_id BETWEEN 1 AND 100000; 

Execution Result:

Execution time: 5,691.076 ms

Results Comparison

Observations

Both indexes showed relatively similar UPDATE performance, with the covering index completing around 526 ms faster in this test.

When an indexed column is updated, PostgreSQL needs to maintain the corresponding index entries.

This applies to both index types:

• Covering Index: Updating an included column requires index maintenance.
• Composite Index: Updating a key column also requires index maintenance.

Normally, PostgreSQL can sometimes perform a HOT (Heap-Only Tuple) update, avoiding the need to create new index entries.

However, updating a column referenced by a regular B-tree index prevents HOT updates, whether the column is a key or an included column. You can read about this in the PostgreSQL HOT Updates documentation.

In our experiment, both indexes required index maintenance, and neither showed a clear, consistent performance advantage based on this single test.

Experiment 2: Covering vs. Composite Indexes with Duplicate Key Values

In our first experiment, document_key contained unique values, and both indexes showed almost identical storage usage and similar performance.

Now, let's see what happens when the first indexed column contains duplicate values.

We'll create two new tables with 1 million records, where each document_key repeats 10,000 times, while document_version remains unique.

Setting Up the Experiment

1. Creating the Tables

CREATE TABLE documents_duplicate_covering ( document_id BIGINT, document_key VARCHAR(100), document_version VARCHAR(200) ); CREATE TABLE documents_duplicate_composite ( document_id BIGINT, document_key VARCHAR(100), document_version VARCHAR(200) ); 

2. Inserting 1 Million Records

First, insert the records into the covering-index table.

INSERT INTO documents_duplicate_covering (document_id, document_key, document_version) SELECT g, 'DOC_' || LPAD((g % 100)::TEXT, 96, '0'), 'VERSION_' || LPAD(g::TEXT, 192, '0') FROM generate_series(1, 1000000) AS g; 

Copy the same data into the composite-index table.

INSERT INTO documents_duplicate_composite (document_id, document_key, document_version) SELECT document_id, document_key, document_version FROM documents_duplicate_covering; 

3. Creating the Indexes

Covering Index:

CREATE INDEX idx_duplicate_covering ON documents_duplicate_covering (document_key) INCLUDE (document_version); 

Composite Index:

CREATE INDEX idx_duplicate_composite ON documents_duplicate_composite (document_key, document_version); 

Both tables now contain identical data, but their indexes handle document_version differently.

1. Storage Comparison

Let's first compare the index sizes after creating both indexes on 1 million records.

SELECT indexrelid::regclass AS index_name, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, pg_relation_size(indexrelid) AS size_bytes FROM pg_index WHERE indexrelid IN ( 'idx_duplicate_covering'::regclass, 'idx_duplicate_composite'::regclass ); 

Results:

This time, the composite index occupied approximately 13 MB more storage than the covering index.

Unlike our first experiment, the leading indexed column now contains duplicate values.

In a covering index, PostgreSQL stores included columns only in leaf entries.

In a composite index, trailing key columns may also be needed in internal B-tree entries to distinguish page ranges when the leading column contains duplicate values.

Both indexes still store the same column values in their leaf entries, but their internal navigation entries can differ.

In our experiment, the composite index required slightly more storage.

2. SELECT Performance Comparison

Next, let's retrieve records using a document_key that appears multiple times.

Before running the queries, we updated the table statistics and visibility information.

VACUUM (ANALYZE) documents_duplicate_covering; VACUUM (ANALYZE) documents_duplicate_composite; 

Query 1: Covering Index

EXPLAIN (ANALYZE, BUFFERS) SELECT document_key, document_version FROM documents_duplicate_covering WHERE document_key = 'DOC_' || LPAD('50', 96, '0'); 

Results:

Query 2: Composite Index

EXPLAIN (ANALYZE, BUFFERS) SELECT document_key, document_version FROM documents_duplicate_composite WHERE document_key = 'DOC_' || LPAD('50', 96, '0'); 

Results:

Observations

Both indexes used an Index Only Scan, returning 10,000 records without fetching any rows from the table heap.

The execution times were almost identical, and both accessed approximately the same number of index pages.

Even with duplicate values in the first indexed column, we didn't observe a noticeable SELECT performance difference.

3. INSERT Performance Comparison

Now, let's insert another 100,000 records into each table while keeping duplicate values in document_key.

Query 1: Covering Index

EXPLAIN (ANALYZE, BUFFERS) INSERT INTO documents_duplicate_covering (document_id, document_key, document_version) SELECT g, 'DOC_' || LPAD((g % 100)::TEXT, 96, '0'), 'VERSION_' || LPAD(g::TEXT, 192, '0') FROM generate_series(1000001, 1100000) AS g; 

Results:

Query 2: Composite Index

EXPLAIN (ANALYZE, BUFFERS) INSERT INTO documents_duplicate_composite (document_id, document_key, document_version) SELECT g, 'DOC_' || LPAD((g % 100)::TEXT, 96, '0'), 'VERSION_' || LPAD(g::TEXT, 192, '0') FROM generate_series(1000001, 1100000) AS g; 

Results:

Observations

This time, the covering index completed the INSERT in approximately 2.89 seconds, while the composite index took 5.71 seconds.

The composite-index test also performed more buffer reads and writes.

The difference was much larger than in our first experiment. However, these are single-run measurements, and cache conditions and disk activity can also affect the execution time.

4. UPDATE Performance Comparison

Finally, let's update document_version for 100,000 records in both tables.

Before the test, we ran:

VACUUM (ANALYZE) documents_duplicate_covering; VACUUM (ANALYZE) documents_duplicate_composite; 

Query 1: Covering Index

EXPLAIN (ANALYZE, BUFFERS) UPDATE documents_duplicate_covering SET document_version = 'UPDATED_' || LPAD(document_id::TEXT, 190, '0') WHERE document_id BETWEEN 1 AND 100000; 

Results:

Query 2: Composite Index

EXPLAIN (ANALYZE, BUFFERS...