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.

67 comments :

  1. I would love this command for Cassandra Database! Really useful.

    ReplyDelete

  2. Escorts 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

    ReplyDelete
  3. Attractive section of content. I simply stumbled upon your blog and in accession capital to assert that I acquire in fact enjoyed
    account your blog posts. Anyway I’ll be subscribing 풀싸롱


    on your augment or even I success you access constantly quickly.

    ReplyDelete
  4. 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. 먹튀검증

    ReplyDelete

  5. Marvelous, what a web site it is! This weblog provides helpful data to us, keep it up. Feel free to visit my website;
    야설

    ReplyDelete
  6. 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

  7. Great info. Thank you for sharing this fascinating information together. I am so blessed to discover this. Feel free to visit my website;
    일본야동

    ReplyDelete

  8. A 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:
    일본야동

    ReplyDelete
  9. 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. !!!!

    ReplyDelete
  10. Drift 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!

    ReplyDelete
  11. people 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
    Will 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

    ReplyDelete
  12. 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.

    ReplyDelete
  13. At Do My Homework we present ourselves as one of the leading educational specialist organizations in the educational market.

    ReplyDelete
  14. Thanks 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.

    ReplyDelete
  15. Hello
    In 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

    ReplyDelete
  16. Thanks for the sharing wonderful information. Keep it up.
    My Health Online Sutter

    ReplyDelete
  17. Great job on your blog! Your writing is engaging and informative, and I enjoyed reading it. Keep up the good work!

    Do 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.

    ReplyDelete
  18. That is what I was looking for, what information, present here at this site!
    DGme

    ReplyDelete
  19. Great article! I realized these are my favorite kinds of GI articles. Enough news and reviews, more stuff like this!
    My BK Experience

    ReplyDelete
  20. This cutoff concentrates nicely for us, favor your heart! Going toward an essentially indistinguishable issue here. Help is respected. PerYourHealth

    ReplyDelete
  21. I appreciate this piece of useful information,Thank You ,I also want to share information about the law
    A 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.

    ReplyDelete
  22. 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.

    ReplyDelete
  23. For this post, I learned a lot of information about this and thanks for sharing such an useful blog for us.
    anulación vs divorcio nueva jersey

    ReplyDelete
  24. Such an informative article I have ever read.Abogado Tráfico Lexington Virginia.Thanks for the innovative ideas.

    ReplyDelete
  25. Thankful to you for sharing this valuable piece of information. Keep up the good work.
    Fit out companies in Dubai | Interior fitout company in Dubai

    ReplyDelete
  26. As a proud Tata Xenon owner, I appreciate the effort you put into compiling this list of Tata Xenon Parts. It's great to have a one-stop destination for all our Xenon needs.

    ReplyDelete
  27. very interesting and good post. I was looking for this kind of unique information.
    A personal injury lawyer in Virginia is a legal professional who specializes in representing individuals who have suffered injuries or harm due to the negligence or misconduct of others.
    personal injury lawyer virginia

    ReplyDelete
  28. I couldn't discover anything in the official documentation on whether or not the telemetry and properties are destroyed after the device is deleted. However, when I examine the database after uninstalling the device, connections the destroyed device's telemetry and characteristics are still intact and have not been deleted.

    ReplyDelete
  29. Your blogs are really good and interesting. It is very great and informative. Lower are queries for the PostgreSQL database to delete telemetry and attributes Divorce Lawyers Alexandria VA. I got a lots of useful information in your blogs. Keeps sharing more useful information in your blogs..

    ReplyDelete
  30. Wow, this blog post is incredibly inspiring! I love how you've shared such valuable insights and practical tips. Your positivity is contagious, and it's clear that you're passionate about this topic. How Much is A Divorce in New York State Thank you for brightening my day with your uplifting content!

    ReplyDelete
  31. Clarke Traffic Lawyer
    The task of managing deleted entities in ThingsBoard requires clarification of the objective and the specific goal. It is important to expand on the types of timeseries data and their significance, such as historical sensor data or other time-dependent information. Specify the entities in ThingsBoard, such as devices or assets, to ensure the reader understands the scope. Elaborate on the deletion process, as this could affect how you approach managing their timeseries data. Highlight potential challenges or complexities associated with this task upfront to set the context for the reader. Consider revising and expanding the description to provide a clearer and more informative overview of the task.

    ReplyDelete

  32. "Deleting timeseries of deleted ThingsBoard entities" likely discusses the importance and process of managing and cleaning up timeseries data associated with removed or deleted entities in the ThingsBoard platform, optimizing data storage and maintenance.

    Trucking accident attorney




    ReplyDelete
  33. In ThingsBoard, managing time series data for deleted entities involves ensuring that historical data associated with those entities is also removed to maintain data integrity and optimize storage. Here are the general steps to delete time series data for deleted ThingsBoard entities.
    hampton virginia personal injury lawyers

    ReplyDelete
  34. It is a well-written and informative piece that covers a complex and important topic. The author, Anton Antonyuk, a ThingsBoard contributor, does a great job of explaining the different ways to delete timeseries of deleted ThingsBoard entities and the implications of each method.
    Bufete de Abogados de Accidentes de Semirremolques
    Liquidación de Promedio de Accidentes de Semirremolques

    ReplyDelete
  35. The "Deleting Timeseries of Deleted" tool is a powerful tool for managing timeseries data efficiently. Its user-friendly interface makes it easy for users with limited technical expertise to delete data. The tool's efficiency is impressive, as it works quickly and accurately, removing deleted data without any remnants. Users can customize the deletion process to their specific needs, whether it's for compliance or data management. Error handling is also excellent, with clear error messages and suggested solutions. However, there is room for improvement in data recovery options and a recycle bin feature. The documentation provided is comprehensive and user-friendly, and the customer support team is knowledgeable and responsive. The tool is compatible with various timeseries databases and platforms, catering to a wide range of users with diverse data storage needs. Data security is a priority, and the tool prioritizes this. More detailed information about security measures would be beneficial. The pricing for this tool is reasonable, making it a cost-effective solution for businesses of all sizes.Abogado FLSA

    ReplyDelete
  36. Amazing, Your blogs are really good and informative. 1.You need to put the list of tables from the output of the previous SELECT query, not just copy the previous VACUUM command bankrupt lawyers near me. I got a lots of useful information in your blogs. It is very great and useful to all. Keeps sharing more useful blogs..

    ReplyDelete
  37. It's very good post which I really enjoyed reading.
    "Public Speaker For Hire"

    ReplyDelete
  38. In ThingsBoard, deleting a timeseries of deleted entities typically involves removing historical data associated with entities that have been deleted from the platform. This can help maintain data integrity and reduce storage usage.
    virginia personal injury attorney

    ReplyDelete
  39. It seems like you want a review or assistance with four lines of code related to deleting time series data of deleted entities in ThingsBoard. However, you haven't provided the actual code. Please share the relevant lines of code, and I'll be happy to review and provide assistance or feedback.
    accident cement truck

    ReplyDelete
  40. To delete the time series data of deleted ThingsBoard entities, you will need to access the database where ThingsBoard stores this information. Before proceeding, make sure to back up your database to prevent data loss in case of errors during the deletion process. Identify the specific tables or data storage structures in the database that contain the time series data. This data is typically organized by entity ID or another unique identifier.
    charlottesville virginia personal injury lawyers

    ReplyDelete
  41. Access the data you want to delete by querying the database or system. Verify that the data is the correct data and that it should be removed according to your data management policies. Use the appropriate delete command in your database or system to remove the time series data. Double-check the data's removal by executing the delete command and confirming it. If the data is critical and needs to be preserved for compliance or historical reasons, back it up before deleting it. Log the deletion event, including a timestamp, the user responsible for the deletion, and the reason for deletion. Notify relevant stakeholders if necessary, depending on your organization's policies. Ensure the deleted data is securely removed and cannot be easily recovered, possibly through overwriting or adhering to data retention and privacy regulations. The specific steps and commands can vary depending on the database or system you are using. Be cautious when deleting data, especially time series data, as it may be valuable for historical analysis or auditing purposes. Always have appropriate backups and consider the potential implications of data deletion before proceeding.Abogado de Lesiones Personales Virginia

    ReplyDelete
  42. truck accident injury lawyer The article provides essential information and queries for managing data in ThingsBoard, especially when dealing with the removal of timeseries and attributes of deleted entities. It explains the importance of these queries to prevent performance degradation and disk space issues. SQL commands are well-documented for identifying and deleting unnecessary data, and VACUUM commands for PostgreSQL databases are helpful for physically removing deleted rows. The article also shares a command for finding telemetry tables that need VACUUMing and clarifies that tables may not be available during this process, suggesting performing it during downtime. Further context on efficient data management and potential risks or considerations when using VACUUM FULL ANALYZE would be beneficial.

    ReplyDelete
  43. truck accident injury lawyer The message addresses a technical issue with ThingsBoard version 2.5.4/3.1.1, which automatically deletes telemetry and attributes when entities are deleted. It provides a solution by offering specific queries for the PostgreSQL database to manually delete these attributes. The message is clear and informative, but could be improved by providing context or background information about the issue. Overall, it is helpful for users using ThingsBoard.

    ReplyDelete
  44. That is what I was looking for, what information, present here at this site! UPSers Login

    ReplyDelete
  45. The message "Deleting timeseries of deleted ThingsBoard entities" seems to be related to ThingsBoard, an open-source IoT platform for data collection, processing, and visualization. This message suggests that timeseries data associated with entities (such as devices or assets) that have been deleted in ThingsBoard is being removed.
    best personal injury attorney in virginia

    ReplyDelete
  46. Your blog post on deleting timeseries of deleted ThingsBoard entities is a lifesaver! The step-by-step guide and clear explanations make the process much more manageable. Thanks for sharing this valuable information—it's a real time-saver for those working with ThingsBoard!
    Divorcio sin Oposición en Virginia

    ReplyDelete
  47. That is what I was looking for, what information, present here at this site!
    ReceiptifyLogin

    ReplyDelete
  48. That is what I was looking for, what information DGME

    ReplyDelete
  49. Addressing performance and disk space concerns in ThingsBoard, the blog provides PostgreSQL queries to delete telemetry and attributes of previously removed entities. Emphasizing the importance of VACUUM commands for space reclamation, it guides users on executing these commands and offers essential considerations for an optimal database maintenance process.
    New Jersey Order of Protection

    ReplyDelete
  50. Instead of paying the full bail amount, which can be substantial, the accused or their family can enlist the services of a bail bondsman. San Bernardino County Bail Bonds

    ReplyDelete
  51. Amazing, Your blogs are really good and informative. I got a lots of useful information in your blogs. However, in PostgreSQL deleted rows are not physically removed from the table, they remain present until the VACUUM command is done, as described here charlottesville virginia personal injury lawyers: It is very great and useful to all. Keeps sharing more useful blogs...

    ReplyDelete
  52. Effortlessly managing and deleting timeseries of deleted ThingsBoard entities is a testament to the platform's efficiency. The seamless process ensures a clean and organized data environment, enhancing user experience and data management capabilities.||New York Divorce Residency Requirements||New York State Divorce Law Spousal Support





    ReplyDelete
  53. The question is unclear about deleting a time series or data related to a deleted time series. To delete a time series data set, all records or data points associated with that specific time series are typically removed. This can be done in a database or data storage system. Handling deleted time series data involves ensuring related metadata or references are appropriately handled, such as updating documentation, informing stakeholders, and potentially implementing data retention policies. If the question pertains to a different context or requires more specific information, additional details can be provided for better assistance.Cuerdo de Solución de Controversias de Contratos

    ReplyDelete
  54. Elevate your job application with our professional cover letter writing service Ireland. Our expert writers craft compelling cover letters tailored to showcase your skills and experience, ensuring you stand out to potential employers. Unlock opportunities and make a lasting impression with our personalized cover letter solutions.

    ReplyDelete
  55. Deleting timeseries of deleted ThingsBoard entities ensures data integrity and efficient database management. By systematically removing obsolete timeseries data associated with deleted entities, the platform optimizes storage space and maintains accurate analytics. This practice enhances system performance and streamlines data processing within the ThingsBoard framework. This blog is a goldmine of information. Your blog packs a punch in just a few sentences. Your words are like gems. Thank you for sharing this! A quick, delightful read that left me inspired.
    Mutual Consent Divorce Maryland

    ReplyDelete
  56. baltimore truck accident lawyerThe text addresses the issue of retained telemetry and attributes in ThingsBoard, providing context for the fix and explaining its impact on performance and disk space. It also provides practical PostgreSQL database queries to delete lingering data. However, there is no guidance on where users should implement these queries, and the text could include potential risks or precautions for executing them.

    ReplyDelete
  57. The question is unclear regarding the deletion or handling of time series data. The approach depends on the programming language or tool used. For instance, in Python with pandas, the `drop` method can be used to remove specific rows based on a condition. If you have a specific scenario or code snippet you need assistance with, please provide more details and I will do my best to help.garbage truck accident

    ReplyDelete
  58. SRIS Law Group offers expert legal defense with experienced sex crime attorney. Trust us to protect your rights and provide a strategic defense. Your future matters, choose SRIS for results.

    ReplyDelete
  59. Deleting timeseries of deleted ThingsBoard entities is a crucial task. Interested to learn the process and its significance in maintaining efficient data management.
    New Jersey District Court Protective Order
    Domestic Violence Cases In New Jersey

    ReplyDelete
  60. When managing a timeseries within the ThingsBoard platform, the process of deleting entities can be critical for data management and system efficiency. Deleting ThingsBoard entities, such as devices or assets, often necessitates the removal of associated timeseries data to maintain data integrity and prevent clutter. This operation ensures that the historical records tied to these deleted entities are systematically purged, preventing obsolete or irrelevant data from affecting analytics and storage efficiency.
    lawyer for motorcycle accident
    virginia personal injury lawyer

    ReplyDelete
  61. Can you write another one like this? I love your writing, Perfect. Thank you so much. ขาย ปลีก ส่ง บุหรี่ไฟฟ้า

    ReplyDelete