Where is the best place to store the global circular buffer of site visits (visitors)?

Question

MySQL

I understand that premature optimization is the cause of many grievances, but nevertheless. Let's say you need to store a global circular buffer of site page visits somewhere. regardless of sessions. Options:
For example (1), just in a file. Type of a cyclic broad gull. When accessing the page, we read the file once, process it (ourselves or with libraries) and write it to the same file. There are two file operations: one for reading, the second for writing.
You can do the same with the base (2). Create a table and add a record to it every time the page is accessed, and delete the oldest one. Minus - garbage accumulates in the database (deleted records are actually still there, and in order for the database not to "swell" in vain, it will sometimes have to be cleaned.
A more perverse way is to store the circular buffer as a file, but in base(3). Start a line with one field fatter, and in it, as in the file of option 1, we add everything. One SELECT, one UPDATE.
It is clear that in cases 1 and 3 some resource will be devoured by parsing this circular buffer. And this time will help to save option 2. But, let's say that this time can be neglected. Which option is more preferable / more correct / more common ?? Maybe there are other options? The buffer is small, the load is moderate, I don’t want to block all sorts of external queue services.

Answer 1

You don't need to delete anything.
One special entry (in a separate table) will store the number (primary key) of the last entry in the log.
Adding a new entry to the log takes exactly one UPDATE (the primary key is retrieved by a subquery), after which the number of the last entry is incremented and the remainder is taken modulo equal to the number of log entries in the rotation. If the DBMS allows, then it would be ideal to do this on the update trigger to the log table, then these updates from the client side become completely atomic, they no longer need to be wrapped in transactions, they can be performed in any order, in a delayed mode (without waiting for a server response) if the database driver allows this (this can greatly increase performance).
As for the file: unless you are an expert in the various modes of fsync and disk transaction atomicity, then I do not advise you to frequently overwrite a file with valuable data. Many DBMS developers got burned on this, not to mention applied coders. (when it seems that the sequence of disk operations simply does not allow the base to remain in an invalid state, but still, after the fall, the base somehow ends up in an invalid state).

Answer 2

Files are definitely not suitable here, because they do not support atomic insertion / deletion of lines. You will have to completely lock the file for reading / writing, otherwise version conflicts will occur and some information will be lost. The same is true for blob records in the database.
So the best option is a standard database table with cleaning by cron/trigger of the database itself.

Answer 3

Has anyone mentioned redis.io yet ? Then I'm the first - https://redislabs.com/ebook/redis-in-action/part-2...
Attach to django - 5 minutes.

Answer 4

Why such an emphasis on cyclicality?
Store anywhere, "cyclicality" is added by regular cleaning of the file / database by cron.
Or, at the code level, select the desired period from the database.
Depends on what you need to store and for what purpose.