I want to retain data for 90 days only for all 15 tables in my Mariadb database. Current db size is 60 GB. What are the options available for performing the same.
Staring with MariaDB-10.9.1, System Versioned tables can auto create partitions and have them expire.
So even for a table without timestamps in an explicit column:
alter table t1 WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME INTERVAL 1 DAY AUTO PARTITIONS 90;
The system versioned table is concept of the current and historical data so the queries of historical data requires special handling and this isn't a drop in replacement to conventional partitioning that in Rick James' answer.
This might or might not make sense depending on your application.
Historical data is queried like:
SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2023-03-02 08:07:06';
So obviously the data is tied to the current system time, and data deleted goes into history and is purged after its 90 days is up.
PARTITION BY RANGE(TO_DAYS(...)) each table. However, it is somewhat strange to have more than one table that is big and in need of purging periodically.
I would recommend weekly partitions. More details: Partition
Be aware that you should redo many of the indexes when Partitioning. And Partitioning is unlikely to provide any performance benefit except for the periodic
DROP PARTITION and
Thanks Rick for this. But for this do I need to recreate the table which is partitioned by range?Also is there any script available for addition and deletion of the partition. I can see some scripts-part is avilable in the link you have suggested Mar 2 at 5:48
@CassandraThrift - To convert the table,
ALTER TABLE .... For 60 GB, it would take a long time. The link I provided has the code to do the drop and reorganize; it would need some minor changes (table name, etc). Mar 2 at 5:57
Thanks a lot Rick. I would test on this Mar 2 at 6:19