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@sss.pgh.pa.us> wrote:
> "Mark Mitchell" <mmitchell@riccagroup.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
>
