High memory usage and potential out-of-memory errors during ordered secondary index scans

Description

On affected versions, a query that uses a secondary index to return rows in index order can consume far more memory than expected. The PostgreSQL backend running the query keeps base table data it has already returned in memory until the scan finishes. Peak memory for that backend grows with the total size of the rows the scan reads. A large scan can exhaust available memory and cause an out-of-memory (OOM) error.

The query results are correct. This issue affects memory usage only.

The plans most likely to trigger it are:

• An ORDER BY that the planner satisfies with an Index Scan on a secondary index, instead of a separate Sort step.
• A Merge Join whose inputs include an Index Scan on a secondary index that supplies the ordering for the merge.

Queries that read a few rows through the index are unlikely to see a noticeable difference. The risk is highest for ordered scans over many rows, or over wide rows, where the index does not cover all the selected columns.

Mitigation

Upgrade to a release that contains the fix (see the above table).

Details

The issue was introduced by a refactor of hash permutation handling in PgGate. That change went into v2026.1 and was backported to the v2025.2 series, first released in v2025.2.1.0. Earlier releases (v2025.2.0 and earlier) do not have the issue.

When a secondary index scan must return rows in index order, YSQL reads the index to get row identifiers (ybctid values) in order and then fetches the matching base table rows in batches. Results from these batches are merged to preserve ordering. On affected versions, the merge step adds a new set of read streams for every batch of base table rows but never releases streams that have finished. Their response buffers stay allocated for the rest of the query. The fix removes finished streams from the merge as the scan progresses, so memory is reclaimed during the scan.

You can check whether a query is affected by running it with EXPLAIN (ANALYZE) and looking at Peak Memory Usage. On an affected version, an ordered secondary index scan reports peak memory close to the total size of the rows it read.

Example

Consider a table of 120,000 rows with about 10 kB per row (roughly 1.2 GB in total), and a range index on a column that does not cover the wide column. Load the rows in batches of 10,000. The following statements create the table and index, insert one batch, and run the scan. Repeat the INSERT with non-overlapping key ranges until the table contains 120,000 rows before you run EXPLAIN.

CREATE TABLE t (a int, b int, payload text);
CREATE INDEX t_a_idx ON t (a ASC);
-- Load 120,000 rows in batches of 10,000, for example:
INSERT INTO t SELECT i, i, repeat('x', 10000) FROM generate_series(1, 10000) i;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM t ORDER BY a;

On v2025.2.5.2, this scan reports a peak memory usage of about 1.1 GB:

 Index Scan using t_a_idx on t (actual rows=120000 loops=1)
 ...
 Peak Memory Usage: 1177114 kB

The same query on v2025.2.0.1 reports a peak memory usage of about 13 MB.