Message-ID: <490F9762.4030603@refractions.net>
Date: Mon, 03 Nov 2008 16:29:22 -0800
From: Kevin Neufeld <kneufeld@refractions.net>
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: many tables vs large tables
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

What is the general consensus around here ... to have many smaller tables, or have a few large tables?

I'm looking at a db model where a client has around 5500 spatial (PostGIS) tables, where the volume of each one varies 
greatly ... from a few hundred rows to over 250,000.

Speed is of the utmost importance. I'm investigating various options, like grouping the tables based on a common 
attribute or spatial type (POINT, LINESTRING, etc) into many multi-million tuple tables.

Or, table inheritance could be my friend here, in terms of performance. Ie. Using inheritance and constraint exclusion, 
the query planner could quickly isolate the tables of interest.  It's orders of magnitude faster to perform a sequential 
scan through a relatively small table than it is to do an index scan on a large, likely unclustered table.  The question 
is, can the underlying OS handle thousands of tables in a tablespace?  Would it overwhelm the parser to perform 
constraint exclusion on 50-100 tables?  Can it be done relatively quickly?

Clearly, serious testing is in order, but I just wanted to get a feel for things before I dive in.

Cheers,
Kevin