If you do very large aggregates, you may need even 1GB on work_mem. However, a setting that high would require very careful tuning and reduction of space used by shared_buffers and the ZFS ARC. Its dangerous since each connection with a large aggregate or sort may consume a lot of memory.
Well, some taks may need a lot, but I guess most wil do fine with the settings we used right now.
So It looks like I can tune the ARC to use more memory, and also increase shared_mem to let postgres cache more tables?
I would recommend tuning one upwards, and leaving the other smaller. The worst case is when they are both similarly sized, it leaves the most opportunity for duplication of data, and produces the worst feedback on checkpoint writes.
You may want to compare the performance with:
larger ARC and smaller shared_buffers
vs
smaller ARC and larger shared_buffers
The results will be rather dependent on how you use postgres, the types of queries you do, and for writes, how you tune checkpoints and such.
