Как будешь использовать партиции для оптимизации?

Использование партиций для оптимизации в Product Analytics

Партиционирование — это одна из ключевых техник оптимизации работы с большими объемами данных в аналитических системах. Как Product Analyst, я использую партиции для значительного ускорения запросов и снижения затрат на хранение.

1. Что такое партиции?

Партиция — это разделение больших таблиц на меньшие физические части на основе значения ключевого столбца. Вместо того чтобы сканировать всю таблицу, база данных может сразу перейти к нужной партии.

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

• По датам (Range Partitioning) — самый распространенный способ
• По значениям (List Partitioning) — по стране, платформе, версии
• По хешу (Hash Partitioning) — равномерное распределение
• По диапазонам (Range) — по возрастным группам, ценовым сегментам

2. Партиционирование по времени — основной подход

Мост частый сценарий в analytics — партиционирование по дате события. Это позволяет:

• Быстро удалять старые данные (целая партия за раз)
• Запрашивать только нужные дни/недели/месяцы
• Параллелизировать запросы по временным интервалам

-- Создание таблицы с партиционированием по месяцам
CREATE TABLE events (
    event_id UUID,
    user_id UUID,
    event_type VARCHAR,
    event_timestamp TIMESTAMPTZ,
    properties JSONB,
    created_at DATE
) 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');

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

3. Индексы на партиционированных таблицах

Оптимизация еще больше усиливается при правильном индексированию партиций.

-- Создание индекса на партиционированной таблице
CREATE INDEX idx_events_user_timestamp
ON events (user_id, event_timestamp);

-- Индексы автоматически создаются на каждой партии
CREATE INDEX idx_events_2024_01_event_type
ON events_2024_01 (event_type)
WHERE event_type IN ('page_view', 'purchase');

4. Пример запроса, который использует партиции

-- Этот запрос будет сканировать ТОЛЬКО партиции за март и апрель 2024
SELECT 
    DATE(event_timestamp) as day,
    event_type,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as unique_users
FROM events
WHERE created_at >= '2024-03-01' 
  AND created_at < '2024-05-01'
  AND event_type = 'purchase'
GROUP BY 1, 2
ORDER BY 1 DESC;

-- Без партиций это был бы полный скан всей таблицы (все месяцы)
-- С партициями база смотрит только events_2024_03 и events_2024_04

5. Partition Pruning — автоматическая оптимизация

Главное преимущество партиций — partition pruning (отсечение партиций). Оптимизатор базы автоматически исключает партии, которые точно не содержат нужные данные.

Хорошо оптимизируется:

-- Условие на столбец партиционирования в WHERE
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01'
-- ✅ БД сразу выберет только events_2024_03

Плохо оптимизируется:

-- Условие на другой столбец
WHERE user_id = 'abc-123' 
-- ❌ БД придется проверить все партии

-- Функции на столбце партиционирования
WHERE YEAR(created_at) = 2024
WHERE DATE(created_at) >= '2024-03-01'
-- ❌ Функции блокируют pruning

6. Управление партициями — TRUNCATE vs DELETE

Одно из главных преимуществ партиций — быстрое удаление старых данных.

-- Удаление данных старше 1 года
-- С партициями это происходит очень быстро (просто удалить партию)
ALTER TABLE events DETACH PARTITION events_2023_01;
DROP TABLE events_2023_01;  -- Мгновенно!

-- Без партиций пришлось бы делать
DELETE FROM events WHERE created_at < '2023-01-01';
-- Это медленно, сложно, нагружает систему

7. Автоматическое управление партициями

Для таблиц с множеством партиций нужна автоматизация их создания и удаления.

-- Примеры инструментов для автоматизации:
-- 1. pg_partman (расширение для PostgreSQL)
SELECT create_parent(
    'public.events',  -- таблица
    'created_at',     -- столбец партиционирования
    'monthly',        -- интервал (daily, weekly, monthly, yearly)
    p_start_partition => '2024-01-01'
);

-- 2. Триггеры и хранимые процедуры
CREATE OR REPLACE FUNCTION create_monthly_partitions()
RETURNS void AS $$
BEGIN
    -- Создание партиции за следующий месяц
    EXECUTE format(
        'CREATE TABLE events_%s PARTITION OF events FOR VALUES FROM (%L) TO (%L)',
        TO_CHAR(CURRENT_DATE + interval ''1 month'', 'YYYY_MM'),
        DATE_TRUNC(''month'', CURRENT_DATE + interval ''1 month''),
        DATE_TRUNC(''month'', CURRENT_DATE + interval ''2 months'')
    );
END;
$$ LANGUAGE plpgsql;

-- Запуск раз в день (через cron)
SELECT create_monthly_partitions();

8. Партиционирование в аналитических OLAP системах

В хранилищах данных (BigQuery, Snowflake, Redshift) партиционирование еще более критично.

-- BigQuery пример
CREATE OR REPLACE TABLE `project.dataset.events` 
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type
AS
SELECT 
    GENERATE_UUID() as event_id,
    'user_123' as user_id,
    'purchase' as event_type,
    CURRENT_TIMESTAMP() as event_timestamp,
    STRUCT<price FLOAT64, currency STRING>(99.99, 'USD') as properties,
    CURRENT_DATE() as created_at;

-- Это снижает количество данных для сканирования на 90%+
-- Запрос только за 3 дня? Сканирует только 3 дня, а не все 365 дней!

9. Стратегии партиционирования для аналитики

Стратегия 1: По дате события (рекомендуется)

• Партиции по дням, неделям или месяцам
• Подходит для временных рядов данных
• Легко удалять старые данные (retention policy)

Стратегия 2: По иерархии (дата + категория)

PARTITION BY RANGE (created_at) 
SUBPARTITION BY LIST (country)
-- Это позволяет быстро фильтровать по дате И стране

Стратегия 3: По регионам/клиентам (multi-tenant)

PARTITION BY LIST (account_id)
-- Каждый клиент или регион получает свою партию
-- Гарантирует изоляцию данных и быстрые запросы

10. Мониторинг эффективности партиций

Нужно отслеживать, действительно ли партиции помогают.

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

-- План выполнения запроса (должен показать Partition Prune)
EXPLAIN (ANALYZE, BUFFERS) 
SELECT COUNT(*) FROM events 
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';
-- Ищем строку "Partitions: 1" — значит прунинг сработал

11. Практический пример: аналитический дашборд

Дашборд, который показывает события за последние 30 дней, должен работать быстро благодаря партициям.

-- Запрос для дашборда (выполняется быстро с партициями)
WITH daily_metrics AS (
    SELECT
        DATE(event_timestamp) as day,
        event_type,
        COUNT(*) as event_count,
        COUNT(DISTINCT user_id) as dau,
        COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) as payers
    FROM events
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
      AND created_at < CURRENT_DATE
    GROUP BY 1, 2
)
SELECT 
    day,
    event_type,
    event_count,
    dau,
    payers,
    ROUND(100.0 * payers / dau, 2) as conversion_rate
FROM daily_metrics
ORDER BY day DESC, event_type;

-- С партициями: сканирует ~30 партиций
-- Без партиций: сканирует всю таблицу (миллиарды строк)

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

• ✅ Таблицы с > 10GB данных партиционированы по датам
• ✅ Используются условия на столбец партиционирования в WHERE
• ✅ Индексы созданы на критических столбцах внутри партиций
• ✅ Настроено автоматическое удаление старых партиций
• ✅ Мониторится размер и количество партиций
• ✅ Partition Pruning работает (проверено в EXPLAIN)
• ✅ Время запросов улучшилось минимум в 2-3 раза

Заключение

Партиционирование — это критическая техника для Product Analysts, работающих с большими таблицами событий. Правильно настроенное партиционирование может ускорить запросы в 10-100 раз, существенно снизить стоимость хранения и облегчить управление старыми данными. Главное правило: партиционируй по датам событий и всегда добавляй условие на дату в WHERE.