How do i lower the current State_ID and Event_ID that is inserted into the database?

Development
1

I have a few sensors that I would like real-time data from when I look at the dashboard.
This means they update as often as possible, every 1s in some cases.
Due to this, my load times have been slow, because the database already has 600K rows or states for 1,5 days of uptime.

I thought of merging older data at intervals:

  • Data that is older than 5 minutes will be merged to the nearest minute.
    This already reduces the data by 60x

  • Data that is older than 1 Hour will be merged to the nearest 15 minutes.
    This would further reduce the data by 15x

resulting in a 900x data reduction total if all sensors would update every 1s

Since I don’t get how HA writes to the database I wanted to just write it in the MSSQL database as a job to run at an interval.
However after just trying to delete a few rows, I noticed that the State_IDs and Event_IDs just continue where they were before.

My entries were at ID 606000 and i deleted all entries with ID > 600000

but HA just continued with writing ID 606001 instead of 600001

I currently only have a few sensors, but I plan on getting more and don’t want the ID INTs to get too high and overflow and causing errors.
This is why I’m searching for a way to lower the number that HA uses for the next INSERT statement

With my limited knowledge of the HA coding and python, I couldn’t figure out where it gets the next number from.
Is there any way I can lower that number?

Thanks in advance,
Ikomhoog

2

I don’t know much about the inner working of sqlite, but in SQL Server, if a field is autoincrement, it is SQL Server who remembers the last value and increments it, not the task doing inserts.

3

This behaviour is common to mostvif not all databases. Autogeneration of primary keys never fills gaps in numbering. It always continue from last generated value - not last stored one.

Regarding datatype: INT covers 4 trillions of values (0 up to 4 or -2 up to 2 depending on database and datatype). If it’s not enough, use BIGINT - don’t know if it’s supported by sqllite but should be enough.

However, what I can see you are trying to achieve your needs by “hacking” the data. Such workarounds never work longterm. Use specialized tool for that: let’s say prometheus + grafana or other systems which can aggregate older data on the fly

4

Thank you for your information.

It was indeed the database that remembered the value and increments it.
DBCC CHECKIDENT (states, RESEED, 0) sets it back to 1 for the next insert.

However, what I can see you are trying to achieve your needs by “hacking” the data. Such workarounds never work longterm. Use specialized tool for that: let’s say prometheus + grafana or other systems which can aggregate older data on the fly

I know that it is hacking for now, but once I have it finalized I hope that a more experienced programmer wants to integrate it into Home Assistant.