[lastofus]

PostgreSQL is hands down the way to go when you need a relational DB (which is 99% of the time).

Some features to read up on:

  * COALESCE, CASE
  * Aggregate functions + GROUP BY + HAVING
  * Subqueries
  * The LEFT/RIGHT/INNER JOINs, and things you can achieve with JOIN conditions
  * Different index types and how to use them
  * Different constraint types and how they will save your ass
  * Using temporary tables
  * Views + Materialized Views
  * CTEs, including recursive
  * Full text search
  * Trigger functions
  * Window function
  * DISTINCT ON vs DISTINCT & GROUP BY (a PG extension - why it's useful)
  * Partial/functional indexes
  * Schemas - especially useful for multi-tenancy
  * COPY to load in datasets fast 
  * Transaction isolation levels
  * Manual locking
  * EXPLAIN + EXPLAIN ANALYZE

The above is just scratching the surface, but it's what I've found most useful doing web dev. The PG manual covers all these things well, along with supplemental stuff that can be googled. If you read the PG manual cover to cover, your mind you be blown by what the DB can accomplish.

For learning about performance and what's going on under the hood, check out "PostgreSQL 9.0 High Performance".

Most of the really complex queries I've written consist of writing several CTEs/sub-queries/views/temp tables which create a number of intermediate tables containing transformed data, which in turn are available to the final top level query. There's a lot of overlap with functional programming here: build small queries, and use them as reusable building blocks.

If you find yourself needing to dynamically generate complicated queries, instead of trying to construct them w/ string concatenation in your app, try building temporary views, and then referencing those views in later queries, as opposed to doing it all in one go. I usually have one function/method per view in the app, and I can call the needed functions to create the foundation for a later query.