3

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.

2
  • 1
    Do all 15 tables have dated columns?
    – J.D.
    Mar 2, 2023 at 5:19
  • Yes we have date column Mar 2, 2023 at 5:44

2 Answers 2

2

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.

1
  • Thanks Danblack . Would test this too Mar 2, 2023 at 6:37
3

You could 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 REORGANIZE PARTITION.

3
  • 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, 2023 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).
    – Rick James
    Mar 2, 2023 at 5:57
  • Thanks a lot Rick. I would test on this Mar 2, 2023 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.