pg_restore -j
Systems administrators will rejoice in the new ‘-j’ option for pg_restore. Now you can specify the number of jobs that pg_restore will start in parallel for a restore. This is a brand new feature, so the authors are still working on figuring out what level of parallelization makes the most sense for a given workload. The rule of thumb for now is one job per processor.
One trick with a large database is to remove the constraints on tables before you restore, and then reapply them after, so that restore jobs don’t block against each other while the constraints are being checked. Remember to test this before you actually need to perform a production restore!
stats_temp_directory
Another useful new feature is the ability to specify a location for the statistics file. Previously, moving this file required a patch to the PostgreSQL source code and a re-compile. Now, there is a configuration parameter: stats_temp_directory. This allows you to relocate where statistics are written to a swap or memory filesystem, thereby reducing the disk I/O required for statistics gathering.
Determining that your statistics should be relocated could be determined through an application like atop, which can show per-process I/O utilization. Taking a sample of your I/O utilization during busy times could show you that your statistics writer is consuming more I/O than you’d like.
Statistics writer enhancement
Another statistics-related patch enables writing of statistics information only when a Postgres backend requests information from statistics tables. For example: through an interactive query of a statistics table or maybe an AUTOVACUUM. Previous versions of Postgres write this file every 500 ms. On a large, and very busy server, this overhead can be significant. No configuration is required to take advantage of the new behavior.
default_statistics_target default increased
A small change is that the default_statistics_target parameter was increased to 100 by default. Previously, this parameter was set to 10. ANALYZE uses this parameter to generate estimates of the selectivity of various WHERE clauses. Increasing this parameter too high can make ANALYZE very expensive, but testing and feedback from the PostgreSQL community convinced the development team to make this change.
The benefit is that your database will automatically be generating more detailed statistics for the query planner, and likely will make the query planner more informed and hopefully better at choosing the most efficient plan when executing your queries.
This was only a small slice of the new performance features in 8.4. There are more! Plus there are lots of new SQL and developer features like Common Table Expressions (recursive queries) and Windowing Functions.
More Information
Looking for more info on the latest version of PostgreSQL? Here are a few presentations about new features in 8.4 you might be interested in:
Magnus Hagander - “What’s new in 8.4”
Robert Treat - “Intro to Postgres 8.4 Tutorial“
Download the beta and try it out today!
Loading ...
