When you need to split a large database into different sections according to usage, it’s called partitioning.
I’m not that familiar with sqlite, but a quick search shows this: SQLite database partitioning - Stack Overflow
1 Like
A post was merged into an existing topic: Disk usage discrepancy?
Never heard of that. I have no idea how this works but at least it sounds interesting. I found some explanation from a Sqlite partitioner project:
Overview | Sqlite3 Timeseries Partitioner
By partition data based on a specified interval, the number records per table will capped by the size of the interval. This means the table that contains data from three months (or even 1 hour) ago won’t need to have it’s indices restructured when you are inserting data right now.
I wonder if we could have one partition for every day and have only that specific partition in RAM?
I also don’t know if it helps, but it should have a small memory footprint then and maybe it is faster to work with overall.
I’m familiar with a database partitioning and did it many times, but for PostgreSQL. However, I didn’t know, that SQLite supports this, thanks!
Hm, in that link it doesn’t sound like it’s supported the way as I would expect, i.e. you cannot create a simple query for the one table and the engine would support it in the background across partitions using the common index.
In the case of SQLite it would be a very complicated dynamic query (the code should know about how many SQLite files there… and handle them accordingly). Not sure that it would be better than now.
It would certainly be interesting to see a separate piece expiration database per satellite. Then there would be no need to store satellite ID in every single row, making row storage, but also indices quite a bit smaller. This is partitioning going extreme, but, well, might be worth it. But there might be approaches better than using sqlite altogether.
2 Likes