PostgreSQL 8.4 Feature List

Note on Additional Modules

Some of the features described below install with the core PostgreSQL code, and others are what are known as "Additional Modules." These are not installed by default, but can be optionally installed using the One-Click installer, RPM packages, or the source code. Historically, these were known as "contrib" or "contrib modules." Items which are Additional Modules are marked with an (AM) below.

SQL

With each version, the PostgreSQL implements both more advanced features in the ANSI SQL standards, and adds non-standard extensions to allow users to do more complex tasks through our object-relational features.

Windowing Functions

Otherwise known as "windowing aggregates," these functions allow you to do aggregate operations, such as count, sum, and rank, over a subset of your data, and without grouping output rows. This means that multilevel reports which would have taken 3 or 4 queries (and possibly procedural code) can now be generated with a single query. It also broadens the number of Business Intelligence applications PostgreSQL can support.

Common Table Expressions & Recursive Queries

CTEs allow users to create "named subqueries" which can then be referenced in other clauses of the query to which they are attached. In addition to removing the need to create temp tables for some operations, the most exciting use of CTEs is for recursive queries, in which you can transverse a tree or graph structure with a single query, efficiently. This is extremely useful for the many applications which have tree-structured data, such as forums, file managers, and org charts.

ALTER SEQUENCE RESTART and TRUNCATE TABLE RESTART IDENTITY

More easily and safely start autonumber sequences back at 1 when you alter other objects in the database, or when you empty a table.

ALTER VIEW add column

Allows you to add columns to an existing view (at the end), without having to rebuild the view dependencies. Modification or removal of columns still requires a dependency rebuild.

ALTER VIEW rather than ALTER TABLE to act on views

For historical reasons, ALTER TABLE was used to modify VIEWs. That has now been corrected by creating an "ALTER VIEW" statement.

ALTER DATABASE / SET TABLESPACE

This command physically moves any tables or indexes in the database's old default tablespace to the new tablespace. Note that tables and indexes in non-default tablespaces are not affected.

LIMIT (expression or subquery)

You can now LIMIT the number of rows returned by a query using a calculated expression, or even a subquery, instead of having to use a constant. This makes it easier for a single VIEW or stored procedure to support multiple dynamic page displays of your data.

"AS" keyword optional

Per SQL specification, using the "AS" keyword for column aliases is now optional. This should help users migrating from MySQL.

SQL Standard Interval handling

In addition to our rich set of non-standard interval-handling operators and functions, 8.4 includes support for SQL Standard interval year-month intervals (like '1-2') and day-time intervals (like "select interval '1' day").

TABLE command

The command "TABLE tablename" is equivalent to "SELECT * FROM tablename," per the SQL standard.

Administration

Version 8.4 implements a laundry list of longstanding user requests and new ideas to make PostgreSQL easier to administer.

Database-level Collations

Character collations (the order in which things sort) are now a database instead of an installation property. This allows users to easily have multiple languages fully supported in the same PostgreSQL installation, as well as paving the way for per-column collations in a later version of PostgreSQL.

Visibility Map

This is a new register which tracks data pages dirtied by multiple transactions. Most importantly, this allows VACUUM to only read the data pages which actually need vacuuming instead of the whole table, resulting in large decreases in required VACUUMing on large tables. It also paves the way for future index-only access to infrequently updated tables.

Auto-Tuning Free Space Map

Didn't you love that max_fsm_pages setting? Well, it's gone now. The Free Space Map is now stored on disk, not shared memory, which means it always shows all free space in your tables and resizes dynamically.

Autovacuum table configuration with ALTER TABLE / SET

You no longer need to UPDATE into the pg_autovacuum system table to change per-table settings for autovacuum; you can use ALTER TABLE. This makes it more user-friendly. As a bonus, the values you set will now be saved by pg_dump.

Warm Standby Usability

Several modifications and improvements make Warm Standby databases easier to set up, administer, and fail over. If you were using it already, chances are you'll prefer its new default behavior, but double check the docs for API changes.

pg_conf_load_time() function

You won't ever have to guess if the postgresql.conf file has been modified since you restarted the server: compare its modification time against the result of pg_conf_load_time().

Deadlocked Query Display

No more log detective work to track down which operations deadlocked; the information is right there.

pg_settings Now Shows Available Options

Many settings accept a list of potential values. The pg_settings system table now displays this list for quick reference at runtime.

Multi-column GIN Indexes

GIN indexes, used for advanced indexing such as arrays and full text search, can now be created to cover multiple columns at once, removing the requirement to concatenate them before indexing.

ON TRUNCATE Triggers

Administrators can now create triggers which fire (per statement) when tables are TRUNCATEd. As well as supporting data auditing, this enables trigger-based replication systems (such as Slony-I and Londiste) to support the TRUNCATE statement.

pg_terminate_backend()

This administration function allows you to safely terminate "idle in transaction" sessions on the command line or from scripts, eliminating database issues caused by hung client applications.

Security & Connections

PostgreSQL is "the most secure by default" and part of that is making security tools easy to use. 8.4 makes our existing connection and access control more flexible and simpler to understand.

Column-Level Permissions

DBAs can now grant permissions (SELECT, UPDATE) on specific columns as well as on entire tables. This makes it easier to secure sensitive data in your database.

SSL Authentication

Users can now authenticate using SSL certificates, and DBAs can define access control for specific SSL certificates. This makes SSL-based authentication schemes much more flexible and secure.

TRUNCATE Privilege

Administrators can now restrict users from TRUNCATE while granting them the ability to UPDATE and DELETE specific rows.

pg_hba.conf Name=Value Syntax

Makes all authentication options use one consistent syntax for their specific settings, and pg_hba.conf access-control files vastly more intuitively understandable. However, this change breaks backwards-compatibility: users of LDAP, PAM and Ident authentication will need to re-write their configuration files.

pg_hba.conf Usermaps

Allows the use of a mapping file which controls which shell or network user connects as which database user, for all authentication types instead of just IDENT.

Check pg_hba.conf on Reload

Checks the access control file before attempting to restart the database server, which prevents downtime due to mistakes editing the file.

Monitoring

8.4 adds several new monitoring features and tools to increase the suitability of PostgreSQL for large enterprise environments.

pg_stat_statements (AM)

Allows real-time monitoring of summary query statistics, letting you see at a glance which of your normalized queries is executing the most often and using the most system time. pg_stat_statement will speed up the process of "bad query" troubleshooting by an order of magnitude.

auto_explain (AM)

Lets you automatically log select EXPLAIN plans to the PostgreSQL log for later analysis. Will help a great deal with troubleshooting slow stored procedures, as well as queries which perform differently between production and testing. Also allows logging of EXPLAIN plans for nested statements, that is, statements executed from within a function.

pg_stat_user_functions

This system view tracks how many times each of your functions is accessed, as well as aggregate execution times spent both inside the function and on nested function calls. Administrators of stored procedure applications will now be able to definitively identify their slowest procedures.

New Dtrace / SystemTap Probes

43 new probes make tracing your server in real time to know what's happening inside the database engine and operating system even more informative and powerful.

Performance

"PostgreSQL continues to improve performance in every major release. Version 8.4 has added several optimizations, such as semi-joins and anti-joins, which provide dramatic improvement in the run time of some of our most demanding queries," says Kevin Grittner, Wisconsin Court System.

Hash Methods for DISTINCT/UNION/INTERSECT/EXCEPTION queries

Previously, operations like DISTINCT would require Postgres to sort and then eliminate data to achieve the distinct results. Now PostgreSQL can use hash based methods (similar to those used for GROUP BY) to achieve these results. This should make many of these queries much faster without needing any changes. Note, however, that these kinds of queries will no longer produce sorted output without an ORDER BY clause.

Semi-Joins and Anti-Joins

This refactor of how we handle folding subqueries into the main query, such as IN() and EXISTS() clauses, now reliably chooses the best execution method for semantically equivalent subqueries rather than requiring the user to experiment with different query syntax. It should accelerate existing complex reporting queries by executing them more intelligently.

Improved Hash Indexes

PostgreSQL's hash indexes now locate single rows faster than B-Tree indexes, and have become useful for indexing ID fields in some databases. However, the 8.4 implementation of hash indexes has some data recovery limitations which will be corrected in later versions.

Cursor_tuple_fraction GUC

This setting tells the planner what fraction of a cursors results you expect to return for a given cursor query. This allows the planner to use plans that are more optimal, based on estimated percentage of rows you expect to return.

default_statistics_target and column statistics changes

After extensive testing, we've raised the default statistics sample size to 100 for planner statistics, which makes a better compromise between the statistics needed by simple queries and complex ones. To support large data warehouses, we've also raised the upper limit of statistics to 10,000.

Partial Match support for Full Text Search

In addition to searching large bodies of text for word matches, full text search can now match the beginnings of words. This does require upgrading users to rebuild their GIN indexes.

Improved Statistics for Full Text Search

The query planner now estimates selectivity of your full text searches more intelligently, so you get better query plans and often faster text search queries. Produced by a Google Summer of Code project.

EXPLAIN VERBOSE column level output

This used to expose internal data that only a handful of people were able to benefit from reading. It's now telling you useful things such as which columns each node outputs to its parent, which will show you how bad SELECT * is.

pgstat File Optimizaion

The pgstat file tracks usage statistics in your database, and can generate a lot of I/O. 8.4 both reduces usage of this file, and makes relocatable, so you can put it on a faster disk resource or ramdrive. This will be a performance boon to small databases with high data write volume.

Procedures

Many PostgreSQL developers use PostgreSQL not just as a database, but as an entire middleware development environment. These extensions to our stored procedure functionality make it possible to build even more sophisticated business logic inside the database.

Variadic Parameters

As a PostgreSQL-only feature, users can now create stored procedures and functions with a variable number of arguments. This makes it easier and more natural to write stored procedures in interpreted languages (Perl, Python, Ruby, PHP, Tcl). It also helps with change management of stored procedure based applications.

Default Parameters

Permits definition of default values for stored procedure parameters not supplied by the user. This makes stored procedure change management, and porting database applications from SQL Server and Sybase, significantly easier.

CASE Control Structure in PL/pgSQL

No more IF .. ELSIF .. ELSIF .. ELSIF in PL/pgSQL; we now have a proper switched CASE statement, which allows you to execute code based on comparing a value with a list of conditions.

HINT, DETAIL, and SQLSTATE in RAISE command for PL/pgSQL

Output additional error information for easier debugging of PL/pgSQL stored procedures, or enhanced error control in your application.

RETURNS TABLE for PL/pgSQL functions

A SQL-standard shortcut for RETURNS SETOF tabletype or complex OUT parameters. Makes it faster to write stored procedures which behave like tables, returning a rowset.

RETURN QUERY EXECUTE support in PL/pgSQL

Makes it simpler to then return results from that query, by removing the requirement to loop over the result set.

EXECUTE USING for PL/pgSQL

Creates a new call syntax for PL/pgSQL functions calling other functions, which is similar to "placeholder" syntax for external languages. This eliminates quoting steps which were previously needed for EXECUTE.

Call Set-Returning Functions in the SELECT Clause

Allows you to call functions which return rows in the SELECT clause as well as the FROM clause, which is handy when you want to call a function on the results of another function, e.g.: SELECT g(y) FROM (SELECT f(x) FROM t) as sub(y);

Other Developer Features

No new version of PostgreSQL would be complete without a few advanced features for application developers.

Foreign Data Wrapper support

Paving the way to a full implementation of the SQL/MED (Management of External Data) standard in the next version, it's now possible to register foreign server and users mapping, enhancing remote procedure calls security when using cross-database connection tools like dblink.

Case Insensitive Text Data Type (AM)

This allows matching queries to match text whether it's UPPER case or lower case, which comes in very handy when managing emails for example. This does require you to use the "citext" data type.

B-Tree GIN Implementation (AM)

Primarily as a demonstration of GIN index implementation, this module offers a B-Tree-on-GIN index example.

Tools

You can't run a production database without administration and testing tools. The new version adds a major new tool and makes a few others more powerful.

psql Improvements

The most popular interface to PostgreSQL databases is our command-line shell, psql, possibly because it has so many features to let users administer their databases as fast as they can type. Version 8.4 comes with a plethora of enhancements to psql.

Version-Awareness

8.4's psql will be backwards-compatible with several earlier versions of PostgreSQL, including the informational commands. This will allow users to upgrade to the latest version of psql without worrying about having full features with older servers.

Function Editor

\ef <function_name> will bring up a special function editor with placeholders for the parts of a function, making it simple to maintain functions directly in psql rather than needing to use text files.

Table, Database, Tablespace Size Display

Using \dt+ and \l will now tell you how much your disk space objects use.

Display Referencing Tables in \dt+

Knowing what foreign keys are pointing to the table you're seeing comes in handy!

Improved handling of long lines and tab characters in psql

Line wrapping and tabs are no longer a chronic annoyance in the psql shell, but behave the way most users expect.

Improved Function Listing

Functions now clearly show when they are regular, windowing or aggregate functions so that you know how to use them.

\timing (on | off)

The \timing switch which allows checking execution times is now scriptable with on and off parameters.

Remove System Objects from \dX Commands

The \d commands now hide built-in system objects by default, allowing you to just browse user-created database objects.

New Built-in Functions

One of the things which makes PostgreSQL more than just a database is the vast host of built-in functions which perform mathematical, statistical, XML, array, timestamp, and text-manipulation operations for users. With each release, we add a few more; version 8.4 is no exception.

Time based generate_series() Functions

generate_series() is the swiss-army knife of SQL looping tricks, and now can loop between two dates rather than just two integers.

generate_subscripts() Function

Allows for easier array traversal. You used to be able to do this with generate_series and array_upper and array_lower, but this new version should be easier to use and faster as well.

quote_nullable()

Quotes and escapes strings in a way which is automatically suitable for inclusion in an EXECUTE SQL string. Particularly, it quotes NULL strings as "NULL".

suppress_redundant_updates_trigger()

This carefully coded C trigger function will detect when a tuple being UPDATEd didn't change at all, and when it's the case, it'll stop PostgreSQL from executing any other trigger. This allows your application to stop optimizing UPDATEs when you have costly triggers (or trigger based replication), as the system will do it for you.

array_agg() Function

This aggregate accumulates all of the values in a column into an array, functionality which is useful both for passing arrays to applications for processing, and for formatting lists of data for display.

unnest() Function

The reverse of the array_agg() function, unnest() takes an array and creates a record set with one row for each array element.