Chris,

>  I work with Marc Fournier, for those who don't know, and have been
> working extensively with the schemas feature for Hub.Org's new Account
> Management package. Each client's data is stored in a seperate schema
> set asside just for them (though they will never have direct access to
> it, it helps us "balance" out the data. Each schema has the same set of
> tables, with which different data is stored, depending on the client.

While this is an interesting use of Schema, it is not, IMHO, a good way to 
partition client data.    Far better to have monolithic tables with a 
"client_id" column and then construct *views* which are available in each 
schema only to that client.   Otherwise, as you've discovered, top-level 
management becomes a royal pain.

I'd be happy to discuss this further on PGSQL-SQL, which is really the 
appropriate mailing list.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Wed, 5 Nov 2003 13:58:34 -0800
Josh Berkus <josh@agliodbs.com>
Re: Schema boggle...

Actually, the use of schema's was my idea, to speed up some dreadfully
slow queries dealing with traffic stats from a table that was growing
painfully monolithic ... the idea is/was that it would be easier to
backup/remove all data pertaining to a specific client if they decided to
close their account ...

On Wed, 5 Nov 2003, Josh Berkus wrote:

> Chris,
>
> >  I work with Marc Fournier, for those who don't know, and have been
> > working extensively with the schemas feature for Hub.Org's new Account
> > Management package. Each client's data is stored in a seperate schema
> > set asside just for them (though they will never have direct access to
> > it, it helps us "balance" out the data. Each schema has the same set of
> > tables, with which different data is stored, depending on the client.
>
> While this is an interesting use of Schema, it is not, IMHO, a good way to
> partition client data.    Far better to have monolithic tables with a
> "client_id" column and then construct *views* which are available in each
> schema only to that client.   Otherwise, as you've discovered, top-level
> management becomes a royal pain.
>
> I'd be happy to discuss this further on PGSQL-SQL, which is really the
> appropriate mailing list.
>
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

Wed, 5 Nov 2003 21:45:39 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: Schema boggle...
Hi Marc,

I was actually leaning towards schema's as a method to partition the data 
out when I was beginning to plan AMS 2, your suggestions of schema's 
confirmed this as a good approach for what we were looking for so I started 
to code the initial layout for the Schema in April/May, but at the time I 
did not have the full picture in my head on dealing with some sections of 
the database....

To address Josh's comments though.

  Monolithic tables have their uses, I don't and wont contest that, however 
we've been using a monolithic structure in our current AMS distribution and 
in a lot of areas it has been quite a pain to try and bring some uniformity 
into the structure. First off, I have an ever changing platform and one of 
the issues I had was that development under the monolithic structure 
because problematic and very difficult to merge new elements in, granted 
the SQL schema was not all that great and a number of areas could have used 
improvement, but over all it was more trouble then it was worth.

  By breaking out our client content into a schema structure, I've 
simplified the management of my data immensely, I can easily delete 
old/unused data with out having to worry about updating scripts that have 
been written to clean out structures. I can give clients greater control 
over their own data with out worrying about some "security" issue that I 
might have missed in my programming, so there's and added level of safety 
in an already tight structure. I've "objectified" out the content into an 
easily changeable/"update friendly" system of tables, views and functions. 
I've reduced a fair amount of overhead by limiting the order of tables, For 
instance, with our original monolithic dataset, we have approx 90+ tables 
handling all of the content that we use on a regular basis. With this new 
structure I've reduced that down to a total of 30 tables, 8 of which are 
repeated across the schemas. The method we are using the schemas in also 
allows me to work in some assumptions that were tricky to code under the 
monolithic structure, with the schema system, simplicity of the table set 
allowed allowed me to simulate those assumptions with out having to 
re-write code each time I have an update, thus giving me a highly dynamic 
dataset. That and I'm more confident working on a 3D level, versus 2D, the 
schema levels introduce the third dimension into my structure and simplify 
visualizing how I want things to work.

  Within that third dimensional structure, it made sense to be able to code 
out an SQL statement that would have searched the schema set for matching 
patterns to that given in the SQL query, similar to the way one can search 
for a pattern on data in a column. But Tom has given me an idea that will 
allow me to work out how to search multiple schemas within a dynamic 
plpgsql function that figures out all my patterned schemas and executes the 
resulting query as he suggested, I just need to learn plpgsql programming 
so that I can work out all of the details (Tom, I might bug you about that 
once in a while).


At 09:45 PM 11/5/03 -0400, Marc G. Fournier wrote:

>Actually, the use of schema's was my idea, to speed up some dreadfully
>slow queries dealing with traffic stats from a table that was growing
>painfully monolithic ... the idea is/was that it would be easier to
>backup/remove all data pertaining to a specific client if they decided to
>close their account ...
>
>On Wed, 5 Nov 2003, Josh Berkus wrote:
>
> > Chris,
> >
> > >  I work with Marc Fournier, for those who don't know, and have been
> > > working extensively with the schemas feature for Hub.Org's new Account
> > > Management package. Each client's data is stored in a seperate schema
> > > set asside just for them (though they will never have direct access to
> > > it, it helps us "balance" out the data. Each schema has the same set of
> > > tables, with which different data is stored, depending on the client.
> >
> > While this is an interesting use of Schema, it is not, IMHO, a good way to
> > partition client data.    Far better to have monolithic tables with a
> > "client_id" column and then construct *views* which are available in each
> > schema only to that client.   Otherwise, as you've discovered, top-level
> > management becomes a royal pain.
> >
> > I'd be happy to discuss this further on PGSQL-SQL, which is really the
> > appropriate mailing list.
> >
> > --
> > -Josh Berkus
> >  Aglio Database Solutions
> >  San Francisco
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >



Wed, 05 Nov 2003 22:31:53 -0400
Chris Bowlby <excalibur@hub.org>
Re: [HACKERS] Schema boggle...
Chris,

>   Monolithic tables have their uses, I don't and wont contest that, however
> we've been using a monolithic structure in our current AMS distribution and
<snip>
> dataset. That and I'm more confident working on a 3D level, versus 2D, the
> schema levels introduce the third dimension into my structure and simplify
> visualizing how I want things to work.

TMI!  TMI!  Ok, I believe that it works for you.   You've made a tradeoff, 
though, and some operations that were simple in the old monolithic structure 
will now be more difficult -- however worthwhile the overall tradeoff may be.

> resulting query as he suggested, I just need to learn plpgsql programming
> so that I can work out all of the details (Tom, I might bug you about that
> once in a while).

Just go ahead and post your PL/pgSQL questions to the list.  There are people 
on this list who've written thousands of lines of PL/pgSQL ...

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Wed, 5 Nov 2003 21:01:58 -0800
Josh Berkus <josh@agliodbs.com>
Re: [HACKERS] Schema boggle...

"Marc G. Fournier" <scrappy@postgresql.org> writes:

> Actually, the use of schema's was my idea, to speed up some dreadfully
> slow queries dealing with traffic stats from a table that was growing
> painfully monolithic ... the idea is/was that it would be easier to
> backup/remove all data pertaining to a specific client if they decided to
> close their account ...

I would add an <aol> me too </aol> to the comment that this is a horrible idea
and will be an enormous pain. You're denormalizing your data in a way that
will cause you great pain. 

What you're trying to implement is called "partitioned tables". And you're
right, bulk loading and deleting is one of the big advantages of partitioned
tables. But Postgres doesn't support partitioned tables, so trying to roll
your own is hard.

Using schemas seems like a bad impedance match here too, as you've found out
with the search_path. Inherited tables is a closer match, still a lot of
things won't be automatic, and primary keys may be a problem.

But what most people use as the closest approximation to partitioned tables in
Postgres is partial indexes. It lets you keep your indexes to a reasonable
size but still accelerate the poor-selectivity client_id column in parallel.

But you still would be storing all the records in a single table and would
have to do some big vacuuming whenever you delete a whole client. vacuum full
may be your friend.

I don't understand why the queries should be any slower dealing with the
normalized data in a single table versus the dernormalized tables in multiple
tables. The one big exception is any query doing "where client_id = ?" where
the latter allows the use of a sequential scan instead of an index scan.

-- 
greg


06 Nov 2003 00:34:04 -0500
Greg Stark <gsstark@mit.edu>
Re: Schema boggle...