MySQL Ring Buffer Best Practice

[LarianBretto]

Hi Everyone, I have just installed MySQL Community Server on a windows machine, I’ve created a table and I am inserting a row every second. At this stage I have not gone live so I can still change pretty much anything I need to. One of the requirements is that I retain the last 14 days of data so I believe [color=#333333]webhotelli[/color] I want to implement a ring buffer, ideally the database would look after the ring buffer itself(i.e the removal of ‘old’ rows). After the ring buffer it’s implemented I intend to replicate my database in azure so that I have both local and cloud versions

[highrise]

You could have the DB clean old data using a trigger.

If you add some ON AFTER INSERT trigger on the table, this will be fired whenever a new row is inserted. The trigger then could do a delete for the old stuff.

To identify the data you add an additional field to your scheme with a default value of CURRENT_TIMESTAMP and a modyfier ON UPDATE CURRENT_TIMESTAMP.

Be aware that this is event-driven. If nothing gets inserted, also nothing gets cleaned up.

That's the closest you can get.

If you require to absolutely have no data older than 14 days stored, you will have to use an external service that is periodically cleaning.