Re: Scalability with large numbers of tables

To me, the issues of scalability come from a management perspective.
I'm dealing with a schema that has tens of thousands of tables, but
they are inherited from a relatively small set of superclass tables.

I imagine there are similar issues to managing schemas as there are to
managing subclass tables. I have to deal with indexes for the thousands
of tables because there is no inheritance of indexes or constraints. So
any time I discover a new column that should be indexed, I have to make
sure to propagate the index across all subclasses.

Where this hits performance is certainly peculiar to inheritance
because doing even primary key lookups in the superclass tables becomes
a slow operation when it's having to break this down into lookups in
every child table.

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Feb 20, 2005, at 7:24 AM, Phil Endecott wrote:

> Dear Postgresql experts,
>
> I have a single database with one schema per user. Each user has a
> handful of tables, but there are lots of users, so in total the
> database has thousands of tables.
>
> I'm a bit concerned about scalability as this continues to grow. For
> example I find that tab-completion in psql is now unusably slow; if
> there is anything more important where the algorithmic complexity is
> the same then it will be causing a problem. There are 42,000 files in
> the database directory. This is enough that, with a "traditional"
> unix filesystem like ext2/3, kernel operations on directories take a
> significant time. (In other applications I've generally used a guide
> of 100-1000 files per directory before adding extra layers, but I
> don't know how valid this is.)
>
> I'm interested to know if anyone has any experiences to share with
> similar large numbers of tables. Should I worry about it? I don't
> want to wait until something breaks badly if I need architectural
> changes. Presumably tablespaces could be used to avoid the
> too-many-files-per-directory issue, though I've not moved to 8.0 yet.
>
> Thanks
>
> Phil.