Skip to content

Instantly share code, notes, and snippets.

@elyezer
Last active May 16, 2024 03:38
Show Gist options
  • Save elyezer/6450054 to your computer and use it in GitHub Desktop.
Save elyezer/6450054 to your computer and use it in GitHub Desktop.
How to create a ring buffer table in SQLite
-- Example table
CREATE TABLE ring_buffer (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
-- Number 10 on where statement defines the ring buffer's size
CREATE TRIGGER delete_tail AFTER INSERT ON ring_buffer
BEGIN
DELETE FROM ring_buffer WHERE id%10=NEW.id%10 AND id!=NEW.id;
END;
@srgian
Copy link

srgian commented Feb 12, 2021

this approach not suitable for large tables. id%10 predicate from the where clause would require computing over table scan. I would suggest delete from ring_buffer where id < NEW.id-[some_buffer_capacity] so NEW.id and buffer capacity are scalars and id is indexed

@dsidirop
Copy link

dsidirop commented Jun 23, 2021

Based on srgian's insight a more accurate approach would be:

       -- Example table
       CREATE TABLE ring_buffer (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
       
       -- Number 10 on where statement defines the ring buffer's size
       CREATE TRIGGER delete_tail AFTER INSERT ON ring_buffer
       BEGIN
           DELETE FROM ring_buffer where id < NEW.id-10000;
       END;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment