I'm new here, so forgive me if this is a bad idea or my lack of knowledge on
how to optimize PostgreSQL.
I find PostgreSQL to be great with a large number of small transactions,
which covers most use cases. However, my experience has not been so great
on the opposite end -- a small number of large transactions, i.e. Big Data.
I had to increase work_mem to 3GB to stop my queries from spilling to disk.
However, that's risky because it's 3GB per operation, not per
query/connection; it could easily spiral out of control.
I think it would be better if work_mem was allocated from a pool of memory
as need and returned to the pool when no longer needed. The pool could
optionally be allocated from huge pages. It would allow large and mixed
workloads the flexibility of grabbing more memory as needed without spilling
to disk while simultaneously being more deterministic about the maximum that
will be used.
Thoughts?
Thank you for your time.
Joseph D. Wagner
My specifics:
-64 GB box
-16 GB shared buffer, although queries only using about 12 GB of that
-16 GB effective cache
-2-3 GB used by OS and apps
-the rest is available for Postgresql queries/connections/whatever as
needed