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

Re: More then 1600 columns?

From: "Clark C(dot) Evans" <cce(at)clarkevans(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Mark Mitchell" <mmitchell(at)riccagroup(dot)com>
Cc: "'Dmitriy Igrishin'" <dmitigr(at)gmail(dot)com>, "'Dann Corbit'" <DCorbit(at)connx(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: More then 1600 columns?
Date: 2010-11-13 03:13:36
Message-ID: 1289618016.27217.1405061119@webmail.messagingengine.com (view raw or flat)
Thread:
Lists: pgsql-general
Tom,

Thank you for the helpful response.

It is very reasonable for the PostgreSQL developers to 
decide this isn't a common enough problem to justify the 
effort to change and/or the runtime cost.  For example, 
I'd rather advocate for other features myself (such as CUBE).

The solution "in the field" is to shard the columns into
sets (I call them facets).  Your instrument table then 
has N subordinate tables with a 1 to 0/1 relationship
implemented by placing a UNIQUE key on the FK columns.
The consequence is that application software has to 
manage the column-based partitioning.

The suggestion to not use a relational model (HSTORE,
XML, or EAV table) is also valid for some use cases.
However, it often replaces one problem with another:  
you now need to write your own query language.  IMHO,
if you go this far, you should switch to RDF+SPARQL.

What would be most helpful though is if the answer to 
this question stop being an attack on the business
requirement analysis, database design skills, and/or 
sanity of the requester.  It's a limitation of 
PostgreSQL's implementation; a deliberate performance
trade-off that is infeasible to change.  That's fine.
PostgreSQL is a fantastic database -- it's welcome to
have a few limitations.

Best,

Clark

On Fri, 12 Nov 2010 17:10 -0500, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Mark Mitchell" <mmitchell(at)riccagroup(dot)com> writes:
> > I think it's very obvious that Postgres developers have no interest in
> > going over 1600 columns in the foreseeable future and which forces us
> > to find creative ways around it but I just don't see why it has to be
> > this way.
> 
> Well, it's a tradeoff.  Supporting > 1600 columns would require widening
> t_hoff, which means another byte occupied by row headers, which is a
> data structure that we have sweated blood to minimize and aren't eager
> to bloat just to support what seems extremely dubious database design
> practice.  The other possible inefficiencies are minor by comparison
> to that objection: larger row headers are a cost that will be paid by
> *every* user of Postgres.
> 
> 			regards, tom lane
> 






In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2010-11-13 03:15:01
Subject: Re: index row requires 10040 bytes, maximum size is 8191
Previous:From: smallfishDate: 2010-11-13 03:12:38
Subject: Re: Basic Tutorials for 9.0

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