From PostgreSQL wiki

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.3. 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.3 cycle.

For more information, see the 8.3 Release Notes



  • HOT
    • HOT stands for Heap Only Tuple, and attempts to solve some performance problems associated with frequently updated tuples. HOT optimizes a special but important case where UPDATE does not change any index key columns or columns referenced in index expressions and predicates. HOT helps early reclaiming of dead space in a table, thus reducing the need for frequent VACUUM and reduces relation and index bloat.
  • Just-in-time background writing strategy
    • This estimates how many buffers it should try to clean based on moving averages of recent allocation requests and density of reusable buffers
  • Asynchronous Commit
    • Allows the option of COMMIT returning control before WAL is completely written out, which allows taking the risk that some COMMITed data may be lost in case of system failure.
  • Synchronized Scans
    • Allows "piggybacking" onto Seq Scans of other users
  • Load Distributed Checkpoints
    • Delays and spreads out checkpoint work, when system is under load, which allows smoother performance when under heavier load
  • Circular Buffer in Tuplestore
    • Allows Merge Joins to avoid the need to spill tuplestores to disk if none of the values exceed work memory
  • Varlena header size reduction ("Var-Varlena")
    • Shrinks header sizes for most variable size fields. Can shrink overall database size up to 20%.
  • Speed Increases For LIKE/ILIKE searching
  • Top-N sorting
  • Lazy XID Assignment
    • in PostgreSQL, transactions are all given a specific transaction id. as these ids are used up, one has to do extra maintenance to make sure that the transaction ids never conflict. this change changes PostgreSQL to only use transaction ids for transactions that involve DDL/DML operations, which are typically the only ones that we have a significant need to record a transaction id
    • anyone using PostgreSQL will benefit, since overall transaction id usage will decrease. this will be especially helpful to those running databases that do large numbers of read only transactions, reducing the need to vacuum to prevent XID rollover
  • MS Visual C++ Support;
    • Allows the PostgreSQL source code to be compiled using MS C++ compiler, rather than the 3rd party MinGW toolset. This will improve performance and should improve stability on MS platforms.;
    • Anyone using PostgreSQL on win32 will benefit, and since the standard Windows distribution will now use it, you don't need to do anything except upgrade.


  • CSV Log output
    • Allows logging to a csv file, enabling easy load into a database for performance analysis.
  • Function Costing
    • Improved optimization of query plans.
  • 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.
    • Anyone using PostgreSQL functions should review this and see if they need to set things like predefined search_paths for schema, or possibly other settings.
  • GSSAPI Support
    • A new authentication method that replaces Kerberos on several platforms. This should allow for better integration of PostgreSQL into existing network authentication systems
    • Anyone using GSS for security (commonly found on Windows networks)
  • Create table like including indexes
    • Modification to the alter table syntax that allows one to also have indexes created on the new table
    • anyone using create table like on tables with indexes. particularly useful for making new table partitions.
  • Index Advisor ?
  • Multiple Autovacuum Workers
    • A change that 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.
    • Anyone using autovacuum who has large tables and small, frequently updated tables, which used to get bloated waiting for other tables to finish.
  • Pg_standby
    • Makes it easier to use PITR functionality for standby servers
  • BGwriter system views
    • Lets you see if you need to tune BGWriter.


  • Full Text Search;
    • The code that used to be available in contrib has been pushed into the server. This will make tsearch easier to get started with, easier to use, and should make upgrades less painful as well.
    • Anyone currently using tsearch, or anyone looking to implement Full Text Search into their database
  • Plan Invalidation
    • allows for PostgreSQL to clear cached query plans from functions
    • Useful to anyone using plpgsql, especially those using temp tables.
  • 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 may be generated by a suitable algorithm so that it is very unlikely to be generated by anyone else, and provides better uniqueness guarantees than sequence generators.
  • 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 particularly function developers will have more flexibility in getting data in and out if functions and variables.
  • Table Function Support
  • Updatable Cursors
  • Scrollable Cursors in PL/pgSQL
  • ORDER BY nulls first/last
    • new syntax that 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 with easier syntax
  • ENUM;
    • A data type that allows you to define a specific number of elements, with a pre-defined sort order. It's commonly used as a replacement for small static look-up tables.
    • Developers and DBAs designing systems can use this to simply schema and code in a number of ways. Also users migrating from other databases that have enums will now have an easier time.
  • 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

3rd Party

  • 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
  • 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
  • [1]
    • Allows selective interaction with pl/pgsql code
    • Anyone using or planning to develop using pl/pgsql

Put off to 8.4

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

  • concurrent psql
    • Allow psql to open multiple connections at the same time. This will help people test features which could affect more than one session.
  • Dead Space Map
    • FSM-like structure to collect locations of pages where tuples have expired, which should support a more efficient VACUUM process
  • PL/PSM - implementation of ANSI standard stored procedure language
  • On disk bitmap index
    • Helps with OLAP loads
    • The existing "bitmap index scan" capability made this feature less urgent.
  • POSIX shared memory support
  • Stream bitmap scans
  • Grouped Index Tuples
    • More efficient representation of indexes where multiple tuples have the same index value
  • Maintaining CLUSTER order on INSERT
    • Should lead to better performance for queries that benefit from CLUSTER ordering.
Personal tools