Scout APM is PHP application performance monitoring designed for developers. With tracing logic that ties issues back to the line of code causing them, you can pinpoint n+1 queries, memory leaks, and other abnormalities in real time so you can knock them out and get back to building a great product. Start your free 14-day trial today and get the performance insight you need in less than 4 minutes.

How to clean up huge tables in Laravel with ease

Original – by Freek Van der Herten – 3 minute read

Using our laravel-model-cleanup package, you can easily remove old unneeded records from your database. We recently released a new major version that adds support for safely cleaning out huge tables.

In this blog post, I'd like to introduce this new release of the package to you.

Using the package

The models you wish to clean up should have a method cleanUp which returns the configuration of how the model should be cleaned up.

Here's an example where all records older than five days will be cleaned up.

use Illuminate\Database\Eloquent\Model;
use Spatie\ModelCleanup\CleanupConfig;
use Spatie\ModelCleanup\GetsCleanedUp;

class YourModel extends Model implements GetsCleanedUp
{
    ...
    
     public function cleanUp(CleanupConfig $config): void
     {
         $config->olderThanDays(5);
     }
}

After registering the model in the config file, running the clean:models artisan command will delete all records that have been created more than five days ago.

It's that simple!

The package contains various other methods for specifying which records should be deleted.

If you need more fine-grained control over when records are considered old, you can use the olderThan method. Here's an example where all models older than a year are considered old.

 public function cleanUp(CleanupConfig $config): void
 {
    $config->olderThan(now()->subYear());
 }

By default, models get cleaned up by performing a single delete query. When you want to clean up a huge table, this single query could lock your table for a long time. It even might not be possible to get the lock in the first place.

To solve this, the package can delete records in chunks using the chunk method. In this example, all records older than five days will be removed in chunks of 1000 records.

 public function cleanUp(CleanupConfig $config): void
 {
    $config
       ->olderThanDays(5)
       ->chunk(1000);
}

The package will stop deleting records when there are no more left that should be removed.

You can pass a closure as a second argument to chunk. Returning false in the closure will stop the deletion process.

In the example below, the deletion process will continue until all records older than five days are deleted, or the record count of the model goes below 5000.

 public function cleanUp(CleanupConfig $config): void
 {
    $config
       ->olderThanDays(5)
       ->chunk(1000, fn() => YourModel::count() > 5000);
}

Watch me create this package

I've streamed most of the development process live on YouTube. These sessions were unrehearsed, and I've tried as much as possible to think aloud.

Session 1

Session 2

I also took the opportunity to use Pest for the test suite. In this stream, Pest creator Nuno Maduro joined to assist me in converting PHPUnit to Pest tests.

In closing

The package has some more handy features not mentioned in this blog post. Head over to the readme of the package on GitHub to know more. I'm already using this package in a couple of projects, and I hope it's helpful for you.

This isn't the only package that our team has created. Take a look at this big list of packages we created previously. I'm sure there will be something that could be of use in your next project.

Stay up to date with all things Laravel, PHP, and JavaScript.

Follow me on Twitter. I regularly tweet out programming tips, and what I myself have learned in ongoing projects.

Every two weeks I send out a newsletter containing lots of interesting stuff for the modern PHP developer.

Expect quick tips & tricks, interesting tutorials, opinions and packages. Because I work with Laravel every day there is an emphasis on that framework.

Rest assured that I will only use your email address to send you the newsletter and will not use it for any other purposes.

Comments

Webmentions

Salman Zafar liked on 29th July 2020
Laravel School retweeted on 29th July 2020
Fahim Ahmed liked on 28th July 2020
Codefuel liked on 28th July 2020
Knight  liked on 28th July 2020
Nour-eddine liked on 28th July 2020
Jan Boddez liked on 28th July 2020
Daniel liked on 28th July 2020
Solomon APPIER-SIGN liked on 28th July 2020
Reed Jones replied on 28th July 2020
Thanks for sharing the videos too, looking forward to watching these
Reed Jones liked on 28th July 2020
Alejandro Pérez liked on 28th July 2020
Ahmad Ripaldi liked on 28th July 2020
Flamur Mavraj liked on 28th July 2020
Krom Ayε dε retweeted on 28th July 2020
Krom Ayε dε liked on 28th July 2020
Luka Rasmussen liked on 28th July 2020
Alonso Chavarria liked on 28th July 2020
Nithin Wilson liked on 27th July 2020
Alex Planting liked on 27th July 2020
Cofe Ding liked on 27th July 2020
Rajiv liked on 27th July 2020
mimremo liked on 27th July 2020
Omar Khairy liked on 27th July 2020
Wyatt liked on 27th July 2020
Toan Nguyen retweeted on 27th July 2020
Estern Winluck liked on 27th July 2020
Jesus O. liked on 27th July 2020
Guus liked on 27th July 2020
Scorp974 liked on 27th July 2020
Rob Tamas liked on 27th July 2020
/dev/faisal liked on 27th July 2020
Francois Laporte liked on 27th July 2020
Nii Adjetey Sowah liked on 27th July 2020
Martin Medina liked on 27th July 2020
Ravi Teja retweeted on 27th July 2020
Paradinight liked on 27th July 2020
Ravi Teja liked on 27th July 2020
Ahmet Mirzabeyoğlu liked on 27th July 2020
HendubDE replied on 27th July 2020
What are the use cases for that package. Since the tables are locked while cleaning up, some projects might not run properly. Usualy where using percona tools that we're not locking while touching a big set of data
Peter Brinck 🤘 liked on 27th July 2020
JOHN DOE liked on 27th July 2020
Spatie retweeted on 27th July 2020
Kevin Purwito retweeted on 27th July 2020
Niels liked on 27th July 2020
Aniket Mahadik liked on 27th July 2020
Muhammad Imran Israr liked on 27th July 2020
Kawan Koding liked on 27th July 2020
spiritBreaker retweeted on 27th July 2020
Cameron Scott liked on 27th July 2020
Manuel liked on 27th July 2020
spiritBreaker liked on 27th July 2020
Ahmed Abd El Ftah liked on 27th July 2020
Jigal Sanders liked on 27th July 2020
Manish Sharma liked on 27th July 2020
O'cakes retweeted on 27th July 2020
O'cakes liked on 27th July 2020