From: Josh Berkus <josh@agliodbs.com>
To: Max <maxabbr@yahoo.com.br>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: One huge db vs many small dbs
Date: Thu, 05 Dec 2013 16:01:33 -0800
Message-ID: <52A113DD.1050809@agliodbs.com> (raw)
In-Reply-To: <WM!38cc5dcea86bd77b3006a17454f69879316c4b4e87665ee1e63fc115b3c285140d9f2c828ba6e444442fd3dae38394b7!@asav-1.01.com>
References: <WM!38cc5dcea86bd77b3006a17454f69879316c4b4e87665ee1e63fc115b3c285140d9f2c828ba6e444442fd3dae38394b7!@asav-1.01.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 12/05/2013 02:42 AM, Max wrote:
> Hello,
>
> We are starting a new project to deploy a solution in cloud with the possibility to be used for 2.000+ clients. Each of this clients will use several tables to store their information (our model has about 500+ tables but there's less than 100 core table with heavy use). Also the projected ammout of information per client could be from small (few hundreds tuples/MB) to huge (few millions tuples/GB).
>
> One of the many questions we have is about performance of the db if we work with only one (using a ClientID to separete de clients info) or thousands of separate dbs. The management of the dbs is not a huge concert as we have an automated tool.
In addition to the excellent advice from others, I'll speak from experience:
The best model here, if you can implement it, is to implement shared
tables for all customers, but have a way you can "break out" customers
to their own database(s). This allows you to start with a single
database, but to shard out your larger customers as they grow. The
small customers will always stay on the same DB.
That means you'll also treat the different customers as different DB
connections from day 1. That way, when you move the large customers out
to separate servers, you don't have to change the way the app connects
to the database.
If you can't implement shared tables, I'm going to say go for separate
databases. This will mean lots of additional storage space -- the
per-DB overhead by itself will be 100GB -- but otherwise you'll be
grappling with the issues involved in having a million tables, which Joe
Conway outlined. But if you don't have shared tables, your huge schema
is always going to cause you to waste resources on the smaller customers.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: josh@agliodbs.com, maxabbr@yahoo.com.br
Subject: Re: One huge db vs many small dbs
In-Reply-To: <52A113DD.1050809@agliodbs.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