The train
table has around 60million records all of which have their updated_at
timestamp in the year 2021. The schema of the table is as below.
Table "public.train"
Column | Type | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+---------
id | uuid | | not null |
updated_at | timestamp without time zone | | not null |
status | task_status | | not null |
status_details | json | | |
Indexes:
"train_id_updated_at_key" UNIQUE CONSTRAINT, btree (id, updated_at)
I have created a temp table train_temp
with the same schema but partitioned on updated_at
with Partition key: RANGE (date(updated_at))
I ran the below query to add the train
table as a partition to train_temp
in an RDS instance that is connected to the application server.
ALTER TABLE train_temp ATTACH partition train FOR VALUES FROM ('2021-01-01') to ('2022-01-01');
The query takes around 5 minute.
Before running the query I had taken a snapshot of the RDS instance. I restored the snapshot on a couple of instances with the same configurations as the original RDS instance.
But when I run the query in the new instances the query takes around 1hr which is very bizarre since it is the same data in an equivalent instance.
Because of the uncertainty in the execution time, I am not able to come up with a concrete plan on how I could take this to production.
Any help would be much appreciated.
ERROR: cannot attach a permanent relation as partition of temporary relation