Background:
I am considering a db schema with many tables (in the
thousands). I once read a message on this list that said
that having a lot of tables could affect performance,
depending on how the OS handles having many files in a
directory.
Questions:
1. Does anyone have more specific information about the
perils of having many tables in a db? (I notice that with
7.1 there are even more files, because of the toast files.)
2. One of the reasons I am considering breaking the data
into multiple tables is that I want to cluster a large table
(on an rtree index) and I am running out of disk space
during the clustering operation, since all of the data is
being duplicated. Is there some other way to get around
this problem? (Other than installing another disk, which
isn't as easy as it sounds, because I'm not the sys admin.)
3. Another reason for splitting up the data is that I am
worried about the size of the rtree. I have some suspicion
that the rtree size grows more than linearly with the size
of the table. Is that the case?
4. Another possible reason is query speed. Any guesses on
which would be faster, two or three queries on tables of
about 40K records, or one query on a table of about 10M
records? (All queries are geometric range searches on an
rtree index.)
Thanks.
Ken