From: David Johnston <polobo@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: One huge db vs many small dbs
Date: Thu, 5 Dec 2013 08:14:39 -0800 (PST)
Message-ID: <1386260079195-5781924.post@n5.nabble.com> (raw)
In-Reply-To: <1386240130.78000.YahooMailNeo@web163002.mail.bf1.yahoo.com>
References: <1386240130.78000.YahooMailNeo@web163002.mail.bf1.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
maxabbr 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.
>
> At Google there's lots of cases about this subject but none have a
> scenario that matchs with the one I presented above, so I would like to
> know if anyone here has a similar situation or knowledgement and could
> share some thoughts.
>
>
> Thanks
>
> Max
My untested thoughts here is a hybrid approach. Allow any one database to
contain any number of stores on a common schema with a controlling clientId
column. But allow for multiple databases. Furthermore, any non-client
shared data could belong to a separate database of reference with the
possibility of caching said data in each of the client databases where
applicable.
Though until your needs dictate that level of complexity you can have just
one data and schema set for all clients.
While row-level-security will make this more tenable generally this model
works best if all client access is made via middleware. You mitigate that
by using separate databases for any clients with a higher risk profile
(i.e., larger datasets, direct access to the DB, etc...)
Adding in clientId overhead will degrade performance somewhat but increase
your flexibility considerably. That is often a worthwhile trade-off to make
even if you decided to create separate schemas/databases.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/One-huge-db-vs-many-small-dbs-tp5781827p5781924.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.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: polobo@yahoo.com
Subject: Re: One huge db vs many small dbs
In-Reply-To: <1386260079195-5781924.post@n5.nabble.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