Re: performance difference: multiple db vs single db

On Thursday 05 February 2004 17:32, John Sidney-Woollett wrote:
> Instead of having to deal with issues of splitting data across multiple
> servers and all the associated pain, why not take a look at the Linux
> Virtual Server project, http://www.linuxvirtualserver.org/

I think John was talking about the same machine, just different databases.

> I don't know for sure that Postgres will run on it, but the beauty if it
> did is that you can keep adding more servers as you need more horsepower
> (maybe?).

Don't think PG will fly, unless they have shared memory working at a decent
speed. There is someone selling PG-Cluster (or some other brand-name) but
that's on some pretty slick high-speed interconnect AFAIK.

> > Here're two cases -
> > 1. 20 tables in one huge database A on one machine
> > 2. 10 tables in each database if they can functionally separated, so two
> > databases A1 and A2 on one machine
> >
> > What's the estimated performance difference on queries from A2 and A1
> > comparing the same querying from A in general using PostgreSQL?
> > 1) What if A1 contains 10 bigger tables [80% of A], A2 container 10
> > tables with less data [20% of A]
> > 2) And A1 and A2 contains 50% of A each

Depends on usage patterns.
IF A2 is used most of the time
AND A2 fits in RAM
BUT A1+A2 don't fit in RAM
THEN it might be worth splitting them.

Or, if you will want to backup/restore them separately perhaps.

Otherwise, if your important queries are using indexes, you probably won't see
too much difference. Unless you can test with a realistic load you'll
probably never know.

--
Richard Huxton
Archonet Ltd