search

smarthomeNG / plugins

Plugins for SmartHomeNG - The device integration platform for your smart home
https://www.smarthomeNG.de
45 stars 100 forks source link

database: Provide pack mechanism to keep database small #566

Open msinn opened 2 years ago

msinn commented 2 years ago

This issue is moved over from the core repo. The complete issue can be read over there: https://github.com/smarthomeNG/smarthome/issues/216

The sqlite plugin have a pack mechanism which keeps the database small (like RRD files does). The database plugin does not have this mechanism and keeps all the values in the database.

To be able to have small databases (e.g. for embedded systems and maybe completely replace sqlite plugin) the database plugin should also have such a mechanism.

Different ideas exist

Reduce by aggregation: aggregate and pack values for different timespans to different details Reduce by dumping/exporting: automatic export/dump function which exports data (e.g. into CSV files) and removes them > from database after export completed Reduce by moving to history tables: make use of history tables (e.g. move everything older than one year into a log_YYYY table) Reduce by removing "duplicates": duplicated values, eg same value in multiple records can be aggregated to one record Reduce by remvoing: Simply delete data older the given amount of time Reduce by max. amount of records: Only given amountof records will be kept in database, other ones will be removed Reduce by kepping database size: calculate usage of each item and remove given percentage frkm all of them Reduce by replacing details with inaccurates: replace details by calculating an inaccurate value for a set of values (e.g. only store changes for temperature without changes in floating point) ... other ideas? Can be configured:

Globally in plugin configuration On a per item basis ... other ideas

(leftover from #165)

msinn commented 11 months ago

Would the automatic packing be of value?

If setting maxage for the first time, I see a real value in packing the Database.

When deleting database entries periodically without packing, the space within the database is reused by new records. This would result in a database that just does not grow.

Packing after deleting records would result in a smaller database only for a small amount of time, since new records make the database grow again.