Datamining recorder data in MariaDB

Hi, I’m looking for some guidance as how to query sensor data in the MariaDB
I’m familiar with recorder purging, yet I’d like a little more insight as to what’s going on


  1. I have 200+ devices around the house, which generate A LOT of sensor data.
  2. I’ve dialed this down somewhat by curbing what Recorder actually will store,
  3. Nevertheless I still have a 2.3GB Maria DB and in periods it’s really getting pummeled, although holding up fine. Btw using Dbeaver as a console for the db

Specifically I’m looking to:

a) Query top x entity data volume, i.e. which sensors are spamming the most. This would allow me to go back and tweak my recorder config accordingly.

b) Query dormant/stale data. I understand that HA eventually will purge stale sensor data, yet i’d like to identify data in the db which hasn’t been updated for x amount of time

c) Is there a way to monitor purge activity? let’s say I’ve kicked off a manual purge after making changes, where can I see the progress, or just if it’s actually finished? I checked both supervisor and core logs yet curiously nothing there mentions a purge.

If somebody’s been grappling with similar issues, and perhaps have wrangled some SQL queries into existence, I’m all ears! Thank you.