Data Storage

In the default installation of Home Assistant, all history logging is done to a SQLite file database, which is excellent with a small number of entities and a short history. Choosing the right database is crucial for optimal performance and data management.

As you can see below, SQLite handles queries very well, but when it comes to DML operations - insert, update, and delete - it starts to slow down.

Comparison of performance of different database operations

Graph Interpretation

The values in the graph indicate the number of nanoseconds needed for one operation. Smaller is better.

Another feature of SQLite is that it runs on the same machine and disk as Home Assistant, which can be an advantage or disadvantage depending on the situation.

Purging Historical Data

If Home Assistant starts to become very slow and you don't need much history directly in it, the purge_keep_days parameter is useful. This parameter allows you to set the number of days to keep in the DB. The default value is 10 days, and data purging occurs every day at 4:12 AM local time.

configuration.yaml

# Use only one db_url according to the DB you want to connect HA to
recorder:
  ...
  purge_keep_days: 14 # if we want to keep 2 weeks of data

Supported Databases

• SQLite ≥ 3.31.0 (2020)
• MariaDB ≥ 10.3 (2017) a MySQL ≥ 8.0 (2018)
• PostgreSQL ≥ 12 (2019)

Database Connection Settings

Database Connection Library

For connecting to all databases, HA uses a unified library SQLAlchemy, making the connection configuration very similar for all databases.

configuration.yaml

# Use only one db_url according to the DB you want to connect HA to
recorder:
  # SQLite
  db_url: sqlite:////cesta/k/db_souboru

  # MySQL nebo MariaDB
  db_url: mysql://uzivatel:heslo@server/databaze?charset=utf8mb4

  # PostgreSQL
  db_url: postgresql://uzivatel:heslo@server/databaze

Additional Recorder Settings

In addition to setting up the database connection, we can define the exclusion of entities to be recorded in the DB in the HA configuration file.

configuration.yaml

recorder:
  db_url: !secret postgre_conn
    exclude:
    # Exclude entire domains
    domains:
      - automation
      - update
    # Exclude specific entities
    entities:
      - sun.sun

Database Installation

MariaDB

If you have add-ons available in HA, you can use the installation of MariaDB this way. In the configuration, you will only need to set the database password, which is not included in the default installation, and start the add-on.

Database Backup

SQLite

If you are using SQLite, which is a standard part of and the default configuration of HA, then the data file is part of the Home Assistant backup, and you just need to perform this backup.

MariaDB/MySQL

If you are using MariaDB as a Home Assistant add-on, which I recommend, then the easiest way to back up MariaDB is to back up the entire add-on.

Alternatively, you can use your favorite MySQL/MariaDB client if it supports backups, or install the mysqldump utility in the terminal.

Installing mysqldump on Ubuntu/Debian

sudo apt update
sudo apt install mariadb-client
mysqldump --version

Using mysqldump for Backup

mysqldump -h [mariadb_server_ip] -P [port] -u [username] -p[password] [database_name] > [backup_file].sql

Example of backing up MariaDB running on a local machine:

mysqldump -h 127.0.0.1 -u homeassistant -phomeassistant homeassistant > bck_homeassistant.sql

Database Migration

You may find yourself in a situation where you initially chose one database (or just left the default SQLite) and now want to start using another. Ideally, you would like to keep all the data you already have in the database. Fortunately, this is possible.

MariaDB/MySQL -> PostgreSQL

The following is a method for migrating from MariaDB/MySQL to PostgreSQL, but the steps for all other database combinations should be very similar.

PostgreSQL Installation

The guide below assumes that you already have a PostgreSQL database installed and running.

1. Stop Recording to DB

The first step I recommend is to stop recording to the database, which can be done by calling the recorder.disable service. This step allows us to safely download data from the database without having to stop Home Assistant.

2. Convert Data from MySQL/MariaDB to PostgreSQL

Installing pgloader on Ubuntu/Debian

sudo apt-get update
sudo apt-get install pgloader

Installing pgloader on MacOS

brew install pgloader

Data Conversion

• Create a conversion definition file and save it as mysql-to-postgres.load - use your favorite editor.

  mysql-to-postgres.load

  LOAD DATABASE
      FROM      mysql://homeassistant:[email protected]/homeassistant
      INTO postgresql://homeassistant:[email protected]/homeassistant
  
  WITH include drop, create tables, create indexes, reset sequences,
        workers = 8, concurrency = 1,
        multiple readers per thread, rows per range = 50000
  
  SET work_mem to '32 MB', maintenance_work_mem to '64 MB';

• Run this file using pgloader:

  pgloader mysql-to-postgres.load

Different DB Structure

It may happen that you have a DB created in an older version of Home Assistant and it still contains columns that are no longer needed and will likely be empty. These columns can cause problems during migration and unfortunately cannot be simply removed, but their type can be changed, usually to char(1).

The current definition of HA tables can be found here.

3. Switch DB in Configuration and Enable Recording

• In the HA configuration.yaml file, change the recorder settings as shown in the section above.
• Enable recording to the DB by calling the recorder.enable service