Here are a few extra tips:<p>- "select relname, indexrelname, idx_scan from pg_stat_user_indexes" will show how many times an index is used, so you can remove unused indexes<p>- you can create indexes without locking tables (ie, while your site is up) by using "create index concurrently"<p>- you should use the autovaccuum daemon instead of manually running vacuum analyze in most cases
This is particularly important if you are working on Heroku. I just made the switch from MySQL to Postgres and while they both run SQL, they have numerous syntactical and performance considerations that need to be addressed so you can transition into the Heroku cloud easier.<p>It's definitely worth considering for 9 and up, as at least when I was in grad school for databases (~2008), MySQL outperformed Postgres.
I can vouch for the inner joins blowing outer joins out of the water. Setting up your postgresql.conf appropriate to its environment is helpful as well, <a href="http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server" rel="nofollow">http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serve...</a> has a good deal of explanation.
Put your write ahead logs on a mirrored set of ssds as long as your write patterns can handle a build up of logs before archiving to slower storage. This gets the fsync out of the way so the application will block for a shorter amount of time.<p>Postures relies heavily on disk cache, so be sure to tune shared memory small enough to allow the kernel to cache commonly used disk pages. ZFS on FreeBSD and OpenSolaris has the ARC as a second level of caching between disk cache and slow disk IO. Te Adaptive Readahead Cache can take advantage of some ssds to up access for your hotspots.
A bunch of these performance tips are equally applicable to any SQL database. MySQL doesn't have partial indexes or GiST, for instance, and it uses ANALYZE TABLE instead of VACUUM ANALYZE, but the rest of the tips apply to it too.