How to create a ring buffer table in SQLite

ring_buffer.sql

-- 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;

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

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;