PotsgreSQL question (differences for 7.4 ,8.1 and 8.2)

Bill Moran wmoran at collaborativefusion.com
Fri Feb 23 18:17:15 UTC 2007

In response to Harald Schmalzbauer <h.schmalzbauer at omnisec.de>:

> Am Freitag, 23. Februar 2007 18:05 schrieb Bill Moran:
> > In response to Harald Schmalzbauer <h.schmalzbauer at omnisec.de>:
> > > Hello,
> > >
> > > I'm new to PostgreSQL and see that there are 3 versions in the Ports and
> > > bsd.mk.ports defaults to 7.4 to resolve pgsql dependencies.
> > > I yahood/msnd/googled a lot but couldn't find a comprehensive comparison
> > > regarding features/performance. There are severaly MySQL comparisions but
> > > that aren't of interest any more since that's what I've been using
> > > before.
> > >
> > > So please can one explain me in short words what the main featrue
> > > difference is between 7.4, 8.1, 8.2? (I found out that 7.4 and 8.2 are
> > > incompatible regarding php5-pgsql conjunction)
> >
> > 7.4 is good.  8.1 is better.  8.2 is best.
> >
> > Feature changes are minimal.  8.2 has significant performance improvements
> > over previous version.
> Version or versions? I mean, even better than 7.4? I read some tests that 
> performance of 8.x is in some statements a big regression (runtime 25ms on 
> 7.4 while 8.x needs 1000ms (1s!))

Where did you read this?

A good example is to try SELECT count(*) FROM xxx on both systems.  8.2
is _significantly_ faster than 7.x.  There have been optimizations added
for this specific case.

I'm not aware of any significant regressions.  I'd be interested to be
proven wrong, but every time I've seen it come up in conversation or
actual testing, the latest version is always the fastest.

One specific thing is that versions newer than 7 have much better
management of shared memory.  This more efficient usage means you can
allocate more shared memory to Postgres and get additional performance
gains (due to caching) than were possible in 7.x.

> It sounds that you have done some real world and sensible tests, that's why 
> I'm asking. I don't give too much for synthetic benchmark results, but a 
> factor of 40 surely would be noticable in real world...

I don't have any real-world tests that could give any real numbers between
specific versions.  My answer is based on personal "feel" over the last
few years, as well as involvement with the postgres-perform mailing lists.

Bill Moran
Collaborative Fusion Inc.

More information about the freebsd-questions mailing list