From PostgreSQL wiki

Revision as of 10:43, 7 June 2009 by Mastermind (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.

Furthermore, the items have been split into 4 sections:

  • SQL, for new SQL-standard and query features
  • Administration features which help you keep your database up and running
  • Monitoring for tools which help you see what's happening inside your database
  • Performance, for performance-related features.
  • Procedures, for new function, PL/pgSQL, trigger and similar features
  • Tools, for improvements in psql and other built-in tools
  • Developer, for developer features not included in any of the above.
  • 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



  • 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. 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 reference 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 an 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.
    • More easily and safely start autonumber sequences back at 1 when you alter other objects in the database, or when you empty a table.
  • Default Parameters for stored procedures.
    • Permits definition of default values for stored procedure parameters not supplied by the user. This makes stored procedure

¿Need better definition?

  • allow limit based on subquery
  • sql standard interval handling
  • add TABLE sql 2008 spec command


  • 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
    • An in-memory 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. Also paves the way for future index-only access to infrequently updated tables.
  • Auto-Tuning Free Space Map
    • Did you love that max_fsm_pages setting? Well, it's gone now. The Free Space Map now automatically sizes itself to the amount of free space you need to track (available memory permitting).
  • PgBench with timed execution
  • pg_conf_load_time() function
  • EXPLAIN VERBOSE column level output
  • report all queries involved in a deadlock error
  • pg_settings now shows available options for guc with defined set
  • 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
    • 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

Security & Connections

  • Column 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.


  • pg_stat_statements (contrib module)
    • 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 (contrib module)
    • 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.


    • 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.
  • default_statistics_target = 100


  • Variadic Parameters
    • Enables of stored procedures with variable numbers 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, and migrating databases from SQL Server and Sybase.
    • Application developers, users porting databases
change management, and porting database applications from SQL Server and Sybase, significantly easier.
    • Application developers, users porting databases
  • CASE control structure support in PL/pgSQL
  • RETURNS TABLE for PL/pgSQL functions
  • Support HINT, DETAIL, and SQLSTATE in RAISE command for PL/pgSQL
  • allow srf functions to be called in select clause for PL/pgSQL


  • Parallel Restore
    • pg_restore can now be run in parallel process mode, and loading data and creating database objects in several parallel streams. Depending on your hardware and database design, this allows restore of database backup files two to eight times faster than single-process restore. 8.4's parallel restore can also be used to restore 8.3 or 8.2 databases.
    • Database administrators, upgrading users
  • pg_migrator
    • This beta tool allows in-place upgrade of databases from 8.3 to 8.4, without the downtime of dump and reload. Currently has some limitations around full text indexing.
    • Upgrading users, DBAs

psql Improvements

  • Improved handling of long lines and tab characters in psql
  • Column storage type display in psql
  • Improved sequence display in psql
  • Better control of \timing in psql
  • display enum values in enum type display for psql
  • add table size display in psql
  • 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
  • remove system objects from \dX commands in psql
  • added the \ef in psql for functions edition
    • \ef <functioname> will bring up a function definition in your editor, so you can edit functions on the server.
  • pgstat temp file

Other Developer Features

  • Common Table Expression (aka CTE, WITH queries)
  • Case Insensitive Text module
  • Partial Match support for Full Text Search
    • 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.
  • Support for statement level triggers for TRUNCATE command
  • make column alias keyword "as" optional (per sql spec)

This ones shall be moved to "procedures"?

  • generate_subscripts() function
  • Time based generate_series() functions
  • quote_nullable()
  • suppress_redundent_updates() trigger
  • array_aggregate() function
  • unnest() function

3rd Party Tools

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