如何用Django高效删除20万条一对一关联数据库记录？

场景与现状 #

我有一对Django一对一关联模型：

class User(models.Model):
    user_name = models.CharField(max_length=40)
    type = models.CharField(max_length=255)
    created_at = models.DateTimeField()
    # 其他字段

class Book(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)

数据库（PostgreSQL）中约有20万条关联记录需要删除，技术栈为Python+Django+PostgreSQL。

我尝试过的方案：

user_ids = User.objects.filter(type='sample', created_at__gte='2022-11-15 08:00', created_at__lt="2022-11-15 08:30").values_list('id',flat=True)[:200000] # 拉取20万条用户ID
for i, _ in enumerate(user_ids[:: 1000]):
    with transaction.atomic():
        batch_start = i * self.batch_size
        batch_end = batch_start + self.batch_size
        _, deleted = User.objects.filter(id__in=user_ids[batch_start,batch_end])

该方案的资源消耗：

• 内存占用约300MB
• CPU占用偏高
• 完成耗时超15分钟

现有方案的核心问题 #

1. 内存占用过高：一次性加载20万条ID到内存，直接导致内存占用飙升；
2. 查询效率低下：id__in传入大数量ID时，PostgreSQL会生成超长查询语句，解析和执行成本极高；
3. 代码逻辑错误：缺少delete()调用，切片语法错误（应为user_ids[batch_start:batch_end]），批次计算逻辑混乱。

优化方案 #

方案1：小批量分批次删除（无需全量加载ID） #

每次仅加载小批量符合条件的ID，避免内存过载，同时利用Django的事务和级联删除特性：

from django.db import transaction
from datetime import datetime
from django.utils import timezone

batch_size = 1000
target_start = timezone.make_aware(datetime(2022, 11, 15, 8, 0))
target_end = timezone.make_aware(datetime(2022, 11, 15, 8, 30))

while True:
    with transaction.atomic():
        # 每次仅获取batch_size条符合条件的用户ID
        user_ids = list(User.objects.filter(
            type='sample',
            created_at__gte=target_start,
            created_at__lt=target_end
        ).values_list('id', flat=True)[:batch_size])
        
        if not user_ids:
            break
            
        # 小批量删除，PostgreSQL自动级联删除关联的Book记录
        deleted_count, _ = User.objects.filter(id__in=user_ids).delete()
        
        if deleted_count == 0:
            break

优势：内存占用控制在MB级，查询逻辑简单，Django ORM原生支持，无需额外学习成本。

方案2：原生SQL直接删除（性能最优） #

跳过Django ORM的对象实例化、信号触发等额外开销，直接用PostgreSQL原生语法分批次删除：

from django.db import connection, transaction

batch_size = 1000
while True:
    with transaction.atomic():
        with connection.cursor() as cursor:
            cursor.execute("""
                DELETE FROM app_user
                WHERE type = %s
                  AND created_at >= %s
                  AND created_at < %s
                LIMIT %s
            """, ['sample', '2022-11-15 08:00', '2022-11-15 08:30', batch_size])
            deleted_rows = cursor.rowcount
            
        if deleted_rows == 0:
            break

注意：将app_user替换为你的User模型对应的数据库表名（默认是应用名_模型名小写）。
优势：资源消耗极低，耗时最短，适合超大量数据的删除操作。

方案3：临时关闭Django信号（若有） #

如果User或Book模型注册了pre_delete/post_delete信号接收器，每条记录删除时都会触发信号逻辑，大幅增加耗时和资源消耗。可临时关闭信号：

from django.db.models.signals import pre_delete, post_delete
from myapp.models import User, Book

# 保存原信号接收器
user_pre_receivers = pre_delete._live_receivers(User)
user_post_receivers = post_delete._live_receivers(User)
book_pre_receivers = pre_delete._live_receivers(Book)
book_post_receivers = post_delete._live_receivers(Book)

# 断开信号
for receiver in user_pre_receivers:
    pre_delete.disconnect(receiver, sender=User)
for receiver in user_post_receivers:
    post_delete.disconnect(receiver, sender=User)
for receiver in book_pre_receivers:
    pre_delete.disconnect(receiver, sender=Book)
for receiver in book_post_receivers:
    post_delete.disconnect(receiver, sender=Book)

# 执行批量删除（使用方案1或2）

# 恢复信号
for receiver in user_pre_receivers:
    pre_delete.connect(receiver, sender=User)
for receiver in user_post_receivers:
    post_delete.connect(receiver, sender=User)
for receiver in book_pre_receivers:
    pre_delete.connect(receiver, sender=Book)
for receiver in book_post_receivers:
    post_delete.connect(receiver, sender=Book)

注意：若信号包含必要业务逻辑，请勿关闭，否则会导致数据不一致。

优化后预期效果 #

• 内存占用：降至几十MB以内；
• CPU占用：大幅降低；
• 耗时：压缩至3-5分钟（取决于数据库性能和批量大小）。

内容的提问来源于stack exchange，提问作者Thành Lý