PostgreSQL 8.3 is just around the Corner

PostgreSQL 8.3 is currently in Beta 4 and promises to offer some whoppingly neat features. First before we go over the new features we are excited about in this upcoming release, we'd like to briefly cover what was added in past releases.

The big 8.0 Highlights

  1. The biggest feature in 8.0 was native support for Windows
  2. Dollar quoting syntax for stored functions which made it a lot easier to write stored functions since instead of having to escape quotes you could use the $something$ body of function $something$ delimiter approach.
  3. Next favorite was - Tablespaces so that people no longer needed to resort to messy symlinks to simulate this needed feature
  4. Save points, and improved buffer management.

8.1 Highlights

  1. Introduced Bitmap indexes which allowed for ability to use multiple indexes on a table simultaneously and less need for compound indexes.
  2. Auto vacuuming. Auto Vacuuming was a huge benefit that all could appreciate. It allowed for automatic cleaning of dead space without human or scheduled intervention.
  3. Also introduced in this release was improved shared locking and two phase commit
  4. change in security - introducing login roles and group roles plus prevention of dropping roles that owned objects.
  5. Constraint exclusion which improved speed of inherited tables thus improving table partitioning strategies.

8.2 Highlights

  1. Query optimization improvements
  2. 8.2 introduced multi row valued lists insert syntax (example here) similar to what MySQL has . As a side note, SQL Server 2008 will introduce a similar feature as row constructors.
  3. Improved indexed creation that no longer required blocking concurrent insert, create, delete.
  4. Ability to remove table inheritance from a child table without having to rebuild it.
  5. Aggregates that can take multiple inputs and SQL:2003 statistical functions
  6. Introduction of Fill Factor for tables similar to Microsoft SQL Server's Fill Factor functionality

8.3 upcoming Highlights

8.3 has numerous highlights just as previous versions, but we shall focus on our favorite ones.

  1. Support Security Service Provider Interface (SSPI) for authentication on Windows - which presumably will allow PostgreSQL databases to enjoy the same single signon you get with Microsoft SQL Server 7-2005.
  2. GSSAPI with Kerberos authentication as a new and improved authentication scheme for single signon
  3. Numerous performance improvements - too many to itemize - check out Stefan Kaltenbrunner's 8.3 vs. 8.2 simple benchmark
  4. The new QUERY functionality in plpgsql which offers a simpler way of returning result sets
  5. Scrollable cursors in PLPgSQL
  6. Improved shared buffers on windows
  7. TSearch - Full Text Search is now integrated into PostgreSQL instead of being a contrib module
  8. Support for SQL/XML and new XML datatype
  9. ENUM datatype
  10. New add-on feature to PgAdmin III - a PL debugger most compliments of EnterpriseDB