Home-assistant_v2.db structure / content

Configuration
1

Hi there,
the content of home-assistant_v2.db can start rising rapidly. In HA OS 8.5 an evaluation like this could help (DB Browser for SQlite):

SELECT 
    entity_id,
    COUNT(*) AS cnt
FROM states
GROUP BY
    entity_id
ORDER BY
    COUNT(*) DESC;

Actually (HA OS 12.0) the result is like this:
entity_id:Null cnt: 98000

So where is the data of entity_id gone / why is entity_id empty now? How can I get information of memory waste? And how can I get rid of this?

2

The db schema has changed. I am no db expert, but entity_id is in the states_meta table.

And this is probably nothing to do with the OS version. The database is defined and maintained in core.

3

try

select metadata_id,count(*) as cnt from states group by metadata_id order by count(*) asc;
4

Fine, thanks