PostgreSQL Tips 'n' Tricks [closed]

This is meant to be a community wiki for collecting together different bits of PostgreSQL knowledge. Nothing is too common or too obscure. Post anything you know that would help others make the most of this database.

Performance tuning guide: http://anchor.com.au/hosting/dedicated/Tuning_PostgreSQL_on_your_Dedicated_Server

one of the first handy things i learned about postgres is some of the postgres tables that store info about the postgres instance, for example:

 su - postgres
 psql
 SELECT * FROM pg_stat_activity;

Shows you alot of info about current activities on the postgres instance, ie what clients are connected to it and what query are they currently running and for how long etc