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.