SQLite Forum

How to implement a table working like a ring buffer
While SQL can do a lot of things, it may be simpler to do it in code.  Do you need a fixed size, or just an "approximately fixed" size?  Lets say you *want* 100k rows, and are willing to have the count fluctuate between 100k and 101k rows.

In whatever function inserts into this table, keep a static temp_count variable that counts the # of inserts since the last purge.  Once temp_count exceeds 1000, run a DELETE statement in the same transaction with your desired filter and order logic and then clear temp_count when you commit.  You're guaranteed that the size will never exceed 101k.  This allows you to amortize the delete statement unless that unevenness in the speed of the operations is an issue.