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

• Don't use RULES, ever.
• Don't ever call a view, from within a view.
• Understand that the selectivity on a view, isn't that same as selectivity inside a view.
• Views are mostly a bad idea if they're anything more than a thin veneer.
• Postgresql COPY is brittle, less than it used to be, but get used to using a scripting language if you'll be working with a lot of CSVs.
• Don't ever use VARCHAR. It's slower to insert, and bigger to store than TEXT. Don't use it to protect against large insertions. Only use it if you want the effect of CHAR() without space-padding.
• Be careful with CTEs, they have different performance profiles.
• PLPERL and PLPERLU is the best thing sliced bread.

Answer 4

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 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.

Answer 6

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