Re: Re: Re: Speed of locating tables - Mailing list pgsql-general

"carl garland" <carlhgarland@hotmail.com> writes:
>> I'm sorry, but my feeling is that 1M+ tables for a datamodel is
>> preposterous.

> Perhaps I can shed a little more light on the issue by giving some
> background.  Over the last year I have developed a web site service that was
> built on a complete open source architecture (Linux, AOLServer, Postgres,
> etc.) This site allows group collaboration allowing each user to potentially
> setup information pooling and the type / amount of this info can vary for
> each group. The reason I am asking about 1 million plus tables is that the
> model dynamically builds a large # of tables and they come and go
> periodically.  If / when the site becomes popular  I want to be sure of
> scalability.

In that case you haven't got *one* datamodel, you've got N usergroups
each with their own model.  Seems to me that you'd want to give each
usergroup a separate database so as to avoid problems with namespace
collisions, security considerations, etc.  The previous responses were
all assuming that you wanted 1M tables in a single database, but
I don't think you really do.

My guess is that you are focusing on the wrong worry --- there are other
issues you are probably going to hit before you run into any limits
related to the sheer number of tables.  I'd be more worried about how
many simultaneous users you expect to support, and how big server iron
you expect to be able to do it with.  (However, if you are dividing the
users into separate databases, you could finesse that issue by setting
up separate servers for collections of databases/usergroups.  It's only
really a concern if you insist on serving them all with one Postgres
installation.)

> I am mainly concerned with how pg deals with large number of tables and
> noone has concretely answered this issue.

We told you not to do it; isn't that concrete enough ;-) ?

Seriously, I think that the real answer is that we know the current
implementation will not behave well with more than a few thousand tables
per database, but in a year or two the situation might be completely
different.  There is ongoing discussion about restructuring the way PG's
tables are stored in the Unix filesystem, and one of the side-effects
of whatever is done there might well be to eliminate this issue.  That
would be especially likely if some of the contributors to the work are
concerned about making the world safe for lots of tables.  Now that
you've told us your concern is long-term rather than short-term, I'd
say the question is not so much what the system does now as whether you
are willing to work on it some to help make it do what you want.

Postgres is not a static object ... it is a work in progress.

            regards, tom lane