From: Jim C. Nasby <jnasby@pervasive.com>
To: Jorge Godoy <jgodoy@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [SQL] (Ab)Using schemas and inheritance
Date: Tue, 23 May 2006 18:26:09 -0500
Message-ID: <20060523232609.GP64371@pervasive.com> (raw)
In-Reply-To: <200605231716.58784.jgodoy@gmail.com>
References: <200605231716.58784.jgodoy@gmail.com>
Moving to -general, where it's more likely that others will have input.
On Tue, May 23, 2006 at 05:16:54PM -0300, Jorge Godoy wrote:
> I'm modelling an application that will have data -- financial data, human
> resources, etc. -- for several hundred (even thousands) of companies. This
> is for an accounting office.
>
> I could put some kind of "company_id" column in all of my tables to separate
> data in a more standard way, I could create a separate schema for each client
> and then create all needed tables in there (could I? I'd be abusing schemas
> here and this is part of my doubt) and, finally, I could create a "base"
> schema, define all my standard tables and create an individual schema for
> each client where I'd inherit from those base.tables.
>
> This would allow me to separate all information with a "SET search_path TO
> company" without having to make the restriction "by hand" (on "company_id",
> for example). It would also allow me to view some complete statistics
> grouping all clients by SELECTing data from the base schema. We're testing
> views and functions to see how they behave with inherited tables and changes
> on "search_path", and it looks like we can do that for, at least, a small
> number of schemas.
>
> Of course, this has implications on permissions as well, so there will be a
> large number of groups -- probably at least one per schema + some common
> groups -- and roles as well...
>
>
> Is this a good idea? Would this be too bad, performance-wise, if I had
> thousands of schemas to use like that? Any advice on better approaches? Any
> expected problems?
One issue is that you'll probably be breaking new ground here a bit; I
suspect there's very few people that are using more than a handful of
schemas. Shouldn't pose any issues, but you never know; although any
issues you do run into should only be performance problems.
Another consideration is that the free space map doesn't care too much
for tracking space info on tons of small tables.
Perhaps the biggest issue is: what happens when you need to do DDL? If
you have 1000 schemas that should be identical, you'll need to perform
any DDL 1000 times.
But as you point out, there's some interesting advantages to using
schemas like this.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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-sql@postgresql.org
Cc: jnasby@pervasive.com, jgodoy@gmail.com, pgsql-general@postgresql.org
Subject: Re: [SQL] (Ab)Using schemas and inheritance
In-Reply-To: <20060523232609.GP64371@pervasive.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