0

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.

2
  • ERROR: cannot attach a permanent relation as partition of temporary relation
    – jjanes
    Apr 15, 2021 at 15:16
  • nope, I dont get this error.
    – Surya
    Apr 17, 2021 at 16:33

0

Your Answer

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

Browse other questions tagged or ask your own question.