Re: table count limitation - Mailing list pgsql-general

Marcin Inkielman wrote:

> On Sat, 26 Aug 2000, Jurgen Defurne wrote:
>
> > Date: Sat, 26 Aug 2000 07:36:25 +0200
> > From: Jurgen Defurne <defurnj@glo.be>
> > To: Marcin Inkielman <marn@wsisiz.edu.pl>
> > Cc: postgreSQL general mailing list <pgsql-general@postgresql.org>
> > Subject: Re: [GENERAL] table count limitation
> >
> > Marcin Inkielman wrote:
> >
> > > HI!
> > >
> > > I have such problem:
> > > Is the amount of tables limited in Postgresql7.0?
> > > Has anybody tried to use EFFECTIVELY a database
> > > with 10000 tables at all?
> > >
> > > Thx for help.
> >
> > Are you really sure you NEED a database with 10000 tables ?
>
> yes ;)

I suggest you read this message which also came up today on  the mailing
list :

Patrick,

Any time your design is heading in this direction, take a good hard look
at
it. Proper organization with the appropriate indexes is the way to go.

With tens of hundreds of tables, how will you decide which to use?
How will you write your queries? Customize them for the different
tables?
Will you be generating a lot of data, thereby creating a lot of tables?
How
long will they take to create and populate?

With fewer, large tables you are appending data at the end, and
maintaining
indexes. An inherently simpler operation. Queries are written to a known

design and structure. You will, admittedly, have large index files, but
you
will not have hundreds to thousands of tables, each with indexes.

The Fishcart ecommerce system, which can be implemented in PostgreSQL,
has
only 20 tables, four of which have any degree of traffic.

A proprietary system done in here in Halifax for the employer's
association
has about 16 core tables, two of them are regularly updated, the rest
contain
relatively static information on members, rates, tax rates, piers, etc.

Rethink your design, talk it over with the fencepost, draw little
pictures,
ask "what if", do some rough data storage calculations -- but the
general rule
of thumb, with proper normalization, is "fewer is better".

Regards - Miles Thompson

Patrick Goodwill wrote:

> Hi!
>
> I'm writing a system which i could logically separate it into
hundreds,
> perhaps thousands, of tables, or it could put it all into one big
table.
> Since each tables could probably only grow to 10s of MBs in size, from
a
> design, speed, and scalability perspective, is it perferable to split
up
> the tables (as I've currently programmed it) or to put everything into
one
> gigantic, multi-GB table?  This is for the web, so transaction speed
is
> important.
>
> Patrick.


Jurgen