Since we migrated to RDS, I've rolled out a disaster recovery strategy that involves creating daily offsite backups using spatie/laravel-backup (in addition to the point-in-time recovery from AWS). I have a reminder in my calendar once every 3 months to check if the backup is healthy by restoring it on my machine locally.

As the reminder popped up on my laptop this week, I downloaded the backup, but immediately noticed the backup size was around 90gb which is a massive jump from the 75GB back in april, which I migrated to RDS. We've grown a lot in customers and order volume since, which kinda explains the growth, but I wanted to investigate which tables were growing the fastest.

Checking the table size

Using the following query, you can quickly see the table sizes in a given database:

SELECT 
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'YOUR_DB'
ORDER BY total_mb DESC;

Depending on the size of your tables, the query should run pretty fast and output something like this:

One table stood out to me - failed_jobs, with over 19gb (!) in size. Turns out my failed_jobs table hadn’t been pruned in 15* years and was taking up 19GB 🫠

*We migrated to Laravel in 2015, but the app has been around since ~2010 and was initially Node, Mongo & Angular. We had a concept of jobs since the early days and migrated it over to Laravel.

Interestingly enough, every row on average was ~8KB and we 'only' had a million of rows. Doing some quick math shows us 8KB * 1.000.000 is nowhere near 19GB, but 8GB.

💡 Where does the rest come from?

Internally, InnoDB stores TEXT and LONGTEXT columns (like the payload and exception fields in Laravel’s failed_jobs table) in 16KB pages by default.
Each page is allocated in full, even if the actual data only uses part of it. This means an 8KB row still takes up 16KB.

16KB * 1.000.000 = 16GB. Sprinkle in 10-20% overhead from indexing, fragmentation & transaction metadata, and that lands us in the 19GB ballpark we observed.

How to prune failed jobs in Laravel

Lucky for me, the solution was pretty easy, you just have to prune the jobs as follows:

php artisan queue:prune-failed --hours=192

(In my defence, it's only been documented since Laravel 8 🙈).

After running the command, the impact on the free storage space on RDS was substantial. (The slight dip is the binlog filling up for replication, which clears up quickly).

Set it & forget it

So remember - automate this command by scheduling it in your console/Kernel.php, and keep your failed_jobs table slim!

$schedule
  ->command('queue:prune-failed --hours=192')
  ->dailyAt('01:00')
  ->timezone('Europe/Brussels');