Re: Speed of locating tables

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

On Thu, 1 Jun 2000, Jurgen Defurne wrote:

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.

the last 1TB machine I saw came with > 5GB of RAM. 32MB sounds
reasonable.

all kidding aside, I'd have to agree that the I/O issue sounds rather
negative.

R.

Jurgen Defurne wrote:

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.

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).

If the filesystem uses block allocation. If the filesystem uses
extent-based allocation this wouldn't be a concern.

(I'm no expert on filesystems. Moshe Bar just happened to write an
article on filesystems in this month's Byte - www.byte.com).

... With the directory search above
deleted, you still have to search your inode table.

Which could be enormous. Yuck.

Are there clever ways of managing huge numbers of inodes?

-Ron-

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.

Sorry but just little old me came up with the design which gave birth to
this model ;)

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. The tables are dynamic in both size and type ie one table may
have 6 columns of all one type and another may have 20 of various type in
random order. I *could* change the data model to use fewer tables but I
*want* to push the technology and keep the model as simple as possible given
the dynamic nature of it. 95% of these tables will probably fit in one 8k
block that postgres uses but the other 5% could probably grow to +20Meg.

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 of this points to the OS and not PostgreSQL although with the model I
have described 95% of the time you wouldn't worry about fragmenting or i/o.
I am mainly concerned with how pg deals with large number of tables and
noone has concretely answered this issue.
When and where would a data model like this run into problems in pg. With
pg trying to become enterprise ready pushing the limits like this would be
beneficial and are there hurdle to overcome on the pg side of things or is
performance more of an OS issue.

Carl Garland
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

"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