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.
I would love this command for Cassandra Database! Really useful.
ReplyDelete
ReplyDeleteEscorts Service in Connaught Place, here are incredibly evolved and they appreciate the customer’s needs since this city has every sort of individual and the fight is there for everyone. Our escort knows this reality, so they give you 100% satisfaction guaranteed. We Provide bestCall Girls in Vasant Kunj that will you never forget and come again and again. Our Agency maintain the quality and an immense collection which contains a range of categories of escort girls.
Russian Escorts in Delhi
Foreigner Call Girls in Gurgaon
Housewife Escorts in Mahipalpur
Call Girls in Mahipalpur
Attractive section of content. I simply stumbled upon your blog and in accession capital to assert that I acquire in fact enjoyed
ReplyDeleteaccount your blog posts. Anyway I’ll be subscribing ķģøė”±
on your augment or even I success you access constantly quickly.
It has fully emerged to crown Singapore's southern shores and undoubtedly placed her on the global map of residential landmarks. I still scored the more points than I ever have in a season for GS. I think you would be hard pressed to find somebody with the same consistency I have had over the years so I am happy with that. ėعķź²ģ¦
ReplyDeleteThanks for your post. The article is neatly organized with the information I want, so there are many things to refer to. Bookmark this site and visit often in the future. Thanks again.^^ keonhacai
ReplyDelete192.168.1.1 is a private IP address used to login the admin panel of a router. 192.168.l.l is pre-specified by the router companies as the default gateway address and can be used to make different changes to the router’s settings. 192.168.0.1
ReplyDelete
ReplyDeleteMarvelous, what a web site it is! This weblog provides helpful data to us, keep it up. Feel free to visit my website;
ģ¼ģ¤
I was searching some blogs to read on google and found this blog post page. I must say it is very informative as well as interesting. Thanks to the author of this post/page for writing such wonderful lines. Feel free to visit my website; ķźµģ¼ė
ReplyDelete
ReplyDeleteGreat info. Thank you for sharing this fascinating information together. I am so blessed to discover this. Feel free to visit my website;
ģ¼ė³øģ¼ė
ReplyDeleteA very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post Also visit my website:
ģ¼ė³øģ¼ė
Thanks for sharing this.,
ReplyDeleteagile coach certification online
icp acc
I trully appretiate your work and tips given by you is helpful to me. I will share this information with my family & friends. This is a great website, keep the positive reviews coming. This is a great inspiring .pay to write my assignment I am pretty much pleased with your good work. You put really very helpful information. I am looking to reading your next post. !!!!
ReplyDeleteDrift Hunters game is a racing game with unllimited money. You can free to buy all the items on the shop. This game will be easy for you. Enjoy the game!
ReplyDeletepeople genuinely need to take a gander at this and understand this side of your story. It's surprising you're not more standard since you undeniably have the gift Thanks for bestowing this best stuff to us! Keep on sharing! I'm new in blog writing.All types composes and posts are not helpful for the readers.Here the author is giving worthy insights and thoughts to each and every perusers through this article. Quality of the substance is the essential help with theory uk segment of the blog and this is the technique for yellowstone blue coat forming and presenting. It was an amazing post without a doubt. I totally liked scrutinizing it in my early afternoon. coat
ReplyDeleteWill come and visit this blog even more consistently. Thankful for sharing. There are such endless fun and empowering exercises and experiences all through the planet that I thought I 'd set up a summary of most adored exercises
Some points in this blog you have mentioned is really helpful and useful. Keep posting are going to be expecting your next blog. Now it's time to avail Locksmith Leeds for more information.
ReplyDeleteAt Do My Homework we present ourselves as one of the leading educational specialist organizations in the educational market.
ReplyDeleteThanks for the informative post. Students who are interested in Software Developing and are looking for UK Student Visa Consultant In Pakistan can also get an idea from your content. Though the content might be complex for the beginners but those students who have a know how of software and how coding works will comprehend a little information from it.
ReplyDeleteThanks for posting.. Krowd Darden
ReplyDeleteHello
ReplyDeleteIn ThingsBoard, when an entity (e.g. device, asset, customer, etc.) is deleted, all of its related data is marked as deleted but not immediately removed from the database. This is because there might still be references to this data from other parts of the system or from backups.
To delete the time series data of a deleted entity, you can follow these steps:
Find the ID of the deleted entity. You can do this by going to the "Deleted Entities" section in the ThingsBoard UI and searching for the entity by name or ID.
Once you have the ID of the deleted entity, you can use the ThingsBoard REST API to delete its time series data. Here is an example curl command:
rust
Copy code
curl -X DELETE 'http://localhost:8080/api/plugins/telemetry/DELETED_ENTITY_TYPE/DELETED_ENTITY_ID/timeseries/delete?deleteAllData=true' \
-H 'Accept: application/json' \
-H 'X-Authorization: Bearer YOUR_AUTH_TOKEN'
Replace DELETED_ENTITY_TYPE and DELETED_ENTITY_ID with the type and ID of the deleted entity, respectively. Replace YOUR_AUTH_TOKEN with a valid ThingsBoard JWT token.
The deleteAllData=true parameter is optional and specifies that all data should be deleted, including the data that was marked as deleted but not yet removed.
After running the curl command, the time series data of the deleted entity should be deleted from the database. You can verify this by checking the "Data Explorer" section in the ThingsBoard UI.
I think this will help you more.
Dinar Updates
Thanks for the sharing wonderful information. Keep it up.
ReplyDeleteMy Health Online Sutter
Great job on your blog! Your writing is engaging and informative, and I enjoyed reading it. Keep up the good work!
ReplyDeleteDo My Homework is a popular academic company that offers a wide range of homework help services to students who want to achieve academic success. With its team of experienced and highly qualified tutors, the company has established itself as a reliable and efficient resource for students seeking homework assistance.
One of the unique features of Do My Homework is its personalized approach to homework help. The company understands that every student has unique learning needs and styles, and therefore, it tailors its services to meet the specific requirements of each student. This personalized approach ensures that students receive the help they need to succeed in their studies and improve their grades.
That is what I was looking for, what information, present here at this site!
ReplyDeleteDGme
Great article! I realized these are my favorite kinds of GI articles. Enough news and reviews, more stuff like this!
ReplyDeleteMy BK Experience
This cutoff concentrates nicely for us, favor your heart! Going toward an essentially indistinguishable issue here. Help is respected. PerYourHealth
ReplyDeleteThanks for sharing this post. I got information on this post. Keep sharing.
ReplyDeletebankrupt lawyers near me
I appreciate this piece of useful information,Thank You ,I also want to share information about the law
ReplyDeleteA Traffic Lawyer Spotsylvania VAis a legal professional who specializes in defending individuals charged with traffic violations in Spotsylvania County, Virginia. Traffic violations can include speeding, reckless driving, driving under the influence (DUI), driving with a suspended license, and other related offenses.
According to Pay Someone To Take My Class Online you can either use a database query or the Administration REST API to erase the device properties or telemetry data.
ReplyDeleteFor this post, I learned a lot of information about this and thanks for sharing such an useful blog for us.
ReplyDeleteanulaciĆ³n vs divorcio nueva jersey