Peripheral Links

Header And Logo

|

• Home
• About
• Downloads
• Documentation
• Community
• Developers
• Support

PostgreSQL 8.3 Feature List

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. For an even more compact format, see the feature matrix (English only).

Upgrade Warning

In order to uphold the PostgreSQL Project's very high standards for data integrity and reliability, in version 8.3 we have cleaned up some of the data type conversions ("casts"). This refactoring may cause problems for some users upgrading older applications which were written to be careless about data type comparisons, especially from versions of PostgreSQL which are several years old. Users who are upgrading very old applications, or who suspect that they may have some sloppy application or stored procedure code, should do extra testing before upgrading their production systems. See the release notes for more information.

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.

Spread Checkpoints

Checkpoint autotuning: delays and spreads out checkpoints, reducing the impact of checkpoints on response times.

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, including:

Improved Recovery Times

The amount of I/O from the Write Ahead Log at recovery time has been halved through efficiency improvements.

Circular Buffer in Tuplestore

Dramatically speeds up small merge joins by avoiding the need to spill to disk.

Faster LIKE/ILIKE comparisons

Improves speed of partial matches, especially with multibyte encodings.

Top-N Sorting

Dramatically faster searches for results with LIMIT.

Lazy XID Assignment

Allows PostgreSQL to avoid assigning transaction IDs for some read-only queries, resulting in noticeably faster throughput on read-mostly or read-only databases.

Function Costing

Allows users to inform the query planner of estimated function execution costs and rows returned, resulting in better query plans.

Large Databases

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

Synchronized Scans

Allows "piggybacking" of large table scans by multiple users, tremendously reducing the overall I/O required.

L2 Cache Scan Protection

New code optimizations prevent thrashing CPU caches which slows concurrent queries.

Varlena Header Size Reduction ("Var-Varlena")

Shrinks header sizes for most variable size fields. Can shrink overall database size up to 20%.

Windows Performance

No, we haven't forgotten our Windows users. 8.3 may allow Windows to finally become 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.

Backend starting code redesign

Drastically reduces the memory space requirements in the postmaster, allowing for more parallel backends running on Windows.

Administration

While PostgreSQL already surpasses proprietary databases for ease of administration, there's always room for improvement. Therefore, we've added quite a few features to version 8.3 to make administering database servers easier and to provide more information to DBAs:

CSV Log Output

Logs to a database-loadable file, enabling the easy creation of ad-hoc auditing and performance analysis tools.

SSPI and GSSAPI Support

Kerberos authentication on PostgreSQL now supports the industry-standard secure APIs: SSPI on Windows and GSSAPI on Unix and Linux, making integration into large enterprise networks easier.

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.

Multiple Autovacuum Workers

Enables configurable parallelism for the PostgreSQL maintenance daemon, making autovacuum feasible for applications with thousands of tables.

pg_standby

An administrative tool which makes configuration of Warm Standby servers much easier.

ORDER BY Nulls First/Last

Permits specifying whether NULLs appear at the beggining or end of the result

Developers

Application Development

Many enhancements help PostgreSQL 8.3 match the top proprietary databases for supporting complex, multi-layered database applications, including :

Full Text Search

TSearch2, our cutting-edge full text search tool, has been fully integrated into the core code, and also has a cleaner API. This will both make TSearch2 easier to use and to extend with new languages, dictionaries and weighting systems.

Plan Invalidation

Both 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

New XML data type fully supports the SQL/XML specification of ANSI SQL:2003, including well-formedness checks, type-safe operations, SQL/XML publishing and XPath queries. Version 8.3 also includes additional functions for XML data export.

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).

ENUM

PostgreSQL now supports ENUM columns with a defined ordered list of alternatives. This will make migrating applications from MySQL to PostgreSQL easier.

Stored Procedures

Two new features expand the utility of PL/pgSQL, PostgreSQL's most popular stored procedure language:

Table Function Support

PL/pgSQL, our most popular procedural language, now supports a simplified syntax for row-returning functions.

Scrollable Cursors in PL/pgSQL

PL/pgSQL now also supports scrollable cursors, allowing PL/pgSQL procedures to perform complex row manipulations.

Accessories

Many important tools are not distributed with the PostgreSQL core code in order to keep the core distribution small and easily maintained. They are part of a collection of several hundred optional PostgreSQL components which support replication, high availability, language interfaces, application integration and experimental cutting-edge features, most of which are accessible from pgFoundry. The following PostgreSQL tools are being released for version 8.3, or achieved maturity during the 8.3 timeframe, including several replication systems which have been modified to use the new replication hooks in the 8.3 backend:

pgBouncer

This multi-threaded connection pooler allows a single PostgreSQL database server to support up to 100,000 application server connections.

PL/Proxy

A distributed, horizontally scaled table interface for PostgreSQL.

pgSNMP

A standards-compliant SNMP interface for PostgreSQL, simplifying integration with large server monitoring networks.

SEpgsql

A security extension based on SELinux model and policy which allows applying a unified SELinux policy to both the OS and DBMS.

PL/pgSQL Debugger

A new graphical interface which supports interactive debugging and step-through execution of PL/pgSQL procedures.

pgPool-II

Building on the success of pgPool, the new replicated query agent for PostgreSQL adds data partitioning.

Bucardo

PostgreSQL's first multi-master asynchronous replication system is now available.

CyberCluster

This newly open-sourced project integrates and improves several existing PostgreSQL clustering tools, such as pgCluster and pgPool.

Slony-I

Version 2.0 of Slony-I, our most popular replication system, now uses the new replication hooks in PostgreSQL 8.3.