I just realized that my replies to my previous question on sparse
arrays went off list due to the way this list server is set up
(sigh). It has occurred to me that for my problem, one possible
solution is columnar indexes and that, in a way, partitioned tables in
Postgres might give me somewhat the same capability.
The basic problem I have is that I have some tables that are
potentially very long (100,000's to millions of rows) and very skinny,
essentially just a FK to a parent table and about 12 bits, maybe less,
of actual data. Now if I use a traditional table an int for FK is as
large or even larger than the data itself which is why I had been
looking at a sparse array (in which case I don't even need 12 bits for
the data, since part of the information is positional in nature).
However, building the routines to manipulate the arrays could be
painful and it's been suggested that their performance will be
non-optimal.
One alternative might be to use partitioned tables. If I partition the
tables in such a way that the table name can be associated directly to
the FK then I no longer need to actually store the FK in the table,
and I end up with maybe a total of 12 bits of data in each row.
Normally, I'd shy away from such a scheme since it strikes me as
potentially problematic:
1) you've got to join through DB metadata back to the actual data in question;
2) some process has to have create table auths if any of this is to be automated
My bias against 1) might just be because I come from an Oracle
background and it seems that in Postgres this is not as cumbersome or
as frowned on as it might be elsewhere? The second issue seems
problematic no matter how I look at it. However, I might be willing
to live with it for this particular problem, particulary if I can talk
myself into believing that I'm building a proxy for columnar indexes
:-)....
So I guess two sets of questions:
1) In general how comfortable are members of the Postgres community in
using table names for partitioned tables where the table name itself
conveys some indirect join relationship? It seems there is no extra
performance penalty in joining back to the system tables to do a look
up with Postgres so this is perhaps more of a best practices question
(or even a religious issue for relational purists) than anything
else...
2) If you're comfortable with the idea, how far would you go in
embracing it? In particular, would you add code to Postgres to hide
the fact that you are joining via table name? Would you go as far as
to do it at the system level or would you stick to just wrapping it in
some functions (in which case the create table privilege is still
needed)?
--
Peter Hunsberger
