Postgres Pro
Downloads
Home   >   mailing lists

Thread: Reference-Partitioned Tables

Reference-Partitioned Tables

From
Сергей _
Date:

Hi,

There is a proposal to include in future developments sectioning by reference, as in Oracle

https://docs.oracle.com/database/121/VLDBG/GUID-00923EB3-05F6-41F7-8437-E42FC9BD9571.htm#VLDBG1093

There is a widespread model when data related to properties of a superclass is stored in a base table, and data related to subclasses in a detail table. Or when the main entity has volatile additional parameters. A very simplified example in the attached image.

 

contracts.jpg

The most successful choice of partitioning table Contracts is sectioning by list with key column ContractTypeID. It would be nice if the table ContractDetails  is partitioned automatically like the parent table Contracts. Then we don't have to add a crutch column ContractTypeID to the table ContractDetails   and partition manually in sync with Contracts. This field also consumes disk space, since the table of details is usually large and there can be more than one partitioning key.

Thanks, best wishes.

Attachment

Re: Reference-Partitioned Tables

From
Alvaro Herrera
Date:
On 2020-Nov-06, Сергей _ wrote:

> https://docs.oracle.com/database/121/VLDBG/GUID-00923EB3-05F6-41F7-8437-E42FC9BD9571.htm#VLDBG1093
> 
> There is a widespread model when data related to properties of a superclass
> is stored in a base table, and data related to subclasses in a detail table. Or
> when the main entity has volatile additional parameters. A very simplified
> example in the attached image.

Sounds easier to achieve by using a JSONB column that holds all the
contract details.

> The most successful choice of partitioning table *Contracts* is sectioning
> by list with key column *ContractTypeID*. It would be nice if the table
> *ContractDetails*  is partitioned automatically like the parent table
> *Contracts*. Then we don't have to add a crutch column *ContractTypeID* to
> the table* Contract**Details*   and partition manually in sync with
> *Contracts*. This field also consumes disk space, since the table of details
> is usually large and there can be more than one partitioning key.

Hmm, so you want to partition a table based on values appearing in another
table.  I wouldn't hold my breath waiting for this.