It's been a while since I've been in the weeds, but this is what I remember:
It's a balancing act between max_connections and shared_buffers. Each instance type will have a sweet spot for your use case -- you'll have to find it through experimenting.
Give as much RAM to Postgres as possible -- let it do the memory management. Sometimes going to an instance with double the RAM will give you more than double the performance.
No swap on the box. The moment you hit swap you're screwed.
Vacuum often, maybe continuously, if the database has a lot of updates or deletes. Do your capacity planning so you can vacuum all the time.
1. Swap space does not inherently slow down your system. In fact, not having swap space doesn't mean you won't swap pages. It merely means that Linux has fewer choices about what RAM can be reused when a demand hits. Thus, it is possible for the throughput of a system that has no swap space to be lower than that of a system that has some.
2. Swap space is used for modified anonymous pages only. Your programs, shared libraries and filesystem cache are never written there under any circumstances.
3. Given items 1 and 2 above, the philosophy of “minimization of swap space” is really just a concern about wasted disk space.
...
I find the better balance is to use a file-based swap, at least while experimenting with sizes, the performance loss from the FS overhead really is minimal.
I've heard the "no swap" argument before, but can you explain this a little further?
It seems like there are two potential situations here:
1/ You have swap, you hit swap. Massive slowdown.
2/ You don't have swap, run out of memory and processes die.
#1 is not a good situation, but it seems preferable to #2 doesn't it?
It's a balancing act between max_connections and shared_buffers. Each instance type will have a sweet spot for your use case -- you'll have to find it through experimenting.
Read this: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serve...
And this: http://www.postgresql.org/docs/9.2/static/kernel-resources.h...
Give as much RAM to Postgres as possible -- let it do the memory management. Sometimes going to an instance with double the RAM will give you more than double the performance.
No swap on the box. The moment you hit swap you're screwed.
Vacuum often, maybe continuously, if the database has a lot of updates or deletes. Do your capacity planning so you can vacuum all the time.
I'll update if I think of more.