On Sat, Aug 4, 2012 at 6:05 AM, Menelaos PerdikeasSemantix <mperdikeas.semantix@xxxxxxxxx> wrote: > [1] use just one database and schema and logically segregate companies data > by having all tables have a client_id column as part of their primary key. > [2] use multiple database (in the same server instance) and only the public > schema in each of them for the customer's data. > [3] use one database and multiple schemas to separate the different > customer's data. > > (the [2] and [3] in particular seem practically indistinguishable to me). At my work, we started with a MySQL setup involving one schema (what MySQL calls a "database") for global data and one for each customer's. After moving to Postgres, we solidified that as schemas in a database, and since then have moved quite a bit of data into the global-data schema - so we're in a hybrid of [1] and [3]. > What are the trade-offs in terms of: > > [1] enforcing security and access separation If your customers aren't able to write SQL themselves, this is easy in any scheme. Otherwise, [2] and [3] become far easier (but [1] is still possible, it just requires some careful work with views). > [2] administering the database and responding to inquiries like "please > reset my company's data to the image of yesterday cause we messed up some > tables" or "we are taking our business elsewhere, can we please have a dump > of our data?" or "we would like a weekly DVD with our data". Ouch. This is only possible, AT ALL, on condition that nobody's data affects anybody else's. But assuming that, I'd recommend [2] or [3]. > [3] backup / restore and partitioning Definitely [3], you can back the whole lot up easily. > [4] potential for connection pooling at the Application Server. Never done this, can't speak to it. On the whole, I would advise option 3. You get good separation, and you can do schema upgrades with short outages for each customer rather than a long outage for everyone. Though this could work the other way too - it might be more convenient to go for option 1, guaranteeing that every customer's tables are following the same structure. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
