Hi everyone,
I would like to share some recent experiments integrating SQLite with TsFile, and invite anyone interested to join the discussion, development, and maintenance. As a time-series data file format, TsFile can serve a variety of data collection and storage scenarios. In conversations with users, I have encountered requests to store TsFile data directly on embedded devices. These devices often have tight constraints on storage space, memory, and runtime overhead, which presents an opportunity to benefit from TsFile's compression and storage efficiency. However, using TsFile Writer directly on a device also raises questions about the durability of recent data. Some records remain temporarily in memory while data is organized and written in batches. If the device suddenly loses power or the process terminates unexpectedly, records that have not yet been persisted may be lost. This is particularly relevant to fault diagnosis: the last records before a failure may be exactly what is needed to understand its cause. This concern has made some users hesitant to use TsFile directly on edge devices. To explore this, I have implemented a hybrid storage mechanism combining SQLite and TsFile. SQLite handles recent writes, using its transaction and journaling mechanisms to manage persistence while allowing recent rows to be updated or deleted. Once data no longer needs to be modified, it can be sealed into TsFile to take advantage of its compression and historical data storage capabilities. Sealing is also performed through SQL within SQLite. The extension handles the operation internally, and it participates in SQLite's transaction management. Applications work with the same logical table throughout, querying recent data in SQLite and historical data in TsFile together without having to combine the two sets of results themselves. Sealed rows remain queryable but can no longer be updated or deleted. The current implementation supports three ways to use it: - **Start writing to an empty table:** Declare the columns and a data directory. New rows initially enter SQLite's hot data area and can be sealed as needed. - **Continue writing from an existing TsFile:** Specify the source file, its internal table name, and a new data directory. The schema is inferred from the file. The original file remains unchanged, and new rows with timestamps later than the source table's maximum timestamp enter the hot data area. - **Query an existing TsFile directly:** Specify the source file and its internal table name without providing a data directory. This creates a read-only logical table that can be queried using SQL. The extension also supports exporting the complete contents of a table through SQL. Export automatically seals the current hot data and then produces an independent TsFile copy. Users do not need to seal the data manually beforehand. The goal of this experiment is to combine the strengths of both systems: SQLite for transactional reads and writes in embedded environments, and TsFile for time-series compression and historical data storage. We hope this combination can make TsFile easier to adopt on edge devices and extend the range of scenarios it can serve. The code for this integration experiment is now available on the [experiment-sqlite branch](https://github.com/apache/tsfile/tree/experiment-sqlite) of the Apache TsFile repository. It includes the implementation, tests, and user manuals in Chinese and English. Please refer to the [Chinese user manual](https://github.com/apache/tsfile/blob/experiment-sqlite/cpp/src/sqlite/USER_GUIDE.md) or the [English user manual](https://github.com/apache/tsfile/blob/experiment-sqlite/cpp/src/sqlite/USER_GUIDE_EN.md) for instructions on building and trying it out. Next, I plan to prepare this functionality for inclusion in `tsfile-extras`, continuing to explore how SQLite and TsFile can work together in embedded and edge environments. Feedback and use cases are welcome, as are contributions to development and maintenance. If you have relevant devices, applications, or real-world data, I would be glad to work together to evaluate the approach and improve its reliability, performance, and usability. Thank you! Colin
