Integrating large PostgreSQL table as partition for a new table

Question

I currently have a relatively large table containing timeseries data (628+ mil. live rows). The table definition (some names changed slightly) at the bottom of question.

I want to attach the existing table as a partition to a new (partitioned) table. However, the existing table has a singular id primary key (mainly used because Django requires it). Attaching the table would require me to update the primary key constraint to (id, timestamp) on the old table.

Because id is unique this isn't an issue, but given the size of the table I wonder if this constraint is checked upon creation (leading to quite the query) or whether only newly added/updated rows are checked. Stopping read/writes to the table for a few minutes is possible, but I can't wait for multiple hours.

---

Intended new table

Like the old table the id column is mainly required by our ORM sadly. A similar (prop_id, "timestamp", value) index would be used on the partitioned table as well.

CREATE TABLE "newtable" (
    "id"            bigserial NOT NULL,
    "timestamp"     timestamp with time zone NOT NULL,
    "prop_id"       integer NOT NULL,
    "value"         double precision NOT NULL,
    PRIMARY KEY ("id", "timestamp")
) PARTITION BY RANGE ("timestamp")
;

---

Old table definition

The "id" primary key is an artifact of our ORM (Django) and is inconsequential for any queries we do. We use the (prop_id, "timestamp", value) index 99.9% of the time for index-only scans.

  Column   |           Type           | Collation | Nullable |                         Default                     | Storage | Stats target | Description
-----------+--------------------------+-----------+----------+----------------------------------------------------------+---------+--------------+-------------
 id        | bigint                   |           | not null | nextval('tablename_id_seq'::regclass) | plain   |              |
 timestamp | timestamp with time zone |           | not null |                     | plain   |              |
 value     | double precision         |           | not null |                     | plain   |              |
 prop_id   | integer                  |           | not null |                     | plain   |              |
Indexes:
    "tablename_pkey" PRIMARY KEY, btree (id)
    "tablename_prop_id_timestamp_value_b9bc8326_idx" btree (prop_id, "timestamp", value)
Foreign-key constraints:
    "tablename_prop_id_67f339b0_fk_othertable" FOREIGN KEY (prop_id) REFERENCES othertable(id) DEFERRABLE INITIALLY DEFERRED

Answer 1

Proceed like this:

• Don't create a primary key constraint on the partitioned table for now. Rather, create unique indexes on the new partitions that will later become partitions of the primary key index.

• As soon as the large table is old enough that you don't need the data any more, drop it.

• Then you can create a primary key on the partitioned table that will use the already existing unique indexes as partitions.