Postgres Best Practice

Question

What are your set of best practices when working with PostgreSQL? I'm interested in every aspect of postgres-management/development. Configurations, rules, functions, users, ...

There are a lot of documentation about PostgreSQL, but I haven't found any clear best-practice/cookbook source yet.

Just to clarify: I know about views, transactions, etc (and making extensive use of them daily) so it's not a "how do I create a view"-question I want answered but rather "when should I access a table directly instead of a view" (sort of interface vs concrete-class) type of question.

Regards, Finnsson

Answer 1

Identify and restrict access to what it required - localhost only? Certain IP addresses only?

Create users with varying permissions. At minimum one dba role and one application role (per database) which has no power to alter the schema.

Answer 2

I do think you are familiar with "normal" database-functionality, so look at the advanced features of PostgreSQL:

1. Views
2. Foreign Keys
3. Transactions
4. Inheritance Conclusion

http://www.smvfp.com/pgsqlhtml/tutorial-advanced.html

Answer 3

The most important Pg concept to understand is vacuuming. Understand how and when to do it, either automatic or manual. Yes, it seems a little baroque. It doesn't really have an analogue, although Oracle users will grasp the concept quickly. But it's the key to good Postgresql performance.

Apart from that, use domains. (Think of C typedef declarations). Postgresql has a pretty good implementation. This helps you ensure you don't wind up accidentally performing joins between varchars and integers. But this is an SQL standard, nothing Pg-specific.

Also note that out of the box, Postgresql isn't configured to use all the available resources a machine has to offer; its default configuration is very conservative. There are many settings that can be bumped up by a couple of orders of magnitude or more on a dedicated server. Just what needs to be tweaked depends on your workload.