Database schema changes?

Configuration
1

I have an old installation of Home Assistant that I have cobbled together over the past few years. I have not updated it because I see no need when it is working fine, and there’s only so many things one can do in a day. The whole point of a smart home is to make your life easier, right?

Anyway, I decided to get a fresh start with new hardware and the latest code. I’ve been copying my YAML over to the new machine, and it’s all been going pretty well. That is, until I got to the SQL sensors.

Best I can tell, the schema - nay, the entire concept - has changed. I used to be able to create sensors using simple SELECTs and WHERE clauses. Best I can tell, now it’s impossible to pull any information out of the database without a bunch of INNER JOINs and/or LEFT JOINs and/or subselects. I used to be pretty good at writing SQL but it’s been years and that area of my brain is full of cobwebs now.

Is this true, or am I missing something? I really don’t see any advantage to the new schema over the old one. Am I just looking at it wrong?

2

I think this was done to reduce the size of the database and the amount of bytes written. Last change i know was moving the entity_ids to the states_meta table. Before this, with every state change the entity_id was written into the database, depending how long your entity names are these takes some amount of space.
Now you only need a 16 bit integer value for every state change and the entity_id is only stored once in the table, referenced by the id. This reduces the size of the writes (good for raspberrys with sd card) and shortens the database.
If i remember right the same was done for the event table some time ago. For some users this means they have to do extra work changing their sql statements, but hey, updating home assistant often means you have to do something on your system to get it to work as before :wink:

3

Thanks, that makes sense that it would reduce the size of the database. Well, I had better get crackin’ on these queries.