From: Kevin Neufeld <kneufeld@refractions.net>
To: pgsql-admin@postgresql.org
Subject: many tables vs large tables
Date: Mon, 03 Nov 2008 16:29:22 -0800
Message-ID: <490F9762.4030603@refractions.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kneufeld@refractions.net, pgsql-admin@postgresql.org
Subject: Re: many tables vs large tables
In-Reply-To: <490F9762.4030603@refractions.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox