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   |              |
    "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
  • Will the large partition become obsolete at some point? Do you need foreign key constraints pointing to the partitioned table? Jul 3 '20 at 10:25
  • Yes, the old table will be deleted after a few months. The foreign key contraints are pointing to a sensor definition table which contains more data about the specific timeseries and are required.
    – Exelian
    Jul 3 '20 at 10:49
  • To clarify: a foreign key will be added to prop_id similar like the old table
    – Exelian
    Jul 3 '20 at 10:50

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.