October 22, 2004

PostgreSQL | PostgreSQL 8.0

The next release of PostgreSQL will be 8.0, the first major version number change since 2000. Expected before the end of the year, 8.0 is not a radical makeover, but contains a bundle of new features improving accessibility, functionality and performance for developers, administrators and users alike.

The most prominent feature is be the addition of a native Windows version, albeit only for the modern 32-bit variants, which effectively means 2000 and XP. (For Windows 95, 98 and ME users, installation via the Cygwin compatibilty layer will continue to be the only option). This release should pave the way for a wider adoption of PostgreSQL, which previously was the only major RDBMS not represented on Microsoft platforms. However, early adopters should bear in mind that this is the first venture out of its traditional UNIX environment, and the as the change notes point out, has not yet had the chance to mature in a production environment.

The other major additions are somewhat more exotic, but make life much easier for DBAs. These are:

This enables the location of individual tables (and databases, schemas and indexes) on the local filesystem to be specified through PostgreSQL, improving administrator control over disk usage. Previously this was only possible by manually moving files and creating symbolic links.
Savepoints allow transactions to be segmented into individual sub-transactions, which can be rolled back without aborting the entire transaction. This makes complex operations much easier to handle administer, in contrast to the current all-or-nothing situation.
Commonly abbreviated as PITR, this adds an additional level of robustness to PostgreSQL's already high standards for data integrity. It enables recovery to a given transaction, or to a point of failure, and also allows continuous backup of the server.
ALTER TABLE improvements
It is now possible to perform operations such as ALTER TABLE sometable ADD COLUMN whatever INT NOT NULL DEFAULT 0 (prior to 8.0 this would require three seperate ALTER TABLE statements), and to change the datatypes of existing columns (providing the column does not contain data which would conflict with the new type).

As always, work is continuing to improve PostgreSQL's already impressive performance. An optimized buffer replacement strategy makes better use of available shared buffers, improving performance, while the performance impact of vacuum and checkpoints is also reduced. Cross-data-type index usage, a long-standing "gotcha" where indexes are not used in many queries if if the data types did not match exactly, has also been improved.

As with every major release, there are more minor improvements than can be mentioned here. The release notes contain detailed descriptions, and (time permitting) some will be covered in more detail on this site.

Posted at 12:54 PM