Базы данных

PostgreSQL и партиционирование таблиц: стратегии для хранения временных рядов и больших данных

Ruslan Ismailov Опубликовано 18 мин чтения
P

Введение: когда партиционирование необходимо и когда оно вредит

Партиционирование таблиц в PostgreSQL — один из самых мощных инструментов для работы с большими объёмами данных. Однако его применение оправдано далеко не всегда. Прежде чем принимать решение, важно понять контекст.

Партиционирование нужно, когда:

  • Таблица содержит сотни миллионов и более строк, и запросы всегда фильтруют данные по диапазону (время, регион, категория).

  • Необходимо регулярно удалять устаревшие данные — например, хранить только последние 90 дней логов.

  • Разные части таблицы имеют разную «температуру» доступа: свежие данные читаются часто, старые — редко или никогда.

  • VACUUM и ANALYZE на монолитной таблице занимают слишком много времени и блокируют работу.

Партиционирование вредит, когда:

  • Таблица небольшая (до 10–50 миллионов строк при типичной нагрузке) — оверхед планировщика перевесит выгоду.

  • Запросы не фильтруют данные по ключу партиционирования — планировщик будет сканировать все партиции (partition fan-out).

  • Приложение активно использует ON CONFLICT (UPSERT) — в партиционированных таблицах это работает с ограничениями.

  • Требуются глобальные уникальные индексы, не включающие ключ партиционирования.

В этой статье мы рассмотрим продвинутые стратегии партиционирования в PostgreSQL 15/16 для хранения временных рядов, событийных логов и аналитических данных, разберём интеграцию с Laravel и сравним реальную производительность.

Виды партиционирования в PostgreSQL: RANGE, LIST, HASH

PostgreSQL поддерживает три основных стратегии декларативного партиционирования, введённого в версии 10 и существенно доработанного в версиях 11–16.

RANGE — партиционирование по диапазону

Наиболее распространённый подход для временных рядов. Каждая партиция хранит строки, у которых значение ключа попадает в определённый диапазон.

CREATE TABLE events (
 id BIGSERIAL,
 occurred_at TIMESTAMPTZ NOT NULL,
 user_id BIGINT,
 event_type VARCHAR(64),
 payload JSONB
) PARTITION BY RANGE (occurred_at);

Сценарии применения RANGE: временные ряды (метрики, логи, транзакции), данные с естественной временно́й или числовой прогрессией, таблицы с политикой retention по дате.

LIST — партиционирование по списку значений

Каждая партиция содержит строки с конкретными значениями ключа из заранее определённого списка.

CREATE TABLE orders (
 id BIGSERIAL,
 region VARCHAR(32) NOT NULL,
 created_at TIMESTAMPTZ,
 amount NUMERIC(12,2)
) PARTITION BY LIST (region);

CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('EU', 'UK', 'DE');
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US', 'CA');
CREATE TABLE orders_apac PARTITION OF orders FOR VALUES IN ('JP', 'AU', 'SG');

Сценарии применения LIST: мультитенантные системы (partition per tenant), географическое шардирование, данные с небольшим числом дискретных категорий.

HASH — партиционирование по хешу

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

CREATE TABLE user_activity (
 user_id BIGINT NOT NULL,
 activity_at TIMESTAMPTZ,
 action VARCHAR(128)
) PARTITION BY HASH (user_id);

CREATE TABLE user_activity_0 PARTITION OF user_activity
 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_activity_1 PARTITION OF user_activity
 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- и т.д.

Сравнение стратегий

  • RANGE: лучший выбор для временных рядов, эффективный partition pruning по диапазону дат, простое удаление старых партиций.

  • LIST: эффективен при фильтрации по категории, но требует заранее известного набора значений; плохо масштабируется при большом количестве уникальных значений.

  • HASH: равномерное распределение данных, но partition pruning работает только при точном равенстве ключа, нет простого способа архивирования.

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

Рассмотрим полноценный пример создания партиционированной таблицы событий с ежемесячными партициями.

-- Создаём родительскую таблицу
CREATE TABLE events (
 id BIGSERIAL,
 occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 user_id BIGINT NOT NULL,
 event_type VARCHAR(64) NOT NULL,
 session_id UUID,
 payload JSONB,
 PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

-- Создаём партиции вручную
CREATE TABLE events_2025_01 PARTITION OF events
 FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

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

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

-- DEFAULT-партиция для данных вне диапазонов
CREATE TABLE events_default PARTITION OF events DEFAULT;

Важный момент про PRIMARY KEY: в партиционированных таблицах первичный ключ обязан включать ключ партиционирования. Это ограничение PostgreSQL, связанное с отсутствием глобальных уникальных индексов.

Теперь создадим индексы на каждой партиции (или на родительской таблице — начиная с PostgreSQL 11 индексы на родителе автоматически распространяются на дочерние):

-- Индекс на родительской таблице (PostgreSQL 11+)
CREATE INDEX idx_events_user_time ON events (user_id, occurred_at DESC);
CREATE INDEX idx_events_type ON events (event_type, occurred_at DESC);

Автоматическое создание партиций: pg_partman и ручная автоматизация

pg_partman — расширение для управления партициями

pg_partman — это расширение PostgreSQL, которое автоматизирует создание и удаление партиций по расписанию. Оно поддерживает RANGE-партиционирование по времени и числовым диапазонам.

-- Установка расширения
CREATE EXTENSION pg_partman SCHEMA partman;

-- Настройка автоматического управления партициями
SELECT partman.create_parent(
 p_parent_table => 'public.events',
 p_control => 'occurred_at',
 p_type => 'native',
 p_interval => 'monthly',
 p_premake => 3 -- создавать 3 партиции вперёд
);

-- Обновление конфигурации
UPDATE partman.part_config
SET retention = '12 months',
 retention_keep_table = false,
 infinite_time_partitions = true
WHERE parent_table = 'public.events';

-- Запуск обслуживания (обычно через cron или pg_cron)
SELECT partman.run_maintenance();

pg_partman интегрируется с pg_cron для полностью автоматического цикла жизни партиций:

SELECT cron.schedule('partman-maintenance', '0 * * * *',
 'SELECT partman.run_maintenance(p_analyze := false)');

Ручная автоматизация через PL/pgSQL

Если установка расширений ограничена (например, managed PostgreSQL в облаке), можно реализовать автоматизацию вручную:

CREATE OR REPLACE FUNCTION create_monthly_partition(
 p_table TEXT,
 p_date DATE
) RETURNS VOID AS $$
DECLARE
 partition_name TEXT;
 start_date DATE;
 end_date DATE;
BEGIN
 start_date := DATE_TRUNC('month', p_date)::DATE;
 end_date := (start_date + INTERVAL '1 month')::DATE;
 partition_name := p_table || '_' || TO_CHAR(start_date, 'YYYY_MM');

 EXECUTE FORMAT(
 'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
 FOR VALUES FROM (%L) TO (%L)',
 partition_name, p_table, start_date, end_date
 );

 RAISE NOTICE 'Created partition: %', partition_name;
END;
$$ LANGUAGE plpgsql;

-- Создать партиции на 3 месяца вперёд
SELECT create_monthly_partition('events', (NOW() + (i || ' months')::INTERVAL)::DATE)
FROM GENERATE_SERIES(0, 2) AS i;

Partition Pruning: как планировщик PostgreSQL использует партиции

Partition pruning — это механизм, при котором планировщик запросов исключает из плана выполнения нерелевантные партиции на основе условий WHERE. Это ключевой фактор производительности партиционированных таблиц.

PostgreSQL поддерживает два уровня pruning:

  • Static pruning — исключение партиций на этапе планирования (когда значения в WHERE известны на момент разбора запроса).

  • Dynamic pruning — исключение партиций во время выполнения (для параметризованных запросов, subplans, nested loops).

Проверка через EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT COUNT(*), event_type
FROM events
WHERE occurred_at BETWEEN '2025-03-01' AND '2025-03-31'
GROUP BY event_type;

Пример вывода с эффективным pruning:

HashAggregate (cost=8420.50..8421.00 rows=50 width=40)
 (actual time=45.123..45.198 rows=47 loops=1)
 Buffers: shared hit=3841
 -> Append (cost=0.00..7980.00 rows=176000 width=32)
 Subplans Removed: 11 -- <-- 11 партиций исключены!
 -> Seq Scan on events_2025_03
 (cost=0.00..3240.00 rows=176000 width=32)
 Filter: ((occurred_at >= '2025-03-01') AND
 (occurred_at < '2025-04-01'))
 Planning Time: 2.341 ms
 Execution Time: 45.891 ms

Строка Subplans Removed: 11 означает, что 11 из 12 партиций были исключены планировщиком.

Важно для partition pruning:

  • Условие WHERE должно напрямую использовать ключ партиционирования.

  • Нельзя оборачивать ключ в функции: DATE_TRUNC('month', occurred_at) = '2025-03-01' — pruning не сработает. Используйте явные диапазоны: occurred_at >= '2025-03-01' AND occurred_at < '2025-04-01'.

  • Параметр enable_partition_pruning должен быть включён (по умолчанию ON в PostgreSQL 11+).

SHOW enable_partition_pruning; -- on
SET enable_partition_pruning = on;

Индексы на партиционированных таблицах: локальные vs глобальные, partial indexes

В PostgreSQL 11+ при создании индекса на родительской таблице он автоматически создаётся на всех дочерних партициях. Это локальные индексы — каждая партиция имеет собственный B-tree.

Локальные индексы

-- Индекс на родителе автоматически создаёт индексы на всех партициях
CREATE INDEX CONCURRENTLY idx_events_user_occurred
ON events (user_id, occurred_at DESC);

-- Проверить индексы на дочерних таблицах
SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE tablename LIKE 'events_%'
ORDER BY tablename, indexname;

Ограничения глобальных индексов

До PostgreSQL 17 глобальные уникальные индексы (охватывающие все партиции) не поддерживаются, если ключ уникальности не включает ключ партиционирования. Это принципиальное ограничение. В PostgreSQL 17 планируется поддержка глобальных индексов — следите за обновлениями.

Partial indexes на партициях

Partial (частичные) индексы особенно эффективны на партиционированных таблицах — они индексируют только подмножество строк, снижая размер индекса и ускоряя запросы по «горячим» данным:

-- Partial index только для активных событий на конкретной партиции
CREATE INDEX idx_events_2025_03_errors
ON events_2025_03 (user_id, occurred_at)
WHERE event_type = 'error';

-- Partial index для незавершённых транзакций
CREATE INDEX idx_events_pending
ON events_2025_03 (occurred_at, user_id)
WHERE payload->>'status' = 'pending';

BRIN-индексы для временных рядов

Для таблиц с коррелированными данными (временны́е ряды, где строки физически упорядочены по времени) BRIN-индексы занимают минимум места при сохранении эффективности:

CREATE INDEX idx_events_occurred_brin
ON events USING BRIN (occurred_at)
WITH (pages_per_range = 64);

BRIN-индекс для таблицы в 100 миллионов строк занимает единицы мегабайт против гигабайт для B-tree.

Архивирование и удаление старых партиций: стратегии retention

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

Detach и Drop

-- Быстрое удаление старой партиции (мгновенно, минимальная блокировка)
DROP TABLE events_2024_01;

-- Или: отсоединить партицию без удаления (для архивирования)
ALTER TABLE events
 DETACH PARTITION events_2024_01 CONCURRENTLY; -- PostgreSQL 14+

-- После detach таблица существует как обычная таблица
-- Можно перенести на другой tablespace, сжать, сделать dump
ALTER TABLE events_2024_01 SET TABLESPACE archive_tablespace;

Ключевое преимущество: DROP TABLE на партиции выполняется за миллисекунды независимо от количества строк, тогда как DELETE FROM events WHERE occurred_at < '2024-02-01' на 100 миллионах строк занимает часы и генерирует огромный WAL.

Стратегия retention с автоматизацией

CREATE OR REPLACE FUNCTION drop_old_partitions(
 p_table TEXT,
 p_retention_months INT DEFAULT 12
) RETURNS INT AS $$
DECLARE
 rec RECORD;
 dropped INT := 0;
 cutoff DATE;
BEGIN
 cutoff := DATE_TRUNC('month',
 NOW() - (p_retention_months || ' months')::INTERVAL)::DATE;

 FOR rec IN
 SELECT child.relname AS partition_name
 FROM pg_inherits
 JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
 JOIN pg_class child ON pg_inherits.inhrelid = child.oid
 WHERE parent.relname = p_table
 AND child.relname ~ ('^' || p_table || '_\d{4}_\d{2}$')
 LOOP
 -- Извлечь дату из имени партиции
 IF TO_DATE(
 REGEXP_REPLACE(rec.partition_name,
 '^.*_(\d{4})_(\d{2})$', '\1-\2-01'),
 'YYYY-MM-DD'
 ) < cutoff THEN
 EXECUTE 'DROP TABLE ' || QUOTE_IDENT(rec.partition_name);
 dropped := dropped + 1;
 RAISE NOTICE 'Dropped partition: %', rec.partition_name;
 END IF;
 END LOOP;

 RETURN dropped;
END;
$$ LANGUAGE plpgsql;

-- Использование
SELECT drop_old_partitions('events', 12); -- хранить 12 месяцев

Tablespace-стратегия для холодных данных

Вместо удаления старые партиции можно перенести на более медленные (и дешёвые) диски или в объектное хранилище через расширения типа pg_tiering:

-- Создать tablespace на медленном диске / NFS
CREATE TABLESPACE cold_storage
 LOCATION '/mnt/cold-data/pg';

-- Перенести партицию
ALTER TABLE events_2024_01 SET TABLESPACE cold_storage;

Интеграция с Laravel: работа с партиционированными таблицами

Laravel и Eloquent хорошо работают с партиционированными таблицами PostgreSQL — с точки зрения ORM таблица выглядит как обычная. Однако есть ряд нюансов.

Миграции

Schema Builder Laravel не поддерживает синтаксис PARTITION BY, поэтому миграцию нужно писать через raw SQL:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
 public function up(): void
 {
 // Создаём партиционированную таблицу
 DB::statement('CREATE TABLE events (
 id BIGSERIAL,
 occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 user_id BIGINT NOT NULL,
 event_type VARCHAR(64) NOT NULL,
 payload JSONB,
 PRIMARY KEY (id, occurred_at)
 ) PARTITION BY RANGE (occurred_at)');

 // Создаём начальные партиции
 DB::statement("CREATE TABLE events_2025_01
 PARTITION OF events
 FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')");

 DB::statement("CREATE TABLE events_default
 PARTITION OF events DEFAULT");

 // Создаём индексы
 DB::statement('CREATE INDEX idx_events_user_time
 ON events (user_id, occurred_at DESC)');
 }

 public function down(): void
 {
 DB::statement('DROP TABLE IF EXISTS events CASCADE');
 }
};

Eloquent Model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;

class Event extends Model
{
 protected $table = 'events';
 protected $primaryKey = 'id';
 public $timestamps = false;

 protected $casts = [
 'occurred_at' => 'datetime',
 'payload' => 'array',
 ];

 protected $fillable = [
 'occurred_at', 'user_id', 'event_type', 'session_id', 'payload',
 ];

 // Scope для эффективного использования partition pruning
 public function scopeInPeriod(Builder $query, string $from, string $to): Builder
 {
 // Важно: использовать whereBetween или явные операторы,
 // НЕ оборачивать в функции типа DATE_TRUNC
 return $query
 ->where('occurred_at', '>=', $from)
 ->where('occurred_at', '<', $to);
 }

 public function scopeForUser(Builder $query, int $userId): Builder
 {
 return $query->where('user_id', $userId);
 }
}

Эффективные запросы через Eloquent

<?php

// Хороший запрос — partition pruning сработает
$events = Event::inPeriod('2025-03-01', '2025-04-01')
 ->forUser($userId)
 ->select(['id', 'occurred_at', 'event_type'])
 ->orderBy('occurred_at', 'desc')
 ->limit(100)
 ->get();

// Агрегация с pruning
$stats = Event::inPeriod('2025-03-01', '2025-04-01')
 ->selectRaw('event_type, COUNT(*) as cnt, DATE(occurred_at) as day')
 ->groupBy('event_type', 'day')
 ->orderBy('day', 'desc')
 ->get();

// Raw query для сложной аналитики
$result = DB::select("
 SELECT
 DATE_TRUNC('hour', occurred_at) AS hour,
 event_type,
 COUNT(*) AS count,
 COUNT(DISTINCT user_id) AS unique_users
 FROM events
 WHERE occurred_at >= ? AND occurred_at < ?
 AND event_type = ANY(?)
 GROUP BY 1, 2
 ORDER BY 1 DESC
", [
 '2025-03-01',
 '2025-04-01',
 '{purchase,signup,error}'
]);

Кеширование результатов через Redis

Для аналитических запросов по партиционированным таблицам эффективно использовать Redis в качестве кеша результатов — особенно для исторических данных, которые уже не изменяются:

<?php

use Illuminate\Support\Facades\Cache;

public function getHourlyStats(string $date): array
{
 $cacheKey = "events:hourly:{$date}";

 // Исторические данные кешируем надолго
 $ttl = Carbon::parse($date)->isPast() ? 86400 * 7 : 300;

 return Cache::store('redis')->remember($cacheKey, $ttl, function () use ($date) {
 return DB::select("
 SELECT DATE_TRUNC('hour', occurred_at) AS hour,
 COUNT(*) AS total
 FROM events
 WHERE occurred_at >= ?::DATE
 AND occurred_at < (?::DATE + INTERVAL '1 day')
 GROUP BY 1 ORDER BY 1
 ", [$date, $date]);
 });
}

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

Бенчмарки: реальные цифры до и после партиционирования

Приведём результаты тестирования на таблице событий с 500 миллионами строк (PostgreSQL 16, 32 CPU, 128 GB RAM, NVMe SSD, shared_buffers = 32GB).

Тест 1: Агрегация за один месяц (из 24 месяцев данных)

SELECT event_type, COUNT(*)
FROM events
WHERE occurred_at BETWEEN '2025-03-01' AND '2025-03-31'
GROUP BY event_type;

  • Без партиционирования: 47.3 секунды (Seq Scan, 500M строк)

  • С RANGE-партиционированием (месяц): 1.2 секунды (Seq Scan только партиции events_2025_03, ~21M строк)

  • С партиционированием + B-tree индекс: 0.18 секунды (Index Scan)

  • Ускорение: ~260x

Тест 2: INSERT производительность

-- Пакетная вставка 1 миллиона строк
INSERT INTO events (occurred_at, user_id, event_type, payload)
SELECT
 NOW() - (RANDOM() * INTERVAL '30 days'),
 (RANDOM() * 1000000)::BIGINT,
 (ARRAY['click','view','purchase','error'])[CEIL(RANDOM()*4)::INT],
 '{"v": 1}'::JSONB
FROM GENERATE_SERIES(1, 1000000);

  • Без партиционирования: 8.4 секунды

  • С партиционированием (данные в 1 партицию): 9.1 секунды (+8% оверхед)

  • С партиционированием (данные в 30 партиций): 11.3 секунды (+35% оверхед)

Вывод: партиционирование немного замедляет INSERT из-за оверхеда на маршрутизацию строк. Для высоконагруженных систем рекомендуется писать напрямую в конкретную партицию или использовать COPY.

Тест 3: Удаление данных за месяц

  • DELETE FROM events WHERE occurred_at < '2024-02-01': 38 минут, 12 GB WAL

  • DROP TABLE events_2024_01: 0.003 секунды, минимальный WAL

  • Ускорение: >760000x

Тест 4: Размер индексов

  • B-tree на монолитной таблице (500M строк): 18.4 GB

  • Сумма B-tree на 24 партициях: 18.9 GB (идентично)

  • BRIN на монолитной таблице: 47 MB

  • BRIN на партиционированной таблице: 52 MB

BRIN-индексы дают колоссальную экономию при минимальной потере производительности для последовательных временных запросов.

Заключение

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

Ключевые выводы:

  • Используйте RANGE-партиционирование по времени для логов, метрик и событийных данных — это наиболее распространённый и хорошо оптимизированный сценарий в PostgreSQL.

  • Включайте ключ партиционирования во все критичные запросы, иначе планировщик выполнит полное сканирование всех партиций.

  • pg_partman + pg_cron — стандарт де-факто для автоматизации lifecycle партиций в production.

  • BRIN-индексы на временны́х рядах экономят на порядки больше места по сравнению с B-tree при сопоставимой производительности для range-запросов.

  • Интеграция с Laravel прозрачна — используйте raw migrations и Eloquent scopes, явно передающие условия по ключу партиционирования.

  • Redis идеально дополняет партиционированные таблицы для кеширования агрегатов по историческим, неизменяемым партициям.

В условиях роста данных в 2026 году грамотное партиционирование в PostgreSQL остаётся одним из наиболее cost-effective решений — перед тем как переходить к более сложным архитектурам (шардирование, Citus, TimescaleDB), убедитесь, что возможности нативного партиционирования исчерпаны.

Технологии

Теги

Руслан Исмаилов

Senior Web / Backend разработчик. Senior web/backend разработчик с 9-летним опытом. Стек: PHP, Laravel, PostgreSQL, Redis, Docker, Kubernetes, REST, микросервисы, CI/CD. Подробнее обо мне →