Laravel: Keeping a limited number of records in the database

A neat trick that won’t make your database sweat

Photo by alexandru vicol on Unsplash

One of the problems I’ve found out every time I require to save some records in the database has been limiting a particular group of records by a given amount or window of time.

Case in point, the application I have saves the last five updates done to the Post model as Drafts, cutting the list to the last two weeks. In other words, the sixth draft deletes the last one, and drafts older than fourteen days are never shown.

This trick is not a novelty. It’s something I had in the vault from (a very) long time ago, but I thought I would share anyway.

Removing leftovers at almost no cost

The way to remove records from the table requires a query with a subquery. It can be resumed in the following sentence:

Remove all drafts that are not (find all drafts I want to keep).

The SQL Query would be like this:

DELETE from drafts 
  -- Delete only post from a given parent post
  WHERE drafts.post_id = 33
  -- Find all drafts IDs we want to keep
  WHERE drafts.id NOT IN (
    SELECT drafts.id FROM drafts
      WHERE drafts.post_id = 33
      ORDER BY drafts.id DESC
      LIMIT 5
  )

Laravel’s convention for the database tables is having a primary key, either an auto-incrementing integer or an UUID v7, to identify each row individually. This is great for sorting in descending order, since the latest records always get a bigger number than the older one. Also, since these are primary keys, the database engine can make use of the index to make operations performant.

For performance reasons, there is an index on the post_id column, which helps the database to not sweep the entire table looking for all the Drafts that belong to a given Post. Instead, it looks for the data on the index, which is smaller, most of the time held in memory — that’s something that will depend on your database engine but most of the time using an index is a great performance uplift.

I also require removing drafts older than 14 days. The creation time for drafts is set in the created_at column as a timestamp. We can easily add that constraint in the query to find the drafts that I want to keep that are not older than 14 days, which shouldn’t make the query slower because most of the heavy lifting is already done by the post_id index.

DELETE from drafts 
  -- Delete only post from a given parent post
  WHERE drafts.post_id = 33
  -- Find all drafts IDs we want to keep
  WHERE drafts.id NOT IN (
    SELECT drafts.id FROM drafts
      WHERE drafts.post_id = 33
      WHERE drafts.created_at > '2020-01-01 19:30:00'
      ORDER BY drafts.id DESC
      LIMIT 5
  )

That’s pretty much it. The database query will operate through the primary key index and the post_id index exclusively for deletion, saving time since it doesn’t have to read the whole table.

Now, we can translate that into Eloquent ORM. We can use the booted() method to register an event listener that removes the records out of bounds through a callback each time a Draft is saved.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Draft extends Model
{
    protected static function booted()
    {
        static::saved(function (self $draft) {
            static::where('post_id', $draft->post_id)
                ->whereKeyNot(
                      static::where([
                          ['post_id', $draft->post_id],
                          ['created_at', '>', now()->subDays(14)]
                      ])->orderBy('id')->limit(5)
                )->delete();
        });
    }

    // ...
}

Not showing older records

You will notice the deletion will only trigger each time a draft is saved. This means the user will still see drafts that are older than two weeks.

To avoid this, we can “hide” those older than two weeks through a simple anonymous global scope.

namespace App\Models;
 
use Illuminate\Database\Eloquent\Model;
 
class Draft extends Model
{
    protected static function booted(): void
    {
        // ...

        static::addGlobalScope('newer-only', function (Builder $builder) {
            $builder->where('created_at', '>', now()->subDays(14));
        });
    }

    // ...
}

I’ll remark that this Global Scope is only a scope, as the older records will still exist in the database.

Removing older records by schedule

Of course, a Global Scope it’s better than actively deleting all drafts each time a draft or post is retrieved from the database.

If you really want to go the extra mile and keep the table clean, you can schedule a callback to remove all of them.

use App\Models\Draft;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schedule;
 
Schedule::call(function () {
    Draft::withoutGlobalScope('newer-only')->where(
        'created_at', '<', now()->subDays(14)
    )->delete();
})->hourly();

Having these records going around in the table shouldn’t be a performance problem. Small tables with few hundreds of records can easily delete these older drafts, although without an index on the created_at column the database engine will require a full table sweep to locate those that match the criteria. In that case, you may want to create one.