{"id":4275,"date":"2023-05-25T13:42:43","date_gmt":"2023-05-25T11:42:43","guid":{"rendered":"https:\/\/www.sigalou-domotique.fr\/?p=4275"},"modified":"2026-01-02T17:01:37","modified_gmt":"2026-01-02T15:01:37","slug":"je-mets-ma-base-de-donnees-ha-au-regime","status":"publish","type":"post","link":"https:\/\/www.sigalou-domotique.fr\/en\/je-mets-ma-base-de-donnees-ha-au-regime","title":{"rendered":"I put my HA database on the diet"},"content":{"rendered":"<p>As mentioned in the retex\u00a0<\/p>\r\n<ul>\r\n<li><a href=\"https:\/\/www.sigalou-domotique.fr\/en\/verifier-la-taille-de-sa-base-de-donnees\/\" target=\"_blank\" rel=\"noopener\">I check the size of my database<\/a><\/li>\r\n<\/ul>\r\n<p>It is surprising how quickly the database fills up.<\/p>\r\n<p>In this retex, I will try to identify the entities that register the most, check if their registration is useful and configure HA recorder to limit the size of the base.<!--more--><\/p>\r\n<h3>I identify the entities with the most registrations<\/h3>\r\n<p>I'm going to connect to the SSH command line and I'm going to query the database<\/p>\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">cd ~\/config<\/pre>\r\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">sqlite3 home-assistant_v2.db .header on .mode column .width 50, 10, SELECT states_meta.entity_id, COUNT(*) AS count FROM states JOIN states_meta ON states.metadata_id = states_meta.metadata _id GROUP BY states_meta.entity _id ORDER BY count DESC LIMIT 20;<\/pre>\r\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4279\" src=\"https:\/\/www.sigalou-domotique.fr\/wp-content\/uploads\/2023\/05\/bd1.png\" alt=\"\" width=\"533\" height=\"158\" srcset=\"https:\/\/www.sigalou-domotique.fr\/wp-content\/uploads\/2023\/05\/bd1.png 533w, https:\/\/www.sigalou-domotique.fr\/wp-content\/uploads\/2023\/05\/bd1-300x89.png 300w\" sizes=\"auto, (max-width: 533px) 100vw, 533px\" \/><\/p>\r\n<p>This command displays the 20 entities with the most records (therefore the most database size consuming entities).<\/p>\r\n<p>By changing LIMIT 20 to LIMIT 200 I have the 200 entities with the most registrations\u00a0<\/p>\r\n<h3>I configure recorder<\/h3>\r\n<ul>\r\n<li><a href=\"https:\/\/www.home-assistant.io\/integrations\/recorder\/\" target=\"_blank\" rel=\"noopener\">Documentation<\/a><\/li>\r\n<\/ul>\r\n<p>I created a file <a href=\"https:\/\/github.com\/sigalou\/home-assistant-config-fr\/blob\/main\/recorder.yaml\" target=\"_blank\" rel=\"noopener\">recorder.yaml<\/a> without forgetting to specify it in <a href=\"https:\/\/github.com\/sigalou\/home-assistant-config-fr\/blob\/main\/configuration.yaml\" target=\"_blank\" rel=\"noopener\">configuration.yaml<\/a><\/p>\r\n<p>This file allows you to include or exclude everything I want to save (or not)<\/p>\r\n<p>Being constantly evolving, I do not put here a copy of the code, but the file can be consulted on the <a href=\"https:\/\/github.com\/sigalou\/home-assistant-config-fr\" target=\"_blank\" rel=\"noopener\">GitHub<\/a>.<\/p>\r\n<p>By selecting what I want to save, I can control the size of the database.<\/p>\r\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4089\" src=\"https:\/\/www.sigalou-domotique.fr\/wp-content\/uploads\/2023\/05\/sql3.png\" alt=\"\" width=\"381\" height=\"148\" srcset=\"https:\/\/www.sigalou-domotique.fr\/wp-content\/uploads\/2023\/05\/sql3.png 381w, https:\/\/www.sigalou-domotique.fr\/wp-content\/uploads\/2023\/05\/sql3-300x117.png 300w\" sizes=\"auto, (max-width: 381px) 100vw, 381px\" \/><\/p>","protected":false},"excerpt":{"rendered":"<p>As mentioned in the retex I check the size of my database it is surprising to see the speed at which the database fills up. In this retex, I will try to identify the entities that register the most, check if their registration is useful and configure recorder of HA to limit [...]<\/p>","protected":false},"author":1,"featured_media":4089,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[14,53,166],"tags":[81],"class_list":["post-4275","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-domotique","category-home-assistant","category-retex","tag-developpement"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sigalou-domotique.fr\/en\/wp-json\/wp\/v2\/posts\/4275","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sigalou-domotique.fr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sigalou-domotique.fr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sigalou-domotique.fr\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sigalou-domotique.fr\/en\/wp-json\/wp\/v2\/comments?post=4275"}],"version-history":[{"count":0,"href":"https:\/\/www.sigalou-domotique.fr\/en\/wp-json\/wp\/v2\/posts\/4275\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sigalou-domotique.fr\/en\/wp-json\/wp\/v2\/media\/4089"}],"wp:attachment":[{"href":"https:\/\/www.sigalou-domotique.fr\/en\/wp-json\/wp\/v2\/media?parent=4275"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigalou-domotique.fr\/en\/wp-json\/wp\/v2\/categories?post=4275"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigalou-domotique.fr\/en\/wp-json\/wp\/v2\/tags?post=4275"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}