InfluxDB. Deleting old data and purging the database

My InfluxDB has been storing everything everything for 4 years or more now and the database and backup has reached 17Gb. With the help of others on different threads in this forum, I’ve now set a retention policy (it was infinite) of 800d and added a list of Includes with the only stuff I want to keep. Seems to be working as all the stuff I haven’t ‘included’ doesn’t show recent data when I interogate using Grafana. All the includes are still current. So far so good.

But now I want to 1) delete stuff from the database, and then 2) purge, or compact it, to reduce the physical size of the database file and my backups.

I’ve read some posts that seem to tell me how to delete but the suggestions don’t work. The keyword ‘DELETE’ doesn’t even seem to be recognised in the InfluxDB panel SQL box. I’m guessing the suggestions are old and refer to earlier versions and the methods have now changed. I haven’t seen anything on purging.

Here’s a pic of the InfluxDB panel set to Explore with a random query selected (obviously something I’ve not ‘Included’).

Anyone know how to do either deletes or purges on the latest InfluxDB?

I’ve found another Thread where I’ve resolved the Deleting. It’s a bit long winded so I’m trying to see if wildcards can speed things up. As it is I’m currently using the ‘picker’ to create a query to SELECT a block of entity_id s that I want to delete, and then modify the query to do the deleting.
ie the Select query might look like this

SELECT mean("value") AS "mean_value" 
FROM "homeassistant"."autogen"."%" 
WHERE time > :dashboardTime: 
AND time < :upperDashboardTime: 
AND ("entity_id"='battery_level' 
OR "entity_id"='m2003j15sc_battery_level' 
OR "entity_id"='m30_battery_level') 
GROUP BY time(:interval:) 
FILL(null)

and I edit it down to this

USE "homeassistant"; 
delete FROM "%" 
WHERE "entity_id"='battery_level' 
OR "entity_id"='m2003j15sc_battery_level' 
OR "entity_id"='m30_battery_level'

It works. The entries disappear but I doubt it makes the database file any smaller so still hoping for someone to point me the way on that.

2 Likes

Hey there, I have similar problem. I am new to HA and I created some entity to log my monthy km for my car leasing. My bad, it tracked wrongly before November was okay, since the counter was set correctly. But the data from October is a mess.

  1. it counted in Mm (million meters!) instead of km.
  2. all data from "this year so far is simply not “nicely” displayed.

Is there a possibility to delete the data from influxDB from october or at least only for the entity “monatlich_gefahrene_km”?

I tried some commands, but I am serioulsy overstrained how to use those properly and I dont want to brick influxDB or something

you’ll need to run the compaction service on the bucket if we’re still talking about InfluxDB.

I have no idea what you mean… I dont know about “influxdb compaction service on the bucket”

Is ther any way to delete data from November and earlier?

Can you create a query that only shows data you want to delete? If so then change it as per my example post above.