Primary key and FK constraints on partitioned tables

Question

I have 2 tables with more than 10 million rows.

create table TBL1 (
   GUID       UUID                 not null default gen_random_uuid(),
   DT         DATE                 not null,
   ...
   constraint tbl1_pk primary key (GUID, DT)
)
partition by RANGE (dt);

create table TBL2 (
   GUID       UUID                 not null default gen_random_uuid(),
   DT         DATE                 not null,
   TBL1_GUID  UUID                 not null,
   ...
   constraint tbl2_pk primary key (GUID, DT)
)
PARTITION BY RANGE (dt);

alter table TBL2
   add constraint FK_TBL2__TBL1 foreign key (DT, TBL1_GUID)
      references TBL1 (DT, GUID)
      on delete restrict on update restrict;

There are 24 partition by month to each tables.

Does the order columns in the primary key matter? Does the order columns in the foreign key matter? Do I need to create fk for each partition, not on hole table? Queries are running slowly.

Queries are running slowly.

How does a query look like? when you are selecting a date (`DT`), then it should be the first field in the index. Currently doing `SELECT * FROM TBL1 WHERE DT='2023-04-08'` would need a full table scan to find the needed records. — Luuk, Apr 08 '23 at 10:14
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. — Community, Apr 08 '23 at 11:37

score 0 · Answer 1 · answered Apr 08 '23 at 11:56

I cannot comment about your slow running queries, since you didn't include the queries nor their EXPLAIN (ANALYZE, BUFFERS) output.

But I have a couple of recommendations for you:

Don't define a primary key on (guid, dt). Rather, define a primary key on guid alone for each partition. That is closer to what you'd actually want: a globally unique constraint on guid.
Don't define foreign keys pointing to partitioned tables, because they will keep you from detaching partitions of the referenced table. Instead, define foreign key constraints between the corresponding partitions.
If you plan to join on the common partitioning key guid, set enable_partitionwise_join to on, so that PostgreSQL joins on the partition level, which will normally perform better.

Primary key and FK constraints on partitioned tables

1 Answers1