On Thu, 2004-09-16 at 13:39, Jim C. Nasby wrote:
> Forgive my ignorance, but I didn't think you could have a table that
> inherits from a parent not have all the columns. Or is that not what you
> mean by 'you can drop columns from the table...'?
>
> This is one advantage I see to a big UNION ALL view; if you're doing
> partitioning based on unique values, you don't actually have to store
> that value in the partition tables. For example,
> http://stats.distributed.net has a table that details how much work each
> participant did each day for each project. Storing project_id in that
> table is an extra 4 bytes... doesn't sound like much until you consider
> that the table has over 130M rows right now. So it would be nice to have
> an easy way to partition the table based on unique project_id's and not
> waste space in the partition tables on a field that will be the same for
> every row (in each partition).
Yeah, it is harder to do this automagically, though in theory it should
be possible. Since we have to roll our own partitioning anyway, we've
broken up composite primary keys so that one of the key columns hashes
to a partition, using the key itself in the partition table name rather
than replicating that value several million times. Ugly as sin, but you
can make it work in some cases.
I do just enough work for our queries to behave correctly, and a lot of
times I actually hide the base table and its descendents underneath a
sort of metadata table that is grafted to the base tables by a lot of
rules/triggers/functions/etc, and then do queries against that or a view
of that. As I said, ugly as sin and probably not universal, but you need
a lot of abstraction to make it look halfway normal. I'm going to think
about this some more and see if I can't construct a generic solution.
cheers,
j. andrew rogers
