Hello all – I have a task which is simple at the first look. I have a table which contains hierarchy of address objects starting with macro region end ends with particular buildings. You can imagine how big is it. Here is short sample of table declaration: create table region_hierarchy( gid uuid not null default uuid_generate_v1mc(), parent_gid uuid null, region_code int2, … constraint pk_region_hierarchy primary key (gid), constraint fk_region_hierarchy_region_hierarchy_parent foreign key (parent_gid) references region_hierarchy(gid) ); Being an Oracle specialist, I planned to using same declarative partitioning by list on the region_code field as I did in Oracle database. I’ve carefully looked thru docs/faqs/google/communities and found out that I must include “gid” field into partition key because a primary key field. Thus partition method “by list” is not appropriate method in this case and “by range” either. What I have left from partition methods? Hash? How can I create partitions by gid & region_code by hash? Feasible? Will it be working properly (with partition pruning) when search criteria is by region_code only? Same problem appears when there is simple serial “id” used as primary identifier. Removing all constraints is not considered. I understand that such specific PostgreSQL partitioning implementation has done by tons of reasons but how I can implement partitioning for my EASY case? I see the only legacy inheritance is left, right? Very sad if it’s true. Your advices are very important. Thanks in advance. Andrew. |
