You need to monitor shared buffers cache hit rate (from pg_stat_database view) - if that's low, increase shared buffers. Then monitor and tune slow queries - if a slow query benefits from higher work_mem values, do increase that value. It's nonsense to just increase the parameters to consume more memory.
Makes perfect sense! The system is a OLTP and unfortunately has some issues about how big the single lines are(too many colunms). In some cases I have to bring to app 150k lines(in some not so rare cases, 200k ~300k) to process in a single transaction, then update and insert new rows. It's works fine, except when eventually start to outOfMemory or Connection has been closed forcing us to restart the application cluster. Finally I'll have access to a performance environment to see how is configured(they promised me a production mirror) and then get back to you to provide more detailed information.
Thanks for you time!
Ranier,
Are you using nested connections?
What do you mean with "nested connections"? If you are talking about nested transactions, then yes, and I'm aware of subtransaction problem but I think this is not the case right now (we had, removed multiple points, some other points we delivered to God's hands(joking), but know I don't see this issue)
I mean "nested", even.
Two or more connections opened by app.
If this is case, is need processing the second connection first,
