Как быстро удалить данные из БД?

Как быстро удалить данные из БД

Удаление больших объёмов данных из БД — частая задача, требующая оптимизации. Неправильный подход может привести к блокировкам, утечкам памяти и долгим транзакциям.

1. Прямой DELETE (медленно для больших объёмов)

import psycopg2
from psycopg2.extras import execute_batch

conn = psycopg2.connect("dbname=mydb user=postgres")
cursor = conn.cursor()

# Наивный подход — одной транзакцией удалить ВСЁ
cursor.execute("DELETE FROM users WHERE age < 18")
conn.commit()

Проблемы:

• Одна большая транзакция может запертить таблицу
• Логи транзакций растут
• OOM (Out of Memory) если удаляется миллионы строк

2. Батч-удаление (batch delete) — оптимальный подход

DELETE с LIMIT в цикле:

import psycopg2
import time

def batch_delete(table: str, condition: str, batch_size: int = 10000):
    conn = psycopg2.connect("dbname=mydb user=postgres")
    cursor = conn.cursor()
    
    deleted_total = 0
    while True:
        # Удаляем по батчам
        query = f"DELETE FROM {table} WHERE {condition} LIMIT {batch_size}"
        cursor.execute(query)
        deleted = cursor.rowcount
        
        if deleted == 0:
            break
        
        conn.commit()  # Коммит после каждого батча
        deleted_total += deleted
        print(f"Удалено: {deleted_total} строк")
        
        time.sleep(0.1)  # Небольшая пауза для разгрузки БД
    
    conn.close()

batch_delete("users", "age < 18", batch_size=10000)

Преимущества:

• Короткие транзакции → нет блокировок
• Логи не растут экспоненциально
• БД может обслуживать другие запросы
• Контролируемое потребление памяти

3. Партиционирование таблиц

DROP PARTITION быстрее, чем DELETE для очень больших БД:

def drop_partition(partition_name: str):
    conn = psycopg2.connect("dbname=mydb user=postgres")
    cursor = conn.cursor()
    
    # DROP PARTITION почти мгновенен
    cursor.execute(f"DROP TABLE {partition_name}")
    conn.commit()
    conn.close()

drop_partition("events_2023_01")

4. Архивирование вместо удаления

Часто лучше переместить данные, чем удалить:

def archive_old_data(days: int = 90):
    conn = psycopg2.connect("dbname=mydb user=postgres")
    cursor = conn.cursor()
    
    # Копируем в архивную таблицу
    cursor.execute(f"""
        INSERT INTO users_archive
        SELECT * FROM users
        WHERE created_at < NOW() - INTERVAL '{days} days'
    """)
    
    # Удаляем из основной таблицы
    cursor.execute(f"""
        DELETE FROM users
        WHERE created_at < NOW() - INTERVAL '{days} days'
    """)
    
    conn.commit()
    conn.close()

archive_old_data(days=90)

5. TRUNCATE для полной очистки

TRUNCATE работает быстрее DELETE:

def truncate_table(table: str):
    conn = psycopg2.connect("dbname=mydb user=postgres")
    cursor = conn.cursor()
    
    cursor.execute(f"TRUNCATE TABLE {table}")
    conn.commit()
    conn.close()

truncate_table("temp_data")

6. SQLAlchemy с оптимизацией

from sqlalchemy import create_engine, delete
from sqlalchemy.orm import Session
from models import User

def batch_delete_sqlalchemy(batch_size: int = 10000):
    engine = create_engine("postgresql://user:password@localhost/mydb")
    session = Session(engine)
    
    while True:
        stmt = delete(User).where(User.age < 18).limit(batch_size)
        result = session.execute(stmt)
        deleted = result.rowcount
        
        if deleted == 0:
            break
        
        session.commit()
        print(f"Удалено: {deleted}")
        
    session.close()

batch_delete_sqlalchemy(batch_size=10000)

Чеклист оптимизации

✓ Используй LIMIT при DELETE больших объёмов ✓ Коммит после каждого батча ✓ Добавь паузу между батчами (time.sleep) ✓ Рассмотри партиционирование для больших таблиц ✓ Архивируй вместо удаления, когда возможно ✓ Мониторь блокировки во время удаления