Awesome Postgres Features

Update 2019-09-10

This tutorial Postgres guide by Tushar Korat covers Postgres and is comprehensive and gets you up and going with details and excellent examples.

This goes through some of the great capabilities of Postgres.

Key points that I have experimented with and or used in production are

  • Pub/Sub Notifications - Which is good for most situations if you can keep a database connection open for long periods of time.  Unfortunately most web based applications should not keep active open connections so this was not available for production use
  • Table Inheritance - This works great when you want to maintain consistency against various data types, but this tool leads to abuse in most cases so it was available, but not used as it could lead to the same types of inheritance problems that show up in code.  I am in favor of composition over inheritance
  • Partitioned Tables - This is a big one and will allow you to scale a table into the billions of rows huge benefit and no need to shard a database.
  • Array Types - This is great for the type of non matching or partial fill data type attached to a row record.  Gives you a lot of flexibility and have used this in implementing tagging.

Not mentioned one that is really nice.  The ability to store JSON as a column type in a variety of formats plain text or binary (I wouldn't use binary as it makes debugging harder and when you need to debug a database things are already stressful and bad).  A nice project exposing this feature to give rock solid ACID compliance, but the format of a NoSQL database is the Marten project