strategy: partitioned table, delete millions of rows
Posted: Wed Aug 17, 2022 2:47 pmHello,
I've have a question about deleting millions of rows of partitioned table.
There are 12 table partitions for storing specific customer data.
Each partition holds up data for 3 months, so that these 12 partitions
store data for the last 3 years. I want to delete all records of the
4 oldest partitions and use them then as the paritions for thew newest
customer data coming in. It's like a ring buffer, that holds data for the
past 3 years.
Locking the 4 partitions and deleting all corresponding rows takes too much time
and would prevend new customer data from beeing stored.
I have two ideas how to delete the corresponding records.
1) delete/remove old partitions, create new partitions
2) delete a creatin amount of records of the partitions block wise on a daily basis.
Is there a better way how to delete the old partitions very fast ? What about idea 1. Could that work ?
kind regards,
bob harris
I've have a question about deleting millions of rows of partitioned table.
There are 12 table partitions for storing specific customer data.
Each partition holds up data for 3 months, so that these 12 partitions
store data for the last 3 years. I want to delete all records of the
4 oldest partitions and use them then as the paritions for thew newest
customer data coming in. It's like a ring buffer, that holds data for the
past 3 years.
Locking the 4 partitions and deleting all corresponding rows takes too much time
and would prevend new customer data from beeing stored.
I have two ideas how to delete the corresponding records.
1) delete/remove old partitions, create new partitions
2) delete a creatin amount of records of the partitions block wise on a daily basis.
Is there a better way how to delete the old partitions very fast ? What about idea 1. Could that work ?
kind regards,
bob harris