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

Metric	Covering Index	Composite Index
Initial execution time	25.255 ms	29.351 ms
Warm-cache execution time	26.493 ms	24.660 ms
Rows returned	6,500	6,500
Initial shared-buffer reads	301	301
Warm-cache shared-buffer hits	301	301
Heap fetches	0	0

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

Index Type	Records Inserted	Execution Time
Covering Index	100,000	4,814.892 ms
Composite Index	100,000	4,681.164 ms

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

Index Type	Records Updated	Execution Time
Covering Index	100,000	5,165.132 ms
Composite Index	100,000	5,691.076 ms

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:

Index Type	Index Size
Covering Index	363 MB
Composite Index	376 MB

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:

Metric	Covering Index	Composite Index
Records returned	10,000	10,000
Execution time	2.846 ms	2.626 ms
Scan type	Index Only Scan	Index Only Scan
Heap fetches	0	0
Shared-buffer reads	458	459

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:

Metric	Covering Index	Composite Index
Records inserted	100,000	100,000
Execution time	2,892.511 ms	5,710.005 ms

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)
UPDATE documents_duplicate_composite
SET document_version = 'UPDATED_' || LPAD(document_id::TEXT, 190, '0')
WHERE document_id BETWEEN 1 AND 100000;

Results:

Metric	Covering Index	Composite Index
Records updated	100,000	100,000
Execution time	3,288.368 ms	4,598.804 ms
Scan type	Sequential Scan	Sequential Scan

Observations

The covering index completed the UPDATE approximately 1.31 seconds faster than the composite index in this test.

Both queries used a Sequential Scan because these new tables didn't have an index on document_id.

Also, changing document_version required index maintenance in both cases, whether it was an included column or a composite-index key column.

The composite-index test recorded more buffer writes, which may have contributed to its longer execution time.

Comparison of Both Experiments

Experiment 1: Unique Values in the First Key Column

Operation	Covering Index	Composite Index
Storage (10 million records)	3,625 MB	3,625 MB
SELECT (6,500 records, warm cache)	26.493 ms	24.660 ms
INSERT (100,000 records)	4,814.892 ms	4,681.164 ms
UPDATE (100,000 records)	5,165.132 ms	5,691.076 ms

Experiment 2: Duplicate Values in the First Key Column

Operation	Covering Index	Composite Index
Storage (1 million records)	363 MB	376 MB
SELECT (10,000 records)	2.846 ms	2.626 ms
INSERT (100,000 records)	2,892.511 ms	5,710.005 ms
UPDATE (100,000 records)	3,288.368 ms	4,598.804 ms

In the first experiment, both indexes occupied the same storage space and showed similar SELECT, INSERT, and UPDATE performance.

In the second experiment, where the first indexed column contained duplicate values, the composite index occupied more storage. The covering index also completed the INSERT and UPDATE operations faster in our tests.

This shows why we should carefully decide which columns need to be index keys.

Which One Should You Choose: Composite Index or Covering Index?

In real-world applications, covering and composite indexes are not competing approaches. We can combine both in a single index.

• Key columns: Keep columns required for filtering, joining, or sorting as index keys.
• Non-key columns: Keep additional columns needed only for retrieving data in the INCLUDE clause.

Unnecessarily adding columns as index keys can waste storage, especially when the leading columns contain duplicate values.

PostgreSQL may need those additional key columns in internal B-tree entries. But columns added using INCLUDE are stored only in leaf entries, which can help reduce unnecessary storage.

At the same time, we shouldn't add too many columns to INCLUDE, because they still increase index size and require maintenance whenever their values change.

So, keep only the required columns as index keys, and use INCLUDE for additional columns needed only for retrieval. Choose both carefully based on your actual query requirements.

Thanks for reading! I hope this article helped you better understand covering and composite indexes in PostgreSQL.