Improve dataset-deletion time (with many history run/unit/imagecache rows)

[tcompa]

This is relevant due to:

1. Somewhat slow "job restart" operations
2. At least one case where a delete-dataset endpoint took ~40 seconds (request-to-response)

[tcompa]

(note: it's possible that the relevant slow operation is DELETE HistoryRun)

Next steps:

• Spin up a local instance a reproduce a relevant scenario:
  • Prepare a workflow which creates N fake images (e.g. with N=3_000) and processes them via cellpose.
  • Run this same workflow on 3 different datasets.
• Take note of how many HistoryRun, HistoryUnit, HistoryImageCache rows exist.
• Take note of the current db size with this query.
• Connect to psql and explain the delete operation of one of the datasets (as in https://dba.stackexchange.com/a/226481). This should look like

begin;
explain (analyze,buffers,timing)
delete from datasetv2 where id=XXXX;
rollback;

• Add an index on HistoryUnit.history_run_id and/or HistoryImageCache.history_run_id.
• Take note of the new db size.
• Re-run the explain check - and review the differences.

[tcompa]

On a stress test where we created 10k images and then processed all of them with a subsequent task, this takes a huge amount of time:

$ time curl -X DELETE -H "Authorization: Bearer REDACTED" https://example.org/backend/api/v2/project/233/dataset/1258/

real	4m42.873s
user	0m0.051s
sys	0m0.004s

[tcompa]

A foreign key must reference columns that either are a primary key or form a unique constraint, or are columns from a non-partial unique index. This means that the referenced columns always have an index to allow efficient lookups on whether a referencing row has a match. Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, the declaration of a foreign key constraint does not automatically create an index on the referencing columns.
(https://www.postgresql.org/docs/18/ddl-constraints.html#DDL-CONSTRAINTS-FK)