Snapshots table exploding

[AidasK]

Our snapshots table now contains 9 000 000 records and they take up 160gb of space. Is there a command which could cleanup old snapshot versions? Thanks

[AidasK]

Would you accept this pull request? It ensures that all old snapshots would get deleted after a new one was created.
EloquentSnapshotRepository.php:

    public function persist(Snapshot $snapshot): Snapshot
    {
        /** @var \Spatie\EventSourcing\Snapshots\EloquentSnapshot $eloquentSnapshot */
        $eloquentSnapshot = new $this->snapshotModel();

        $eloquentSnapshot->aggregate_uuid = $snapshot->aggregateUuid;
        $eloquentSnapshot->aggregate_version = $snapshot->aggregateVersion;
        $eloquentSnapshot->state = $snapshot->state;

        $eloquentSnapshot->save();

        $snapshts = $eloquentSnapshot::query()
            ->where('aggregate_uuid', $snapshot->aggregateUuid)
            ->where('aggregate_version', '<', $snapshot->aggregateVersion)
            ->oldest()
            ->limit(5)
            ->pluck('id');
        $eloquentSnapshot::query()->whereIn('id', $ids)->delete();

        return $eloquentSnapshot->toSnapshot();
    }

Notice that I have also added a limit to this query. Probably at this point some aggregate roots might have more than 1000 snapshots and we don't want to timeout these requests.

I am not doing a simple query like below, since mysql performs best, then delete is performed on unique index. I want to avoid gap locking at any cost. Also aggregate_version column has no index and that might create a lock on entire table.

        $this->snapshotModel()
             ->query()
             ->where('aggregate_uuid', $snapshot->aggregateUuid)
             ->where('aggregate_version', '<', $snapshot->aggregateVersion)
             ->delete();

Drawbacks? Migration period might create some temporary latencies while each aggregate root is performing snapshot cleanup.

[AidasK]

Probably we don't even need all of this logic anymore as we could just update one record each time. We could not do this in earlier versions of laravel as upsert method was introduced only recently. Though for this to work we would also need to add unique index on snapshots.aggregate_uuid and that would be a breaking change. Entire application would need to stop to clear duplicate records and to add a new index, it does not sound easy.

$this->snapshotModel()->upsert([
    'aggregate_uuid' => $snapshot->aggregateUuid, 
    'aggregate_version' => $snapshot->aggregateVersion,
    'state' => $snapshot->state,
], ['aggregate_uuid'], ['aggregate_version', 'state', 'updated_at']);

I would be willing to create a pull requests with all of these strategies and tests and make it configurable via config.

[jericopulvera]

Is there any drawback with updating the snapshot aggregate_version approach as default?

I'm trying this with PlanetScaleDB and snapshotting every 10 versions is consuming too many rows read and it's because of the snapshot creating a new aggregate_version instead of updating the current aggregate_version that's causing it.

[AidasK]

Migration is the only drawback. So if you want to do it on the old system, it would be easier to use delete based approach and for new projects it would be better to use updates/upserts.