replacement_sort_tuples

Definition PG 10 manual

When the number of tuples to be sorted is smaller than this number, a sort will produce its first output run using replacement selection rather than quicksort. This may be useful in memory-constrained environments where tuples that are input into larger sort operations have a strong physical-to-logical correlation. Note that this does not include input tuples with an inverse correlation. It is possible for the replacement selection algorithm to generate one long run that requires no merging, where use of the default strategy would result in many runs that must be merged to produce a final sorted output. This may allow sort operations to complete sooner.

The default is 150,000 tuples. Note that higher values are typically not much more effective, and may be counter-productive, since the priority queue is sensitive to the size of available CPU cache, whereas the default strategy sorts runs using a cache oblivious algorithm. This property allows the default sort strategy to automatically and transparently make effective use of available CPU cache.

Setting maintenance_work_mem to its default value usually prevents utility command external sorts (e.g., sorts used by CREATE INDEX to build B-Tree indexes) from ever using replacement selection sort, unless the input tuples are quite wide.

How it works

In PG10, replacement_sort_tuples selected when an in-memory sort used replacement selection to produce a long initial run for external merge sorting. It was removed in PG11 with the old replacement-selection path.

The value counted tuples, not bytes, so its memory implications depended on row width and work_mem. It was an algorithm threshold rather than a general sort-memory ceiling.

Modern PostgreSQL does not recognize the parameter. Migration should delete it and tune current sort behavior through work_mem, plan shape, and measured temporary-file use instead. Its user context permits session- or transaction-local changes; newly performed or newly planned work sees the value.

Operational considerations

Changing replacement_sort_tuples without applying its documented unit and configuration context.

Optimizing an isolated benchmark while ignoring concurrent aggregate resource use.

Assuming a configured value guarantees operating-system or storage behavior.

Failing to retest startup, failover, and workload latency after the change.

Workload guidance

OLAP: Do not carry replacement_sort_tuples into a modern analytical cluster. Benchmark the supported current mechanisms instead of trying to emulate a removed implementation detail.

OLTP: Do not tune replacement_sort_tuples on current PostgreSQL: remove it from upgrade targets and use the current replacement behavior described above. Retain it only when reproducing the historical version.

SMALL: Delete replacement_sort_tuples during version migration; an unknown-parameter startup failure is more likely than a benefit. Historical test instances should keep the old upstream default.