Table constraints and the new Database Partitioning Mode

[jason breen]

Hello!

I've been looking at the new experimental updated DB partitioning support, which is very exciting! As noted in the doc, a big problem with the previous implementation was that the columns that one might use for partitioning were not part of the primary or unique constraints, which meant that they could not be leveraged with DB partitioning in Postgres.

However, I'm curious about how the schema changes don't invalidate the usefulness of the unique constraints, or if you have guidance on how you might choose a partition key.

Here's a stripped down table definition for the hfj_resource table:

CREATE TABLE IF NOT EXISTS public.hfj_resource

(

    res_id bigint NOT NULL,

    partition_id integer NOT NULL,

    res_version character varying(7) COLLATE pg_catalog."default",

    fhir_id character varying(64) COLLATE pg_catalog."default",

    partition_date date,

    res_type character varying(40) COLLATE pg_catalog."default" NOT NULL,

 

    CONSTRAINT hfj_resource_pkey PRIMARY KEY (res_id, partition_id),

    CONSTRAINT idx_res_type_fhir_id UNIQUE (partition_id, res_type, fhir_id)

)

With this change to include the partition_id (as Postgres would require for partitioning), wouldn't it mean that different partitions could leverage the same res_id primary key?

And likewise, wouldn't we now allow Resource/fhir_id collisions across partitions?

Are these constraints being preserved in the software instead, which allows us to relax these constraints in the schema? And if so, do we still need to retain the idx_res_type_fhir_id constraint?

Thanks!
Jason