Overview
With the release of ThingsBoard 4.0, a major dashboard enhancement has been introduced to improve the performance and scalability of entity data queries—the new Entity Data Query Service (EDQS). This innovative service redefines how entity data is retrieved, making dashboards significantly faster while reducing the load on the SQL database.
EDQS is an optional component of the ThingsBoard platform and is recommended for environments managing hundreds of thousands of devices, where high read performance is crucial.
The Challenges with traditional query handling
In previous versions of ThingsBoard, entity data queries were executed directly against the SQL database, which led to two significant challenges:
- The complexity of SQL queries made them difficult to optimize and extend, complicating the overall data management process.
- A heavy read operation load. Dashboards generate a large number of requests, resulting in increased latency and performance bottlenecks, particularly in high-scale deployments.
EDQS approach: Storing data in memory with back up in Kafka
EDQS takes an entirely different approach by eliminating SQL from the query execution path altogether. Instead, it leverages in-memory caching to store entity data and handle queries at runtime.
Architecture diagram
Data synchronization
All entity data that may be used in dashboards is synchronized between the SQL database and EDQS using Kafka-based messaging. The Core services are responsible for publishing Entity data updates to edqs.events Kafka topic. In turn, the EDQ services consume this topic and populate the in-memory cache with the latest data.
A key feature of this architecture is its built-in backup and recovery mechanism. The latest version of entity data is backed up by publishing it to the edqs.state Kafka topic. On startup, EDQS restores its in-memory state by reading from the edqs.state Kafka topic, eliminating the need for initial database queries and enabling rapid readiness. This design significantly accelerates EDQS startup times and supports efficient rollout strategies.
Entity Data Query handling
Entity data query requests originating from dashboards are published to edqs.requests Kafka topic and consumed by one of EDQ Service. Unlike traditional approaches that rely on translating queries into SQL, EDQS bypasses the database entirely by retrieving data directly from its in-memory cache, delivering near-instant results. EDQS uses optimal data structures that significantly improve search speed in large-scale environments—typically with 10,000 or more devices.
This architecture effectively transforms EDQS into a runtime database, optimized for ultra-fast read performance.
EDQS scaling
To ensure optimal performance and reliability, EDQS supports horizontal scaling through one of two partitioning strategies: tenant-based or none.
Tenant-Based Strategy
In the tenant-based partitioning strategy, messages published by the Core service are routed to Kafka partitions based on the tenant ID. Each EDQS instance consumes a specific subset of Kafka partitions, which corresponds to a specific group of tenants.
For high availability, it is recommended to deploy at least four EDQS instances, organized into two instance sets, each containing two nodes. Within a set, both nodes consume the same Kafka partitions, ensuring redundancy and fault tolerance. This configuration provides:
- Load balancing across instance sets.
- High availability through node redundancy.
- Seamless rolling restarts without service interruption.
The none strategy is intended for single-tenant deployments. In this mode, all EDQS instances consume from all Kafka partitions. For high availability, at least two EDQS instances should be deployed.
As the number of tenants and the volume of device data grow, additional EDQS instances can be added to the cluster to maintain performance and responsiveness.
Key Benefits of EDQS
🚀 Blazing-Fast Query Performance
Since all queries are executed against an in-memory dataset, response times for complex queries are dramatically reduced compared to traditional SQL-based queries. Performance improvements can range from 2x to 100x faster, depending on the query type and dataset size.
🏋️ Zero SQL Database Load on Reads
By removing database reads, EDQS eliminates query-related stress on SQL database, improving overall system stability and performance.
Considerations Before Using EDQS
While EDQS offers significant performance improvements, there are a few important considerations:
- Memory Allocation: As all entity data is stored in RAM, the memory size of the machine running EDQS should correlate with the SQL database size. The size should be at least the total size of the device, asset, ts_kv_latest, and attribute_kv tables in the SQL database.
SELECT pg_size_pretty(SUM(pg_relation_size(quote_ident(table_name)))) AS min_size
FROM information_schema.tables
WHERE table_name IN ('device', 'asset', 'ts_kv_latest', 'attribute_kv');- Number of entities: EDQS is recommended for deployments with 10,000 or more entities, where the scale begins to challenge traditional query mechanisms.
- Number of reads: If your ThingsBoard dashboards are accessed concurrently by thousands of users — for example, in large public portals or enterprise-level monitoring setups — EDQS is an ideal solution. It is optimized for high query throughput and minimizes latency under heavy read loads.
Conclusion
The introduction of EDQS in ThingsBoard 4.0 marks a major step forward in query performance optimization. By eliminating SQL database reads and leveraging RAM-based query execution, ThingsBoard dashboards can now deliver real-time performance even under heavy loads.
For organizations looking to scale their ThingsBoard deployments while maintaining lightning-fast dashboard queries, EDQS is a must-have addition.
