Implementation method of last and last_row functions based on time series database caching mechanism

Abstract

The application discloses an implementation method of LAST and LAST_ROW functions based on a time sequence database cache mechanism, comprising the following steps: when data is updated or first queried, independently creating a last cache entry for each sub-table of the database, and saving data corresponding to the last cache entry as a last cache value to an internal memory cache; when a LAST function query request is received, comparing last_ts in the last cache entry with a lower limit of a time window specified by a query statement; if the last_ts is greater than or equal to the lower limit of the window, judging whether the last cache value exists in the internal memory cache; if the last cache value exists in the internal memory cache, directly returning the last cache value from the internal memory cache; if the last cache value does not exist in the internal memory cache, performing a lightweight scan, and traversing from a last time at a tail of a time index to a query window boundary, and reading necessary columns from other storage, but not performing a full table scan.

Description

Technical Field

[0001] This invention relates to the field of time-series database technology, specifically to an implementation method for the LAST and LAST_ROW functions based on a database caching mechanism. This method aims to protect and optimize the implementation mechanisms of the LAST function (returning the last row of a specified column as a non-nullable value) and the LAST_ROW function (returning the last row of a specified column as a value) in time-series databases (such as TDengine). By introducing an efficient caching strategy, it enables fast querying and aggregation of massive amounts of time-series data, reducing I / O overhead and improving query performance. This invention is particularly suitable for high-frequency read / write scenarios such as the Internet of Things (IoT), industrial monitoring, and financial time-series analysis. Background Technology

[0002] With the rapid development of IoT, 5G, and big data technologies, time-series data is experiencing explosive growth. Traditional relational databases face challenges such as high query latency and low storage efficiency when processing time-series data. To address this, databases specifically optimized for time-series data, such as TDengine, have emerged. TDengine employs columnar storage and a time-based indexing mechanism, supporting efficient aggregate query functions, including LAST and LAST_ROW.

[0003] The LAST function retrieves the last non-null values ​​of a specified column in a time-series table, often used for real-time monitoring of the latest status. The LAST_ROW function returns the value of the last specified column, suitable for retrieving the latest snapshot. However, in high-concurrency, multi-table partitioning (super table / sub table) scenarios, existing LAST and LAST_ROW implementations rely on full table scans or reverse traversal of timestamp indexes, causing query overhead to increase linearly with data volume, especially when the data volume reaches TB levels, resulting in significant I / O bottlenecks.

[0004] While existing technologies incorporate some index optimizations, they lack a dedicated caching mechanism for LAST / LAST_ROW, failing to fully leverage the "recentness" characteristic of time-series data (i.e., queries often focus on the most recent data). This invention addresses this pain point by proposing a memory-based caching mechanism that protects core algorithm logic, significantly reduces query response time, and prevents cache invalidation and consistency issues. Summary of the Invention

[0005] The purpose of this invention is to provide a method for implementing LAST and LAST_ROW functions based on a caching mechanism. By introducing a dedicated caching module at the query engine layer, the core implementation logic of LAST / LAST_ROW is protected, enabling efficient "zero-scan" queries.

[0006] According to a first aspect of the present invention, a method for implementing a LAST function based on a time-series database caching mechanism includes the following steps:

[0007] When updating data or performing the first query, a last cache entry is created independently for each sub-table in the database, and the data corresponding to the last cache entry is saved as the last cache value in the memory cache. The last cache entry includes at least the last timestamp last_ts and the mapping structure last_values ​​used to map the last cache value.

[0008] Upon receiving a LAST function query request, the last cache entry is read through the cache interface, and the last_ts in the last cache entry is compared with the lower bound of the time window specified in the query statement.

[0009] If the last_ts is greater than or equal to the lower bound of the window, then the existence of the last cached value in the memory cache is determined by the last_values.

[0010] If the last cached value exists in the memory cache, then the last cached value is returned directly from the memory cache;

[0011] If the last cached value is not present in the memory cache, a lightweight scan is performed, traversing backward from the last time at the end of the time index to the query window boundary, reading necessary columns from other storage, but without performing a full table scan.

[0012] Preferably, the memory cache is a high-speed memory cache LRU; the other storage includes a volatile memory buffer, a persistent memory buffer, and a time-series database.

[0013] Preferably, the query results are written to the memory cache and then synchronized to the persistent memory buffer using an asynchronous write-back strategy.

[0014] Preferably, the LAST function implementation method further includes checking the access frequency of the last cached value stored in the query memory cache in real time. If it is lower than the access frequency threshold, the last cached value is evicted from the memory cache and transferred to the persistent memory buffer.

[0015] Preferably, the LAST function implementation method further includes checking in real time whether the proportion of the last cached value stored in the memory cache exceeds a threshold. If it does, the last cached value is evicted from the memory cache and transferred to a persistent memory buffer.

[0016] Preferably, the last cache entry also includes a sub-table version number table_id; whenever new time-series data is inserted, the sub-table version number table_id is used to detect concurrent conflicts. If there is no conflict, the last_ts and last_values ​​in the cache entry are atomically updated; if there is a conflict, the current cache update is rolled back and the index scan is re-executed to refresh the cache.

[0017] According to a second aspect of the present invention, a method for implementing the LAST_ROW function based on a time-series database caching mechanism includes:

[0018] When updating data or querying for the first time, a last complete row object last_row cache entry is created independently for each sub-table of the database, and the data corresponding to the last_row cache entry is saved as the last_row cache value in the memory cache. The last_row cache entry includes at least the last timestamp last_ts and the mapping structure last_values ​​used to map the last_row cache value.

[0019] Upon receiving a LAST function query request, the last_row cache entry is read through the cache interface, and the last_ts in the last_row cache entry is compared with the lower bound of the time window specified in the query statement.

[0020] If the last_ts is greater than or equal to the lower bound of the window, then the existence of the last_row cache value in the memory cache is determined by the last_values.

[0021] If the last_row cached value exists in the memory cache, then the last_row cached value is returned directly from the memory cache;

[0022] If the last_row cache value is not present in the memory cache, a lightweight scan is performed, traversing backward from the last time at the end of the time index to the query window boundary, reading necessary columns from other storage, but without performing a full table scan.

[0023] Preferably, the memory cache is a high-speed memory cache LRU; the other storage includes a volatile memory buffer, a persistent memory buffer, and a time-series database.

[0024] Preferably, the LAST_ROW function implementation method further includes checking the access frequency of the cached value stored in the query memory cache in real time. If it is lower than the access frequency threshold, the last_row cached value is evicted from the memory cache and transferred to the persistent memory buffer.

[0025] Preferably, the LAST_ROW function implementation method further includes checking in real time whether the proportion of the cached value stored in the query memory cache exceeds a threshold. If it does, the last_row cached value is evicted from the memory cache and transferred to a persistent memory buffer.

[0026] Compared with existing technologies, this invention can reduce the query latency of LAST / LAST_ROW by more than 90%, making it suitable for high QPS (Queries Per Second) scenarios. Attached Figure Description

[0027] Figure 1 This is a flowchart illustrating the query process of the LAST and LAST_ROW functions implemented based on the time-series database caching mechanism of this invention.

[0028] Figure 2 This is a schematic diagram of the overall framework for implementing the LAST and LAST_ROW functions based on the time-series database caching mechanism of this invention. Detailed Implementation

[0029] Introduction to basic concepts:

[0030] Time-Series Database (TSDB): A database specifically designed for timestamped data, supporting efficient insertion, querying, and aggregation.

[0031] The LAST function returns the last non-null value of a time series within a specified time window. For example, `SELECT last(value) FROM table;`

[0032] The LAST_ROW function returns the value of the last row of a specified column and is often used to obtain the latest device status snapshot.

[0033] Caching mechanisms refer to maintaining copies of frequently accessed data in memory, including LRU (Least Recently Used) eviction policies and write-back / write-through modes, in order to minimize disk access.

[0034] Super Table / Sub Table: The core data model of TDengine. The super table defines the schema, and the sub table instantiates the specific data, supporting partitioned queries.

[0035] Timestamp Index: An ordered index based on the ts column, used to quickly locate the last row.

[0036] The implementation method of the LAST and LAST_ROW functions based on the database caching mechanism of this invention includes:

[0037] 1) Cache initialization: When data is updated or during the first query, a last / last_row cache entry is created for each sub-table. The cache entry structure includes: {table_id, last_ts (last timestamp), last_values ​​(LAST result mapping, key is column name, value is last value), last_row (LAST_ROW complete row object)}.

[0038] For the LAST function implementation method, when updating data or querying for the first time, a last cache entry is independently created for each sub-table in the database, and the data corresponding to the last cache entry is saved as the last cache value in the memory cache. The last cache entry includes at least the sub-table version number table_id, the last timestamp last_ts, and the mapping structure last_values ​​used to map the last cache value.

[0039] For the implementation of the LAST_ROW function, when updating data or querying for the first time, a last_row cache entry is created independently for each sub-table in the database, and the data corresponding to the last_row cache entry is saved in the memory cache as the last_row cache value. The last_row cache entry includes at least the sub-table version number table_id, the last timestamp last_ts, and the mapping structure last_values ​​used to map the last_row cache value.

[0040] 2) Query processing flow:

[0041] When receiving a LAST / LAST_ROW query, first check for a cache hit: if the cached value of last_ts is greater than or equal to the lower bound of the query time window, then return the cached value directly.

[0042] When a cache miss occurs, a "lightweight scan" is performed: traversing backward from the end of the time index to the window boundary, reading only the necessary columns (LAST) or a single row (LAST_ROW) to avoid a full table scan.

[0043] Update cache: Write the result to the cache and use an asynchronous write-back mechanism to synchronize it to the persistence layer.

[0044] For the implementation of the LAST function, upon receiving a LAST function query request, the LAST cache entry is read through the cache interface. The `last_ts` value in the LAST cache entry is compared with the lower bound of the time window specified in the query statement. If the `last_ts` value is greater than or equal to the lower bound of the window, the `last_values` value is used to determine whether a LAST cache value exists in the memory cache. If a LAST cache value exists in the memory cache, it is returned directly from the memory cache. If a LAST cache value does not exist in the memory cache, a lightweight scan is performed, traversing backward from the last time at the end of the time index to the query window boundary, reading necessary columns from other storage, but without performing a full table scan.

[0045] For the LAST_ROW function implementation, upon receiving a LAST function query request, the last_row cache entry is read through the cache interface. The last_ts in the last_row cache entry is compared with the lower bound of the time window specified in the query statement. If the last_ts is greater than or equal to the lower bound of the window, the existence of the last_row cache value in the memory cache is determined by the last_values. If the last_row cache value exists in the memory cache, it is directly returned from the memory cache. If the last_row cache value does not exist in the memory cache, a lightweight scan is performed, traversing backward from the last time at the end of the time index to the query window boundary, reading necessary columns from other storage, but without performing a full table scan.

[0046] The memory cache of this invention can adopt a high-speed memory cache LRU; other storage includes volatile memory buffers, persistent memory buffers, and time-series databases.

[0047] 3) Cache management eviction policy: Use LRU ratio combined with access frequency, and set the memory threshold to 10% of memory (configurable).

[0048] For the implementation of the last function, the access frequency of the last cached value stored in the query memory cache is checked in real time. If it is lower than the access frequency threshold, the last cached value is evicted from the memory cache and transferred to the persistent memory buffer. In addition, the proportion of the last cached value stored in the memory cache to the memory cache is checked in real time. If it exceeds the threshold, the last cached value is evicted from the memory cache and transferred to the persistent memory buffer.

[0049] For the implementation of the last_row function, the access frequency of the last_row cached value stored in the query memory cache is checked in real time. If it is lower than the access frequency threshold, the last_row cached value is evicted from the memory cache and transferred to the persistent memory buffer. In addition, the proportion of the last_row cached value stored in the memory cache to the memory cache is checked in real time. If it exceeds the threshold, the last_row cached value is evicted from the memory cache and transferred to the persistent memory buffer.

[0050] 4) Consistency guarantee of cache management: When inserting new data, last_ts and last_values ​​are updated atomically; version numbers are used to detect concurrent conflicts, and if a conflict is found, the system is rolled back and rescanned.

[0051] For the implementation of the last and last_row functions, whenever new time-series data is inserted, the sub-table version number table_id is used to detect concurrent conflicts. If there is no conflict, the last_ts and last_values ​​in the cache entry are atomically updated; if there is a conflict, the current cache update is rolled back and the index scan is re-executed to refresh the cache.

[0052] 5) System architecture for implementing the above methods: including query parser, caching module, storage engine, and consistency coordinator. The caching module is integrated with TDengine's vnodes (virtual nodes) to support distributed deployment.

[0053] The cache consists of two layers: persistent storage and in-memory LRU. When querying the last / last_row of a column in a table, it first searches in the in-memory LRU. If it exists, it is returned directly. If it does not exist, it searches in persistent storage. If it exists, the data in persistent storage is returned and written to the in-memory LRU. If it does not exist, it searches in the original TSDB data.

[0054] Figure 1 The query flow of the LAST and LAST_ROW function implementation method based on the time-series database caching mechanism of this invention is shown, including:

[0055] Step 1: Entry point – Cache query.

[0056] The business initiates a query request: retrieve the last data (last / last_row) of a certain time series, and then proceed with the entire cache read logic.

[0057] Step 2: Access the last / last_row cache and perform a judgment.

[0058] First, determine whether the LRU cache contains the last / last_row cache value.

[0059] If Yes (hit): Directly read from the LRU cache (in-memory LRU cache), return the result, and the query ends. LRU is an in-memory cache, which has the fastest read and write speed and stores the latest values ​​of frequently accessed test points.

[0060] If No (missed): Proceed to the next level of persistent cache for evaluation.

[0061] Step 3: Determine whether the last / last_row cache value is stored in the persistent cache.

[0062] If Yes (hit): Read the last / last_row cache value stored in the persistent cache and return the result. The persistent cache is slower than memory but faster than the underlying TSDB. It is used to store the latest data of test points that have been evicted by LRU but still need to be accessed. The data is not lost after the process restarts.

[0063] If No (missed): The query penetrates to the lowest level of storage.

[0064] Step 4: Underlying data source: Tsdb time series database.

[0065] If both levels of cache miss, the original time-series database Tsdb is queried directly to scan and calculate the last data in the sequence from the complete time-series data and return it. This path has the highest I / O overhead and the longest query time, and is a fallback solution.

[0066] The cache primarily interacts with three external systems: the metadata module (Meta), the query engine (QueryEngine), and the time-series data storage (TSDB).

[0067] Figure 2 This paper demonstrates the overall framework for the implementation of the LAST and LAST_ROW functions based on the time-series database caching mechanism of this invention.

[0068] like Figure 2As shown, the query engine receives user queries and serves as the entry point for all read requests, ultimately retrieving time-series data through the cache interface. The cache interface connects to the query engine, decoupling the cache layer; it also receives instructions from the metadata module. The memory cache (Cache-LRU) stores the last cached value and the last_row cached value, offering the fastest read / write speed but with limited capacity. The persistent cache stores data evicted by LRU but still requiring access; this data is not lost upon process restart, compensating for insufficient memory. The raw underlying cache (Cache-Raw) directly retrieves raw time-series data from the underlying TSDB and repopulates the cache when both of the above two cache levels fail. The metadata management module (Meta) sends control instructions to the cache interface to clear old data in the corresponding columns from the LRU and persistent caches. The virtual node write module (VNode / Write) writes time-series data to the memory buffer (mem). The Virtual Node Deletion module (VNode / Delete) is used to delete time-series data from the memory buffer (mem) for time-series sequence / data deletion operations. After deletion, it synchronously notifies the persistent memory layer (imem) to perform corresponding deletion processing. mem is used to temporarily buffer time-series data. When the mem buffer is full, the data is slacked to the persistent memory layer (imem) to prevent the loss of recently written data in case of process crashes. TSDB is a time-series database used for underlying persistent time-series storage, storing all archived time-series data; when all cache misses occur, Cache-Raw directly reads the raw data from TSDB.

[0069] The present invention has the following technical effects:

[0070] Performance improvement: When the cache is hit, the query time is reduced from O(n) to O(1), and I / O is reduced by 99%.

[0071] Resource efficiency: Low memory usage (only the last row is stored), suitable for edge device deployment.

[0072] Strong consistency: Version number + atomic update mechanism ensures ACID properties and prevents dirty reads.

[0073] Scalability: Seamlessly integrates with the TDengine distributed architecture, supporting horizontal scaling.

[0074] Economic efficiency: Reduced hardware requirements, suitable for cloud-native environments, and high ROI (Return on Investment).

[0075] Compared to existing technologies: superior to MySQL's in-memory tables (without time-series optimization) and InfluxDB's indexes (with coarse caching granularity).

[0076] Although the present invention has been described in detail above, it is not limited thereto, and those skilled in the art can make various modifications based on the principles of the present invention. Therefore, all modifications made in accordance with the principles of the present invention should be understood to fall within the protection scope of the present invention.

Claims

1. A method for implementing a LAST function based on a time-series database caching mechanism, comprising the following steps: When updating data or performing the first query, a last cache entry is created independently for each sub-table in the database, and the data corresponding to the last cache entry is saved as the last cache value in the memory cache. The last cache entry includes at least the last timestamp last_ts and the mapping structure last_values ​​used to map the last cache value. Upon receiving a LAST function query request, the last cache entry is read through the cache interface, and the last_ts in the last cache entry is compared with the lower bound of the time window specified in the query statement. If the last_ts is greater than or equal to the lower bound of the window, then the existence of the last cached value in the memory cache is determined by the last_values. If the last cached value exists in the memory cache, then the last cached value is returned directly from the memory cache; If the last cached value is not present in the memory cache, a lightweight scan is performed, traversing backward from the last time at the end of the time index to the query window boundary, reading necessary columns from other storage, but without performing a full table scan.

2. The LAST function implementation method according to claim 1, wherein, The memory cache is a high-speed memory cache LRU; the other storage includes volatile memory buffers, persistent memory buffers, and time-series databases.

3. The LAST function implementation method according to claim 2 further includes writing the query result into the memory cache and synchronizing it to the persistent memory buffer using an asynchronous write-back strategy.

4. The LAST function implementation method according to claim 2 further includes: checking the access frequency of the last cached value stored in the query memory cache in real time; if it is lower than the access frequency threshold, the last cached value is evicted from the memory cache and transferred to the persistent memory buffer.

5. The LAST function implementation method according to claim 2 or 4 further includes checking in real time whether the proportion of the last cached value stored in the memory cache exceeds a threshold. If it does, the last cached value is evicted from the memory cache and transferred to a persistent memory buffer.

6. The method for implementing the LAST function according to claim 5, wherein, The last cache entry also includes the sub-table version number table_id; whenever new time-series data is inserted, the sub-table version number table_id is used to detect concurrency conflicts. If there are no conflicts, the last_ts and last_values ​​in the cache entry are atomically updated. If a conflict occurs, roll back the current cache update and re-execute the index scan to refresh the cache.

7. A method for implementing the LAST_ROW function based on a time-series database caching mechanism, comprising: When updating data or querying for the first time, a last complete row object last_row cache entry is created independently for each sub-table of the database, and the data corresponding to the last_row cache entry is saved as the last_row cache value in the memory cache. The last_row cache entry includes at least the last timestamp last_ts and the mapping structure last_values ​​used to map the last_row cache value. Upon receiving a LAST function query request, the last_row cache entry is read through the cache interface, and the last_ts in the last_row cache entry is compared with the lower bound of the time window specified in the query statement. If the last_ts is greater than or equal to the lower bound of the window, then the existence of the last_row cache value in the memory cache is determined by the last_values. If the last_row cached value exists in the memory cache, then the last_row cached value is returned directly from the memory cache; If the last_row cache value is not present in the memory cache, a lightweight scan is performed, traversing backward from the last time at the end of the time index to the query window boundary, reading necessary columns from other storage, but without performing a full table scan.

8. The method for implementing the LAST_ROW function according to claim 7, wherein, The memory cache is a high-speed memory cache LRU; the other storage includes volatile memory buffers, persistent memory buffers, and time-series databases.

9. The method for implementing the LAST_ROW function according to claim 7 further includes: checking the access frequency of the last_row cached value stored in the query memory cache in real time; if it is lower than the access frequency threshold, the last_row cached value is evicted from the memory cache and transferred to the persistent memory buffer.

10. The method for implementing the LAST_ROW function according to claim 7 or 9 further includes checking in real time whether the proportion of the last_row cached value stored in the query memory cache exceeds a threshold. If it does, the last_row cached value is evicted from the memory cache and transferred to a persistent memory buffer.

Images