Re: PGSQL database size question

Dan99 wrote:
> Hi,
>
> I am currently brainstorming ideas for a new RIA that I am planing to
> make. In the RIA there would be many different sections which do
> different things. Some of these sections would require data from
> other sections and some sections can sit on there own. Knowing that
> this RIA is meant to be easily to distribute and easy to resize,
> should the RIA use multiple databases and few tables within each
> database, or should there be only one database with lots of tables in
> it? I guess my question is, what is the point at which it is more
> advantageous to use multiple databases? One of the downfalls of using
> multiple databases that I know of, is that it is much harder to
> transfer/manipulate data between two tables in two different
> databases.
>

You can have all your tables available and together whether they have
data in them or not.

Having 10,000 tables in one db will affect performance a lot less than
having 100 million rows in one table.

If you want to separate some of the data then you can use schema's, you
will find it easier to access more than one schema in a single
connection than using two connections to get the other data or using
extra's like plproxy or dbilink to get to the data in another db.

It really gets down to your client design. Will it determine what tables
are available before it runs a select or will it handle zero rows
returned from a select?

Or will you have different clients (or modules) that access the
different tables depending on what options are included into it?

Will this all be from your one site or will it be downloaded and setup
on each client's server?

Then look to the future. As your site grows, will the data grow to the
volume and amount of traffic that you will need to spread the load?
Will you have multiple servers with the same data?
Will you do this by having different tables on different servers?
Will you split data across servers? split by username?

The design of your schema definition is more dependant on what data you
need to store and how it needs to be linked together as well as how your
client will access it, than on the limits of number of tables that can
be created in one db.

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz