Queue-SQL: Run Mass Deletes and Updates Across Parallel Queue Jobs

Executing a single UPDATE or DELETE statement across millions of rows can hold database locks for minutes, cause replicas to fall behind, or timeout during deployment.

Queue-SQL is a Laravel package by Kamran Atayev that splits large database writes into parallel queued jobs. It calculates the minimum and maximum primary keys for your query, cuts that ID range into bounded slices, and dispatches an Illuminate\Bus\Batch to process each slice independently.

Here's what the package gives you:

• Query builder macro: registers a queue() method on both Query Builder and Eloquent instances, exposing update, delete, insert, and upsert.
• Safe SQL compilation: compiles constraints into SQL fragments and bindings so nested closures, whereHas, whereExists, and sub-selects reach workers without closure serialization issues.
• Flexible batch sizing: size jobs using chunk (rows per job) or maxJobs (total job target across the key range).
• Native Laravel Batches: returns an Illuminate\Bus\Batch instance with support for then, catch, finally, and allowFailures.
• Artisan CLI monitoring: track and manage active batches with queue-sql:status and queue-sql:cancel, including a --watch live view and --json format.
• Horizon tagging: automatically tags dispatched jobs with queue-sql, operation types, and target tables.
• Configurable defaults: customize global chunk size, retries, backoff, throttling, delays, and queue names in config/queue-sql.php.

#Queueing a Write Query

To queue a write query, chain queue() onto your query builder before the write method, pass any configuration arguments, and call dispatch() at the end. Nothing is queued until dispatch() is called:

use App\Models\Order;
 
Order::where('status', 'complete')
    ->queue(chunk: 25000, tries: 2, onQueue: 'maintenance', throttle: 4)
    ->update(['status' => 'completed'])
    ->then(fn (Batch $batch) => Log::info("Backfill finished in {$batch->totalJobs} jobs"))
    ->catch(fn (Throwable $e) => report($e))
    ->dispatch();

The throttle argument is especially useful for large backfills: capping the batch at four jobs per second prevents worker processes from saturating database connections or causing replication lag.

Deletes use the same syntax. Bulk inserts skip key-range planning and divide the array of records across jobs instead:

PersonalAccessToken::where('expires_at', '<', now()->subMonths(6))
    ->queue(chunk: 10000)
    ->delete()
    ->dispatch();
 
DB::table('search_index')->queue(chunk: 1000)->insert($rows)->dispatch();

Because each update or delete job targets a fixed slice of primary keys, re-running a job executes against the same row range, making retries idempotent. Bulk inserts, however, do not share this property; a retried insert job can create duplicate rows. The package documentation recommends using a unique index or upsert() for operations requiring retry safety.

Compared to Laravel's built-in Prunable trait and model:prune command—which delete records sequentially in a single process—Queue-SQL runs jobs concurrently and tracks them as a batch. It also supports arbitrary updates and bulk inserts, which Prunable does not handle.

#Previewing the Plan

Replacing dispatch() with dryRun() returns a breakdown of how the query will be batched without queuing any jobs:

Order::where('status', 'complete')
    ->queue(chunk: 25000)
    ->update(['status' => 'completed'])
    ->dryRun();
 
// ['operation' => 'update', 'table' => 'orders', 'jobs' => 412, 'ranges' => 412, 'estimatedRows' => 9842311]

If you prefer to cap the total job count rather than specifying chunk size per job, pass maxJobs instead of chunk:

Order::where('status', 'complete')
    ->queue(maxJobs: 100)
    ->update(['status' => 'completed'])
    ->dispatch();

Key-range chunking calculates ranges based on the minimum and maximum primary key values rather than exact row counts. On tables with large ID gaps, some jobs may process fewer rows than others, though the total job count remains fixed. Note that chunk and maxJobs are mutually exclusive—passing both will throw an InvalidArgumentException.

#Tracking a Batch from the CLI

Calling dispatch() returns an instance of Laravel's Illuminate\Bus\Batch. You can store $batch->id to monitor progress later or inspect active runs using the built-in Artisan CLI commands, which query the framework's batch table directly:

php artisan queue-sql:status            # list all batches
php artisan queue-sql:status {batch}    # inspect a single batch
php artisan queue-sql:cancel {batch}
php artisan queue-sql:status --watch    # auto-refresh view every 2 seconds
php artisan queue-sql:json

Inspecting a batch displays total jobs, pending and failed counts, progress percentages, and current batch status. The --watch flag updates live in interactive terminals, while non-interactive environments (such as scheduled tasks or piped scripts) print output once and exit.

#Installation & Considerations

Queue-SQL requires PHP 8.1+ and Laravel 10 through 13, supporting SQLite, MySQL, and PostgreSQL. Because it relies on Laravel's native job batching, your database must have the job_batches table migrated:

composer require kamranata/queue-sql
php artisan queue:batches-table
php artisan migrate
php artisan vendor:publish --tag=queue-sql-config   # optional

Keep two key operational details in mind when using Queue-SQL:

1. Primary Key Requirement: Slicing queries across jobs requires an incrementing integer primary key. Tables without an integer primary key fall back to running the query in a single queued job.
2. Fixed Key Boundaries: Key boundaries are calculated when dispatch() is called. Rows inserted with IDs higher than the maximum key after dispatch will not be included in the current batch.

The repository includes a benchmark script (benchmarks/lock_duration.php) for testing performance in your environment. In a benchmark deleting 20,000 rows on SQLite, a single un-batched DELETE held a database lock 3.6 times longer than the longest lock duration recorded by Queue-SQL.

You can learn more about this package, get full installation instructions, and view the source code on the queue-sql GitHub repository.