From: Mark Thornton <mthornton@optrak.com>
To: Denis <socsam@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Thousands databases or schemas
Date: Thu, 08 Nov 2012 13:50:45 +0000
Message-ID: <509BB8B5.9080308@optrak.com> (raw)
In-Reply-To: <1352367376704-5731189.post@n5.nabble.com>
References: <1352367376704-5731189.post@n5.nabble.com>
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
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: mthornton@optrak.com, socsam@gmail.com
Subject: Re: Thousands databases or schemas
In-Reply-To: <509BB8B5.9080308@optrak.com>
* 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