Index Build Memory and Parallel Builds #

Building an index on a 900 GB table takes 5 hours, which does not fit the maintenance window. Nothing about the table is unusual; the server has 256 GB of RAM and 32 cores, and the build is using 64 MB of memory and one core, because both relevant settings are at values chosen for small machines years ago.

Index maintenance operations are part of index maintenance and bloat. This page covers making them finish.

The Condition That Governs Build Speed #

CREATE INDEX on a B-tree builds by sorting: it scans the table, sorts the key values, and writes the tree bottom-up from the sorted stream. Two settings govern that sort:

Two important exclusions:

maintenance_work_mem also governs the memory VACUUM uses to collect dead tuple identifiers, so raising it globally affects vacuum behaviour as well — usually beneficially.

How a B-tree index is built Four phases. Workers and the leader scan their shares of the table. Each sorts its key values in its share of maintenance_work_mem, spilling to temporary files if needed. The leader merges the sorted streams. The tree is written bottom-up from the merged stream. parallel scan workers read shares sort keys in maintenance_work_mem merge streams leader combines write tree bottom-up, sequential memory decides whether the sort spills; workers decide how fast it sorts

Annotated Evidence #

SHOW maintenance_work_mem;            -- 64MB
SHOW max_parallel_maintenance_workers; -- 2

\timing on
CREATE INDEX events_account_ts_idx ON events (account_id, occurred_at);
-- Time: 18204112.6 ms  (5h 03m)

Watch a running build:

SELECT phase, blocks_done, blocks_total,
       tuples_done, tuples_total, partitions_done, partitions_total
FROM pg_stat_progress_create_index;
        phase                 | blocks_done | blocks_total | tuples_done | tuples_total
------------------------------+-------------+--------------+-------------+--------------
 building index: loading tuples … |    41204110 |    118204110 |  1204110402 |             0
-- phases: initializing → waiting for writers → building index (scanning, sorting, loading)

With both settings raised for the session:

SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 7;
CREATE INDEX events_account_ts_idx ON events (account_id, occurred_at);
-- Time: 3102448.2 ms  (51m)

Temporary file activity tells you whether memory was enough:

SELECT temp_files, pg_size_pretty(temp_bytes) FROM pg_stat_database WHERE datname = current_database();
-- before the change: temp_files climbing during the build
-- after: unchanged during the build
Diagnosing a slow build Three signals are annotated. Temporary files growing during a build means the sort spilled because maintenance_work_mem was too small. A single backend using one core means no parallel workers joined. A build with CONCURRENTLY is always single-threaded and makes two passes over the table. temp_files rising during the build sort spilled: raise memory one backend at 100% CPU no parallel workers joined CREATE INDEX CONCURRENTLY single-threaded by design pg_stat_progress_create_index shows which phase is slow

Step-by-Step Resolution #

  1. Decide whether the build must be concurrent. Blocking writes for the duration is acceptable for maintenance windows and unacceptable for live tables; CONCURRENTLY is the price for the latter.

  2. Raise memory for the session, not globally:

    SET maintenance_work_mem = '8GB';

    Budget it against RAM: each parallel participant may use its share, and any concurrent vacuum uses its own allowance.

  3. Raise the worker limit and make sure the global pool allows them:

    SET max_parallel_maintenance_workers = 7;
SHOW max_parallel_workers;        -- must leave room beyond queries

  4. Encourage workers on the table if it is large but not obviously so to the planner:

    ALTER TABLE events SET (parallel_workers = 8);

  5. Monitor progress with pg_stat_progress_create_index, and pg_stat_activity to confirm workers exist.

  6. For partitioned tables, build per partition so each build is sized independently and can be parallel and concurrent; then attach the parent index.

  7. Reset session settings afterwards; they are not meant for query workloads.

Build time for one 900 GB table index With 64 megabytes of maintenance memory and no workers the build takes 303 minutes. With 8 gigabytes and no workers, 96 minutes. With 8 gigabytes and 7 workers, 51 minutes. The same index built CONCURRENTLY with 8 gigabytes takes 142 minutes because it cannot use workers and makes two passes. 64MB, no workers 303 min 8GB, no workers 96 min 8GB, 7 workers 51 min CONCURRENTLY, 8GB 142 min CONCURRENTLY trades speed for not blocking writes

Before and After #

-- BEFORE: defaults (64MB, 2 workers considered but table not eligible)
CREATE INDEX events_account_ts_idx …   Time: 5h 03m   temp files written during sort

-- AFTER: maintenance_work_mem = 8GB, max_parallel_maintenance_workers = 7
CREATE INDEX events_account_ts_idx …   Time: 51m      no temp files

Planning the maintenance window #

An index build holds a SHARE lock on the table, which blocks writes but not reads. That makes the non-concurrent build viable for reporting tables and impossible for tables taking continuous writes. The concurrent build takes only a SHARE UPDATE EXCLUSIVE lock, allowing writes, but it waits twice for all transactions that started before each of its phases — so a single long-running transaction elsewhere can stall it indefinitely. Before starting one, check for long transactions, and consider setting a lock_timeout so the build fails fast rather than queueing behind a lock and blocking everything behind it.

Disk space matters too. A build needs room for the finished index plus, when the sort spills, temporary files of roughly the index’s size. A concurrent rebuild of an existing index needs room for both copies at once. Checking free space against pg_relation_size estimates before starting avoids the worst failure mode: running out of disk halfway through a five-hour operation.

Sizing memory without guessing #

A B-tree build sorts entries of roughly the key width plus about a dozen bytes of overhead, so an index on two 8-byte columns over 1.2 billion rows sorts around 30 GB of data. No practical maintenance_work_mem holds that, and the goal is not to avoid spilling entirely but to keep the number of merge passes low: a sort that fits its runs in a few gigabytes merges once, while a 64 MB allowance produces hundreds of runs and merges repeatedly. In practice, values between 2 GB and 8 GB cover most large builds, and going higher yields little.

Estimate before building:

SELECT pg_size_pretty(
         (SELECT reltuples FROM pg_class WHERE relname = 'events')::numeric
         * (8 + 8 + 12)                      -- two bigint keys + per-entry overhead
       ) AS approx_sort_volume;

Then compare it with free memory, remembering that each parallel participant takes a share, and that autovacuum workers use the same setting. On a server with other work running, a session-level SET before the build and a reset afterwards is safer than raising the global value, and it leaves vacuum’s allowance alone.

Common Pitfalls #

Leaving maintenance_work_mem at 64 MB. Every large build spills. Diagnostic signal: temporary files during builds. Fix: raise it per session.

Raising workers without memory. Each worker’s share shrinks and all of them spill. Diagnostic signal: more workers, no speedup. Fix: raise both together.

Expecting CONCURRENTLY to parallelise. It cannot. Diagnostic signal: one process, long runtime. Fix: plan for the longer build or use a maintenance window.

Global settings left raised. maintenance_work_mem applies to autovacuum workers too, multiplying memory use. Diagnostic signal: memory pressure after a maintenance change. Fix: set it per session or per maintenance role.

Frequently Asked Questions #

How do I make CREATE INDEX faster in PostgreSQL? #

Raise maintenance_work_mem for the session so the sort stays in memory, and allow parallel workers with max_parallel_maintenance_workers. Both together typically cut large B-tree build times by several times.

Can CREATE INDEX CONCURRENTLY use parallel workers? #

No. Concurrent builds are single-threaded and make two passes over the table while allowing writes. Use a non-concurrent build in a maintenance window when speed matters more than availability.

How can I monitor an index build’s progress? #

Query pg_stat_progress_create_index, which reports the current phase along with blocks and tuples processed. Combined with pg_stat_activity it also shows whether parallel workers joined the build.

Up: Index Maintenance and Bloat