83ReleaseFeatures

Complete Feature List for 8.3 Release

The following list covers most, but not all, of the new features included in version 8.3. As there are a large number of features, they have been grouped by purpose and the descriptions kept brief. For more detail, see the PostgreSQL documentation and release notes.

Performance

Performance Consistency

These features improve PostgreSQL's ability to deliver consistent response times regardless of server load.

• HOT
  • Heap Only Tuple (HOT) dramatically reduces the database maintenance issues associated with frequently updated data, reducing the need to vacuum and giving substantial throughput improvements for some applications.
• Asynchronous Commit
  • Allows the ability for COMMIT to return control without waiting for a physical disk write. This gives better response time at the expense of potentially losing a set amount of transactions in the event of system failure.
• Load Distributed Checkpoints
  • Checkpoint auto-tuning delays and spreads out checkpoint writes, reducing the impact of checkpoints on response time when the system is under heavy load.
• Just-in-time background writing strategy
  • Auto-tuning for the Background Writer estimates how many buffers it should try to clean based on statistics about recent activity.

Speed Improvements

Many new features significantly improve the speed of specific operations.

• Circular Buffer in Tuplestore
  • Allows Merge Joins to avoid writing temporary files to disk if none of the values exceed available working memory, dramatically speeding up small merge joins.
• Speed increases for LIKE/ILIKE searching
• Top-N sorting optimizer improvements
• Lazy XID Assignment
  • PostgreSQL transactions are all given a specific transaction id, and there is maintenance overhead required to make sure that these transaction ids never conflict. Now PostgreSQL only uses transaction ids for DDL/DML operations, which are typically the only ones where there's a significant need to record a transaction id. This should result in noticeably faster throughput on read-mostly or read-only databases.
• Function Costing
  • Allows you to inform the query planner of estimated function execution costs and rows returned, resulting in better query plans when using custom functions.
• Improved Recovery Times
  • Amount of I/O from the Write Ahead Log at recovery time has been halved through efficiency improvements.

Large Databases

A few improvements will allow users to run even larger data warehouses on PostgreSQL.

• Synchronized Scans
  • When scanning large tables, this allows "piggybacking" onto the sequential scans of other users, tremendously reducing disk reads.
• L2 Cache scan protection
  • New code optimizations reduces CPU cache thrashing to speed concurrent queries.
• Varlena header size reduction ("Var-Varlena")
  • Shrinks header sizes for most variable size fields, reducing overall database size up to 20%.

Windows Performance

No, we haven't forgotten our Windows users. 8.3 brings us closer to Windows being a first-class platform for PostgreSQL.

• MS Visual C++ Support;
  • Allows the PostgreSQL source code to be compiled using Microsoft's C++ compiler, rather than the 3rd party MinGW toolset. This improves performance and stability on MS platforms. Anyone using PostgreSQL on Win32 will benefit, and since the standard Windows distribution uses it you don't need to do anything except upgrade.
• Redesign of the backend starting code:
  • Drastically reduces the memory space requirements in the postmaster, allowing for more parallel backends running.

Administration

• CSV Log output
  • Logs to a database-loadable file, enabling the easy creation of ad-hoc log and performance analysis tools.
• GSSAPI and SSPI Support
  • PostgreSQL now supports the industry-standard secure API GSSAPI for authentication using Kerberos, making integration into large enterprise networks easier. On Windows, SSPI is also supported for both Kerberos and NTLM authentication.
• Per Function GUC Settings
  • Allows for functions to have GUC settings embedded at function creation time. Especially helpful for things like defining the search path of a function at creation time, which tightens security.
• Create table like including indexes
  • Modification to the ALTER TABLE syntax that allows one to also have indexes automatically created on the new table or partition.
• Multiple Autovacuum Workers
  • Allows multiple processes to be launched from autovacuum, so that multiple tables can be vacuumed simultaneously. The are several options to control when and why the extra workers will be launched.
• pg_standby
  • An administrative tool which makes configuration of Warm Standby servers much easier.

Developers

Application Development

• Full Text Search;
  • TSearch2 has been fully integrated into the core code, with a cleaner API. This makes TSearch easier to use and to extend with new languages, dictionaries and weighting systems.
• Plan Invalidation
  • Allows for clearing cached plans by application call and automatically dropping plans when tables are updated.
• Updatable Cursors
  • Cursors now support WHERE CURRENT OF, making cursor-based application designs more flexible.

New Data Types

• XML Support
  • This new data type (XML) validates input for well-formedness and has a set of type-safe operations.
  • SQL/XML publishing functions, per SQL:2003.
  • xpath() function for XPath 1.0 expressions evaluation (with XML Namespaces support).
  • Alternative XML export functions.
• UUID
  • This new 128 bit type is compatible with most common GUID and UUID generators, supporting distributed application design.
• Arrays of Compound Types
  • Arrays can now be created using compound types (types returning multiple columns for a single type, such as a table type or custom type)
  • Anyone currently using custom types can now use arrays with them, and function developers in particular will have more flexibility in getting data in and out of functions and variables.
• ENUM;
  • A data type that allows you to define a specific number of elements, with a predefined sort order. It's commonly used as a replacement for small static look-up tables.

Stored Procedures

• RETURN TABLE statement
• Scrollable Cursors in PL/pgSQL
• ORDER BY nulls first/last
  • This new syntax allows you to control where nulls get sorted in your SQL output. Anyone who has to deal with nulls and sorting will now have more flexibility and simpler syntax.
• Indexes (B-Tree and GiST) could be used for IS NULL clause
  • Suggested by Teodor Sigaev - don't have further explanation yet
• User-defined types now could have a type modifiers
  • Suggested by Teodor Sigaev - don't have further explanation yet

Accessories

• pgBouncer
  • A lightweight connection pooler for PostgreSQL
  • Anyone doing high traffic database usage, with large numbers of connections, especially for those who do not have application level connection pooling available.
• pl/proxy
  • A database partitioning system implemented as a PL/ language
  • People looking to scale their databases horizontally across multiple servers
  • Uses the new replication hooks in 8.3
• pgSNMPd
  • SNMP instrumentation that permits SNMP clients to query aspects of a PostgreSQL instance's status
  • Anyone who currently monitors network systems via SNMP, or those looking for a PostgreSQL monitoring solution
• sepgsql
  • A security extension based on SELinux model and policy which allows applying a unified SELinux policy to both the OS and DBMS.
  • People using SELinux who need fine grained, integrated access controls in their database.
• pl/pgsql debugger
  • Allows selective interaction with pl/pgsql code
  • Anyone using or planning to develop using pl/pgsql
• pgpool-II
  • Enables parallel query processing, where multiple PostgreSQL servers are connected and execute queries simultaneously
  • Anyone who need to accelerate individual queries using replicated servers
• Bucardo
  • Asynchronous PostgreSQL replication system allowing for both multi-master and multi-slave operations
  • Anyone who needs a more complicated replication solution
  • Uses new replication hooks in 8.3.
• Slony-I 2.0
  • Uses new replication hooks in 8.3.