Mastering Home Assistant's History: Using an External Database (MariaDB/MySQL)

Home Assistant's Recorder integration is fundamental to its ability to track the state of your devices and entities over time. By default, this data is stored in a SQLite database file on your Home Assistant instance. While convenient for simple setups, the SQLite database can become a performance bottleneck as your smart home grows, leading to slower restarts and sluggish history browsing.

Migrating your Recorder database to a more robust, external database server like MariaDB or MySQL can significantly improve performance, reliability, and provide better options for long-term data management and analysis.

Why Use an External Database for Home Assistant History?

The default SQLite database is file-based and suitable for small installations. However, it has limitations:

• Performance: SQLite struggles with concurrent writes and large amounts of data, impacting Home Assistant's responsiveness, especially when accessing history or during startup.
• Scalability: As your number of entities and sensors increases, the database file grows rapidly, further degrading performance.
• Reliability: File-based databases can be more susceptible to corruption, especially if Home Assistant is not shut down cleanly.
• Analysis: Analyzing data directly from a SQLite file externally is less straightforward than querying a standard relational database server.

Using an external MariaDB or MySQL server addresses these issues by providing a dedicated database engine designed for performance, concurrency, and large datasets. This allows Home Assistant to offload database operations, freeing up resources and resulting in a snappier interface and more reliable history data.

Prerequisites

Before you begin, you will need:

1. A running instance of Home Assistant (any installation method).
2. Access to a MariaDB or MySQL database server. This could be on the same machine as Home Assistant, another machine on your network, or even a cloud-hosted database. Ensure the database server is running and accessible from your Home Assistant machine.
3. Database credentials: A username and password for a user with privileges to create databases and tables on the server.

If you don't have a MariaDB or MySQL server, you can set one up. For Home Assistant OS/Supervised users, the MariaDB add-on is an excellent choice. For other installations (Docker, Python venv), you'll need to install and configure the database server separately.

Setup Steps: Migrating the Recorder Database

This process involves configuring Home Assistant to use the external database. It does not automatically migrate existing SQLite history data, although there are advanced methods for this if needed. For most users, starting fresh with the external database is simplest.

Step 1: Set Up the Database and User

Connect to your MariaDB or MySQL server as a root user or a user with sufficient privileges. Create a new database and a dedicated user for Home Assistant. Using a dedicated user improves security by limiting Home Assistant's access only to its own database.

CREATE DATABASE homeassistant CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'ha_user'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON homeassistant.* TO 'ha_user'@'localhost';
FLUSH PRIVILEGES;

Replace homeassistant with your desired database name, ha_user with your preferred username, and your_password with a strong password. If Home Assistant is on a different machine than the database server, replace 'localhost' with the IP address or hostname of your Home Assistant machine, or '%' to allow connections from any host (use with caution and proper firewall rules).

Step 2: Configure Home Assistant's Recorder Integration

Edit your Home Assistant configuration.yaml file. Add or modify the recorder section to specify the new database URL.

recorder:
  db_url: mysql://ha_user:your_password@DATABASE_HOST_OR_IP/homeassistant?charset=utf8mb4
  # Optional configurations below
  # purge_keep_days: 7 # How many days of history to keep
  # exclude:
  #   domains:
  #     - switch # Exclude all switches
  #     - persistent_notification
  #   entity_globs:
  #     - sensor.processor_use_*
  #   entities:
  #     - sensor.last_boot
  #   event_types:
  #     - logbook_entry
  # include:
  #   domains:
  #     - light
  #     - cover
  #   entities:
  #     - sensor.outside_temperature

Replace ha_user, your_password, DATABASE_HOST_OR_IP, and homeassistant with your specific credentials and database server details. The ?charset=utf8mb4 is important for ensuring compatibility with various characters.

The Recorder integration supports database URLs in the format dialect://user:password@host:port/database?option=value. For MariaDB/MySQL, it's typically mysql://.

Step 3: Restart Home Assistant

Save your configuration.yaml file. Go to Developer Tools -> YAML and click 'Check Configuration' to ensure there are no errors. If valid, restart Home Assistant.

During the first startup, Home Assistant will connect to the external database, create the necessary tables, and begin storing state and event data there. The old home-assistant_v2.db SQLite file will no longer be used by the Recorder integration.

Device Integration Tips and Recorder Filtering

Once the Recorder is configured to use the external database, it will automatically start recording state changes and events for most entities by default. However, not all data is useful for long-term history, and recording everything can still bloat your database.

The Recorder configuration allows you to fine-tune what data is stored using include and exclude filters. This is crucial for performance and managing database size.

• Exclude first: It's often easier to exclude domains, entities, or glob patterns that generate excessive, unneeded data (e.g., certain sensor attributes, temporary notifications).
• Include specifically: If you have a very large number of entities, you might flip the logic and only include specific domains or entities you care about tracking long-term.
• Sensitive Data: Be mindful of accidentally recording sensitive data (e.g., specific camera states, detailed presence information) if you have strict privacy requirements and external database access.

Use the examples in the configuration snippet above to structure your include and exclude filters. After modifying these filters, you must restart Home Assistant for them to take effect.

Best Practices for Managing Your External Database

Moving to an external database adds a dependency that requires basic maintenance to ensure a reliable smart home ecosystem.

1. Regular Backups: Implement a strategy for regularly backing up your Home Assistant database. This can be done using standard database backup tools (like mysqldump) or through database management interfaces (like phpMyAdmin or Adminer). Store backups securely, preferably off-site.

2. Configure Purging: Home Assistant's Recorder automatically purges old data based on the purge_keep_days setting. Ensure this is configured appropriately for your storage capacity and how much history you need. Purging happens automatically but can be triggered manually via a service call.

3. Database Maintenance: Periodically optimize tables in your database, especially after large purges. This can reclaim space and improve query performance. Commands like OPTIMIZE TABLE tablename; can be run via your database management tool.

4. Monitor Database Performance: Keep an eye on your database server's resource usage (CPU, RAM, disk I/O). If performance degrades, you might need to optimize queries (less common for the Recorder, but possible), filter more aggressively, or scale up your database server resources.

5. Secure Your Database: Ensure your database server is properly secured. Use strong passwords, limit user privileges to only what Home Assistant needs, and configure firewalls to only allow connections from your Home Assistant instance.

Conclusion

Migrating Home Assistant's Recorder database to an external MariaDB or MySQL server is a significant step towards building a more performant and reliable smart home ecosystem, especially as your installation grows. While it adds a dependency and requires basic database management, the benefits in terms of speed, stability, and data longevity are well worth the effort. By following the setup steps, wisely filtering recorded data, and implementing basic maintenance practices, you can ensure your Home Assistant history is robust, responsive, and ready for long-term analysis.