WhatsNew84

From PostgreSQL wiki
Jump to navigationJump to 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 sections:

  • SQL, for new SQL-standard and query features
  • Built-In Functions for new builtin functions
  • Procedures, for new function, PL/pgSQL, trigger and similar features
  • Performance, for performance-related 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
  • Tools, for improvements in psql and other tools
  • 3rd Party Tools 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

SQL

  • 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 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 traverse 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 though.
  • 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
    • 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 SQL standard.
  • Case Insensitive Text module
    • 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. Note you have to install the contribution then use the new data type "citext" instead of "text" or "varchar", but you won't need to edit your SQL queries after that.
  • Partial Match support for Full Text Search
    • Allows to search for documents containing the beginning of a word

New Built-in Functions

  • 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.
  • 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.
  • quote_nullable()
    • When building SQL strings in a function, to use in EXECUTE, adding NULL textual representation in there was unpleasant. Now you can just call quote_nullable() and PostgreSQL will care for you
  • suppress_redundant_updates_trigger()
    • This carefully coded C trigger 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 allow 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 custom aggregate function was in the documentation, the faster version of it is now included. It allows for lots of tricks, basically this function creates an array by accumulating all values in a group, similar to sum() and count().
  • unnest() function
    • Now that you've accumulated rows into a single array, you want to do the reverse, that is seeing each element of an array as a single row. Unnest() is the standard function name for doing just that.

Procedures

  • Variadic Parameters
    • Allows writing stored procedures with variable numbers of arguments. This makes it easier and more natural to write stored procedures in interpreted languages (Perl, Python, Ruby, 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.
  • 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.
  • 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.
  • RETURN QUERY EXECUTE support in PL/pgSQL
    • Want to return all the result of a dynamically created query? Well, no more need to manually loop over the result set.
  • EXECUTE USING for PL/pgSQL
    • Dynamic execution of queries is now easier than ever, supporting parameters. Instead of concatenating values by hand, name the parameters $1 etc in the query string and tell their values in the USING clause.
  • Allow PL/pgSQL set-returning functions to be called in the SELECT clause
    • that's very handy when you want to call a function g on the results of another function, f: SELECT g(y) FROM (SELECT f(x) FROM t) as sub(y);
  • Support for statement level triggers for TRUNCATE command
    • The main use of this will certainly be for add-on replication solution to support propagating your TRUNCATE, but you could also use this to trigger some specific processing at partition removal, e.g.

Performance

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

Administration

  • 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
    • 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.
  • Autovacuum table configuration with ALTER TABLE / SET
    • You no longer need to insert into the pg_autovacuum catalog to change per-table settings for autovacuum. This makes it more user-friendly. As a bonus, the values you set will be saved by pg_dump.
  • PgBench with timed execution
    • you can now ask pgbench how much you can do in a known amount of time, rather than how much time it'll take to do this amount of stuff. The difference is mainly in organizing the tests and analyzing the results: how many queries ran in this time is easier to compare.
  • pg_conf_load_time() function
    • You won't ever have to guess if the postgresql.conf file is newer than what PostgreSQL is running with: compare its modification time against the result of pg_conf_load_time().
  • 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.
  • report all queries involved in a deadlock error
    • so that you won't have to guess them from logs: the information is right there
  • pg_settings now shows available options for guc with defined set
    • A lot of settings accept a enumeration of values, pg_setting shows you that.
  • pg_stat_statements contrib module
    • This module will fill a view with timing information about your SQL statements, with an option to track queries run from inside your procedures. That's an integrated profiling tool!
  • improved SSL certificate handling
    • SSL connections can now prevent from man-in-the-middle kind of attacks, by having the certificates verified.
  • multi-column GIN indexes
  • pgstat temp file
    • In very busy installations, this file can be responsible for a lot of IO on its own. In 8.4 its use has been reduced, and additionally it's easy to put it aside on a RAM file system 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, and DBAs can define access control for specific SSL certificates.

Monitoring

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

Tools

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

psql Improvements

  • Improved handling of long lines and tab characters in psql
    • you know you hate it when you're sitting at the prompt and your query don't fit no more on a single line, because interactive shells are not made for this situation. Well, psql is, and more and more so.
  • Column storage type display in psql
  • Improved sequence display in psql
    • Sequence are the relations under "auto incremental" numeric columns (either integer or bigint), and you'll appreciate even more the way they look.
  • Better control of \timing in psql
    • Want to know how much time your query processing and result retrieval is taking? Easy, type in \timing in the prompt. Wait, you're in a script, and you don't want to toggle it off if it was already on? In 8.4, just \timing on and stop bothering.
  • It will be easier to know which values are in an enum type
  • add table size display in psql
    • Using \dt+ will tell you how much your table occupies disk space, saving you a call to pg_relation_size(). That's not including side tables and indexes, though.
  • add display of referencing tables to \d output in psql
    • Knowing what foreign keys are pointing to the table you're seeing comes handy!
  • 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
    • Remember last time you wanted to list your functions and did \df? Screenfull of pg_catalog function. It's no more the case, 8.4 will only show you your precious own functions there, add in an X to see those system functions back in the list!
  • added the \ef in psql for functions edition
    • \ef <functionname> will bring up a function definition in your editor, so you can edit functions on the server.

3rd Party Tools

  • 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 and certain Contrib Modules.
Retrieved from "https://wiki.postgresql.org/index.php?title=WhatsNew84&oldid=7794"