Re: Performance evaluation of PostgreSQL's historic releases

On Tue, 29 Sep 2009, Gy?rgy Vilmos wrote:

> I've done a benchmark of recent versions of PostgreSQL's last five major
> releases to see, how performance has changed during the past years from
> version to version.

Your comments suggest V8.4 moves backwards as far as performance goes,
which is a bit misleading. A more fair characterization would be to
disclaim 8.4 as potentially being slower on the very simple benchmarks you
ran, not necessarily in general.

What actually happened is some features were retuned to give better
results on difficult queries (increasing default_statistics_target is the
main example there), and one of the major maintenance tasks was removed
(adjusting the max_fsm_* parameters). These and the other 8.4 changes
that touched performance added a small amount of overhead for simple
queries, but in the situations where they help the gain can be big.

Had you instead benchmarked a complicated query where the statistics
change caused the default behavior to provide better query plans, or you
had a deletion-heavy workload where 8.3 had trouble maintaining database
free space, you could have seen significantly better performance on 8.4.
The improvements in that version just don't help trivial examples like the
sysbench ones you ran.

P.S. On your write-heavy tests, increasing checkpoint_segments a lot
should improve overall performance, if you re-test at some point.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD