Open-source IoT Platform

Device management, data collection, processing and visualization
for your IoT projects
Learn about Thingsboard

Wednesday, September 16, 2020

Deleting timeseries of deleted ThingsBoard entities

    As of ThingsBoard v.2.5.4/3.1.1, telemetry and attributes of entities are not automatically deleted when entities themselves are deleted, which causes performance degradation (because of decreasing query speed) and filling up of the disk space . Lower are queries for the PostgreSQL database to delete telemetry and attributes.

    Deleting timeseries of previously deleted entities:


 DELETE * FROM ts_kv WHERE entity_id NOT IN (SELECT id FROM device) AND entity_id NOT IN (SELECT id FROM asset) AND entity_id NOT IN (SELECT id FROM customer) AND entity_id NOT IN (SELECT id FROM tenant) AND entity_id NOT IN (SELECT id FROM entity_group) AND entity_id NOT IN (SELECT id FROM integration) AND entity_id NOT IN (SELECT id FROM converter) AND entity_id NOT IN (SELECT id FROM entity_view) AND entity_id NOT IN (SELECT id FROM tb_user) AND entity_id NOT IN (SELECT id FROM dashboard) AND entity_id NOT IN (SELECT id FROM rule_chain) AND entity_id NOT IN (SELECT id FROM role); 

    Deleting attributes of previously deleted entities:


 DELETE * FROM attribute_kv WHERE entity_id NOT IN (SELECT id FROM device) AND entity_id NOT IN (SELECT id FROM asset) AND entity_id NOT IN (SELECT id FROM customer) AND entity_id NOT IN (SELECT id FROM tenant) AND entity_id NOT IN (SELECT id FROM entity_group) AND entity_id NOT IN (SELECT id FROM integration) AND entity_id NOT IN (SELECT id FROM converter) AND entity_id NOT IN (SELECT id FROM entity_view) AND entity_id NOT IN (SELECT id FROM tb_user) AND entity_id NOT IN (SELECT id FROM dashboard) AND entity_id NOT IN (SELECT id FROM rule_chain) AND entity_id NOT IN (SELECT id FROM role); 

    However, in PostgreSQL deleted rows are not physically removed from the table, they remain present until the VACUUM command is done, as described here: https://www.postgresql.org/docs/11/sql-vacuum.html. In order to reclaim the disk space VACUUM commands with ANALYZE parameter (for statistics refreshing) needs to be executed:


 VACUUM ANALYZE attribute_kv; 

    And if reclaiming space is critical, the parameter FULL should be added (please note that it requires as much free disk space as the table currently takes up):


 VACUUM FULL ANALYZE attribute_kv; 

    In order to find the list of telemetry tables to be VACUUM’ed, the following command needs to be executed:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name like 'ts_kv%' ORDER BY table_name; 

    Then the resulting list of tables is put in the VACUUM ANALYZE command:

    In this example the command looks like this:

VACUUM FULL ANALYZE ts_kv, ts_kv_2004_01, ts_kv_2020_08, ts_kv_2020_09, ts_kv_dictionary, ts_kv_indefinite, ts_kv_latest; 

    Please be aware that:

1.You need to put the list of tables from the output of the previous SELECT query, not just copy the previous VACUUM command.

2.Tables are not available when you perform a VACUUM command (all incoming read and write queries are put in a queue), so the best solution would be to perform it in the (at least relative) downtime.



Your feedback

If you found this article interesting, please leave your feedback in the comments section, post questions or feature requests on the forum and “star” our project on the github in order to stay tuned for new releases and tutorials.

No comments :

Post a Comment