PostgreSQL version 8.3 introduced some performance features, this section describes the only the performance features. For a full list of features introduced in version 8.3 look at the release notes of PostgreSQL: https://www.postgresql.org/docs/8.3/static/release-8-3.html
Let's look at the features in version 8.3:
- Heap-Only Tuples (HOT) allow quicker re-use of the dead space left behind when a row is updated or deleted, as long as that doesn't touch its indexed columns.
- Autovacuum is turned on by default, and multiple autovacuum processes can run at once.
- You can turn off synchronous_commit in order to speed up database commits, at the expense of introducing some potential for lost transactions.
- Checkpoints can be spread over a longer period of time, adjusted using the checkpoint_completion_target setting.
- The background writer in earlier versions was easy to configure so that it decreased performance rather than improving it. The new just-in-time background writer in this version is largely automatic and has fewer parameters to configure.
- pg_stat_bgwriter allows monitoring both the background writer and the writes done by the spread checkpoints.
- Sequential scans use an improved scheme that reduces their memory footprint, so that they're less likely to eject useful pages from the buffer cache as they execute. And if multiple scans are executing against the same table, they will synchronize in order to reduce duplication of reading. This is another reason to make sure you explicitly ORDER things that need to be sorted, because when synchronized scans kick in, the second client joining the scan will get rows starting with wherever the first one happens to be reading against.
- Read-only transactions are allocated virtual transaction ID numbers instead of real ones, reducing their overhead. The rate of transaction use and associated snapshot information can be inspected using a set of added functions that include txid_current() and txid_current_snapshot().
- Temporary files can be moved using the temp_tablespaces parameter.
- Several types of activity that can correlate with performance issues can be more easily logged--autovacuum, checkpoints, and temporary file usage.
- All log activity can also be saved in the Comma Separated Values (CSV) output format, which makes them easier to process with external tools.
- If you comment out a previously set parameter in the postgresql.conf, starting in this version that will return it to the default; in earlier ones that just left it unchanged.
- Live and dead tuples (a tuple is essentially what most people think of as a row) are tracked in pg_stat_user_tables.
- Long-running transactions are easier to find in pg_stat_activity.
- The archive_mode parameter makes it easier to toggle on and off saving write-ahead log segments for a standby server.
- The CLUSTER command is improved to where it's a viable way to clean up after some situations where VACUUM FULL was the only alternative before.
- Performance of the Windows port was improved by building it and using memory in a more efficient manner for that platform.
- The pg_buffercache module can now display page usage counts, allowing more useful introspection of the buffer cache.
- The pgbench utility now saves latency information with a full timestamp for each transaction, enabling analysis such as graphing of the results.
- Indexing of NULL values is greatly improved, including the ability to control where they are placed relative to the rest of the table (beginning or end).