Under the current design, it’s not really possible for the db to clean up. History is tied to the entity ID. Changing the entity ID in the UI does not modify the existing data, it simply becomes orphaned.
Orphaned history is indistinguishable from history for an entity that hasn’t popped into existence yet. For the many, many integrations that do not yet register their entities in the entity registry, HA has no idea what entities are still coming. Integrations aren’t required to create all their entities at startup, they can create entities on the fly when new data comes in that warrants it. There’s also a POST /api/states REST API that allows someone to post state data with any entity id at any time so new entities could pop into existence whenever. When they do you would see any history in the db tied to that entity id in the UI.
Basically if HA used the current list of entities you see in states and cleaned up any history for entities that weren’t in there it could clear out totally valid history for entities that would re-appear eventually. So instead it leaves it until it is cleaned out for being too old based on purge_keep_days.
Now you could say “HA should migrate history when I change the entity id in the UI”. As that’s the point before it becomes orphaned when it’s possible to do something about it. I would agree with you. However that’s a Feature Requests not a bug as it requires an enhancement (migrate history when entity id is changed). Remember history existed before it was possible to change entity IDs in the UI, that came later.
Pretty sure this wth is requesting exactly this (among other things):