Message-ID: <509BB8B5.9080308@optrak.com>
Date: Thu, 08 Nov 2012 13:50:45 +0000
From: Mark Thornton <mthornton@optrak.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121028 Thunderbird/16.0.2
MIME-Version: 1.0
To: Denis <socsam@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Thousands databases or schemas
References: <1352367376704-5731189.post@n5.nabble.com>
In-Reply-To: <1352367376704-5731189.post@n5.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 08/11/12 09:36, Denis wrote:
> We have a web application where we create a schema or a database with a
> number of tables in it for each customer. Now we have about 2600 clients.
>
> The problem we met using a separate DB for each client is that the creation
> of new DB can take up to 2 minutes, that is absolutely unacceptable. Using
> schemes instead (one DB with a number of schemes containing similar tables
> in it) solved this problem (schemes are created in a couple of seconds), but
> created two other blocking points:
> 1. sometimes creation of a new table in a schema takes up to 5 seconds. In
> case when we have create up to 40 tables in a schema this takes way too much
> time.
> 2. "pg_dump -n schema_name db_name" takes from 30 to 60 seconds, no matter
> how big is the amount of data in the schema. Also, the dump of the tables
> structure only takes at least 30 seconds. Basing on this topic
> http://postgresql.1045698.n5.nabble.com/pg-dump-and-thousands-of-schemas-td5709766i60.html,
> pg_dump always analyses ALL the tables in the DB, i.e. in my case more than
> 100 000 tables.
The obvious solution would be to write your own version of pg_dump which 
only examines the tables within a schema. You can even start with the 
source of the standard pg_dump! However, you could then eliminate the 
per customer schema/tables and add an extra 'customer' key column on 
each table. Now you modify pg_dump to only dump the parts of each table 
matching a given customer id.

Mark Thornton