pg12 - migrate tables to partitions structure

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: pg12 - migrate tables to partitions structure
Date: 2019-09-18 18:02:22
Message-ID: CA+t6e1neLiYSQHhwohWwBvi7upM_FLP1H-YOFzELDODuwwyFZw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hey,
Thanks to the new partitions features in pg12 (referencing partition table
is possible) I was trying to migrate some of my tables into a partitions
structure.

Lets assume I have the following non partitions structure :

Product(id int PK,vendor int references Vendor(id),price int)
ProductPic(int picId PK,product int references product(id) )
Vendor(id int PK,name text)
.... more tables that has references to the Product(id).

I understand that the PK on the Product table must include also the
partition column in order to ensure the uniqueness across all the
partitions. However, on the other hand I'll need to add the partition
column (Vendor) to all the tables that has a reference to the Product(id) +
update that column with the relevant data. This type of maintenance
requires a lot of time because I have a lot of references to the Product
table. Is there any other option in PG12 to allow references to partition
table ?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Michael Lewis 2019-09-18 19:09:04 Re: pg12 - migrate tables to partitions structure
Previous Message Stephen Conley 2019-09-18 16:56:32 Re: Question regarding fast-hashing in PGSQL