Positions Partition
Hello everyone, this post fits me like a glove since I have a similar problem and I no longer know what to do.
I have a database with around 60k devices that store around 27 million records a day, I only leave the last 35 days of location history so I have to delete constantly, the bottleneck is that I delete blocks of 100 records per second, which is the maximum I can do without affecting the insertions of new records. I cannot do this all day because it slows down the insertion of new records. I only do it between 1am and 7am, but they are very few. the records that I manage to delete, around 3 million records while 27 million new records enter daily, I don't know what to do... I understand that partitioning the position table could help me a lot in this task? What are your opinions? I would value them very much
It sounds like you have some serious problem with deletion. You should check the SQL query plan for it. You should easily be able to delete thousands of records per second.
No. We have quite a few servers with several millions of records and all of the work just fine.