The Dual-Table Index Trick

Table Storage is fast when your query lines up with the partition key and slow — sometimes catastrophically — when it doesn't. Image metadata is partitioned by camera, which is exactly right for reads: "show me this camera's images over the last hour" is a single partition. But every image the fleet captures has to be deleted eventually, on a retention window, and "delete everything older than N days across all cameras" cuts against that partition key. Done naively, the cleanup job would have to walk every camera's partition on every pass. So each write also lays down a second row in a differently-partitioned index — and that index is what makes deletion cheap.

The one idea to hold on to

Every image-metadata write also writes a row to a time-bucketed index table, so the retention job can find all expired rows with one partition-range scan instead of scanning every camera. The primary repository's CreateAsync is overridden to do both writes, so the index maintains itself:

public async Task CreateAsync(SingleImageMetadataEntity entity)
{
    await base.CreateAsync(entity);      // primary table, PartitionKey = CameraId
    await AddIndexEntryAsync(entity);    // index table, PartitionKey = time bucket
}

The two tables are partitioned for the two opposite access patterns:

Reads stay on the camera-partitioned primary table. Deletes drive off the time-partitioned index, where "older than the cutoff" is a range over the partition key — the query Table Storage is fastest at.

How the cleanup job uses it

The retention job in Alert.CA.MetadataCleanup (which consumes this index) runs in two stages. First, an enqueue pass finds what's expired and fans it out by device:

var cutoff = DateTimeOffset.UtcNow.AddDays(-RetentionDays);
string indexPKey = cutoff.ToString("yyyyMMddHHmm");
// one cheap range scan on the index partition key — not a per-camera walk
var rows = await indexRepo.GetByQueryAsync(x => x.PartitionKey.CompareTo(indexPKey) < 0);
var deviceIds = rows.Select(r => r.DeviceId).Distinct();   // one delete message per device

Then a dequeue pass deletes, per device, in chunked batches — the primary rows first (reconstructed from the index row's DeviceId + RowKey), then the index rows — so no single partition delete is large enough to bottleneck:

foreach (var batch in indexRows.Chunk(DeleteBatchSize))
    await metadataRepository.DeleteBatchAsync(batch.Select(toPrimaryDeleteEntity).ToArray());

That fan-out-by-device through the delete queue is the whole point: without the index, this job couldn't cheaply find the expired rows, and without the batching it couldn't delete them without stalling. The index is the "what's old" lookup; the queue is the "spread the work" lever.

Gotcha: the index bucket is minute-precision, and the comments lie about it

The MetadataIndexEntity XML comment says the PartitionKey is yyyyMMddHH (hour). The code — in both the write and the cleanup query — uses yyyyMMddHHmm (minute). Trust the code: a hand-written cleanup query that assumes hour-buckets would compare against the wrong partition-key format and select the wrong rows. (Verified against Models/TableStorage/MetadataIndexEntity.cs and ImageMetadataCleanup.cs.)

Which types get the trick — and which don't

Only the three image-metadata types are indexed, because only they accumulate at fleet scale and need this retention path. The rest are single-table:

Repository	Indexed?	Notes
SingleImageMetadataRepository	✅	Auto-indexes on CreateAsync
PanoImageMetadataRepository	✅	Also has GetLatestClearDayImageByCamera (filters IsClearDay)
PreStitchImageMetadataRepository	✅	Query by PanoImageCorrelationId
VideoMetadataRepository	❌	No index table; standalone interface
DurableEntityDataRepository	❌	Orchestration mode/rate/timing per device
ImageAcquisitionInstanceRepository	❌	Recent orchestration run history

Wrapping up

Image metadata is written to two tables on purpose: a camera-partitioned primary table that reads are fast against, and a minute-bucketed index table that lets the retention cleanup job find everything past the cutoff with a single partition-range scan — then delete it in device-grouped batches through the delete queue, so purging never bottlenecks. Only the three image types carry an index, and its partition format lives in code, not config.

Next, where the actual image bytes go — and a one-implementation-three-interfaces trick: Binary Assets in Blob Storage.