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

carl garland wrote:

> >  Don't even think about 100000 separate tables in a database :-(.    It's
> >not so much that PG's own datastructures wouldn't cope,    as that    very
> >few Unix filesystems can cope with 100000 files    in a directory.    You'd
> >be killed on directory search times.
>
> This doesnt really answer the initial question of how long does it take to
> locate a table in a large 1000000+ table db and where and when do these
> lookups occur.

Normally, this lookup should occur the first time a table is referenced. After
this the process should keep the file open. In this way, it doesn't need to
lookup the file anymore. If all is really well, then this file is also kept
open
by the OS, so that anyone wishing to use the same file, gets the file handle
from the OS without a directory lookup anymore (is this the case with Linux ?)

>
> I understand the concern for directory search times but what if your
> partition for the db files is under XFS or some other journaling fs that
> allows for very quick search times on large directories.  I also
> saw that there may be concern over PGs own datastructures in that the
> master tables that hold the table and index tables requires a seq
> search for locating the tables.  Why support a large # of tables in PG
> if after a certain limit causes severe performance concerns.  What if
> your data model requires more 1,000,000 tables?
>

If the implementation is like above, there is much less concern with directory
search times, although a directory might get fragmented and be spread out
across the disk (with 1000000+ tables it will be fragmented). However, it
is the bookkeeping of the disk itself that will be of concern. This bookkeeping

is done with i-nodes, of which there are a limited amount.

Suppose you have 1M+ tables, and you have 1TB of space. This makes up
for about 1 MB per table. Suppose you have a FS which works with 4k bloks,
then you need 269 blocks per table. Since the original figure is not a round
one, this gives a waste of 0.2 %, which amounts to 2.1 GB. Since i-nodes have
multiple pointers to manage blocks (amounts to 12 datablocks under Linux
(I think)), this means you need 23 inodes per file, this is 23,000,000 inodes.

This might not be quite unmanageable, but there is also the fragmentation on
all these tables which bogs down your seek times.

All this to show that the usage of 1M+ tables generates an enormous amount
of work, which would tax your IO enormous. With the directory search above
deleted, you still have to search your inode table. You could cache it, but
then
you will need (at an estimate of 128 bytes per inode) probably about 32 MB
of RAM (at 1/100th of the real space needed), which doesn't seem to bad, but
which could be used more productively.

About the size of the datamodel I say this : I think that you'll need a mighty
long time and enormous amount of analysts to reach a datamodel of 1M+
tables, or else it is based upon a large number of simple tables, in which
case it could be reduced in size.

I'm sorry, but my feeling is that 1M+ tables for a datamodel is preposterous.

Jurgen Defurne
defurnj@glo.be