X-Original-To: pgsql-hackers-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D13B69FA47F for ; Tue, 18 Jul 2006 19:01:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 18336-06 for ; Tue, 18 Jul 2006 19:00:52 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id BA68F9FA4A7 for ; Tue, 18 Jul 2006 19:00:51 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 82C1930949; Wed, 19 Jul 2006 00:00:47 +0200 (MET DST) Subject: Re: using constraint based paritioning to fix EAV From: "Andrew Hammond" Date: 18 Jul 2006 15:00:29 -0700 Message-ID: <1153260029.290428.151470@h48g2000cwc.googlegroups.com> References: <1153238828.426611.113540@35g2000cwc.googlegroups.com> <5a0a9d6f0607181344g530b4d09s393f3f0b46764fbe@mail.gmail.com> <1153257622.3681.1.camel@localhost.localdomain> Lines: 37 Organization: http://groups.google.com X-Newsgroups: pgsql.hackers Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Complaints-To: groups-abuse@google.com In-Reply-To: <1153257622.3681.1.camel@localhost.localdomain> User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.0.4) Gecko/20060608 Ubuntu/dapper-security Firefox/1.5.0.4,gzip(gfe),gzip(gfe) X-HTTP-Via: 1.1 choppr01:3128 (Squid/2.4.STABLE7) Complaints-To: groups-abuse@google.com Injection-Info: h48g2000cwc.googlegroups.com; posting-host=207.35.222.39; posting-account=cSUlcA0AAABWACaPjo2Z9C0GBLTio512 To: pgsql-hackers@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/734 X-Sequence-Number: 86589 Hannu Krosing wrote: > =DChel kenal p=E4eval, T, 2006-07-18 kell 16:44, kirjutas Andrew Hammond: > > On 7/18/06, Aaron Bono wrote: > > On 18 Jul 2006 09:07:08 -0700, Andrew Hammond > > 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.