After working with PostgreSQL for many years, my best advice (for repeated queries, e.g. often hit Web app queries) is to make sure they all use indexes and all the indexes fit in memory.
You can get sizes from: SELECT relname,relfilenode,relpages*8/1024 AS MB FROM pg_class ORDER BY MB DESC LIMIT 10;
Newer versions avoid bloat better, but it can still happen if you don't vacuum full, which you don't because of downtime implications. So every now and then it is good to recopy everything in.
I think the article does a decent job of explaining how important properly written SQL is to the health of an application.
I disagree with the notion that you should rely on your hardware or juice up your system to gain performance. This should be a last resort only after ensuring that you have well written SQL running on a properly laid-out database.*
*caveat: Hardware can sometimes be cheaper than paying somebody to write SQL statements the right way.
There are another great tutorial about performance tuning from IBM - Informix Dynamic Server Performance Guide - all those ideas are discussed in that volume.
By the way, Informix Dynamic Server itself is an advanced and very stable product. From version 7.30 which was released almost a decade ago it offers advanced functionality with low resource consumption. (No bloated java stuff and other buzzwords).
It was a collection of multithreaded apps written in portable c++ which were purchased by IBM for $1bn.
I personally had run several big installations for some chain of retail stores in 2000, when all those magazines were never mention linux and informix as enterprice solution.
Today Informix was resurected by IBM which once decided to discontinue it in favour to support its DB/2 monster.
You can get sizes from: SELECT relname,relfilenode,relpages*8/1024 AS MB FROM pg_class ORDER BY MB DESC LIMIT 10;
Newer versions avoid bloat better, but it can still happen if you don't vacuum full, which you don't because of downtime implications. So every now and then it is good to recopy everything in.