I’ve always loved PostgreSQL. It is my database of choice these days and I use it with both CakePHP and with the Django framework for Python. PostgreSQL has always had a very robust feature set and was one of the first truly ACID compliant open source databases. With the recent release of version 9, PostgreSQL has added a slew of requested features that truly put it above the rest.
The biggest new feature in Postgres 9.0 is streaming replication. As many have noted, third party solutions for this have existed for some time but now there is an “official” solution that ships with the Postgres server. This streaming replication allows one or more databases to be replicated from a master server. It’s an asynchronous transfer but very quick.
In addition to the new streaming replication, Postgres 9.0 also features a hot standby ability. This allows a secondary database to be designated as a hot standby should the primary database fail. In addition, the hot standby can be used as a read only database while the primary database is active. This allows for some level of load balancing and brings PostgreSQL closer to the ability to support active-active configurations.
Another big new feature is the addition of an in-place upgrade. Previously, when upgrading from one major version of PostgreSQL to another, you had to run pg_dump and export all your data. Then the database server would be upgraded and all the data had to be re-imported. This was typically due to changes in the way the data was stored on disk or other incompatible changes. The new in-place upgrade feature eliminates these extra steps which will make it much easier to upgrade Postgres in the future.
PostgreSQL runs on a wide range of platforms but up until now, 64 bit Windows was not one of them. Version 9 fixes this and Postgres can now be run on 64 bit Windows. This makes it more attractive as an open source replacement for MS SQL Server. PostgreSQL has many of the same features as MS SQL and one of the best Oracle compatibility layers. This makes it an ideal open source database for enterprises seeking to trim costs.
PostgreSQL has supported triggers for some time but with version 9, it gains SQL standard compliant per-column triggers and conditional trigger execution. This applies to update triggers. Per-column triggers can be set to fire only when specified columns are updated.
PostgreSQL has for some time had the best Oracle compatibility layer. With version 9, Postgres is a serious competitor to Oracle. Many companies have successfully migrated from Oracle to PostgreSQL. In addition, benchmarks have shown that for many common uses, PostgreSQL performs similarly to Oracle. This makes PostgreSQL a serious enterprise database. The new features released in version 9 give it an even stronger competitive edge. It is very likely that as PostgreSQL continues to mature and add enterprise grade features for load balancing and disaster recovery, more enterprises will investigate migrating from their costly Oracle systems to PostgreSQL.