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
