If you have used Home Assistant it is very likely that you also glanced at the "History"-function of the GUI. I really like this view, because it shows you relevant data in a simple way and it is very easy to find basic connections between elements. So I would like to use this feature also in the future. So you might ask yourself "Where is the problem?". If we have a look at the documentation of Home Assistant you will notice that the default database is a sqlite3-database.

Points against sqlite3

In my eyes this implementation does not fit the real needs because:

1. No real date/time-support.
   Most of the data we are interested in is related to timestamps. Because of this lack of functionality Home Assistant implemented a workaround for their functions. They are working with the seconds since UNIX epoch.
2. Made for lightweight applications. It is very rare that sqlite3 is used for data-intense tasks. Normally it is used if you are looking for a simple structured database and do not want to set up a whole server for this task. The best argument for sqlite3 against csv and xml is that you have a basic query language and do not need to reimplement everything.
3. Replication is limited to copy-jobs.
4. Limited query functionality. There are no modern functions like window-functions or something like that. So if you want to do basic statistics you have to implement functions for it.
5. Last but not least: The system which should be operating gets messed.
   1. Switch to your user for Home Assistant.
      sudo su -s /bin/bash homeassistant
   2. Check the filesize.
      -l --block-size=M ~/.homeassistant/
      The output should contain a line like this:
      -rw-r--r-- 1 homeassistant nogroup 206M Mai 1 20:50 home-assistant_v2.db
      So as you can see my instance of Home Assistant logged 206 Megabytes of data in about 45 days.

So do not get me wrong! I really love sqlite3, but I think it is not the right way to store data for an infinite time.

What's the way to go?

The solution is very simple: You just have to activate the "Recorder"-Component. The one and only line you need to add to your configuration is the connectionstring. This string has to be added for the key "db_url". You can choose between different databases like MySQL and PostgreSQL, but there is also the option to use another sqlite3 file! To be able to use one of the first two options you need to install their client-implementations. But watch out to activate the virtualenv (if you used it during the installation process of Home Assistant) before you start installing the dependencies.

Further optimization

You can work with includes or excludes if you want to limit the data which will be stored in the database. It is very important that you use this options within the recorder. If you type it under the history or the logbook entry, everything will be recorded as it has been. The only effect is that limited data will be displayed.

Another possibility is to purge the data. This means that the datasets get a lifetime. If they are older than a specified time range they will be deleted. You can also use this function with the default database. You only need to modify the recoder-section your configuration file like here: (Data will be stored for only one week.)

recorder:
    purge_days: 7