Re: vacuum, performance, and MVCC

> Well, the only thing left is to cluster the database. There are a couple
> ways to do this, one switch to a platform that supports clustering or
> create an API to wrap multiple databases. If your queries are simple and
> limited, you could create an HTTP/XML service that wraps a number of
> postgresql databases, issues a query across all databases, merges multiple
> query sets, and returns one homoginous stream.
>
Our queries are not simple nor limited :-)

We have a big variety of users, with a big variety of needs and talented
user story writers who have imaginative minds... so the same code must
work in quite a few ways, and some of the resulting queries are
dynamically created. It's a tough job to optimize all the queries we
have.

> Inserts would be handled by hash to machine weighted by number of records
> on each machine.
>
> Updates and deletes would have two keys, machine and ID.
>
Such a setup might work for us but I fear it would be a major PITA to
make it reliable and to maintain it. It's not like I can say "let's
allot a month of work for trying out a clustering solution, but I'm not
sure if it will work fine at the end". We still have enough features to
develop, the DB is something to solve part of the problem, not to keep
us busy... the Oracle systems were there first, the application works
more or less fine on them (with occasional need to optimize here and
there). Supporting Postgres was a side-project to see if it works, and
it works decently, so we deployed some systems on it. Both of the DBs
have their quirks, and I can voice here the ones I don't like in
Postgres... and then some developer might do something about it or not,
and I find that OK. If my mind wouldn't refuse so categorically to learn
C style programming (tried that and gave up), I would probably scratch
my itches. I did it for smaller scaled ones, like truncating
timestamp(0) instead of rounding so that it is consistent with what
Oracle does, but that was just a one file modification... I simply don't
find it fun to browse C code, compared to how easy is to understand Java
code which I work with here. So unless somebody ports Postgres to Java,
I'll further need to voice my itches here in the hope that they'll be
solved by others... sorry for the long rant.

> It sounds like you have a "big" problem and you need a "big" solution.

Well, Postgres does a decent job as it is. The problem is under peek
load, sometimes it gets bogged down and the usual things like vacuum
will not help immediately. I think a few more features like the dead
space map for quick vacuum and even something like the original post's
proposition would make postgres fly under heavy load too...

Cheers,
Csaba.