More often than not, we have some kind of logging table with short-lived data that should be deleted after some time, usually after a couple of hours or so.  

Moreover, the insert rate in such tables can be quite high, with thousands of new rows getting inserted every second. 

To keep our system tidy, we regularly delete old rows from such tables. 

But that’s where things get tricky. 

During that process, we are also faced with different issues to consider, some of them being: 

  • Delete is an intensive DML (Data Manipulation Language) operation, putting additional pressure on the processor, memory, and storage resources. Old rows are usually deleted continuously, making deletion a constant background task. 
  • Various relational database systems often deal with deletes in the form of soft deletes. In other words, a DELETE operation flags rows for removal, while the background process actually deletes them asynchronously. The asynchronous process is usually single-threaded, and quite often, it can’t deal with the sheer number of deleted rows in data-intensive environments. This leads to bloated storage filled with so-called “ghost records.” 
  •  For those who want to know more about this process in SQL Server, here is the MS article. 
  • Whenever we execute a delete statement, we inevitably include some kind of lock—row, range, or table—it doesn’t matter; it’s not exactly ideal in a system that thrives on speed and responsiveness. So we all love locks, right? 

Let’s now explore some smarter, more efficient alternatives. 

Partitioning intro

Partitioning is a technique for dividing a large table into smaller pieces that are more manageable than a whole table. 

While you can still refer to the original table as you usually do, partitioning can offer different options in daily work, like: 

  • efficient querying, 
  • improved data ingestion, 
  • easier execution of administrative tasks. 

One neat feature to use is the easy removal of one or more partitions instantly from the base table, using DDL (Data Definition Language) commands. 

Three key elements to set up partitioning are: 

  1. Partition function 
    This object defines the key used for partitioning. 
  2. Partition scheme 
    This object defines filegroups (groups of data) to which the aforementioned partition function will be applied. 
  3. Partitioned table 
    This table is assigned to store its data according to the partition scheme. 

Without further ado, let’s now move on to the… 

Example

Let’s shed a bit of light on partitioning with the following example: 

Starting point 

Let’s suppose that we have a logging table in our system. We want to keep just three or so hours of logged-in data in it. Everything older than that should be deleted. This table looks somewhat like this: 

CREATE TABLE [dbo].[Logs]  

(  

[Id] [BIGINT] NOT NULL IDENTITY(1, 1),   

[LogTime] DATETIME NOT NULL  

CONSTRAINT DF_Logs_LogTime DEFAULT GETUTCDATE(), /*Log time*/  

[Text] VARCHAR NULL, /*Log text*/  

CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED ([Id]) /*Primary key*/  

) ON [PRIMARY];

GO

As you can see, we haven’t introduced any partitioning there, it’s just a simple table. 

Let’s also suppose that, to keep this table neat and tidy, we introduced a continuous cleanup process that deletes stale data. 

As our ingestion process intensifies, the same happens with the cleanup process. This leads to locking, ghost records, pressure on the storage system… problems are piling up there! 

We need some help. 

Partitions to the rescue 

So, what if we started using partitioning? 

Our data could be partitioned by an hour of row insertion. That way we could easily remove partitions with stale data. 

As we’ve mentioned before, there are 3 key elements to set up in order to introduce partitioning. 

  1. First, the partition function.  

We agreed to have partitions per hour to keep just the last few hours of data. Let’s define it. 

CREATE PARTITION FUNCTION [partFnByHour](TINYINT) AS RANGE RIGHT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24); /*Define range of hours as partition function*/ 

GO

This is quite a nice use case, as our partition function is static (one day is only 24 hours). In other cases (imagine date partitioning), we often deal with dynamic functions that then introduce additional administrative overhead (like sliding window scenarios), but let’s keep it simple here. 

  1. Then, we have to create a partition scheme. 

To keep this example as simple as possible, we won’t delve into defining additional file groups and files. We will use the PRIMARY filegroup instead. 

CREATE PARTITION SCHEME [partSchLog] AS PARTITION [partFnByHour] ALL TO ([PRIMARY]); /*Assign your file group to use partition function*/ 

GO

You can define a separate file group in your partition scheme statement for every element of the partition function. The KISS principle led me to use the “ALL TO” clause. 

  1. Finally, let’s modify our table to start using partitioning. 
CREATE TABLE [dbo].[Logs]  

(  

[Id] [BIGINT] NOT NULL IDENTITY(1, 1), 

[Text] [VARCHAR](4000) NULL,           /*Log text*/ 
[LogTime] DATETIME NOT NULL 
   CONSTRAINT DF_Logs_LogTime 
       DEFAULT GETUTCDATE() /*Log time*/, 
[LogHour] [TINYINT] NOT NULL 
   CONSTRAINT [DF_Logs_LogHour] 
       DEFAULT (CONVERT([TINYINT], DATEPART(HOUR, GETUTCDATE()))) /*OUR PARTITION KEY*/ 
 
   CONSTRAINT [PK_Logs] 
   PRIMARY KEY CLUSTERED ( 
                             [Id], 
                             [LogHour] 
                         )            /*Primary key*/ 
 

) ON [partSchLog] ([LogHour]); /*Assign table to partition scheme*/ 

GO

The key is to add a new column `LogHour` that will be used for aligning data according to the values of the partition function. In other words, we divided our log table by hour. This new column has a default constraint, so there’s no need to change the way we populate the table. 

Excellent, you say, but how will this help with those ugly deletes? 

I delete without DELETE 

Well, you won’t use the DELETE command anymore to get rid of stale data. Let me introduce you to the TRUNCATE TABLE WITH PARTITION command. 

TRUNCATE TABLE WITH PARTITION is part of the DDL set of commands, unlike DELETE, which is a DML command. That means we don’t deal with actual data anymore. It removes partitions just by modifying metadata about them in the system catalog.  

This process is finished in milliseconds, whereas DELETE spends continuously tens of seconds to get rid of stale data. 

Here is an example of a cleanup query.

DECLARE @dryrun BIT = 1; /*If you want just to see executing statement, you know that to do...*/ 

DECLARE @sql NVARCHAR(MAX); 

DECLARE @hours INT = 2; 

DECLARE @dtEnd SMALLDATETIME = DATEADD(HOUR, DATEDIFF(HOUR, 0, GETUTCDATE()), 0); 

DECLARE @dtStart SMALLDATETIME = DATEADD(HOUR, -@hours, @dtEnd); 

DECLARE @partitions NVARCHAR(MAX) = N''; 

WITH src /*Hours to keep*/ 

AS (SELECT @dtStart AS dt, 

           DATEPART(HOUR, @dtStart) loghour 

    UNION ALL 

    SELECT DATEADD(HOUR, 1, src.dt) dt, 

           DATEPART(HOUR, DATEADD(HOUR, 1, src.dt)) loghour 

    FROM src 

    WHERE DATEADD(HOUR, 1, src.dt) <= @dtEnd), 

     hrs /*Hours of day*/ 

AS (SELECT CAST(loghour AS TINYINT) loghour 

    FROM 

    ( 

        VALUES 

            (0),(1),(2),(3),(4),(5),(6),(7),(8), 

            (9),(10),(11),(12),(13),(14),(15), 

            (16),(17),(18),(19),(20),(21),(22),(23) 

    ) x (loghour) ) 

SELECT @partitions = STRING_AGG($PARTITION.partFnByHour(h.loghour), ',') /*Hours to remove*/ 

FROM hrs h 

WHERE NOT EXISTS 

( 

    SELECT * FROM src s WHERE h.loghour = s.loghour 

); 

SET @sql = CONCAT(N'TRUNCATE TABLE dbo.Logs WITH (PARTITIONS (', @partitions, '))'); 

IF @dryrun = 1 

    PRINT CONCAT('CurrentDateTime = ',FORMAT(GETUTCDATE(),N'yyyy-MM-yy hh:mm:ss tt'),'; Command = ', @sql); 

ELSE 

    EXEC sys.sp_executesql @sql = @sql;

GO

As you can see, we dynamically prepare our statement by selecting appropriate partition numbers according to the time of day. By executing a single command, we can remove several hours of data in milliseconds without any data movement. 

Pros and cons 

Like a coin has two sides, there are some pros and cons to this solution. Let’s discuss them below. 

Pros

  • Reduced pressure on computing power and storage 
    While DELETE-based cleanup processes run continuously, this one can be executed only once per hour. No more locks, endless page reads, modifications, and the like. 
  • No more pain caused by soft deletes 
    This process actually gets rid of data when it is executed 
  • Applicable to most RDBMSs 
    Yes, you can set up a similar process on PostgreSQL, too. 

Cons

  • Administrative overhead 
    Before you can start using this process, you need to write some additional code and set up some objects. All indexes on a table should be aligned to the same partition function. You can use different partition schemes, but these should be created using the same partition function, which can be a bit complicated and sometimes forgotten. 
  • Quirky query optimizer 
    The query optimizer can sometimes produce a bad execution plan while dealing with a partitioned table, so I recommend you review your queries. 
  • Locks are still there 
    While you won’t cause more long-running locks, the TRUNCATE TABLE WITH PARTITIONS still needs a Schema lock to perform truncation. But its duration is minuscule compared to its DELETE equivalent. 

Summary

The concept described here is a result of several brainstorming sessions organized just to find a solution to all the pain points mentioned in the post.  

We’ve been successfully using this solution for several months now, and it works flawlessly.  

I hope this story and the examples will help you somehow in your future work!