Data retention in Mariadb

Question

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.

Answer 1

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.

Answer 2

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.