CODEWITHSUNDEEP
postgresqldatabase-designauto-incrementdatabase-partitioning
user3440012
user3440012

Reputation: 245

Is it guaranteed that bigserial column is unique across all partitions of a partitioned table?

I created the partitioned table in Postgres 15 with composite primary key:

CREATE TABLE T (
    id bigserial NOT NULL,
    date date,
    ..., 
    PRIMARY KEY(id, date)
) PARTITION BY RANGE (test_date);

I added several partitions like this:

CREATE TABLE T_2020 PARTITION OF T for values from ('2020-01-01') to ('2021-01-01');
CREATE TABLE T_2021 PARTITION OF T for values from ('2021-01-01') to ('2022-01-01');

Question: is it guaranteed that the id bigserial column will be unique across all partitions?

I see from metadata that there is just 1 sequence for table T created automatically for this bigserial column. There is no individual sequence per partition.

Does it mean that id column will be unique across all partitions?

Upvotes: 4

Views: 713

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658432

Question: is it guaranteed that the id bigserial column will be unique across all partitions?

Answer: No.

Quoting from the chapter "Limitations" in the manual:

To create a unique or primary key constraint on a partitioned table, the partition keys must not include any expressions or function calls and the constraint's columns must include all of the partition key columns. This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure itself must guarantee that there are not duplicates in different partitions.

The serial column (or bigserial for that matter) draws values from a single SEQUENCE. While inserting default values, that results in unique id values across the whole partitioned table.

However, there is no guarantee. We can manually insert duplicates at will. Just like a serial column in a plain (non-partitioned) table is no guarantee for uniqueness. The underlying SEQUENCE helps to avoid unique violations. Only a UNIQUE index (directly, or indirectly as implementation detail of a PRIMARY KEY or UNIQUE constraint) actually enforces distinct values. (With a loophole for null values - but PK columns are also always NOT NULL.)

fiddle

A more up-to-date IDENTITY column tends to do a better job. Still no guarantee, though. See:

Upvotes: 3

Related Questions