Looks like you can handle 256MB work_mem. Give it a shot and monitor temp_files. You do have log_temp_files = 0, right? Also, keep an eye out for "out of memory" log file errors. What PG version are you using?
Karthik Krishnakumar wrote on 3/23/2023 6:21 AM:RAM - 256GB
shared_buffers - 64GB
maintenance_work_mem - 1GB
work_mem - 24MB
there are ~50 active connections at any given time.
from pg_badger - average temp file size for bulk insert/update is about 200MB, max size is multiple GB's depending on the table.
it is a write heavy workload - with inserts/updates happening around the clock.
thanks
karthik
Regards,
Michael Vitale
Michaeldba@sqlexec.com
703-600-9343