closeio/sync-engine

Optimize deleting large calendars

Opened this issue · 0 comments

def _delete_calendar(db_session, calendar):
"""
Delete the calendar after deleting its events in batches.
Note we deliberately do not rely on the configured delete cascade -- doing
so for a calendar with many events can result in the session post-flush
processing (Transaction record creation) blocking the event loop.
"""
count = 0
for e in calendar.events:
db_session.delete(e)
count += 1
if count % 100 == 0:
# Issue a DELETE for every 100 events.
# This will ensure that when the DELETE for the calendar is issued,
# the number of objects in the session and for which to create
# Transaction records is small.
db_session.commit()
db_session.commit()
# Delete the calendar
db_session.delete(calendar)
db_session.commit()
is fetching all the calendar data, which is inefficient and causes timeouts.

We should use DELETE with a LIMIT until there are no events (~1s for a batch size of 1K, ~4s for a batch size of 10K).

DELETE FROM event WHERE calendar_id=XXXX LIMIT 1000;