When PostgreSQL Can't, You Can

After using PostgreSQL for a while, you realize that there are missing features that would make it significantly easier to use in large production environments. Thankfully, it's extremely easy to make add-ons to enable some of those features right now. And you don't even have to know a single line of C code! Over the past view years I've been developing tools that have made PostgreSQL administration for our clients much easier.

Table partitioning is one of the best methods for providing query performance improvements on large tables in PostgreSQL. While the documentation gives instructions on how this can be set up, it's still a very manual process. For time/serial based partitioning, the maintenance of creating new partitions is huge part of that process. PG Partition Manager is an a extension that aims to provide easy setup & maintenance for the most common partitioning types. It also provides advanced features for taking advantage of constraint exclusion and retention to drop unneeded tables.

Another area where we ran into limitations was when one of our clients needed an easy method to keep the database schema checked into version control. The pg_extractor tool grew from this and has become a popular method of finely tuning the extraction of database objects from PostgreSQL into individual files.

Autonomous transactions is another one of the more popular features in other RDMS systems that is missing from PostgreSQL. Begin able to reliably log a function's run status requires that function failure not roll back the entire transaction and erase entries made to the log table. PG Job Monitor takes advantage the dblink contrib module to give the behavior of an autonomous transaction, providing logging & monitoring for mission critical functions.

PostgreSQL's built in replication is all or nothing, but sometimes you just need to replicate specific pieces of your database. There are several 3rd-party systems capable of accomplishing this, but their setup can been daunting to most new users and overkill if you just need to grab a few tables. Mimeo is a logical replication tool that requires minimal permissions and setup and provides several specialized means of replicating from one or many databases to a single destination.

This talk will discuss these project and hopefully bring insight on how easy it is to contribute to the PostgreSQL community.