PostgreSQL version 8.4 introduced some performance features, this section describes only the performance features. For a full list of features introduced in version 8.4, look at the release notes of PostgreSQL: https://www.postgresql.org/docs/8.4/static/release-8-4.html
Let's look into the features now:
- The free space map used to track space that used to belong to now deleted data is tracked on disk instead of in shared memory, which removes any limits on how large it can be. This removes needing to worry about the max_fsm_pages and max_fsm_relations parameters, which are removed in this version. It also makes it much less likely systems that are not being vacuumed frequently enough will end up in a situation where it's difficult to recover from that situation.
- The default value of the default_statistics_target parameter was increased from 10 to 100. This makes query planning time longer, and is responsible for a significant portion of the sometimes measured degradation in performance between 8.4 and 8.3 seen in many benchmarks. This mainly impacts trivial queries, however, in more real-world ones, having improved statistics to work with will improve performance by making it less likely the query planner will pick a particularly bad execution plan. It's always possible to reduce this parameter for 8.4 systems that don't need to plan more complicated queries.
- In earlier versions, queries running against partitioned tables would only know how to limit their search to individual partitions if the constraint_exclusion parameter was changed from its default value, because that overhead would otherwise apply to every query. A new partition setting for this parameter, now the default, allows partitioned queries to be supported without impacting the performance when a partition isn't involved.
- Bitmap index scans, often used when combining multiple relevant indexes used by a query, can do read-ahead set by the effective_io_concurrency parameter to improve performance. This feature was tested mainly on Linux. It can have a significant benefit on systems that support more than one disk operation at once, such as when a striped RAID volume is available.
- SQL WINDOW functions allow writing new types of queries far more efficiently than before, such as queries that need an idea of what the contents of nearby rows are.
- WITH clauses such as WITH RECURSIVE allow Common-Table Expression (CTE) programming. A common use of CTEs is to execute recursive query programs, which greatly simplifies and speeds up handling structures such as trees stored in the database.
- pg_restore can now do some operations in parallel to improving loading speed on systems that are limited there by CPU speed rather than disk throughput.
- The auto_explain feature makes it easy to log EXPLAIN output only for queries that take longer to run.
- A new pg_stat_statements add-in module allows for the tracking of execution statistics of all SQL statements executed by a server. This replaces several situations where parsing the database log files was needed to gather statement profiling information needed for query tuning.
- User-defined functions can be monitoring using pg_stat_user_functions.
- EXPLAIN VERBOSE gives a more useful set of information than before.
- SQL standard LIMIT and OFFSET queries are now supported, and they can use sub-selects.
- Autovacuum parameters can now be easily adjusted per table using the CREATE TABLE and ALTER TABLE storage parameter mechanism.
- VACUUM is more likely to make progress on systems with really long-running transactions. Partial VACUUM was also added, which allows processing to skip sections of a table that it knows cannot have anything to clean up left on them.
- It's possible to adjust some of the server block size parameters at build time using parameters passed to the configure script, rather than needing to edit the source code.
- The database statistics file, which is both written to often and not necessarily critical for all situations, can be run against a different location such as a RAM disk using the stats_temp_directory parameter. When this is enabled, a normal startup/shutdown procedure will save the data changes to disk, but some statistics could be lost in an unplanned crash.
- More DTrace probes were added, and now they work on platforms other than Solaris (such as Mac OS X).
- pgbench can be run for a period of time rather than just for a number of transactions. The names of the tables it uses now are all prefixed with pgbench_ to avoid confusion with real user tables.
- Much smaller sizes of data might be compressed using the TOAST scheme.