Thread: Problems with Large Databases

In a previous post Ed Loer 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 didnt 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.

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?
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

carl garland writes:

> This didnt 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.

In the current system there are several places that do sequential scans on
pg_class (which holds information on tables and indexes). Most if these
look quite unnecessary and are on the hit-list, but using stock sources
you will definitely have performance problems.

Assuming that all of these are converted to index scans eventually, you
can test the performance yourself by creating a 1000000+ row table,
defining an index and querying it a bunch of times. At that point I
believe the file system will be at least as much of a problem.

--
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden