Currently clearing it using the recorder purge service. This is what it looks like after running it for 48 hours
MariaDB [(none)]> USE homeassistant;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [homeassistant]> SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)", ROUND(data_free / 1024 / 1024, 2) AS "Free/Reclaimable Space (MB)" FROM information_schema.TABLES WHERE table_schema = "homeassistant" AND table_name = "states";
+--------+-----------+-----------------------------+
| Table | Size (MB) | Free/Reclaimable Space (MB) |
+--------+-----------+-----------------------------+
| states | 38181.56 | 274.00 |
+--------+-----------+-----------------------------+
1 row in set (0.004 sec)
Here’s a filesystem view:
core-mariadb:/data/databases/homeassistant# ls -lhSr total 41G
-rw-rw---- 1 root root 67 Jul 8 2024 db.opt
-rw-rw---- 1 root root 1005 Nov 22 13:09 schema_changes.frm-rw-rw---- 1 root root 1.4K Nov 9 04:38 statistics_runs.frm
-rw-rw---- 1 root root 1.5K Nov 9 04:35 event_data.frm
-rw-rw---- 1 root root 1.5K Nov 9 04:35 state_attributes.frm
-rw-rw---- 1 root root 1.5K Nov 9 04:35 recorder_runs.frm
-rw-rw---- 1 root root 1.7K Nov 22 13:09 event_types.frm
-rw-rw---- 1 root root 1.9K Nov 22 13:09 migration_changes.frm
-rw-rw---- 1 root root 2.3K Nov 9 04:38 statistics_short_term.frm
-rw-rw---- 1 root root 2.3K Nov 9 04:35 statistics.frm
-rw-rw---- 1 root root 2.4K Nov 22 13:09 states_meta.frm
-rw-rw---- 1 root root 3.3K Nov 9 04:35 events.frm
-rw-rw---- 1 root root 5.0K Nov 22 08:45 states.frm
-rw-rw---- 1 root root 5.7K Nov 22 13:10 statistics_meta.frm
-rw-rw---- 1 root root 96.0K Nov 22 13:09 schema_changes.ibd-rw-rw---- 1 root root 96.0K Nov 22 13:10 migration_changes.ibd
-rw-rw---- 1 root root 112.0K Nov 22 18:00 recorder_runs.ibd
-rw-rw---- 1 root root 112.0K Nov 22 13:09 event_types.ibd
-rw-rw---- 1 root root 352.0K Nov 24 22:51 statistics_runs.ibd
-rw-rw---- 1 root root 7.0M Nov 22 17:33 statistics_meta.ibd
-rw-rw---- 1 root root 9.0M Nov 24 07:06 states_meta.ibd
-rw-rw---- 1 root root 11.0M Nov 24 22:37 event_data.ibd
-rw-rw---- 1 root root 29.0M Nov 24 22:49 events.ibd
-rw-rw---- 1 root root 268.0M Nov 24 22:51 state_attributes.ibd
-rw-rw---- 1 root root 520.0M Nov 24 22:51 statistics_short_term.ibd
-rw-rw---- 1 root root 1.9G Nov 24 22:02 statistics.ibd
-rw-rw---- 1 root root 38.1G Nov 24 22:51 states.ibd