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.
closed as not a real question by Mark Henderson♦ Feb 28 '13 at 20:43
It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.
locked by HopelessN00b Jan 13 '15 at 16:36
This question exists because it has historical significance, but it is not considered a good, on-topic question for this site, so please do not use it as evidence that you can ask similar questions here. This question and its answers are frozen and cannot be changed. More info: help center.
Read more about locked posts here.
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
- 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.
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.
Signals that PostgreSQL responds to (applies to newer versions). Thanks to Evan Anderson!
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.