Advanced YSQL Index Backfill Tuning for Low-Tablet-Count and Partitioned Tables

YugabyteDB’s distributed index backfill can take advantage of multiple tablets and YB-TServers to build an index in parallel. In most environments, the standard index-backfill tuning controls are sufficient.

However, there are some special cases where additional tuning can make a significant difference:

● A very large table has only one or a few tablets.
● Increasing normal backfill concurrency does not improve performance.
● The table is partitioned.
● A maintenance window allows the cluster to temporarily dedicate more CPU and disk bandwidth to index creation.
● You want to push backfill performance beyond the normal conservative settings.

This tip focuses specifically on those cases.

Start with the standard tuning recommendations first.

This tip is a companion to:

How to Speed Up YSQL Index Backfill (Especially on Large Tables)

That tip covers the normal starting points, including backfill_index_write_batch_size, backfill RPC timeouts, num_concurrent_backfills_allowed, rate limiting, and monitoring cluster load. The recommendations below are intended for situations where those techniques alone are not enough.

The existing tip covers the standard index backfill tuning controls. Before moving into more aggressive tuning, however, first determine whether the table has enough tablets to take advantage of distributed backfill parallelism.

Check the Tablet Count First

Before increasing batch sizes, fetch sizes, or write concurrency, determine how many tablets the source table actually has.

A large table with many tablets can distribute backfill work across multiple YB-TServers. A similarly sized table with only one or a few tablets has much less natural parallelism, so increasing backfill concurrency alone may provide limited benefit.

This matters because YugabyteDB’s online index backfill uses distributed workers to backfill data from source-table tablets. num_concurrent_backfills_allowed controls how many backfill jobs can execute on each YB-TServer, but that concurrency is only useful when there is enough underlying work to execute in parallel. YugabyteDB automatically chooses the default backfill concurrency based on CPU count.

For example, imagine two tables that each contain 100 million rows:

				
					Table A
100 million rows
24 tablets
Distributed across multiple YB-TServers

				
					Table B
100 million rows
1 tablet
Hosted by one tablet leader

Table A exposes substantially more natural parallelism.

Table B can become constrained by the amount of work that can be driven through a single source tablet.

Additional recommendation: Treat tablet count as part of the index-backfill tuning exercise. If a very large table has only one or a few tablets, simply increasing num_concurrent_backfills_allowed may provide little benefit because there may not be enough independent tablet work available to use the additional workers.

Prefer Tablet-Level Parallelism Before Aggressive Tuning

The best scenario is to have enough tablets that YugabyteDB can naturally distribute the work.

If practical, address insufficient tablet parallelism before compensating for it with extremely large batches.

This gives you two different tuning approaches:

Preferred:

				
					More source tablets
        |
        +-- More independent backfill work
        |
        +-- More YB-TServers participate
        |
        +-- Better distributed parallelism

Special Case:

				
					Very few source tablets
        |
        +-- Limited natural parallelism
        |
        +-- Increase work done by each backfill task
        |
        +-- Larger fetches
        +-- Larger backfill batches
        +-- More writes in flight

The second approach can work very well, but it intentionally concentrates more CPU and disk activity on the YB-TServers hosting the relevant table and index tablets.

Partitioned Tables: Build the Child Indexes Independently

Partitioned tables deserve special treatment.

YugabyteDB does not support CREATE INDEX CONCURRENTLY directly against a partitioned parent table. The documented approach is to create the parent index using ONLY, build corresponding indexes on the individual partitions, and attach those indexes to the parent.

Assume an anonymized table such as:

				
					CREATE TABLE activity_history (
    account_id BIGINT,
    activity_date DATE,
    activity_id BIGINT,
    activity_type TEXT
)
PARTITION BY RANGE (activity_date);

Create the parent index definition:

				
					CREATE INDEX activity_history_lookup_idx
ON ONLY activity_history (
    account_id,
    activity_date,
    activity_id
);

The parent index initially remains invalid.

Now create the indexes on the partitions:

				
					CREATE INDEX CONCURRENTLY activity_history_2026_01_idx
ON activity_history_2026_01 (
    account_id,
    activity_date,
    activity_id
);

CREATE INDEX CONCURRENTLY activity_history_2026_02_idx
ON activity_history_2026_02 (
    account_id,
    activity_date,
    activity_id
);

Then attach them:

				
					ALTER INDEX activity_history_lookup_idx
ATTACH PARTITION activity_history_2026_01_idx;

ALTER INDEX activity_history_lookup_idx
ATTACH PARTITION activity_history_2026_02_idx;

Once all required partition indexes are attached, YugabyteDB promotes the parent index out of its invalid state.

Additional recommendation: For large partitioned tables, treat each partition as an independent index-build opportunity. This allows child indexes to use concurrent backfill and also gives you more control over scheduling index creation across partitions.

Large Table, Small Tablet Count: Tune the Fetch Side Too

The standard index-backfill tuning discussion usually concentrates on:

				
					backfill_index_write_batch_size
num_concurrent_backfills_allowed
backfill timeouts

But when the source table has very little tablet-level parallelism, there are additional controls worth evaluating.

These include:

● yb_fetch_size_limit
● yb_fetch_row_limit
● ysql_max_in_flight_ops

yb_fetch_row_limit defaults to 1024, while yb_fetch_size_limit defaults to 0. YugabyteDB documents that setting yb_fetch_row_limit to 0 allows fetches to be governed by the size-based limit instead. If both limits are enabled, the lower effective limit controls the fetch.

Setting	Default	Advanced Test Value	Purpose
`yb_fetch_size_limit`	`0`	Start around `10MB` and test larger values where appropriate.	Allows more source-table data to be returned in each fetch.
`yb_fetch_row_limit`	`1024`	`0` when intentionally using size-based fetching.	Removes the row-count limit so the size limit can control the fetch.
`ysql_max_in_flight_ops`	Verify for your YugabyteDB release.	Values such as `40000` can be evaluated when CPU and disk headroom are available.	Allows more asynchronous write operations to remain outstanding before the pipeline must wait.

YugabyteDB’s YSQL implementation uses ysql_max_in_flight_ops as one of the controls around buffered and asynchronous operations.

Example Advanced Session Settings

One configuration worth testing is:

				
					ALTER ROLE postgres SET yb_fetch_size_limit = '10MB';
ALTER ROLE postgres SET yb_fetch_row_limit = 0;
ALTER ROLE postgres SET ysql_max_in_flight_ops = 40000;

This example assumes normal application traffic does not connect using the postgres role.

Using a dedicated administrative role would make the scope even clearer.

After the index maintenance is complete:

				
					ALTER ROLE postgres RESET yb_fetch_size_limit;
ALTER ROLE postgres RESET yb_fetch_row_limit;
ALTER ROLE postgres RESET ysql_max_in_flight_ops;

Additional recommendation: Prefer applying aggressive YSQL settings to a dedicated maintenance role rather than changing behavior for every application session. This also makes it easier to return the environment to its normal configuration after the index build.

Do Not Treat Very Large Backfill Batches as General Defaults

The documented default for backfill_index_write_batch_size is 128.

Current YugabyteDB documentation suggests testing values such as 1024 or 2048 when index creation on large tables is slower than expected. It also warns that timeout settings may need to be adjusted when larger batches are used.

However, controlled experiments with an extremely low tablet count have shown that much larger values can sometimes produce dramatic improvements.

For example:

				
					backfill_index_write_batch_size = 1000000

or even:

				
					backfill_index_write_batch_size = 10000000

These should be treated very differently from normal tuning.

Additional recommendation: Use values in the hundreds of thousands or millions only as controlled, workload-specific experiments. They are not general production recommendations. Test progressively and watch CPU, disk throughput, write latency, and backfill retries after every increase.

Separate Performance Tuning From Timeout Protection

Another useful way to think about the settings is to divide them into two categories.

Settings That Can Increase Performance

				
					num_concurrent_backfills_allowed
backfill_index_write_batch_size
yb_fetch_size_limit
yb_fetch_row_limit
ysql_max_in_flight_ops

These settings can increase the amount of work performed concurrently or per backfill operation.

Settings That Primarily Prevent Timeouts

				
					ysql_index_backfill_rpc_timeout_ms
backfill_index_timeout_grace_margin_ms

Increasing timeout settings doesn’t inherently make the index build faster.

Instead, the larger timeout allows an intentionally larger backfill operation enough time to finish instead of being treated as failed and retried.

YugabyteDB currently defaults backfill_index_timeout_grace_margin_ms to -1, which allows the system to calculate the margin automatically. For YSQL index backfill, the automatic baseline is three minutes.

Additional recommendation: Don’t increase timeout values simply because an index build is slow. Increase them when the work performed by an individual backfill operation has intentionally become larger and therefore legitimately requires more time.

Controlled Test Results

The following anonymized tests show why this type of tuning can be useful.

Both source tables were intentionally limited to one tablet, and the resulting index also used one tablet.

Test	Rows	Table Size	Index Size	Default	Tuned	Improvement
Large test	90 million	10.35 GB uncompressed 2.37 GB compressed	6.3 GB uncompressed 1.8 GB compressed	2500 seconds	760 seconds	Approximately 3.3× faster
Medium test	9 million	1 GB uncompressed 325 MB compressed	640 MB uncompressed 170 MB compressed	165 seconds	70 seconds	Approximately 2.4× faster

The aggressive test configuration included…

Large test:

				
					backfill_index_write_batch_size = 10000000
yb_fetch_row_limit              = 40000
ysql_max_in_flight_ops          = 10000

Medium test:

				
					backfill_index_write_batch_size = 10000000
yb_fetch_row_limit              = 30000
ysql_max_in_flight_ops          = 10000

The important point isn’t that these exact values should be copied.

A Better Tuning Order

For these special cases, it’s recommend using the following order:

1. Determine how many tablets the source table has.
2. Determine where those tablets are hosted.
3. Confirm that the cluster has sufficient CPU and disk headroom.
4. If the table has adequate tablet parallelism, tune num_concurrent_backfills_allowed before using extreme per-worker settings. YugabyteDB warns that excessive concurrency can interfere with foreground work and can actually cause more backfill retries if CPU becomes saturated.
5. If the table has only one or a few tablets, experiment with fetch size, row limits, and write concurrency.
6. Increase backfill_index_write_batch_size progressively rather than immediately jumping from 128 to millions of rows.
7. Adjust backfill timeouts only when the larger operations legitimately need more time.
8. For partitioned tables, build and attach child indexes individually rather than performing the entire operation through the parent.
9. Monitor foreground SQL latency along with the index build. A faster index build isn’t necessarily a win if it severely impacts the application.
10. Reset temporary tuning after the maintenance operation.

Final Takeaway

The normal YugabyteDB index-backfill tuning recommendations should remain the starting point.

But when a large table has very few tablets, the problem changes.

At that point:

● More backfill workers

may not be enough.

The additional levers become:

● More data per fetch
● More rows per backfill task
● More writes in flight
● Long enough deadlines for those larger operations

For partitioned tables, there is another opportunity: build the child indexes independently and attach them to the parent.

The most important additional recommendation is therefore to understand where the parallelism is coming from before tuning the backfill. A 100-million-row table distributed across many tablets and a 100-million-row table stored in one tablet may require very different strategies.

Have Fun!

Today’s super-early weekend movie: Spider-Man: Brand New Day at 8:30 AM! 🕷️🎬

I thought it was pretty good! I missed the last Spider-Man movie, so I was a little confused about why his girlfriend and best friend couldn’t remember him. 😂 Apparently, I have some catching up to do!

Still, it was entertaining and a fun way to start the weekend.