Large deletes: an optional size limit and chunked deletes

Offen
#1,695 0 Kommentare 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen

Maintainer antworten meist innerhalb von 6 Tagen

Dieses Issue hat noch niemand übernommen.

Bewertung

Schwierigkeit
5/5
Geschätzter Aufwand
Über eine Woche
Anfängerfreundlichkeit
18/100
Issue-Typ
Feature
Klarheit
Größtenteils klar
Aktivitätsstatus
Aktiv
Tech-Stack
mysql, postgresql, python
Bereich
databases

Rechercherichtung

This is a design proposal, not a scoped change: start from the delete() implementation and the cascade preview dj.Diagram.cascade(...).counts() to see how the cascade is enumerated and where the transaction is opened, then read the discussion in #1496 (traversal for delete in 2.4) and #1682 (the confirmation prompt). The open questions — batching by seed-table keys vs per-table rows, and what a partial delete guarantees together with part_integrity — have to be settled with the maintainers before any code is written. Done means an agreed design note covering limit semantics, batching strategy, and resume behaviour, not a merged patch.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Beschreibung

enhancement

Problem

delete() runs the whole cascade in one transaction: it deletes from every affected table, leaves first, and commits at the end. For a large cascade (a subject with many sessions, each with many computed results), that transaction can delete millions of rows. On a server shared with other users it then:

  • holds row locks, and on MySQL locks on the index ranges around them, for as long as the deletes take;
  • builds a large undo log, so a rollback or a failure late in the cascade is slow;
  • can delay replication and backups.

The preview (dj.Diagram.cascade(...).counts()) shows the size beforehand, but nothing stops an unexpectedly large delete from running, and nothing splits a large delete into smaller transactions.

Proposal (to be designed)

  1. An optional size limit. For example delete(max_rows=...), or a setting, that refuses the delete before anything is deleted when the cascade would remove more rows than the limit, and reports the per-table counts. A caller who has reviewed the preview raises the limit or passes None.
  2. Chunked deletes. An option that deletes a large cascade in bounded batches, leaves first, each batch in its own transaction, so locks are held briefly and each transaction stays small.

Questions to settle

  • Chunked deletes give up atomicity: a failure partway leaves some rows deleted. What does a partial delete guarantee, and how is it resumed? Deleting leaves first keeps foreign keys valid at every step; part-integrity checks (part_integrity) then need to hold per batch or be checked up front.
  • Batches by what: rows of the seed table (each batch is the cascade of a slice of seed keys), or rows per table?
  • Interaction with the new traversal for delete in 2.4 (#1496), which decides which rows the cascade reaches.
  • Defaults: off by default, with the limit and chunking chosen explicitly by the caller.

Related: #1682 (the confirmation prompt holds the delete transaction open; production code previews and then calls delete(prompt=False)).

Vorherrschende Sprache
Python
Sterne
198
Forks
103
Ø Merge
6 T. 1 Std.
Gemergte PRs (30 T.)
10

Entwicklungsumgebung

Erste Schritte

  1. Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
  2. Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
  3. Forken Sie das Repository und arbeiten Sie in einem Branch.
  4. Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.

Mehr aus datajoint/datajoint-python

Alle Issues in datajoint/datajoint-python

Ähnliche Issues

Weitere Issues zu Python

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.