Navigating PostgreSQL Parallelism: A Deep Dive into min_parallel_table_scan_size and min_parallel_index_scan_size

As database architectures scale to handle enterprise-grade workloads, managing hardware resources efficiently becomes paramount. In the PostgreSQL ecosystem, query parallelism is one of the most powerful tools available for accelerating resource-intensive operations. However, governing how and when PostgreSQL deploys parallel workers requires a nuanced understanding of internal planner parameters.

Among the most critical, yet frequently misunderstood, tuning knobs in PostgreSQL are min_parallel_table_scan_size and min_parallel_index_scan_size. Far from being simple binary thresholds, these parameters dictate both the eligibility of relations for parallel execution and the complex algorithmic "ladder" that the query planner climbs to determine worker allocation.

Main Facts: Decoding the Minimums

At their core, min_parallel_table_scan_size and min_parallel_index_scan_size define the baseline physical size requirements a relation must meet before the PostgreSQL query planner will even consider it for a parallel execution plan.

• Table Scans: The default value for min_parallel_table_scan_size is set to 8MB. If the query planner estimates that a table scan will read less than this threshold, a parallel plan is immediately disqualified from consideration.
• Index Scans: The default value for min_parallel_index_scan_size is set to 512kB. Similarly, index scans projected to read fewer blocks than this minimum bypass the parallel computation queue.

Crucially, these settings do more than just act as gatekeepers. They form the foundational bottom rung of a geometric scaling ladder. Each time a relation’s size triples past the initial minimum threshold, the planner attempts to allocate an additional parallel worker, up to the maximum limit imposed by other configurations like max_parallel_workers_per_gather.

Both parameters operate within a user context, meaning they can be modified dynamically at the session level, per role, or per database. The configurable range spans from 0 to 715,827,882 blocks (approximately 5.3TB, or one-third of INT_MAX), a boundary specifically engineered to prevent overflow during internal calculations. Furthermore, administrators must pay close attention to unit specifications; omitting a unit like MB treats the raw integer as a measurement in database blocks (typically 8kB each), which can drastically alter expected behavior.

SET min_parallel_table_scan_size = 8;
SHOW min_parallel_table_scan_size;
-- Result: 64kB (treating '8' as 8 blocks, not 8 megabytes)

Chronology: The Evolution of PostgreSQL Parallelism

The architecture governing parallel scans in PostgreSQL has matured significantly over the years, transforming from a monolithic parameter into the granular controls seen in modern releases, such as PostgreSQL 19.

The Era of PostgreSQL 9.6: A Single Parameter

When parallel query execution was first introduced in PostgreSQL 9.6, the system relied on a single unified setting: min_parallel_relation_size. Because this parameter attempted to govern both table scans and index scans simultaneously, it lacked the precision needed for complex schemas where table sizes and index sizes varied wildly. The underlying logic, including the tripling loop used to scale worker counts, was drafted during this period with temporary comments in the source code noting that the system "needed something here for now."

PostgreSQL 10: Granular Separation

With the release of PostgreSQL 10, the database engine gained support for parallel index scans and parallel bitmap heap scans. To accommodate these advanced execution paths, the single legacy parameter was split into the modern duo: min_parallel_table_scan_size and min_parallel_index_scan_size. This separation allowed database administrators to fine-tune table and index parallelism independently.

Modern Enhancements (PostgreSQL 17 through 19)

Over subsequent major releases, PostgreSQL has continuously expanded the reach of these parallel controls. PostgreSQL 17 introduced parallel builds for BRIN (Block Range Index) indexes. Most recently, PostgreSQL 19 added further capabilities, including parallel TID (Tuple Identifier) range scans and expanded parallel autovacuum coverage, ensuring that the logic established by these minimum scan parameters influences background maintenance tasks just as heavily as front-end analytical queries.

Supporting Data: The Mechanics of the Scaling Ladder

Understanding how the planner utilizes these minimum sizes requires examining the interaction between the size thresholds, the cost model, and the worker allocation ladder.

The Tripling Loop and Worker Allocation

When a table exceeds min_parallel_table_scan_size, the query planner calculates the number of workers by scaling up from the base size in increments of three. Consider a 116MB table evaluated under various settings for min_parallel_table_scan_size (with max_parallel_workers_per_gather set to 16 and parallel cost parameters zeroed out to isolate the ladder’s effect):

The anomaly at 0 deserves careful attention. Setting the parameter to 0 does not disable the minimum; instead, the internal source code floors the starting point at a single block (8kB). The ladder then ascends geometrically: 8kB, 24kB, 72kB, and so on. For a 116MB table, this results in eight tripling steps, aggressively assigning up to nine workers.

While PostgreSQL regression tests routinely set these minimums to 0 to force parallel plans out of small toy tables, doing this in a production environment can be catastrophic. On a constrained test instance, a filtered count(*) over a modest 9.6MB table ran serially in roughly 10ms, but execution time degraded to 37ms when injected with seven parallel workers due to unnecessary inter-process coordination overhead.

Eligibility Versus Cost-Based Choice

Meeting the minimum scan size only makes a query eligible for a parallel plan. Whether the planner ultimately chooses it depends entirely on a strict cost comparison—primarily weighed against parallel_setup_cost (default 1000).

• Narrow Rows and Cheap Filters: For tables with high row density per page, cost models typically do not favor parallelism until the dataset reaches approximately 20MB to 25MB, meaning the default 8MB minimum is satisfied long before the cost model agrees to parallelize.
• Expensive Workloads: As per-row computational overhead increases (e.g., complex user-defined functions in the WHERE clause), smaller tables become viable candidates for parallelism at lower thresholds, such as 4MB.

Exceptions to the Rule

Certain architectural patterns bypass standard minimum calculations entirely:

1. Explicit Storage Parameters: Setting parallel_workers directly on a table definition overrides global minimum constraints.
2. Append Child Nodes: Partitions, inheritance trees, and individual arms of a UNION ALL statement generate parallel paths regardless of their individual sizes, operating on the principle that numerous small partitions aggregate into a massive workload. Sixteen 2MB hash partitions will comfortably produce a Parallel Append plan even if the table scan minimum is locked at 1GB.
3. Lookup Joins: Small tables falling well below the minimum threshold can still feature inside parallel execution plans; they simply cannot serve as the driving table whose scan is partitioned among workers.

Official Responses and Expert Insights

Database internals engineers and community experts consistently emphasize that these parameters should be handled with extreme care. The official documentation and source code notes indicate that the geometric scaling mechanisms were designed as pragmatic heuristics rather than mathematically optimal predictors.

When consulted on production tuning issues, PostgreSQL performance engineers offer clear directives:

• Leave Defaults Intact for General Workloads: In most production environments, the default settings (8MB for tables, 512kB for indexes) strike an optimal balance between avoiding unnecessary parallel overhead on small queries and enabling parallelism for truly heavy analytical scans.
• Diagnose the Root Cause: If queries are executing in parallel when they are too small to benefit, the correct parameter to adjust is parallel_setup_cost, not the scan sizes. Conversely, if massive tables are starved of workers, administrators should look to max_parallel_workers_per_gather.
• Beware the Copy-Paste Trap: Any configuration file found in a production environment featuring min_parallel_table_scan_size = 0 outside of an explicit automated test suite typically indicates that a developer or administrator improperly copied settings from a testing tutorial.

Implications for Maintenance and Operations

The influence of min_parallel_table_scan_size and min_parallel_index_scan_size extends far beyond analytical SELECT queries, directly impacting intensive database maintenance operations.

Index Creation (CREATE INDEX)

When executing a CREATE INDEX statement, PostgreSQL consults the session’s min_parallel_table_scan_size to determine how many workers to deploy for scanning the underlying table.

• Raising this parameter globally in postgresql.conf to suppress parallel queries on small tables will inadvertently force all index builds on tables below that size to execute completely serially.
• For instance, setting the minimum to 1GB will cause B-tree and BRIN index builds on a 482MB table to drop their parallel worker requests and run as single-threaded processes. This behavior directly impacts routine administrative operations, including every single index built during a pg_restore procedure.

Vacuuming (VACUUM)

Manual and automated vacuum processes leverage min_parallel_index_scan_size to evaluate which indexes are sufficiently large to warrant parallel worker allocation. Unlike query planning—which relies on statistical estimates of index page reads—VACUUM evaluates the exact, real-world size of the index on disk.

• Indexes falling below the threshold are processed serially.
• Explicitly lowering the index scan minimum within a maintenance session (SET min_parallel_index_scan_size = 0) immediately allows commands like VACUUM (PARALLEL 2) to distribute index cleanup across multiple background workers.

Strategic Tuning Recommendations

For database administrators seeking to optimize mixed-workload clusters, the safest strategy involves targeted tuning rather than global overrides:

1. Isolate Heavy Roles: If specific analytical roles require higher concurrency limits (e.g., raising max_parallel_workers_per_gather to 8), those same roles may encounter excessive worker allocation on medium-sized tables.
2. Stretch the Ladder Deliberately: Administrators can apply a tailored setting such as min_parallel_table_scan_size = '64MB' to specific high-concurrency database roles. This effectively pulls the worker allocation ladder down, ensuring that medium-sized tables request fewer workers without tampering with the global server defaults or sacrificing performance on multi-gigabyte relations.

By respecting the underlying mechanics of PostgreSQL’s scaling ladders and avoiding blanket zero-value configurations, administrators can harness the full power of parallel execution while maintaining the pristine efficiency of lightweight transactional queries.