Table size not decreasing after deleting large amount of data in MySQL

Hi Traccar team,

I am using MySQL with Traccar, and I recently deleted a large amount of data from one of my tables. However, I noticed that the physical size of the table did not decrease after the deletion.

Here are the details of the table(tc_positions):

• Table size: 32.3 GiB
• Row count: 165,432,147
• Downtime is not acceptable for this operation

I am looking for guidance on the best strategy to reclaim disk space or optimize the table without causing downtime.

Specifically:

1. Can I safely use OPTIMIZE TABLE or ALTER TABLE in this scenario?

Note:

1. We have already implemented clearing database mentioned in https://www.traccar.org/clear-history/

Any advice or guidance would be greatly appreciated!

Thank you.

It is absolutely expected that the file size doesn't decrease when you delete the data. Why do you need to decrease it?

otherwise the physical storage will increase linearly ?

Why would it increase? If you removed data, that space will be reused.

Copy database, delete table, recreate table, point traccar to new database, delete old database.
Down time = seconds.

If you just nuke the whole positions table there's an easier way - use TRUNCATE.

@Walter Spada, our table is like 33.5 Gib. don't think its down time is seconds.

@Anton Tananaev, can't do that, we keep latest 2 months data.