Horton

Re: Default setting for enable_hashagg_disk

Peter Eisentraut <peter.eisentraut@2ndquadrant.com>

View thread View on postgr.es
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Jeff Davis <pgsql@j-davis.com>, pgsql-hackers@postgresql.org
Date: 2020-07-13T15:12:30Z
Lists: pgsql-hackers, pgsql-docs 
On 2020-07-13 16:11, Tomas Vondra wrote:
> Why is running out of disk space worse experience than running out of
> memory?
> 
> Sure, it'll take longer and ultimately the query fails (and if it fills
> the device used by the WAL then it may also cause shutdown of the main
> instance due to inability to write WAL). But that can be prevented by
> moving the temp tablespace and/or setting the temp file limit, as
> already mentioned.
> 
> With OOM, if the kernel OOM killer decides to act, it may easily bring
> down the instance too, and there are much less options to prevent that.

Well, that's an interesting point.  Depending on the OS setup, by 
default an out of memory might actually be worse if the OOM killer 
strikes in an unfortunate way.  That didn't happen to me in my tests, so 
the OS must have been configured differently by default.

So maybe a lesson here is that just like we have been teaching users to 
adjust the OOM killer, we have to teach them now that setting the temp 
file limit might become more important.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Commits

  1. Add hash_mem_multiplier GUC.

  2. HashAgg: use better cardinality estimate for recursive spilling.

  3. Remove hashagg_avoid_disk_plan GUC.

  4. Doc fixup for hashagg_avoid_disk_plan GUC.

  5. Rework HashAgg GUCs.

  6. Disk-based Hash Aggregation.

  7. Implement partition-wise grouping/aggregation.

  8. Defer creation of partially-grouped relation until it's needed.