From PostgreSQL wiki

Revision as of 17:38, 13 March 2009 by Robe (Talk | contribs)

Jump to: navigation, search

This page is designed as a resource for people writing articles, presentations, brochures, or other advocacy related materials, to give them an overview of some of the new features coming in PostgreSQL 8.4. The format of the entries is:

  • Feature
    • What does it do and/or what does it change.
    • Who should care, who will benefit the most.

Furthermore, the items have been split into 4 sections:

  • Performance, for performance-related features.
  • Admin, for features mostly likely to help DBA's
  • Developer, for features likely to help people developing applications for PostgreSQL.
  • 3rd Party, for features that are not included with the core server, but which are new in the 8.4 cycle.

For more information, see the 8.4 Release Notes



    • 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 group by) to achieve these results. This should make many of these queries faster without needing any changes.
  • Cursor_tuple_fraction GUC
    • Tell 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.
  • Visibility maps
  • parallel pg_restore


  • New Free-Space-Map (fsm) implementation
  • Database Level LC_Collate/LC_Type
  • PgBench with timed execution
  • Column storage type display in psql
  • Improved sequence display in psql
  • Better control of \timing in psql
  • Track usage statistics for functions
  • Improved handling of long lines and tab characters in psql
  • pg_conf_load_time() function
  • display enum values in enum type display for psql
  • add table size display in psql
  • EXPLAIN VERBOSE column level output
  • add display of referencing tables to \d output in psql
  • add tablespace and database size information to \l in psql
  • improved tab completion support for tables in multiple schemas
  • report all queries involved in a deadlock error
  • pg_settings now shows available options for guc with defined set
  • auto-explain contrib module
    • this contrib module allows the dumping of explain plans for slow queries into the log. this allows for better troubleshooting, as explain plans for queries can very widely for different query executions.
  • remove system objects from \dX commands in psql
  • pg_stat_statments contrib module
  • column level privilege support
  • improved SSL certificate handling
  • add columns to views
    • allow you to add columns to a view (at the end), without having to recompile the view / dependencies. still can't modify / remove columns though.
  • multi-column GIN indexes
  • \ef in psql
    • \ef <functioname> will bring up a function definition in your editor, so you can edit functions on the server.
  • pgstat temp file
    • the pgstat file tracks usage statistics in postgresql. currently it can lead to very high i/o on the fs, which can be hard to track down. 8.4 reduced usage of file, but also made the file relocatable, so you can put it on ramdrive or similar.
  • pg_hba.conf name=value syntax
  • pg_hba.conf usermaps


  • Common Table Expression (aka CTE, WITH queries)
  • Case Insensitive Text module
  • RETURNS TABLE for plpgsql functions
  • Variable Argument Functions for plpgsql
  • Partial Match support for Full Text Search
  • CASE control structure support in plpgsql
  • Support HINT, DETAIL, and SQLSTATE in RAISE command for plpgsql
  • Time based generate_series() functions
  • RETURN QUERY EXECUTE support in plpgsql
  • generate_subscripts() function
    • Generate subscripts 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.
  • EXECUTE USING for plpgsql
  • Support for statement level triggers for TRUNCATE command
  • quote_nullable()
  • allow limit based on subquery
  • make column alias keyword "as" optional (per sql spec)
  • allow srf functions to be called in select clause for plpgsql
  • suppress_redundent_updates() trigger
  • array_aggregate() function
  • unnest() function
  • add TABLE sql 2008 spec command
  • default values for functions
  • sql2008 window functions
  • sql standard interval handling

3rd Party

Put off to 8.5

There is already an active "queue" of items that are being deferred for 8.4, in addition to the ToDo Lists.

Personal tools