Re: Changing work_mem

On 08/13/2019 07:41 PM, Peter Eisentraut wrote:
> On 2019-08-13 17:16, rihad wrote:
>> If I increase it in postgresql.conf and SIGHUP the master server, will
>> the change be applied to all running backends, or only to the ones
>> started after the change? Thanks.
> It will be applied to all running backends.
>

Thanks, but this isn't what I'm seeing in the logs.

After I increased work_mem from 256MB to 512MB I still see lines even 
with very small files created as before, including much larger ones, 
hundreds of MB.


[dbname] LOG:  temporary file: path 
"base/pgsql_tmp/pgsql_tmp93683.257381", size 594


It seems unlikely that the temp file is still 594 bytes bigger than 
512MB after the change.

Maybe some other stuff unconditionally creates temp files regardless of 
what's in work_mem?

All these "tempies" are the reason our SSD disks hosting a single 
database are seeing 1TB writes in a day, according to SMART.