| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Thomas Carroll <tomfecarroll(at)yahoo(dot)com> |
| Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Memory exhaustion due to temporary tables? |
| Date: | 2018-12-10 20:45:27 |
| Message-ID: | 2584.1544474727@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Thomas Carroll <tomfecarroll(at)yahoo(dot)com> writes:
> Postgres version: 10.5. work_mem setting: 4MB, shared_buffers setting: 800 MB, connections typically around 30-40.
I imagine you checked this already, but ... what is temp_buffers set to?
That constrains the maximum memory used for temporary-table buffers in
each process, and an unreasonable setting for it could lead to the
described behavior.
Another thing to keep in mind with long-lived "temporary" tables is
that autovacuum can't do anything with them; so it's incumbent on your
application to periodically VACUUM and/or ANALYZE them as needed.
Otherwise such tables will bloat, which could contribute to excessive
use of temporary-table buffers.
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Laurent FAILLIE | 2018-12-10 21:15:26 | pg_dump: ERROR: array size exceeds the maximum allowed (268435455) |
| Previous Message | Laurenz Albe | 2018-12-10 20:04:41 | Re: Memory exhaustion due to temporary tables? |