Thread: Re: performance difference: multiple db vs single db

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


Thanks.

johnl

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 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?).

John Sidney-Woollett

John Liu said:
>
> 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
>
>
> Thanks.
>
> johnl
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

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