How to clear a table except for the last N records?

Question

Django

There is a history table:
user
value
datetime_open
It is necessary to delete all but the last 20 records for each user. How to do it? Preferably in djanga ways

Answer 1

Well something like

import itertools

_ids = [u.histories.order_by('-datetime_open').values_list('pk', flat=True)[:20] for u in User.objects.all()]
last_20_history_ids = list(itertools.chain.from_iterable(_ids))
History.objects.exclude(pk__in=last_20_history_ids).delete()

Answer 2

I would do it with one SQL query.

DELETE FROM my_history_table t 
WHERE t.id <= (
    SELECT t2.id
    FROM my_history_table t2 
    ORDER BY datetime_open DESC
    SKIP 20
    LIMIT 1
)

Correct the SQL dialect to taste.