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
prop_id
similar like the old table