PostgreSQL Tips 'n' Tricks

Question

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.

Answer 1

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

Answer 2

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

Answer 3

For newcomers:

Don't just start the service and think that it's ready-to-go out of the box. Most installs have default settings for memory consumption that make the service run as if it's on a PDA. You'll want to bump these figures to something higher.

If you're tuning a setup for a dedicated server, a tip I picked up was to set the total memory consumption - both shared and per-process - to no more than 1/3 to 1/2 of the server's memory. The reason for this is that PostgreSQL makes certain assumptions about how data is accessed. One of those is that your OS will know how to do a better job of caching files and blocks better than it does, so you will want to leave RAM available for the OS to cache filesystem access.

Answer 4

Signals that PostgreSQL responds to (applies to newer versions). Thanks to Evan Anderson!

Answer 5

General consensus on ServerFault so far is that JFS is the journaled filesystem of choice for database engines running on Linux installs.

If I can find other recommendations for the BSDs then I'll re-edit and post here.