Re: using constraint based paritioning to fix EAV

Hannu Krosing wrote:
> Ühel kenal päeval, T, 2006-07-18 kell 16:44, kirjutas Andrew Hammond:
> > On 7/18/06, Aaron Bono <postgresql(at)aranya(dot)com> wrote:
> > On 18 Jul 2006 09:07:08 -0700, Andrew Hammond
> > <andrew(dot)george(dot)hammond(at)gmail(dot)com> wrote:
> >
> > I have a client with the following EAV inspired
> > schema.
> >
> > CREATE TABLE many_tables (
> > table_id text primary key, -- defines which
> > virtual table is
> > encoded
> > attribute1 text,
> > attribute2 text,
> > attribute3 text,
> > attribute4 text,
> > ...
> > );
>
> Maybe you can approach the problem from another end, and make the
> many_tables table the virtual one and all the others into real tables ?

The many_tables table already exists. It works about as poorly as you'd
expect. My goal is to migrate away from this attrocious design. My
question is how best to do that. I'd like to take advantage of table
partitioning and constraint based exclusion, but I can't figure out how
to do it since the underlying tables are heterogenous in nature.

Perhaps I'm going about this the wrong way though. I think I'll
partition based on table_id and leave the native storage in the crappy
text fields. Then create views of what should be the real, relational
tables. That lets the developers migrate and (I hope) eases the
performance burden somewhat. Once the code migration is complete I can
finalize the data move.