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.


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

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.

  • On the flip side, I'd argue that as dumb as the old defaults were it's still better not to optimize prematurely. And, the new defaults render this even more on the side of bad advice. – Evan Carroll Feb 28 '13 at 17:57
  • The funny part - the "1/3 to 1/2 memory" recommendation came from one of the PgSQL authors. – Avery Payne May 7 '13 at 8:21

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.