The drumbeat of improvements keep coming from the PostgreSQL community that hopes to make that database an enterprise-class alternative to the MySQL and 11g databases controlled by Oracle, the three DB2 databases sold by IBM, and the SQL Server database from Microsoft.
With today's delivery of the PostgreSQL 9.1 community edition release, the database management system gets ten new features, including synchronous replication, support for foreign tables and per-column collation, serializable snapshot isolation, unlogged tables to boost performance, support for SELinux security and a Security Label command, and writable common table expressions.
The latter, which you can read about in detail in the release notes for PostgreSQL 9.1, is something that no other database has, Robert Haas, the senior architect at EnterpriseDB, tells El Reg. (EnterpriseDB is the commercial entity that is pushing support for the community, open source version of PostgreSQL as well as selling proprietary extensions to the database for supplemental fees.)
With writable common table expressions, Insert, Update, and Delete commands that refer to different tables can be combined into a single command. For instance, you can select a bunch of data from one table, delete it, and simultaneously move that data and insert it into a different table as a quick and dirty way to move data between tables. Under normal circumstances, such an operation would require someone to use a database client to access the database table, select the data, download it to their client, delete that data, then upload it manually into the second table.
Haas says that the other key features in the new release include a tweak to the streaming replication that debuted with PostgreSQL 9.0 and replaced an earlier and clunkier version of asynchronous replication for databases. With PostgreSQL 9.1, the streaming replication can now be done synchronously, which means that in a high availability cluster, the transactions being processed in the primary database are not committed until they are replicated in the backup database. Haas did not have any performance figures to show what kind of hit synchronous replication might have. "You're definitely going to have a performance hit, so you are going to need a fast connection between the machines," Haas said.
But for companies that absolutely cannot lose any transactions, this has always been the case with synchronous replication. The point is, now PostgreSQL has it, just like Oracle, DB2, and SQL Server have had for years. This synchronous replication can be applied to servers whole cloth or to specific transactions, and perhaps more importantly, database administrators can set replication to be either synchronous or asynchronous (which has lower overhead and doesn't impact latencies as much) for specific transaction sets in the database management system.
On the other end of the performance spectrum, there are some intermediate tables, such as end user session tables, that not only don't need replication, they don't even need the kind of protection that is afforded a regular relational database table. And so, to push the performance level up on these tables, which can be easily created on the fly, PostgreSQL 9.1 now supports unlogged tables.
These tables are not written to the write-ahead log and are not replicated, either, which means they are not protected from crashes. But they do zip along faster, in the range of 8X to 10X according to Haas, with bulk loading speeds being about twice as fast as normal PostgreSQL tables. (If these tables, such as the end user session tables, crash, the users merely have to log back in and the table is recreated.)
PostgreSQL 9.1 is available for download now here and runs on various Linux and Windows versions as well as on Solaris, HP-UX, Mac OS X, and FreeBSD. Karen Padir, vice president of products and marketing at the upstart database distributor, says that its Postgres Plus Advanced Server, its goosed version of the open source database that has "skinning" to emulate DB2 and Oracle databases, will be available in the first quarter of 2012. If you want to buy a support contract for the open source PostgreSQL 9.1 database, it is available from EnterpriseDB starting today.
Elephants in the cloud
In a separate announcement, EnterpriseDB has fluffed up a variant of the PostgreSQL 9.0 and 9.1 databases as well as Postgres Plus Advanced Server 9.0 that can run on clouds. Called Cloud Server, it is currently in beta on Amazon's EC2 cloud and will be supported on EC2 as well as on private clouds using the Eucalyptus cloud fabric from Eucalyptus Systems (which emulated the EC2 cloud and its API stack) when it becomes available at the end of November.
Padir says that Postgres Plus Cloud Server will also eventually be available on Red Hat's OpenShift development platform cloud announced in May and somewhat paradoxically, Cloud Server will also be available in a "bare metal" implementation for Linux, Windows, and Unix servers so developers can play with it before they deploy to a cloud.
Pricing for Cloud Server has not yet been set, but will obviously need to be a utility-style scheme to match the utility pricing on public clouds. ®