Upgrade to 2023.4.4 (with mariadb), no more history, slow queries on db, is it the migration?

Installation
21

Examples linked in the release notes and here https://data.home-assistant.io/

1 Like
22

Querying has gotten a bit more complex with these recent releases. Below is an example of getting the last day of changes to a sensor, regardless whether the state or attribute changed (old query shown below new). I use Postgresql, not Mariadb so your SQL syntax will probably be different. I have a lot of queries to update… Good hunting!

-- get freezer temperature for last 24 hours
SELECT
    states.state,
    to_char(to_timestamp(states.last_updated_ts), 'YYYY-MM-DD HH24:MI:SS') AS last_updated_ts,
    to_char(to_timestamp(states.last_changed_ts), 'YYYY-MM-DD HH24:MI:SS') AS last_changed_ts,
    state_attributes.shared_attrs,
    states.context_id_bin
FROM
    states
    LEFT JOIN states_meta ON (states.metadata_id = states_meta.metadata_id)
    LEFT JOIN state_attributes ON (states.attributes_id = state_attributes.attributes_id)
WHERE (states_meta.entity_id = 'sensor.freezer_govee_temperature'
    AND to_timestamp(states.last_updated_ts) > now() - interval '1 days')
ORDER BY
    states.last_updated_ts DESC;

SELECT
    *
FROM
    states
WHERE (entity_id = 'sensor.freezer_govee_temperature'
    AND last_updated > now() - interval '1 days')
ORDER BY
    last_updated DESC;

freezer-query
freezer-query1920×1551 253 KB

23

More complex, but I supposed that is the price to pay in order to reduce the DB size (more relational), by the way nice example! Thanks!

24

Wowwwww it’s everything!! Thanks a lot!

25

I’m experiencing the problem again after migrating from 2023.4.5 to 2023.4.6. That’s frustrating.

I’m rolling back to 2023.4.5