Home Assistant database – Recorder, SQLite and MariaDB

Home Assistant does more than show the current state of devices. It also records a history of changes, allowing you to review earlier entity states, temperatures, device operation and data presented on graphs.

Recorder is primarily responsible for this part of the system. In a small installation, the database may remain something the user hardly needs to think about for a long time. As the system expands, however, the number of records can increase significantly.

This is not determined only by the number of devices. A few sensors that update very frequently can generate more records than many devices whose state changes only a few times a day.

This guide explains what Recorder is, how the default SQLite database works, what affects the amount of data being stored and when moving to MariaDB may be worth considering.

Recorder is the part of Home Assistant responsible for saving entity states and events to a database.

When a device or integration reports a new state, Recorder can save that information. The data in the database is used by History, Activity, dashboard graphs and statistics, among other features.

For example, a temperature sensor may change its value from 21.4°C to 21.5°C. For the user this is a small change, but for Recorder it is another state that may be stored.

If there are many similar entities or they update very frequently, the number of write operations increases.

A large number of devices can increase the amount of data, but there is no single threshold beyond which the database automatically becomes a problem.

The frequency of changes is particularly important.

Large amounts of data can be generated by, for example:

• energy and instantaneous power sensors,
• frequently updated temperature and humidity measurements,
• technical and diagnostic sensors,
• network data,
• devices that report their state very frequently,
• integrations that provide many dynamic entities.

One hundred entities that change state occasionally may therefore generate fewer records than a much smaller number of sensors that update very frequently.

When analysing Recorder load, it is worth looking not only at the number of devices, but also at which entities save new data most often.

Home Assistant uses SQLite by default. It does not require a separate server or additional configuration. The standard database is stored in the Home Assistant configuration directory in a file named home-assistant_v2.db.

SQLite is currently the default and recommended Recorder database engine. Home Assistant also describes it as the most thoroughly tested option, and newer versions of the system are highly optimised for it.

If Recorder is working correctly, history is complete and the database is not causing problems, there is no need to replace SQLite simply because MariaDB is available.

Not every Home Assistant stability problem is related to the database.

However, Recorder is worth checking when you see:

• Recorder errors in the logs,
• missing sections of history,
• problems saving states,
• problems when using history,
• a rapidly growing database,
• little free storage space,
• errors related to the SQLite file.

With SQLite, the storage medium itself can also matter. Home Assistant lists failing flash storage, an inadequate power supply and an unclean system shutdown as possible causes of database corruption.

When these symptoms occur, it is therefore worth determining whether the problem is actually in the database or whether its source is the storage medium, power supply, lack of space or another part of the installation.

In my case, the problems became clearly visible as I expanded Home Assistant and increased the number of devices and the amount of stored data.

Home Assistant appeared to be working, but there were occasional interruptions and Recorder did not operate correctly. It did not look like a single problem with one automation or a particular device.

After moving from SQLite to MariaDB, the problems disappeared. Since then, MariaDB has been running stably in my system for about a year, including through many Home Assistant updates.

This does not mean that MariaDB will solve every stability problem. It is an experience from one specific, extensive installation. Similar symptoms in another system may have a different cause.

Home Assistant lets you specify which entities, domains and events should be stored by Recorder.

The include and exclude settings are used for this. Excluding data whose history is not needed can reduce the number of records and limit database size.

This is especially relevant for technical entities that change state frequently but whose earlier values are never reviewed by the user.

If a sensor updates very frequently, it is worth asking whether the full history of that particular entity is actually needed.

Before excluding it, however, check whether the data is later used by history, diagnostics or other system functions.

purge_keep_days applies to the detailed history kept by Recorder. Its current default value is 10 days.

Home Assistant also maintains long-term statistics for suitable sensors. Short-term data is stored in greater detail, while long-term statistics retain hourly summaries and are not automatically removed in the same way as detailed states.

Therefore, setting history to 10 days does not automatically mean that all older data will disappear from every graph after ten days. For sensors that support long-term statistics, Home Assistant can use the collected aggregated data.

The purge_keep_days parameter specifies how many days of detailed history remain in the database after a purge.

The default value is 10 days. auto_purge is enabled by default, and Recorder periodically removes older data. The current configuration also includes auto_repack, which periodically helps reclaim space previously occupied by deleted records.

Longer retention means more detailed data has to be stored.

Keeping the full history of every entity for a very long time is not always useful. Retention should match the data that will actually be needed later.

Recorder uses the commit_interval parameter.

Its current default value is 5 seconds. Increasing it reduces the frequency of disk write operations, but also introduces a longer delay before data is physically committed to the database. Home Assistant mentions this option particularly for installations using flash storage and SD cards.

There is no need to change commit_interval unless you are addressing a specific problem. It is a Recorder parameter, not a universal method for speeding up the whole of Home Assistant.

Recorder performs regular write operations.

If Home Assistant is running on slow or worn storage, the database may become one of the factors affecting the system. The official documentation draws particular attention to SD cards because intensive writes can affect both system response time and storage life.

When investigating problems, check:

• the amount of free space,
• the condition of the storage medium,
• possible file-system errors,
• power supply stability,
• whether the system is regularly powered off without a clean shutdown.

Changing the database will not replace fixing a disk, power supply or free-space problem.

MariaDB is one of the database engines supported by Recorder, alongside SQLite, MySQL and PostgreSQL.

Home Assistant OS provides an official MariaDB app. It can be installed through Settings → Apps → Install app. You then prepare a database and user and configure Recorder to store data in MariaDB instead of the default SQLite file.

MariaDB then runs as a separate database service to which Recorder connects.

After correct configuration, MariaDB can simply run in the background alongside Home Assistant.

It has been working this way in my system for about a year. During that time Home Assistant has been updated many times, and the database itself has not required ongoing attention from me.

In practice, once it was running, I continued using Home Assistant normally and did not need to deal with the database after every system update.

It should be included in backups, just like the other important parts of the installation.

There is no single number of devices after which SQLite should be replaced with MariaDB.

MariaDB may be considered when there is a specific reason arising from the behaviour of a particular installation, for example, Recorder-related problems appear, history does not work correctly or the system generates a very large number of records.

Before making a change, check:

• Recorder logs,
• the amount of free space,
• the condition of the storage medium,
• the size and behaviour of the current database,
• entities that update frequently,
• the include and exclude configuration,
• the retention period for detailed history.

SQLite remains the default and recommended Home Assistant database. MariaDB is an additional supported option that may suit a particular installation.

In Home Assistant OS, the basic process is as follows:

1. create a current Home Assistant backup;
2. go to Settings → Apps → Install app;
3. install the official MariaDB app;
4. prepare the database, user and a strong password;
5. start MariaDB and check its log;
6. configure Recorder in Home Assistant to use the prepared MariaDB database;
7. restart Home Assistant;
8. check the Recorder logs;
9. check that new states appear in History;
10. check graphs and other elements that use recorded data.

The official MariaDB app supports configuring the database, users and permissions, and the documentation shows how to connect it to Recorder through db_url.

This is important to understand before making the change.

Home Assistant officially warns that changing the database used by Recorder may result in the loss of existing history. Migrating existing data between database engines is not officially supported.

Before changing the database, create a backup and assume that the new database may begin recording history from the moment Recorder is switched over.

If Home Assistant behaves inconsistently, first determine the nature of the problem.

If devices lose communication, the source may be Zigbee, Wi-Fi or a particular integration.

If an automation does not perform an action, the problem may be in the trigger, condition, action or device.

If Recorder errors, gaps in history, problems saving states, rapid database growth or storage problems appear, the database is one of the elements worth checking.

Home Assistant integrations → Home Assistant automations →

If Home Assistant works correctly with SQLite, there is no need to change the database simply because MariaDB is available.

If an extensive system develops problems related to Recorder, history or a high volume of writes, first determine their source. Sometimes limiting unnecessary data or improving the hardware layer is enough, while in other cases moving to MariaDB may be a reasonable solution.

In my system, moving to MariaDB resolved the problems that had occurred with SQLite, and this configuration has been stable for about a year.

This does not mean the same result will occur in every installation. The important thing is to establish what is actually happening in the particular system and choose a solution on that basis.

In many installations, Home Assistant can run on the default SQLite database for years without requiring any attention to the database.

In a more extensive system, however, it is useful to understand what Recorder is, which entities generate many records, how long detailed history is retained and which storage medium holds the data.

SQLite remains the default and recommended Home Assistant solution. MariaDB is a supported alternative that may be considered when the behaviour of a particular system provides a reason to do so.

In my case, moving to MariaDB resolved Recorder problems, and the database has remained stable for about a year, including after subsequent Home Assistant updates.

If Recorder is not working correctly, your history has gaps, the database is growing quickly, or you are considering moving from SQLite to MariaDB, I can help review the current configuration, identify possible causes and choose an appropriate solution.

Contact me.The initial assessment of your request is free and without obligation. → click here