Most likely the recorder was designed with the intent of moving to a NOSQL database at some point, or to one of the growing number of SQL databases that support JSON as a native format.
This is not a reason for a complete recorder rewrite- it is wat allows the recorder to be flexible in what it stores. To badly translate a Dutch proverb: A relational SQL database is about as flexible as a lead door. This is why NOSQL databases are gaining so much traction.
Edit:
Geeesus, why you write this, now i just got another ânewâ Option 
to look into
I donât know the reasons, but itâs obvious that there are quite a few that seems to have âmissedâ that the Recorder Now Records ALL, so people have to make their choices, depending on their needs/installation etc. Flexibility a High level ⌠Number 1 Choice i went for HomeAssistant
To be fair, they do use some native datatypes in the table. I think this was done maybe a year or two ago ? I remember at some point they had to update the db format and migrate the db (which resulted in some cryptic error on my setup and the db being corrupted, but thatâs another story). Right now, the states table format looks like this:
Lots of varchars (some of which are unavoidable to some extent), a hideous TEXT (see below) and lots of redundant data being stored. Every single state change stores the full entity ID (as text), full domain name (as text too) and, worst of all, a full json dump of all the attributes:
A full json text dump for every single state change of every sensor.
The amount of redundancy in that db is insane. Not only will this bloat the db size, it also makes it slow to load. Not so much on the SQL query side, but all that redundant data needs to be parsed back into Python objects. And parsing is slow, and Python is slow. The problem here is, as you said, thereâs no easy fix for this besides a full redesign of the recorder system from the ground up. State changes need to identify their sources over short index IDs or GUIDs that link back into another table for the entities and attributes to cut down on both parsing overhead and redundancy.
I just did a simple check on a random state table entry on my db for a temperature sensor. A single state, as it is, uses 294 bytes in the db for a single temperature value. The exact size varies by sensor and even state, due to liberal use of variable length cells. If you remove most redundant overhead, keep the states and event IDs, a timestamp, an additional ID to link to the source entity and attribute table and finally, a IEEE float for the value, that data can be compressed into 24 bytes. Thatâs 8% of the original size. And technically this could be made even smaller.
You do have to take into account that what you are talking about is ONLY the data, and not how this data should be stored, and Not how the system/core and front-end have to call it⌠so you canât compare, if you donât take in consideration how an sqlite vs relational-db works ⌠i fully agree that there are stored âa-lotâ , one can reduce this some by not calling every âentityâ i.e. ( sensor.myawasome_temperature_sensor_on first_floor ), and not include âentitiesâ from a device that you know youâll never use etc. and âmeasurementâ and âunit_of_measurementâ is hardly âredundantâ in this sqlite-DB ⌠and attributes, you donât have much choices as itâs individual âinformationâ from specific-device/sensor, if you call this information âredundantâ, you have to study âAttributesâ closer, as there are âvariable-stringsâ, âstatesâ etc. etc. in the Attributes- therefore the TEXT -type
And i just did a âcompact dbâ, which reduced it 1/3, i know we are going towards a bigger and bigger âdefaultâ Installation, with alot more integrations, and therefore a bigger DB, there are Documentation in regards to this, Recorder etc. Tips on what to exclude, to reduce your installation and keeping it Healthy
A New default âExternalâ DB is hardly a way forward for a product like HA, a Maria-DB wonât make any different, in the end itâs about how the user choose to Use the product ⌠default it works for many, for others they HAVE TO follow up on the Upgrade-notes, and yes have to make additional decisions/settings to âRoll-Onâ
PS: and Aron was talking about DB-Size vs Data-Size ⌠itâs simply wrong, and taken out of a very importent context DB-in-Aplication
Iâm afraid you donât really understand how data is stored in databases on a low level.
The backend issues an SQL query over a time range defined by timestamps. Thatâs all there is on the call side. The problem is the way the data is stored (the data layout) and the subsequent processing into natively readable objects.
Of course it is. Your unit of measurement for a temperature sensor is not going to change from one sample point to the next. Storing it for every single sample is completely redundant, as are the attributes like friendly name, etc. This is high level structural data that will never change when acquiring data points. It needs to be stored in a separate table (or not at all, friendly name for example can be retrieved from the HA config instead) and indexed from the states.
Data size is directly correlated to DB size and vice-versa in this context. The size of the management data itself is insignificant compared to the size of the stored data, as long as the redundancies are removed. In SQL, every native type has a fixed size in a cell, except for varchars (which are being much too liberally used by HA).
i just checked my entities( potential events and states âspammerâ )
about 300 ( if i use a ânaming strategiâ that reduce the Name and Entity Name with lets say 15 letters each ) ( 300x30=9000 Bytes) every 10 seconds ( 54000 Bytes a minut ) here we are talking âRedundantâ
i know , but this is not how this db works as i understand, but in a relation-db yes
There i agree, and donât understand, as itâs already registered in config-files, as well as device-name ⌠thou then, if itâs not stored in the event, then the underlying process have to read from these config- files ⌠bump, +30% faster to read from db-blob ⌠so we are back to âmaintenanceâ of DB, if we want to have a fast responding UI and core
i see 3 varchers in the example, state( canât be different, unless TEXT) , entity_id ( you want INT here ? ), Domain ( what was it now 3 was ) ⌠yes, i dont understand what you want it to be instead
This has nothing to do with âhow db worksâ. This is about the recorder component choosing how to store data in the DB by splitting it up into appropriate tables and interlinking them in such a way that static data gets only stored once while dynamic data is stored in a highly optimized cell layout linking back to the immutable data.
I noticed. Donât worry about it.
i know the configuration/setup of the db didnât came out of the blue 
, and i has also hope on that someone, some day will offer his time and expertise to develop the DB into todayâs circumstances/demands ( Maybe even to MY satisfaction/ needs and preferences)( i have lots of idea and ways i want it), but im not in a position to complain about it, and donât really find it appropriate, as i have âoptionsâ AS everyone else with just a little Computer knowledgeâs ⌠itsâ just a click away, and scrolling a few docs, or dozen
PS: above would be Edit: 77 760 000 Bytes a day ( redundant_loooong_names ) ⌠start thinking positive, thereâs a-lot one can do, beside complaining
I still would like to âseeâ that query in âactionâ ⌠for i.e. A Switch 1 Basement, with 4 âAttributeâ ( Total Power Switch 1 Basement (ad-ele) , Current Switch 1 Basement, Voltage switch1 Basement, Power switch 1 Basement ) ⌠and your âproposedâ Database-Model, tables-setup, relation etc ⌠and why not an estimated comparing with current model , performance-vice ( I.E (open/close)Total and, read from config-files etc,⌠but of-cause also your use-case, for average user in terms of administration etc. on a âdefault MariaDBâ, if thatâs your thought, ⌠or âjust a new/rebuildâ sqlite ⌠im sure the "default image, will increase a-little( but we are there already( with all new Feature-Reqests/integrations/Product-support etc. ) implemented lately ( which i assume you also have taken in consideration in your judgement off current DBâs expansion, (beside the new entities and automatons you added, during same time , and the fact that Update-note, and âHeads-upâ , in regards that Recorder Records ALL eventâs and State-Changes now, if not âToldâ other-vice, and to be honest, and why not those âExtraâ entries you create-in-db, regardless of DB model, when you lower you sensor to âspewâ every second, instead of 3 or 5 )
PS: I do know how a table look like, have experience from Oracle, MySQL, and now also a-little on how Grafana/Influx wantâs to be asked nice ( havenât manged to create a âunfortunate Loopâ ) ⌠yet
I think in my absence everything I was going to reply to was already said. I only get time to visit here occasionally and mainly on weekends.
Just a few points I want to make reallyâŚ
SQLite is a perfectly capable relational database engine, so everything said here regarding changing the database structure to proper relational tables to avoid storing redundant data applies to it as well.
Relational database are by their very nature less flexible. That is a large part of the the point, actually. Storing data within that rigid structure means storing efficiently, predictably and in formats the database engine is optimized to work with.
The HA database is nothing special. It really isnât. It would be trivial to design a relational schema to efficiently hold the data in say, 15 minutes. Probably most reading this thread with some database experience have already done so in their head. (I have).
Relational database engines are optimized to perform best when data is respectful of native data types and broken down to its elements. It is very costly for a database engine to parse a TEXT field of JSON to extract a value (and yes, Iâm aware that JSON types now exist in many DB engines like MariaDB, however this is not universal). It is trivial for the DB engine to simply link together a few tables via JOINS. Especially if they are indexed properly.
If anyone is curious about how data can be stored across multiple tables and then combined in a query via relations, hereâs a great place to start:
There are actually a lot of decent tutorials out there (I just Googled) to get you working with a relational database and make the concepts much clearer. If you have Microsoft Access, Google up the famous âNorthwind sample databaseâ or install it directly from Access templates. Itâs a relational database which is designed as an example and used in many tutorials.
As Mentioned, there is a big difference in âRead Aboutâ and Understanding ⌠but if you âAlreadyâ figured it out, i look forward to see Your proposed Model
To be honest, you might be waiting a long time. I havenât created a database schema in anywhere except my head and donât really see the need to put it down in this thread. Primarily because (I hope this does not come through as hostile, I am just lacking a better way to say it) I have nothing to prove. In a way, this is database design 101 and there is no need to prove the assertion that a relational database which eliminates the redundant data and stores data in native data types instead of just VARCHAR and TEXT would be orders of magnitude more efficient. It is is.
Considering how long the database has been an issue, despite database corruption, performance and size threads being a constant in this forum since I began with HA (sometime around version 0.40), fixing the database is of very low priority in development.
I understand, you have really thought it through, maybe you should have looked at various âAttributesâ, tried to understand what those really are, so you knew before you started âthinkingâ out a solution, and taken in consideration the impact in performance, and additional DB usage use(CPU, RAM) your âsolutionâ would lead to.
I sincerely doubt a âDictionaryâ in another table will solve anything, just make it worse, you will brake a-lot functionality ⌠AND before you commend any further, please go through your âAttributesâ , as i recommended you before, and my âkey-wordâ was then âVariablesâ ⌠( or maybe i should say âentitiesâ ⌠with state = Boolean, strings(bigger than Varcher) as-well as INT, with multiple âintegration-specificâ values âunit_of_measurementsâ Names etc. etc. ) ⌠so every time a sensor update states/events(as now in a single âBLOBâ) (open/close), this âdictionaryâ table(with Rows corresponding to 5-10 times or even more, than entities, if you count device-tracker, etc. etc.) should be updated in all itâs related "Attributes-Rows and corresponding Columns(which might have to be a few TEXT column also)
To be honest, I am âastonishedâ over your âSolutionâ
PS: Some people have problems with their DB, others not, for various reasons
Clear answer: no, backups are not useless (anymore 
). Because:
Seems this is working meanwhile. Had to restore a HA backup and database looks fine so far. Details @ Fix corrupted / malformed SQLite database (home-assistant_v2.db) - #23 by e-raser.
With recent updates, HA has gone to a relational model for attributes and other parts of the database as was described in this thread by myself and others. The performance improvements and data size reduction are impressive.
For me backups are still useless. My HA instance is huge, database file has 6GB, no way to back it up and restore it the convenient way. What a bummer, I will take the long way by manually replacing the db files and hope for reliable backup process soon.