Moebius Strip

Table Pattern: Rotating Log / Ring Buffer

Most database systems need some form of log table to keep track of events, for example for auditing purposes. To avoid the log growing forever, it is often a good idea to regularly rotate old log entries out of this table. For small log tables, running a DELETE statement works well for this purpose. However, as the log throughput grows, it is often preferable to use partition switching instead. In this blog, I will show you an implementation of a rotating log table.

The Table Structure

A rotating log is essentially a ring buffer where the writes happen at one part of the buffer and the old records are removed at the other end. For the implementation example, we will assume you wish to rotate the log daily, removing and aggregating old entries and making sure that the new entries are added to a place that is easy to rotate in the future.

It is possible to traditional partition “sliding window” techniques and add new partitions daily to achieve this. However, I find the splitting, merging and switching a bit clunky for this purpose. Instead, I prefer to implement a rotating log like this:

 

With this design, I never have to alter the partition function or scheme, I can simply switch out the old parts of the ring buffer.

For the purposes of this implementation, let me add a few more tables:

The RingBuffer_SwitchTarget will be used to switch out the old entries in RingBuffer. The RingBuffer_Aggregate is used to keep an aggregate of old data, which of course will be much smaller.

To illustrate, here is the flow we are implementing:

Ring Buffer Implementation

 

Rotating the Log

With the table structures in place, we can now write the procedure that rotates the log. Using a few simple math tricks, we can rotate and the log with a simple piece of code:

And that’s really all there is to it.

Summary

In this blog, I have shown you how to implement a rotating log without the need for a lot of partition function management. Using a ring buffer of dates, a full day of data can be rotated around with a single SWITCH statement.

The ring buffer mechanism can also be used for heaps or tables with only a cluster index that are so large that online index rebuilds don’t fit in memory and tempdb. Instead of rebuilding the entire table, each entry in the ring buffer can be rebuilt, one at a time, in a rotating manner. It is trivial to modify the above stored procedure to implement this functionality.

 

  8Comments

  1. Patrik   •  

    Hello
    I know it’s an older topic, but I’m very interested in this solution and wondered if there would be a convenient way to change the number of partitions at a later stage (say I only need logs of the last 14 day but might want to extend it to 30 later on). I have about 15 to 20 million rows per day, so copying of data should be avoided…
    Adding partitions is the easy part – just change the partition function, but I couldn’t find out how I can change the calculated column (I would have to change the modulus to 30).
    Is this a drawback of the ‘rotating ring buffer’ pattern in contrast to the ‘sliding window’ approach?
    Thanks & cheers
    Patrik

    • Thomas Kejser   •     Author

      Hi Patrik. Not readily. If you need to do that, I think the easiest is to create a new table with the desired partitioning strategy and move the data

  2. Jason Triplett   •  

    After looking at the partition stats after creating test data, I see that the 0 partition doesn’t get any use, and the last partition gets double usage.

    I suggest that the partition function be 1 through 13 in this case.

    • John   •  

      +1

      • John   •  

        actually, the mod of the Offset should be 13 instead of 14:
        Offset AS CAST(DATEDIFF(day, 0, EventTime) % 14 AS TINYINT) PERSISTED

        • Thomas Kejser   •     Author

          Good catch! You are right. Fixed! Thanks for finding it

  3. mark   •  

    Hi,
    Is “DECLARE @P INT = (@CurrentPartition + 1) % @BufferSize” mean “DECLARE @P INT = (@OldestPartitionToKeep + 1) % @BufferSize”?

    • Thomas Kejser   •     Author

      Hi Mark

      No, the code as it is there works. Because this is a ring buffer, it does not matter if we start from the one “ahead of us” and move clockwise in the ring, or start from the oldest and move counter clockwise.

Leave a Reply

Your email address will not be published. Required fields are marked *