Nice update to the sections!
It needs one more thing and then I can convert all my dashboards 
Is changing the sections layouts’ grid size on the roadmap… now they are all fixed so making 3 equal icons on a row still needs an hstack card extra…
In my experience, the DB migration requires at least as much free disk space as the size of your current DB. After the migration, the DB is about 25% bigger.
1 Like
Seems that a similar estimation is for a “repack of a native sqlite base” case.
1 Like
Worth to mention that if it’s true, then it’s not db limitation but HA architecture.
I understand 10days limit for sqlite running on raspberypi class HW. But with resonable resources said postgress can maintain and serve in real-time data out of unlimited stored size. If db and app are properly designed.
on the other side of the coin, indeed time series databases are even more efficient for such a job. I recently installed Postgres with TimescaleDB, LLTS and Grafana. All running on rpi4 together with HA. and indeed, graphs provided by grafana are rendered way faster than Apex charts.
Another benefit is, that such postgres-based db cannot break (benefit of fully transactional db) In worst scenario some records might be lost but anomalies in stored data will definitively not affect HA (which cannot be said about sqlite sometimes messed up by HA)
2 Likes
btw the Energy dashboard. I can see some love put on this recently.
What hit me not long ago is, that the dashboard is tightly coupled with sensors set to it (with their entity names)
If one collects energy data for years and then installs FVE, he likely has to change the dashboard settings replacing energy sensors with others, likely provided by a solar inverter (consumption and production)
In such case house consumption for past days/years is gone from the dashboard.
The same will happen if you change the energy sensor for another one, unless you secure the same entity name
Do you know about incoming improvement in this area?
I don’t, but there’s this to help, even though it’s quite manual: HA Energy Dashboard FAQ · GitHub. See #1.
That actually is a big issue now that HA assumes what those values mean.
Let’s take my installation. I have:
- Smart meter reporting as one sum over all phases
- A sensor for each of the 3 phases for the whole house (reporting 2 values, inbound and outbound)
- A sensor for each of the 3 phases per subpanel
- Multiple sensors for individual circuits in the subpanels
- Individual devices reporting their own consumption
Assuming the lower 4 can be summed up and compared to the first one is nonsense.
(It’s already annoying that the smart meter sums don’t match any combination of the per-phase measurements of my own sensors.)
Thank you for pointing me out to that. Didn’t know about it at the time I changed the dashboard. And this is the worst thing: most of us probably notice that days after changing the dashboard.
From the linked article, it seems like it’s possible with a little bit of trickery. But if “old” sensor has to stay in the system (my case) it’s a kind of embarrassing proposal.
BTW I’m not sure it’s even possible to rename sensors provided by an inverter integration.
??? really why? I’m admin of multiple Postgres databases, size really is not an issue. And if (possibly) is table partitioning is our friend. For example OpenStreetMap is running on Postgres and whole planet is really big. Compared to OSM, 900 days history is probably very small DB.
I don’t understand the strange approach for updating schemas. Convert integer column to bigint is really simple, for example alter table ha.states alter column state_id type bigint and we’re done.
I remember some HA DB migration in the past. Whole schema had about 700 MB size. The migration took hours on SSD and generated 35 GB (GIGABYTES!) of transaction logs. for migration 700 MB database. It’s about 50 times bigger size of logs than was the whole database size. I saw queries as “update … set old_state = state limit 999”, “delete … limit 999” etc.
How can happen “Not all migrations were successful despite being marked as successful.” ? Doing the migration in transaction can prevent this (all or nothing).
If I understood recorder/migration.py, upgrade to 2024.9 will migrate DB schema to version 46 - it will drop foreign constraints, will change bigint to bigint and schema version 47 will re-create constraints dropped in schema version 46, right? In this case we should insert rows into table ha.schema_changes to avoid this unnecessary migration
I love HA, but DB migration should be really improved a lot. Why this strange approach? Is some SQLALCHEMY limitation?
2 Likes
There are very clever people working on HA. However the database seems to me too have always been a weak point. Perhaps they need more database people. Yes, that is a hint!
6 Likes
This should probably come in the future…
First, this graph should also have the ability to hide specific devices (would be perfect, if it would just inherit the settings from the ‘track single devices’ graph above)
As long as an entity had a unique ID, it can be renamed. It’s not an issue in most cases.
Read carefully. It doesn’t stay. It’s just the order that’s very important: if you delete the old ones you’ll delete its history too, so you need to e sure there’s overlap. Or do you mean the old entity might have a name that’s tied in a very descriptive way to the old integration or setup? Then yes, perhaps that’s not ideal, but personally I rename my entities to keep them functional when I first use them. For example, I won’t have an entity called sensor.shelly_pm_energy_1. That might be the name when it’s first created, but already then I’ll rename it to sensor.outbuilding_energy which is descriptive of its function and then it doesn’t matter if it comes from an old or new integration.
1 Like
is a custom component.
You should report issues within the repository of this integration
If you’re just looking for an argument, perhaps take this somewhere else, or get your hands dirty and contribute to the project. Either way, you’re trivialising this. Simple, fast, risky and efficient isn’t all the same.
In really big databases that must be online 24/7 it is not that simple. Actions like these will lock up a system with table/page/row locks (for one). If downtime cannot be scheduled, then you’d use tricks such as operating on a copy of a table (underneath a view), or drop your indexes to make the alter faster before re-adding the indexes (and/or the FK like they’ve done here). Even for an offline migration that can be too much. In the case of HA, people don’t run their systems on commercial hardware, even though a 10-100GB DB doesn’t seem that big.
Partitioning would’ve made no difference: When doing an alter, your DB needs to be fully consistent before you resume operation. It’s not like you can change one partition by itself. You can fake things under a view, but that adds a lot of complexity.
I think part of the challenge is that ideally you’d want to redesign the whole DB from the ground up, but that will make migrations very hard (changing a flying plane and all that). HA also supports many engines, which means you need to account for the quirks of each, and that you cannot fully utilise the specific strengths of certain engines.
7 Likes
hm… this could already be done.
You can add your individual water meters to the water usage dashboard, and they will be shown individually…
(Just similar, as you could also add your individual devices to the main electrical energy usage graph)
This should work just similar as in this screenshot
Looking like a nice release
I saw that the Energy dashboard now supports adding a “total” device that monitors the home power consumption as a total and it is then compared to the rest of the devices.
Does anybody know how/where to define that device?
In my case I have a Shelly 3EM monitoring total. I have been abusing the dashboard and set the Shelly 3EM to grid consumption and the individual devices as return to grid. That also gives me the delta. But the update should make that obsolete
So I would guess that somehow I need to tell HA to use the three Shelly 3 EM entities as total?
And does anybody know it the ista ecotrend integrations need some kind of manual input to pull the old data?
I do not see my old data after the update. One of my favorite updates of this release 
.
yes, this is - unfortune.
the best Idea I would have about this is - not to ‘replace’ the entity in the Dashboard Settings.
It really depends on the setup - if you get your measurements from the Meter via an IR reader, you could probably just replace the meter, and the IR reader will still continue to work - and continue counting.
If you are using something like a shelly, and this needs to be replaced, I would expect that the integration would probably create a new, additional device with its entities.
In this case, I would just add the new entity to the dashoard and also keeping the old one for the history.
If you need to replace a meter, which comes from your solar installation, it might be a bit more complex, ad it would depend on the integration if it will just use the existing entity with some ‘new’ values - or if it would create new entities…
With the help from @bdraco I managed to find key and the key slot from my old backup, and set it manually with new integration in my current installation:
I did a complete reset (factory reset) and set everything up again with a new account. The key is therefore out of date - I tested it from a backup.
Hi dev,
thanks again for your hard work!
I noticed that with 2024.9 release i was unable to start and stop motion detection on my camera (unifi video integration). i’ve test trough developer option calling the relate action!