Partitioning
Partitioned by viewed_at, a month of views goes with one DROP PARTITION instead of a delete per row. The package does
not create or drop partitions, but works on a partitioned table unchanged: keep rollups folding and views:anonymise
anonymising, and let dropping partitions take the place of prune.after.
Creating the table
Section titled “Creating the table”The partition key must be part of the primary key, so create the table yourself instead of with the published
migration. Run the SQL with DB::unprepared() from the up() method of a migration of your own, and delete the
published create_views_table migration.
CREATE TABLE views ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, viewable_type VARCHAR(255) NOT NULL, viewable_id BIGINT UNSIGNED NOT NULL, viewer_type VARCHAR(255) NULL, viewer_id BIGINT UNSIGNED NULL, visitor VARCHAR(255) NULL, collection VARCHAR(255) NULL, context JSON NULL, viewed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, viewed_at), INDEX views_viewable_viewed_at_index (viewable_type, viewable_id, viewed_at), INDEX views_viewer_type_viewer_id_index (viewer_type, viewer_id), INDEX views_viewed_at_index (viewed_at))PARTITION BY RANGE (UNIX_TIMESTAMP(viewed_at)) ( PARTITION p2026_01 VALUES LESS THAN (UNIX_TIMESTAMP('2026-02-01 00:00:00')), PARTITION p_future VALUES LESS THAN MAXVALUE);CREATE TABLE views ( id BIGINT GENERATED BY DEFAULT AS IDENTITY, viewable_type VARCHAR(255) NOT NULL, viewable_id BIGINT NOT NULL, viewer_type VARCHAR(255) NULL, viewer_id BIGINT NULL, visitor VARCHAR(255) NULL, collection VARCHAR(255) NULL, context JSON NULL, viewed_at TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, viewed_at))PARTITION BY RANGE (viewed_at);
CREATE INDEX views_viewable_viewed_at_index ON views (viewable_type, viewable_id, viewed_at);CREATE INDEX views_viewer_type_viewer_id_index ON views (viewer_type, viewer_id);CREATE INDEX views_viewed_at_index ON views (viewed_at);
CREATE TABLE views_2026_01 PARTITION OF views FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');Adding partitions
Section titled “Adding partitions”Create the partition for a month before its first view arrives, for example from a monthly scheduled task.
Split the catch-all p_future partition, which stays empty as long as the months ahead exist:
ALTER TABLE views REORGANIZE PARTITION p_future INTO ( PARTITION p2026_02 VALUES LESS THAN (UNIX_TIMESTAMP('2026-03-01 00:00:00')), PARTITION p_future VALUES LESS THAN MAXVALUE);A row with no partition to go to fails to insert, so keep a month ahead:
CREATE TABLE views_2026_02 PARTITION OF views FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');Dropping old months
Section titled “Dropping old months”Drop a month only once every rollup has folded it, then record the drop with views:prune --before, so the rollup
source knows the views table no longer holds it:
use CyrildeWit\EloquentViewable\Querying\Rollups\Contracts\Watermarks;
Schedule::call(function (): void { $oldest = now()->subMonths(13)->startOfMonth(); $end = $oldest->copy()->addMonth();
if (app(Watermarks::class)->clamp($end)->lt($end)) { return; }
DB::statement("ALTER TABLE views DROP PARTITION p{$oldest->format('Y_m')}"); Artisan::call('views:prune', ['--before' => $end->toDateTimeString()]);})->monthly()->onOneServer();use CyrildeWit\EloquentViewable\Querying\Rollups\Contracts\Watermarks;
Schedule::call(function (): void { $oldest = now()->subMonths(13)->startOfMonth(); $end = $oldest->copy()->addMonth();
if (app(Watermarks::class)->clamp($end)->lt($end)) { return; }
$partition = "views_{$oldest->format('Y_m')}";
DB::statement("ALTER TABLE views DETACH PARTITION {$partition}"); DB::statement("DROP TABLE {$partition}"); Artisan::call('views:prune', ['--before' => $end->toDateTimeString()]);})->monthly()->onOneServer();What to know
Section titled “What to know”- Leave
prune.afterunset, soviews:maintaindoes not delete rows from months you mean to drop whole. - Publish the retention and rollups migrations as usual. Neither touches the
viewstable.