Configuration and monitoring

Configuration and monitoring related changes in this release:

• Server parameters can be adjusted per user/database combination
• When the postgresql.conf file is reloaded via pg_ctl reload or sending the server a SIGHUP signal, the server log file notes which parameters were changed as a result
• Background writer statistics can now be reset using pg_stat_reset_shared('bgwriter')
• Table and function counters can be individually reset using pg_stat_reset_single_table_counters() and pg_stat_reset_single_function_counters() functions
• The log_temp_files parameter is now specified in kilobyte units, and its documentation corrected (the description in earlier versions was misleading)
• The log_line_prefix option can now track the SQLSTATE value set for messages such as errors, allowing logs to be more carefully analyzed by their exact error codes
• The somewhat confusing relation size functions include new variations named pg_table_size() and pg_indexes_size(), which don't require knowing as much about database storage trivia to interpret usefully

Changes related to tools:

• pgbench can now run multiple benchmarking processes/threads at once. Earlier versions could easily become limited by the speed of the pgbench client coordination part of the program itself instead.
• The contrib/auto_explain output displays the query being executed, in addition to its plan.
• Query log data collected by contrib/pg_stat_statements includes counts for how much buffer cache activity was associated with each statement.

Let's look at changes in the internals:

• A utility named contrib/pg_upgrade allows upgrading to 9.0 from either version 8.3 or 8.4 without needing to dump and reload the database, what's normally referred to as an "in-place upgrade".
• A 64-bit Windows version is released, which allows the server to use more memory than earlier versions. It's still not expected that extremely large settings for shared_buffers will be useful on that platform, however. But this does allow you to be more aggressive with settings such as work_mem than you were able to before.
• VACUUM FULL has been changed to use the same rebuild mechanism that CLUSTER uses in earlier versions. This requires more disk space, but is normally faster and it avoids the index bloat issues that VACCUM FULL used to introduce in many situations.
• You can use CLUSTER on any system catalog tables that are per database, but still not ones that are shared by all databases. However, shared system catalogs can now have REINDEX executed on them safely. Previously, that was only possible in the special database maintenance standalone mode, and even there it was dangerous.
• Some variations of ALTER TABLE require making a whole new copy of the table that includes the added/changed information, in a similar way to how CLUSTER rewrites a table. Since the copy in progress is not important unless it is complete, the block changes to create it are no longer written to the WAL. This is the same way that new and truncated tables avoid WAL logging when you write to them in a transaction that clears them. If the transaction doesn't complete, the partial copy is just destroyed. In this new case, as with the older CREATE TABLE/TRUNCATE ones, if you have WAL archiving enabled to send data to a standby server this optimization can't be used.
• When compiling the database, the default is now to include the feature that used to require requesting --enable-thread-safety. That option allows multi-threaded client programs to be compiled using the database client libraries and run safely. In earlier versions, this was disabled by default, and required recompiling the database to fix.
• The database has a better idea how to deal with the Linux Out of Memory (OOM) killer process. With the appropriate matching changes to server startup scripts, this allows making the main database server process resistant to the killer. Individual clients will instead be targeted if their memory usage goes out of control. In earlier versions, it was usually the main server process that was killed in that situation.
• Several commands now allow a new syntax where options can be passed using a list in a () block rather than more traditional SQL syntax. EXPLAIN, COPY, and VACUUM all have this alternate syntax available for some parameters. In some cases it's the only way to use newer features added to those commands.