Работало ли на проекте партиционирование автоматически

Работало ли на проекте партиционирование автоматически

Это типичный вопрос собеседующего, чтобы понять практический опыт с оптимизацией больших таблиц. Разберу, как реально работает партиционирование, когда его нужно и как организовать автоматическое управление.

Что такое партиционирование

Партиционирование — разбиение большой таблицы на несколько подтаблиц по определенному критерию (например, по дате). Это улучшает производительность запросов и управляемость.

-- Пример: таблица событий партиционирована по дате
CREATE TABLE events (
    id BIGSERIAL,
    user_id INT,
    event_type VARCHAR(50),
    created_at TIMESTAMPTZ,
    PRIMARY KEY (id, created_at)  -- Партиционный ключ в PK
) PARTITION BY RANGE (created_at);

-- Каждый месяц автоматически создается новая партиция
CREATE TABLE events_2024_01 
    PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02
    PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

Когда партиционирование помогает

Практика: полностью автоматическое партиционирование

На большинстве проектов я работал так:

Подход 1 — PostgreSQL 11+ с декларативным партиционированием:

В PostgreSQL 11 можно определить шаблон, и новые партиции создаются автоматически через триггеры или расширения.

-- Установить расширение pg_partman для автоматизации
CREATE EXTENSION IF NOT EXISTS pg_partman;

-- Настроить автоматическое создание партиций
SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'created_at',
    p_type := 'range',
    p_interval := '1 month',
    p_premake := 2  -- Создавать партиции на 2 месяца вперед
);

-- Запустить фоновое задание для maintenance
SELECT cron.schedule(
    'partition-maintenance',
    '0 2 * * *',  -- 2:00 AM каждый день
    'SELECT partman.maintain_replication_set(p_parent_table := ''public.events'')'
);

Подход 2 — Python скрипт для управления партициями:

import psycopg2
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta

class PartitionManager:
    def __init__(self, connection_string):
        self.conn = psycopg2.connect(connection_string)
        self.cursor = self.conn.cursor()
    
    def create_monthly_partitions(self, table_name, months_ahead=2):
        """Создать партиции на несколько месяцев вперед"""
        current_date = datetime.now()
        
        for i in range(months_ahead):
            start_date = current_date + relativedelta(months=i)
            end_date = current_date + relativedelta(months=i+1)
            
            partition_name = f"{table_name}_{start_date.strftime('%Y_%m')}"
            
            sql = f"""
                CREATE TABLE IF NOT EXISTS {partition_name}
                PARTITION OF {table_name}
                FOR VALUES FROM ('{start_date.date()}') TO ('{end_date.date()}');
            """
            
            try:
                self.cursor.execute(sql)
                self.conn.commit()
                print(f"✓ Created partition: {partition_name}")
            except psycopg2.Error as e:
                self.conn.rollback()
                print(f"✗ Error creating {partition_name}: {e}")
    
    def vacuum_old_partitions(self, table_name, retention_months=12):
        """Удалить старые партиции"""
        cutoff_date = datetime.now() - relativedelta(months=retention_months)
        
        sql = f"""
            SELECT schemaname, tablename 
            FROM pg_tables 
            WHERE tablename LIKE '{table_name}_%'
            AND schemaname = 'public'
        """
        
        self.cursor.execute(sql)
        partitions = self.cursor.fetchall()
        
        for schema, partition in partitions:
            # Парсим дату из имени партиции
            date_part = partition.split('_')[-2:]
            partition_date = datetime.strptime('_'.join(date_part), '%Y_%m')
            
            if partition_date < cutoff_date:
                drop_sql = f"DROP TABLE IF EXISTS {partition};"
                try:
                    self.cursor.execute(drop_sql)
                    self.conn.commit()
                    print(f"✓ Dropped old partition: {partition}")
                except psycopg2.Error as e:
                    self.conn.rollback()
                    print(f"✗ Error dropping {partition}: {e}")
    
    def close(self):
        self.cursor.close()
        self.conn.close()

# Использование
manager = PartitionManager("postgresql://user:password@localhost/analytics")
manager.create_monthly_partitions('events', months_ahead=3)
manager.vacuum_old_partitions('events', retention_months=24)
manager.close()

Подход 3 — Apache Airflow DAG для автоматизации

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime, timedelta

default_args = {
    'owner': 'data_team',
    'retries': 2,
    'retry_delay': timedelta(hours=1),
}

dag = DAG(
    'partition_management',
    default_args=default_args,
    schedule_interval='0 2 * * 1',  # Каждый понедельник в 2 AM
    start_date=datetime(2024, 1, 1),
)

def create_partitions():
    manager = PartitionManager("postgresql://...")
    manager.create_monthly_partitions('events', months_ahead=3)
    manager.close()

def cleanup_old_partitions():
    manager = PartitionManager("postgresql://...")
    manager.vacuum_old_partitions('events', retention_months=24)
    manager.close()

create_task = PythonOperator(
    task_id='create_new_partitions',
    python_callable=create_partitions,
    dag=dag,
)

cleanup_task = PythonOperator(
    task_id='cleanup_old_partitions',
    python_callable=cleanup_old_partitions,
    dag=dag,
)

create_task >> cleanup_task

Проблемы, которые я встречал

Проблема 1: Неправильный выбор ключа партиционирования

-- Плохо: партиционируем по редко используемому полю
CREATE TABLE transactions 
PARTITION BY HASH (user_country);  -- Выиграем мало

-- Хорошо: партиционируем по дате (очень часто используется в WHERE)
CREATE TABLE transactions 
PARTITION BY RANGE (transaction_date);  -- Выиграем много

Проблема 2: Забыли включить ключ партиции в запрос

-- Плохо: partition pruning не сработает
SELECT * FROM events 
WHERE user_id = 123;  -- Сканирует все партиции!

-- Хорошо: partition pruning активен
SELECT * FROM events 
WHERE created_at >= '2024-03-01' AND user_id = 123;  -- Сканирует только одну партицию

Проблема 3: Слишком много маленьких партиций

# Плохо: партиции по дню для таблицы на 1млн строк
# -> 365 партиций по 2700 строк каждая
p_interval = '1 day'  # Слишком мелко

# Хорошо: партиции по месяцу
p_interval = '1 month'  # Оптимально

Мониторинг партиционирования

-- Смотреть размер каждой партиции
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE tablename LIKE 'events_%'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Проверить partition pruning (EXPLAIN ANALYZE)
EXPLAIN ANALYZE
SELECT COUNT(*) FROM events 
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';
-- Должна быть только одна partition (events_2024_03)

Заключение

На практике автоматическое партиционирование работает хорошо, если:

1. Используем PostgreSQL 11+ с pg_partman или custom Python скрипт
2. Автоматизируем через Airflow/Cron
3. Выбираем правильный ключ (обычно дата)
4. Регулярно чистим старые партиции
5. Мониторим размеры и эффективность partition pruning

Выигрыш огромный: запросы становятся в 10-100x быстрее на таблицах > 1 млрд строк.