Re: Simple task with partitioning which I can't realize - Mailing list pgsql-performance

De : Marc Millas <marc.millas@mokadb.com>
Envoyé : mardi 1 mars 2022 19:00
À : Andrew Zakharov <Andrew898@mail.ru>
Cc : pgsql-performance@lists.postgresql.org
Objet : Re: Simple task with partitioning which I can't realize

 

Andrew,

 

contrary to Oracle, in postgres you can add the indexes and/or the constraints which are meaningful to you at partition level.

I was not saying NOT to create keys, but I was saying to create them at partition level.

 

Marc MILLAS

Senior Architect

+33607850334

www.mokadb.com

 

 

 

On Tue, Mar 1, 2022 at 5:45 PM Andrew Zakharov <Andrew898@mail.ru> wrote:

Hi Marc –

Since there is a DWH fed by ETL there no risks to have same gids in different region partitions. I considered simple partitioned table w/o any keys but I’d believed there is a solutions with keys that’s why I’m seeking the clue.

Thanks.

Andrew.

 

From: Marc Millas <marc.millas@mokadb.com>
Sent: Tuesday, March 01, 2022 7:29 PM
To: Andrew Zakharov <Andrew898@mail.ru>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Simple task with partitioning which I can't realize

 

Hi,

 

is there any chance (risk ?) that a given gid be present in more than one region ?

if not (or if you implement it via a dedicated, non partition table), 

 

you may create a simple table partitioned by region, and create unique indexes for each partition.

this is NOT equivalent to a unique constraint at global table level, of course.

Marc MILLAS

Senior Architect

+33607850334

www.mokadb.com

 

 

 

On Tue, Mar 1, 2022 at 4:37 PM Andrew Zakharov <Andrew898@mail.ru> wrote:

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.

 _________________________________________________________________________________________

Hi

To say it using Oracle vocabulary, PostgreSQL doesn’t offer GLOBAL INDEXES. Even when we create an index on the partitioned table which is now possible, PostgreSQL create LOCAL indexes on each partition separately.

There is no global indexes on partitioned tables in PostgreSQL. So it is not simple to offer uniqueness at global level using indexes. That is why, it is required that partition key columns be part of the primary key AND any other UNIQE constraint.

 

Michel SALAIS