A Few Reasons to Switch to PostgreSQL 9.0

postgresql is widely considered to be the main open source competitor to mysql.  it's been five years since the release of postgresql 8.0, but this week, postgresql developers showed that their project has evolved significantly in that time.  the beta release of postgresql 9.0 has burst onto the scene with handsome new features such as hot standby and streaming replication, which are making it even more lucrative to switch from mysql to postgresql.

1. hot standby

this feature allows connection to a server that is in archive recovery mode. users with a server in recovery mode can still process read-only queries and move to normal operations without disconnecting.  there are only a few usage and administrative differences when a server is running queries in recovery mode.  hot standby greatly improves tasks such as log shipping replication and precise restoration of a backup state.

2. streaming replication

streaming replication allows a standby server to connect to the primary server and receive a stream of wal (write ahead log) records as they are generated, instead of having to wait for those records to be written to disk and picked up later.  this allows the standby to be more up to date than it is with file-based log shipping.  the streaming replication is asynchronous, so there's a slight delay between committing a transaction in the primary and seeing it in the standby, but it's much shorter than with file-based shipping.  archive-timeout is no longer required to reduce the data loss window thanks to streaming replication.

3. a few other features

the listen/notify events in postgresql 9.0 have been moved from a system table to a memory queue for better performance.  notify is now able to pass an optional string to listeners.  postgresql 9.0 also includes sql compliant per-column triggers and anonymous functions using the do statement.  server side language support has been enhanced as well.
exclude can now be used in the create table statement as a non-traditional constraint. in the documentation you can find an example of how exclude can ensure that no two records contain overlapping circles .

4. more in store

where a project is going is just as important (if not more important) than where it currently is, if you're considering migration to that project.  a blog from robert haas indicates that the postgresql project will continue adding features that distinguish it from competitors.  there are several big patches planned for versions 9.1, 9.2, and beyond.  knngist will allow the use of indices to accelerate order by queries.  partitioning support will be improved with built-in syntax to reduce the amount of manual setup required.  another feature will be index-only scans, which reduce i/o for index scans by intelligently skipping or delaying tuple visibility checks.
___
the postgresql developers originate from several major open source companies including red hat, f-secure, and enterprisedb (a commercialization of postgresql). postgresql is released under the postgresql license, which is a liberal license that's similar to the bsd or mit licenses.