Какие проблемы могут возникать при увеличении объема данных в таблице?

Проблемы при увеличении объёма данных в таблице

Не все базы данных одинаково справляются с ростом объёма. Рассмотрю типичные проблемы.

1. Замедление запросов

Индексы становятся неэффективными при очень больших объёмах.

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

# Проблема: даже с индексом запрос медлет
class UserRepository:
    @staticmethod
    async def get_user_by_email(session: AsyncSession, email: str):
        # Таблица users имеет 100 млн записей
        # Индекс есть, но сложный WHERE
        result = await session.execute(
            text(
                'SELECT * FROM users '
                'WHERE email = :email '
                'AND status = :status '
                'AND created_at > :date'
            ),
            {'email': email, 'status': 'active', 'date': '2024-01-01'}
        )
        return result.scalar_one_or_none()

# Решение: создать составной индекс
"""
CREATE INDEX idx_users_email_status_created 
ON users(email, status, created_at);
"""

# Или в SQLAlchemy
from sqlalchemy import Index
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    # Поля...
    
    __table_args__ = (
        Index('idx_email_status_created', 'email', 'status', 'created_at'),
    )

2. Увеличение памяти

Индексы занимают RAM, полнотекстовый поиск требует больше памяти.

Примерно:
- Таблица 1 млн записей: 1GB
- 5 индексов к таблице: 5GB
- Full-text индекс: 2-3GB

Всего: 8-9GB RAM требуется

# Оптимизация: используй CLUSTER (PostgreSQL)
"""
CREATE INDEX idx_users_id ON users(id);
CLUSTER users USING idx_users_id;
"""

# После CLUSTER данные физически отсортированы по индексу
# Кеш эффективнее использует холодные/горячие страницы

from sqlalchemy import text

async def cluster_table(session):
    await session.execute(
        text('CLUSTER users USING idx_users_id')
    )
    await session.commit()

3. Проблемы с VACUUM и ANALYZE

PostgreSQL требует регулярной очистки мёртвых строк.

Проблема:
- DELETE/UPDATE создают "мёртвые" строки
- При большом объёме данных VACUUM работает часами
- В это время таблица заблокирована
- Другие запросы зависают

# Решение: использовать AUTOVACUUM
"""
ALTER TABLE users SET (
    autovacuum_vacuum_scale_factor = 0.01,  # 1% от размера таблицы
    autovacuum_analyze_scale_factor = 0.005 -- 0.5%
);
"""

# Или вручную планировать maintenance window

async def maintenance(session):
    # Ночью, когда мало трафика
    await session.execute(text('VACUUM ANALYZE users'))
    await session.commit()

4. Проблемы с блокировками (Locking)

Одновременные UPDATE/DELETE могут заблокировать таблицу.

# ❌ Плохо: долгая блокировка
class BulkUpdate:
    @staticmethod
    async def update_user_statuses(session: AsyncSession):
        # Таблица 100 млн строк, 10 млн UPDATE
        # Блокировка на 5+ минут!
        await session.execute(
            text(
                'UPDATE users '
                'SET status = :new_status '
                'WHERE status = :old_status '
                'AND last_login < :date'
            ),
            {'new_status': 'inactive', 'old_status': 'active', 'date': '2024-01-01'}
        )
        await session.commit()

# ✅ Хорошо: батчами с разделением
from datetime import datetime, timezone

class BulkUpdateBatch:
    @staticmethod
    async def update_user_statuses_batch(session: AsyncSession, batch_size: int = 1000):
        # Обновляем батчами
        offset = 0
        
        while True:
            result = await session.execute(
                text(
                    'UPDATE users '
                    'SET status = :new_status '
                    'WHERE status = :old_status '
                    'AND last_login < :date '
                    'LIMIT :limit'
                ),
                {
                    'new_status': 'inactive',
                    'old_status': 'active',
                    'date': '2024-01-01',
                    'limit': batch_size
                }
            )
            
            if result.rowcount == 0:
                break
            
            await session.commit()  # коммитим часто
            offset += batch_size
            
            # Слегка ждём, чтобы дать читателям шанс
            import asyncio
            await asyncio.sleep(0.1)

5. Проблемы с JOIN'ами

JOIN'ы больших таблиц требуют много памяти.

# ❌ Плохо: тяжёлый JOIN
class OrderRepository:
    @staticmethod
    async def get_orders_with_details(session: AsyncSession):
        # orders: 100 млн записей
        # order_items: 500 млн записей
        # order_items_details: 1 млн параметров
        # Очень медленно!
        result = await session.execute(
            text(
                'SELECT o.*, oi.*, oid.* '
                'FROM orders o '
                'JOIN order_items oi ON o.id = oi.order_id '
                'JOIN order_item_details oid ON oi.id = oid.item_id '
                'WHERE o.created_at > :date'
            ),
            {'date': '2024-01-01'}
        )
        return result.fetchall()

# ✅ Хорошо: несколько точечных запросов или денормализация
class OrderRepositoryOptimized:
    @staticmethod
    async def get_orders(session: AsyncSession):
        # Запрос 1: заказы
        orders = await session.execute(
            text(
                'SELECT id, user_id, total, status '
                'FROM orders '
                'WHERE created_at > :date '
                'LIMIT 100'
            ),
            {'date': '2024-01-01'}
        )
        orders = orders.fetchall()
        
        # Запрос 2: детали отдельно (если нужны)
        order_ids = [o[0] for o in orders]
        details = await session.execute(
            text(
                'SELECT order_id, item_id, quantity, price '
                'FROM order_items '
                'WHERE order_id IN :ids'
            ),
            {'ids': tuple(order_ids)}
        )
        
        return orders, details.fetchall()

# Или денормализовать в отдельной таблице
"""
CREATE TABLE order_summaries (
    order_id BIGINT PRIMARY KEY,
    total DECIMAL,
    item_count INT,
    created_at TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

CREATE INDEX idx_order_summaries_created ON order_summaries(created_at);
"""

6. Проблемы с LIMIT/OFFSET

OFFSET на больших таблицах очень медленный.

# ❌ Плохо: OFFSET на таблице в 100 млн строк
class UserPagination:
    @staticmethod
    async def get_users_page(session: AsyncSession, page: int):
        # OFFSET 1000000 требует прочитать 1 млн строк!
        result = await session.execute(
            text(
                'SELECT * FROM users '
                'ORDER BY id '
                'LIMIT 100 OFFSET :offset'
            ),
            {'offset': (page - 1) * 100}
        )
        return result.fetchall()

# ✅ Хорошо: cursor-based pagination
class UserPaginationCursor:
    @staticmethod
    async def get_users_cursor(session: AsyncSession, last_id: int = 0):
        # Запрос в 100 раз быстрее!
        result = await session.execute(
            text(
                'SELECT * FROM users '
                'WHERE id > :last_id '
                'ORDER BY id '
                'LIMIT 100'
            ),
            {'last_id': last_id}
        )
        return result.fetchall()

# Клиент передаёт last_id вместо страницы
# GET /api/users?cursor=1000000

7. Проблемы с индексами

Индексы замедляют INSERT/UPDATE, занимают место.

# Диагностика
from sqlalchemy import text

async def analyze_indexes(session):
    # Список всех индексов
    result = await session.execute(
        text(
            'SELECT schemaname, tablename, indexname, indexdef '
            'FROM pg_indexes '
            'WHERE schemaname = \'public\''
        )
    )
    
    for schema, table, index, definition in result:
        # Проверить индекс не используется
        unused = await session.execute(
            text(
                'SELECT idx_scan, idx_tup_read, idx_tup_fetch '
                'FROM pg_stat_user_indexes '
                'WHERE indexrelname = :name'
            ),
            {'name': index}
        )
        
        stats = unused.fetchone()
        if stats and stats[0] == 0:  # idx_scan == 0
            print(f'Index {index} is unused! Remove it.')

8. Проблемы с уникальностью

UNIQUE констрейнты требуют проверки на весь объём данных.

# ❌ Плохо: медленно при большом объёме
class EmailValidator:
    @staticmethod
    async def validate_unique_email(session: AsyncSession, email: str) -> bool:
        # Поиск в 100 млн записей
        result = await session.execute(
            text('SELECT 1 FROM users WHERE email = :email'),
            {'email': email}
        )
        return result.scalar() is None

# ✅ Хорошо: UNIQUE индекс на БД уровне
"""
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
CREATE INDEX CONCURRENTLY idx_email ON users(email);
"""

# БД само справляется эффективнее

9. Проблемы с репликацией

Огромные таблицы медленно реплицируются.

Топология:
Primary (100 млн записей) → Replica 1 (lag: 2-3 часа)
                           → Replica 2 (lag: 5+ часов)

Проблема: lag между primary и replicas велик

# Мониторинг лага репликации
from sqlalchemy import text

async def check_replication_lag(session):
    result = await session.execute(
        text(
            'SELECT '
            'EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) as lag_seconds'
        )
    )
    lag = result.scalar()
    
    if lag > 3600:  # > 1 часа
        print(f'WARNING: Replication lag is {lag} seconds!')

10. Общие рекомендации

class OptimizedDatabase:
    """
    1. Партиционирование (sharding) больших таблиц
    2. Архивирование старых данных
    3. Денормализация для читаемости
    4. Кеширование (Redis) горячих данных
    5. Читающие реплики для read-heavy операций
    6. Батчевая обработка вместо одного большого запроса
    7. Регулярный мониторинг производительности
    8. Версионирование индексов и таблиц
    9. Логирование slow queries
    10. Нагрузочное тестирование перед production
    """

Вывод: На масштабе 100+ млн записей нужна специальная архитектура с партиционированием, кешированием и тщательной оптимизацией запросов.