From: Gregory Stark <stark@enterprisedb.com>
To: "Thomas Andrews" <tandrews@soliantconsulting.com>
Cc: "Mark Lewis" <mark.lewis@mir3.com>,  <pgsql-performance@postgresql.org>
Subject: Re: Thousands of tables versus on table?
In-Reply-To: <46645DD9.7030805@soliantconsulting.com> (Thomas Andrews's
	message of "Mon, 04 Jun 2007 14:45:45 -0400")
Organization: EnterpriseDB
References: <C289C6B1.A63%tandrews@soliantconsulting.com>
	<1180980943.31471.322.camel@archimedes>
	<46645DD9.7030805@soliantconsulting.com>
Date: Mon, 04 Jun 2007 20:43:38 +0100
Message-ID: <877iqjmro5.fsf@oxford.xeocode.com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


"Thomas Andrews" <tandrews@soliantconsulting.com> writes:

> I guess my real question is, does it ever make sense to create thousands of
> tables like this?

Sometimes. But usually it's not a good idea. 

What you're proposing is basically partitioning, though you may not actually
need to put all the partitions together for your purposes. Partitioning's main
benefit is in the management of the data. You can drop and load partitions in
chunks rather than have to perform large operations on millions of records.

Postgres doesn't really get any faster by breaking the tables up like that. In
fact it probably gets slower as it has to look up which of the thousands of
tables you want to work with.

How often do you update or delete records and how many do you update or
delete? Once per day is a very low frequency for vacuuming a busy table, you
may be suffering from table bloat. But if you never delete or update records
then that's irrelevant.

Does reindexing or clustering the table make a marked difference?

I would suggest you post your schema and the results of "vacuum verbose".

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com