maintenance_work_mem formula lands too low for compression throughput: +70% measured from raising it, plateaus by 1.5 GB #1777

Field measurement from a production instance (16 GB RAM class host), three real data points during a one-time compression catch-up of pre-#1767 backlog chunks. Numbers are measured, not estimated.

The formula

The installer sets maintenance_work_mem = min(5% of host RAM, 1 GB) (see DarlingManagedPostgresTests.cs). On a 16 GB host that lands at 809 MB. TimescaleDB compression's sort/segment-building uses maintenance_work_mem (not work_mem), so this value directly gates compression throughput.

Measurements

• BASELINE (809 MB, query_stats chunk): 87,270,375,424 bytes -> 17,270,996,992 (5.05x) in 2h 32m 24s = ~9.1 MB/s of uncompressed input. Duration confirmed by the backend's own "connection to client lost" log line, not a guess.
• TRY 1 (1536 MB, procedure_stats chunk): 27,023,474,688 -> 7,046,070,272 (3.84x) in 27m 37s = 15.5 MB/s. Null-hypothesis check: pure linear scaling from baseline predicts ~2,833s; actual 1,657s = 58.5% of predicted. Real effect, not noise.
• TRY 2 (4096 MB, procedure_stats chunk): 21,928,402,944 -> 5,913,698,304 (3.71x) in 21m 38s = 16.1 MB/s. Scaling try 1's improved rate predicts ~1,317s; actual 1,298s. Going 2.7x further past 1536 MB bought nothing measurable.

Conclusion and honest limits

Raising maintenance_work_mem above the current formula's landing point bought ~+70% compression throughput and PLATEAUS fast: 1536 MB already captured the benefit. Unknown: the exact threshold between 809 MB and 1536 MB. The three points are different tables at different sizes - a strong directional signal, not a controlled experiment.

Fix shape

1. Controlled repro first: same chunk (or equal-size clones), only maintenance_work_mem varying, to find the threshold before committing a new default.
2. Then adjust the FORMULA, not just the cap - 809 MB sits UNDER the existing 1 GB cap, so the 5%-of-RAM term is what lands too low on 16 GB hosts. Candidate shape from the field (recorded as a hypothesis, not a recommendation): max(1.5 GB, min(5% RAM, cap)) so small-RAM hosts get a usable floor - but the floor must respect small hosts' actual RAM (a 4 GB host cannot give 1.5 GB to maintenance_work_mem; the floor needs its own guard).
3. Session-level override for manual/catch-up compression is already possible (SET maintenance_work_mem) and is how these measurements were taken; the default only needs to serve the SCHEDULED path.

Context: the chunks involved were pre-#1767 backlog (inline payload era). Post-dedup steady-state chunks are far smaller, so urgency is moderate - but every store still compresses every chunk, and +70% throughput on the background job is worth having. Related: scheduling half of the same saga in the companion issue.