Postgres 6.5 Is Fantastic! - Mailing list pgsql-sql

Hi everyone!

You may remember me as the one who was having lots of trouble using
Postgres 6.4.2 with spinlock, BTP_CHAIN, and other miscellaneous problems.
I tried patches and other tricks but everything was running really badly
and we constantly had to intervene to keep the database running.

Well, last Sunday I made the decision to upgrade to Postgres 6.5 - we had
done some extensive thrash testing and we were very impressed with how it
handled the load. The machine was a lot more idle, the disks were being
used less, and so on, when compared to similar testing performed on a
6.4.2 database. Also, we managed to reproduce our spinlock problem under
6.4.2 but it never appeared on 6.5, probably due to the really cool MVCC
concurrency code.

So we did a pg_dump and reload into 6.5, which ran very smoothly -
especially considering we have lots of functions, plpgsql procedures,
triggers, rules, views, along with a few million rows in our tables. We
restarted our software, and everything worked flawlessly. (Well almost..) 
It even worked using the libpq from 6.4, although we recompiled with the
6.5 version just in case. Everything worked right out of the box, which is
good because we were worried about compatiblity problems with our programs
running under MVCC. We had one problem with the reload, and that was the
problem where the backend would get SIGSEGV when we tried to load in our
plpgsql functions, which I've written another email about... 

The only thing we had to change were a few GROUP BY clauses in our
queries, the parser in 6.4.2 was a bit slack in checking the arguments and
making sure you did a group by on all columns not in an aggregate, and 6.5
didn't like some of the queries - we fixed them up and that wasn't a
problem any more. 

The next day, everything ran like a complete dream. The machine was idle
almost all day (compared to very busy on the CPU and disks) even during
times when Postgres was doing lots of queries. In the past, when we ran
really large queries during times when the machine was loaded down, we
would always get either BTP_CHAIN or a spinlock problem - this is no more!
We can thrash the machine to our hearts content and it always performs
brilliantly without any one else even noticing!

The postmaster has been running for a week now without requiring any form
of human intervention, compared to once per day under 6.4.2. As you can
imagine this is also good :)

As of now, we've only found one problem with the plpgsql functions, but
apart from that, I am *really* happy with this version of Postgres. With
6.4.2 I was having serious doubts about the reliability of the code, it
just wasn't working for us - we were almost ready to give up on it and go
to Oracle or something which I can imagine has its own set of problems and
issues as well. 6.5 on the other hand, especially with the MVCC support,
is absolutely awesome and my faith in Postgres has been restored. This is
definitely the first commercial quality release that I could recommend to
others. (By commercial quality, I mean reliable enough that you can thrash
it real hard and it won't ever fall over). 6.4 and predecessors used to
work fine but only under lightly loaded conditions and would sometimes
break with wierd error messages.

So for anyone who is using anything less than 6.5, you should not walk,
but *RUN* to your local mirror site and grab a copy of 6.5 and install
immediately! It is absolutely awesome .... Probably the most exciting part
about it is the MVCC stuff, it puts Postgres up there in the big leagues
and you don't have to lock whole tables when doing writes, this makes a
huge difference. Also the snapshot backups are really cool too - my
project runs 24 hours per day and it can't be shut down to perform backups
so this is a real plus.

I'd just like to offer my congratulations and thanks to all the developers
of Postgres for your efforts and work - it is truly a great program and
I hope to be able to help more with its development and support.

[I hope this information is useful to the developers, I think someone
said they wanted some feedback on 6.5]

MVCC!

Regards,
Wayne

------------------------------------------------------------------------------
Wayne Piekarski                               Tel:     (08) 8221 5221
Research & Development Manager                Fax:     (08) 8221 5220
SE Network Access Pty Ltd                     Mob:     0407 395 889
222 Grote Street                              Email:   wayne@senet.com.au
Adelaide SA 5000                              WWW:     http://www.senet.com.au