The PostgreSQL Global Development Grouprecently released PostgreSQL 9.0, with major new features and more than 200 addons and improvements for the popular database.
If you look at the release notes you’ll find a ton of new features and enhancements to existing features. For example, this release brings better error messages for unique constraints, improvements in PL languages for stored procedures, and a lot more. Wading through the PostgreSQL 9.0 release notes is a DBA’s delight, but what are the top features in this release? I pinged PostgreSQL core team member Josh Berkus and got some input on the most important features for PostgreSQL 9.
Binary Replication
At the top of the list? Binary replication. Berkus says this is comparable to Oracle’s DataGuard. Binary replication is actually two features, hot standby and streaming replication.
The upshot of the two features landing in PostgreSQL 9.0 means that you have better load balancing, and the first replication feature that ships as part of PostgreSQL. Third party replication features have been available for some time, but this is the first that is part of PostgreSQL’s official release.
Berkus also notes that this will make PostgreSQL better for cloud hosting environments.
In-place Upgrades
One area where PostgreSQL has been a bit less than perfect is in upgrades. Specifically, upgrading a major version of PostgreSQL often meant extra hardware or downtime. With PostgreSQL 9.0, organizations that have PostgreSQL 8.3 or 8.4 to upgrade in-place to 9.0.
For organizations with mission-critical systems, this makes PostgreSQL even more attractive. For organizations that can tolerate the downtime, it means the IT department doesn’t have to schedule upgrades for late-night hours or weekends to minimize the impact on services. This could be known as the “give database administrators and system administrators a life” feature.
Easier Database Object Privilege Management
PostgreSQL 9.0 adds new SQL commands to help improve privileges for objects in a database with many tables, views, and/or functions. PostgreSQL 9.0 adds a global GRANT/REVOKE statement, so it’s easy to add or revoke permissions globally without having to script the interaction.
And 9.0 provides a feature to alter default privileges, so new objects will have the proper permission as well as the existing objects in a database.
As an aside, this release also provides a module to check poor passwords for short passwords, passwords that contain the username, and so on. It’s not a perfect solution, but it could be used to find some of the worst passwords that might make it in.
Join Removal
The first three features cover security, replication, and upgrades. What haven’t we talked about so far? Speed. Users don’t care how secure your database is, or whether you have a backup, or what version of PostgreSQL is running. They care about how fast their applications return information, which is dependent on how fast PostgreSQL returns queries.
To speed up queries, or some anyway, PostgreSQL 9.0 adds Join Removal. Berkus describes this as a way to speed up “messy” queries generated by GUI-driven reporting systems and object-relational mappers (ORMs) that might create less-than-optimized queries. And the feature is automatic, so it requires no intervention to work. Upgrade to PostgreSQL 9.0 and they throw in the extra speed for free.
Windows 64-bit Builds
Yes, yes. This is Linux.com, who cares about Windows builds? Well, like it or not, many enterprises do want 64-bit builds of PostgreSQL for Windows. In most environments, Linux is running side-by-side with Windows, and cross-platform support can be important. Also, a 64-bit release means better compatibility with 64-bit versions of PostgreSQL on Linux and versions of Unix.
If that doesn’t sway you, consider that 64-bit versions of PostgreSQL provide a viable alternative to SQL Server and other proprietary databases on Windows that are already 64-bit. Displacing SQL Server or other proprietary databases can be a step towards moving away from Windows down the road.
Summary
PostgreSQL 9.0 is a major update over the 8.x series, and well worth a look. If your organization is already using PostgreSQL, it’s time to start planning the upgrade. If not, it’s time to take a look at PostgreSQL.
