Partial and Covering Indexes for Read-Heavy Workloads

The main chapter introduced partial and covering indexes. This section provides the complete methodology: when to use each type, how to combine them, and the measured impact on the content platform’s read and write paths.

Partial Index Design Methodology

A partial index includes only rows matching a predicate. The design process:

1. Identify the query’s WHERE clause
2. Determine which predicate values are queried frequently
3. Create an index with a WHERE clause that matches those predicates
4. Verify the planner uses the partial index

Case 1: Published Articles Only

The content platform’s public API queries only published articles. The admin API queries draft and archived articles. These are separate access patterns with different index needs.

Current state: a full index on status:

-- Full index on status: 500,000 entries, 440 pages, 3.4 MB
CREATE INDEX idx_articles_status ON articles (status);

The public API query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, slug, published_at
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20;

Limit  (cost=0.43..4.87 rows=20 width=82)
  (actual time=0.041..0.089 rows=20 loops=1)
  Buffers: shared hit=4
  ->  Index Scan Backward using idx_articles_published_at on articles
        (cost=0.43..35124.00 rows=487000 width=82)
        (actual time=0.039..0.084 rows=20 loops=1)
        Filter: (status = 'published')
        Rows Removed by Filter: 0
        Buffers: shared hit=4

PostgreSQL ignores idx_articles_status and uses idx_articles_published_at because 97.4% selectivity makes the status index useless for published articles. The index occupies 3.4 MB and is maintained on every INSERT and status UPDATE, providing zero value for the dominant query pattern.

Replace with partial indexes for the minority cases:

-- Drop the full index
DROP INDEX idx_articles_status;

-- Partial index for admin: draft articles ordered by update time
CREATE INDEX idx_articles_draft_updated
    ON articles (updated_at DESC)
    WHERE status = 'draft';

-- Partial index for admin: archived articles
CREATE INDEX idx_articles_archived_published
    ON articles (published_at DESC)
    WHERE status = 'archived';

Index sizes:

SELECT indexrelname,
       pg_relation_size(indexrelid) AS size_bytes,
       pg_relation_size(indexrelid) / 8192 AS pages
FROM pg_stat_user_indexes
WHERE indexrelname LIKE 'idx_articles_draft%'
   OR indexrelname LIKE 'idx_articles_archived%';

         indexrelname              | size_bytes | pages
-----------------------------------+------------+------
 idx_articles_draft_updated        |     106496 |    13
 idx_articles_archived_published   |      16384 |     2

13 + 2 = 15 pages (120 KB) instead of 440 pages (3.4 MB). A 97% reduction in index storage. Write-side impact: INSERTs (which create draft articles) update 1 partial index instead of 1 full index. Status transitions from draft to published remove the entry from the partial index and do not add to any status-based index. Net write cost: lower.

Admin draft listing query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, slug, updated_at
FROM articles
WHERE status = 'draft'
ORDER BY updated_at DESC
LIMIT 50;

Limit  (cost=0.29..8.42 rows=50 width=82)
  (actual time=0.028..0.142 rows=50 loops=1)
  Buffers: shared hit=6
  ->  Index Scan using idx_articles_draft_updated on articles
        (cost=0.29..142.33 rows=12000 width=82)
        (actual time=0.027..0.134 rows=50 loops=1)
        Buffers: shared hit=6
Planning Time: 0.089 ms
Execution Time: 0.168 ms

The partial index delivers sorted results directly (no separate Sort node). 6 buffer hits for 50 results.

Case 2: Recent High-Traffic Articles

The content platform’s recommendation engine queries articles published in the last 30 days with more than 1,000 views:

-- Without partial index
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, slug, view_count, published_at
FROM articles
WHERE status = 'published'
  AND published_at > NOW() - INTERVAL '30 days'
  AND view_count > 1000
ORDER BY view_count DESC
LIMIT 50;

Sort  (cost=4842.33..4845.83 rows=1400 width=86)
  (actual time=8.112..8.142 rows=50 loops=1)
  Sort Key: view_count DESC
  Sort Method: top-N heapsort  Memory: 32kB
  Buffers: shared hit=2842
  ->  Bitmap Heap Scan on articles  (cost=124.56..4742.33 rows=1400 width=86)
        (actual time=2.112..7.442 rows=1387 loops=1)
        Recheck Cond: (published_at > '2026-04-29'::timestamp)
        Filter: ((status = 'published') AND (view_count > 1000))
        Rows Removed by Filter: 12613
        Heap Blocks: exact=2418
        Buffers: shared hit=2842
        ->  Bitmap Index Scan on idx_articles_published_at
              (cost=0.00..123.21 rows=14000 width=0)
              (actual time=1.442..1.442 rows=14000 loops=1)
              Buffers: shared hit=42
Planning Time: 0.142 ms
Execution Time: 8.212 ms

The Bitmap Heap Scan reads 14,000 rows matching the date range, then filters to 1,387 matching the view count threshold. 12,613 rows read and discarded.

Partial index targeting this access pattern:

CREATE INDEX idx_articles_hot_recent
    ON articles (view_count DESC)
    WHERE status = 'published'
      AND published_at > '2026-01-01'
      AND view_count > 1000;

Limit  (cost=0.29..4.42 rows=50 width=86)
  (actual time=0.031..0.112 rows=50 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using idx_articles_hot_recent on articles
        (cost=0.29..112.33 rows=1400 width=86)
        (actual time=0.029..0.108 rows=50 loops=1)
        Buffers: shared hit=4
Planning Time: 0.089 ms
Execution Time: 0.142 ms

From 8.2 ms to 0.14 ms. 4 buffer hits instead of 2,842. The partial index contains only the approximately 1,400 hot articles, so scanning the top 50 by view_count requires traversing a few leaf pages.

The trade-off: the WHERE clause on published_at > '2026-01-01' uses a constant date. This index will not automatically include articles published after the date the query pattern changes. Updating the partial index predicate requires a REINDEX or CREATE INDEX CONCURRENTLY followed by dropping the old index. Automate this with a monthly maintenance script:

-- Monthly refresh of the hot articles partial index
CREATE INDEX CONCURRENTLY idx_articles_hot_recent_new
    ON articles (view_count DESC)
    WHERE status = 'published'
      AND published_at > NOW() - INTERVAL '6 months'
      AND view_count > 1000;

DROP INDEX idx_articles_hot_recent;
ALTER INDEX idx_articles_hot_recent_new RENAME TO idx_articles_hot_recent;

Covering Index Design

A covering index adds non-key columns via INCLUDE so the query can be answered entirely from the index. The design decisions:

1. Which columns go in the key (used for filtering and ordering)?
2. Which columns go in INCLUDE (returned in SELECT but not filtered/sorted)?
3. Is the resulting index size acceptable?

Case 1: Category Page Listing

The category page shows title, slug, excerpt, and published date for articles in a category:

-- Standard composite index
CREATE INDEX idx_articles_cat_pub
    ON articles (category_id, published_at DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT title, slug, excerpt, published_at
FROM articles
WHERE category_id = 5
  AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

Limit  (cost=0.43..24.87 rows=20 width=292)
  (actual time=0.052..0.312 rows=20 loops=1)
  Buffers: shared hit=64
  ->  Index Scan Backward using idx_articles_cat_pub on articles
        (cost=0.43..4521.33 rows=7284 width=292)
        (actual time=0.051..0.301 rows=20 loops=1)
        Index Cond: (category_id = 5)
        Filter: (status = 'published')
        Rows Removed by Filter: 2
        Buffers: shared hit=64

64 buffer hits. The index locates the 20 matching rows (4 buffer hits for index traversal), but PostgreSQL must fetch each row from the heap to read title, slug, and excerpt (approximately 60 additional buffer hits for heap pages).

Covering index:

CREATE INDEX idx_articles_cat_pub_covering
    ON articles (category_id, published_at DESC)
    INCLUDE (title, slug, excerpt, view_count);

Limit  (cost=0.56..4.87 rows=20 width=292)
  (actual time=0.042..0.089 rows=20 loops=1)
  Buffers: shared hit=4
  ->  Index Only Scan Backward using idx_articles_cat_pub_covering on articles
        (cost=0.56..2842.33 rows=7284 width=292)
        (actual time=0.041..0.084 rows=20 loops=1)
        Index Cond: (category_id = 5)
        Filter: (status = 'published')
        Rows Removed by Filter: 2
        Heap Fetches: 0
        Buffers: shared hit=4
Planning Time: 0.112 ms
Execution Time: 0.118 ms

4 buffer hits instead of 64. Heap Fetches: 0. Execution time: 0.118 ms instead of 0.312 ms.

Index size comparison:

SELECT indexrelname,
       pg_relation_size(indexrelid) AS size_bytes,
       pg_relation_size(indexrelid) / 8192 AS pages
FROM pg_stat_user_indexes
WHERE indexrelname IN ('idx_articles_cat_pub', 'idx_articles_cat_pub_covering');

          indexrelname             | size_bytes | pages
-----------------------------------+------------+------
 idx_articles_cat_pub              |   11468800 |  1400
 idx_articles_cat_pub_covering     |   34406400 |  4200

The covering index is 3x larger (4,200 pages vs 1,400). Each INSERT must write a larger index entry. But for a read-heavy workload where the category page is the most common access pattern, eliminating 60 heap page reads per query justifies the storage cost.

Case 2: Tag Cloud with Article Counts

The sidebar shows tags with article counts:

EXPLAIN (ANALYZE, BUFFERS)
SELECT t.name, COUNT(*)
FROM tags t
JOIN article_tags at ON t.id = at.tag_id
JOIN articles a ON at.article_id = a.id
WHERE a.status = 'published'
GROUP BY t.name
ORDER BY COUNT(*) DESC
LIMIT 20;

Limit  (cost=28842.33..28842.38 rows=20 width=44)
  (actual time=142.112..142.142 rows=20 loops=1)
  Sort Key: (count(*)) DESC
  Sort Method: top-N heapsort  Memory: 26kB
  Buffers: shared hit=18924
  ->  HashAggregate  (cost=28642.33..28742.33 rows=500 width=44)
        (actual time=141.442..141.842 rows=500 loops=1)
        Group Key: t.name
        Buffers: shared hit=18924
        ->  Hash Join  (cost=418.00..21842.33 rows=1360000 width=36)
              (actual time=4.221..98.442 rows=1358742 loops=1)
              ...
Planning Time: 0.421 ms
Execution Time: 142.221 ms

142 ms. The query joins 500,000 articles with 1.36 million article_tag relationships and 500 tags, filtering by status.

Create a covering index on article_tags that includes enough information to avoid the articles table join for the status filter:

-- Cannot include articles.status in an article_tags index
-- Instead, use a partial index on article_tags joined condition

-- Better approach: materialized view for this aggregation
CREATE MATERIALIZED VIEW tag_counts AS
SELECT t.id, t.name, COUNT(*) AS article_count
FROM tags t
JOIN article_tags at ON t.id = at.tag_id
JOIN articles a ON at.article_id = a.id
WHERE a.status = 'published'
GROUP BY t.id, t.name;

CREATE UNIQUE INDEX idx_tag_counts_id ON tag_counts (id);
CREATE INDEX idx_tag_counts_count ON tag_counts (article_count DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT name, article_count
FROM tag_counts
ORDER BY article_count DESC
LIMIT 20;

Limit  (cost=0.27..1.42 rows=20 width=44)
  (actual time=0.021..0.042 rows=20 loops=1)
  Buffers: shared hit=2
  ->  Index Scan using idx_tag_counts_count on tag_counts
        (cost=0.27..28.42 rows=500 width=44)
        (actual time=0.019..0.038 rows=20 loops=1)
        Buffers: shared hit=2
Planning Time: 0.068 ms
Execution Time: 0.058 ms

From 142 ms to 0.058 ms. The materialized view pre-computes the aggregation. Refresh it on a schedule or after bulk content changes:

REFRESH MATERIALIZED VIEW CONCURRENTLY tag_counts;

The CONCURRENTLY option allows reads during refresh but requires the unique index on id.

Combined Partial + Covering Indexes

The most powerful index design combines partial indexing (reduce index size) with covering indexing (eliminate heap fetches):

-- Partial + covering: recent published articles for the homepage
CREATE INDEX idx_articles_homepage
    ON articles (published_at DESC)
    INCLUDE (title, slug, excerpt, featured_image)
    WHERE status = 'published'
      AND featured_image IS NOT NULL;

This index covers only published articles with featured images (the homepage carousel). It includes all columns needed for rendering.

EXPLAIN (ANALYZE, BUFFERS)
SELECT title, slug, excerpt, featured_image, published_at
FROM articles
WHERE status = 'published'
  AND featured_image IS NOT NULL
ORDER BY published_at DESC
LIMIT 10;

Limit  (cost=0.29..2.42 rows=10 width=348)
  (actual time=0.022..0.048 rows=10 loops=1)
  Buffers: shared hit=3
  ->  Index Only Scan using idx_articles_homepage on articles
        (cost=0.29..842.33 rows=42000 width=348)
        (actual time=0.021..0.044 rows=10 loops=1)
        Heap Fetches: 0
        Buffers: shared hit=3
Planning Time: 0.078 ms
Execution Time: 0.062 ms

3 buffer hits. Zero heap fetches. The index size is small because it only includes the subset of published articles with featured images (approximately 42,000 out of 500,000).

Visibility Map and VACUUM Interaction

Index Only Scans rely on the visibility map to confirm that all tuples on a heap page are visible to all transactions. If the visibility map bit is not set (because of recent modifications), PostgreSQL must check the heap to verify visibility, resulting in Heap Fetches > 0.

After a bulk update:

UPDATE articles SET excerpt = 'Updated excerpt' WHERE category_id = 5;
-- 7,284 rows updated

EXPLAIN (ANALYZE, BUFFERS)
SELECT title, slug, excerpt, published_at
FROM articles
WHERE category_id = 5
ORDER BY published_at DESC
LIMIT 20;

Index Only Scan using idx_articles_cat_pub_covering on articles
  (cost=0.56..4.87 rows=20 width=292)
  (actual time=0.089..0.312 rows=20 loops=1)
  Index Cond: (category_id = 5)
  Heap Fetches: 20
  Buffers: shared hit=24

Heap Fetches: 20 means all 20 result rows required heap access to verify visibility. After VACUUM:

VACUUM articles;

EXPLAIN (ANALYZE, BUFFERS)
SELECT title, slug, excerpt, published_at
FROM articles
WHERE category_id = 5
ORDER BY published_at DESC
LIMIT 20;

Index Only Scan using idx_articles_cat_pub_covering on articles
  (cost=0.56..4.87 rows=20 width=292)
  (actual time=0.042..0.089 rows=20 loops=1)
  Index Cond: (category_id = 5)
  Heap Fetches: 0
  Buffers: shared hit=4

VACUUM updates the visibility map, enabling true Index Only Scans. For tables with frequent updates, aggressive autovacuum settings ensure the visibility map stays current:

ALTER TABLE articles SET (autovacuum_vacuum_threshold = 500);
ALTER TABLE articles SET (autovacuum_vacuum_scale_factor = 0.01);

This triggers VACUUM after 500 + 1% of total rows are modified, instead of the default 50 + 20%. For a 500,000-row table, the threshold drops from 100,050 to 5,500 modified rows.

Index Design Decision Matrix

Query Pattern	Index Type	Storage Cost	Write Cost	Read Benefit
Filter on rare values	Partial	Low (subset)	Low (rare updates)	High (small index)
Select few columns	Covering (INCLUDE)	Medium (wider leaves)	Medium (wider entries)	High (no heap fetch)
Filter rare + select few	Partial + Covering	Low	Low	Highest
Sort + filter	Composite key order	Medium	Medium	High (no Sort node)
Full table aggregation	Materialized view	Separate storage	Refresh cost	Highest (pre-computed)

The decision comes down to workload ratio. For tables with a read:write ratio above 10:1 (like the content platform’s articles table at approximately 100:1 for published articles), covering indexes pay for themselves. For tables with a read:write ratio below 2:1 (like view_events), minimize indexes to maximize write throughput.