Skip site navigation (1) Skip section navigation (2)

Re: using schema's for data separation

From: Erik Jones <erik(at)myemma(dot)com>
To: snacktime <snacktime(at)gmail(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: using schema's for data separation
Date: 2006-09-29 14:39:03
Message-ID: 451D3007.2040509@myemma.com (view raw or flat)
Thread:
Lists: pgsql-general
snacktime wrote:
> I'm re evaluating a few design choices I made a while back, and one
> that keeps coming to the forefront is data separation.  We store
> sensitive information for clients.  A database for each client isn't
> really workable, or at least I've never though of a way to make it
> workable, as we have several thousand clients and the databases all
> have to be accessed through a limited number of web applications where
> performance is important and things like persistant connections are a
> must.  I've always been paranoid about a programmer error in an
> application resulting in data from multiple clients getting mixed
> together.  Right now we create a schema for each client, with each
> schema having the same tables.  The connections to the database are
> from an unprivileged user, and everything goes through functions that
> run at the necessary privileges.  We us set_search_path to
> public,user.  User data is in schema user and the functions are in the
> public schema.  Every table has a client_id column.
>
> This has worked well so far but it's a real pain to manage and as we
> ramp up I'm not sure it's going to scale that well.  So anyways my
> questions is this.  Am I being too paranoid about putting all the data
> into one set of tables in a common schema?  For thousands of clients
> what would you do?
Hi, where I work we have similar issues wherein we have thousands of 
clients who each have large amounts of the same kind of data that  needs 
to be kept separate.  What we've done is to use table inheritance.  So, 
we have a group of base account data tables and whenever a new account 
is added they get a set of tables that inherit from these base tables.  
This works well in that whenever we need a global schema change to any 
of these tables we can just alter that pertinent base table and the 
change will cascade down to the child tables.  In addition, we can 
customize individual accounts' tables however we may need without 
worrying about screwing up other accounts' data.

-- 
erik jones <erik(at)myemma(dot)com>
software development
emma(r)


In response to

Responses

pgsql-general by date

Next:From: John D. BurgerDate: 2006-09-29 14:53:21
Subject: Re: Expected accuracy of planner statistics
Previous:From: Just SomeoneDate: 2006-09-29 14:30:50
Subject: Re: using schema's for data separation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group