Управление данными с помощью разделения PostgreSQL и pg_partman

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

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

Введение

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

Зачем разделение?

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

Нативное разделение vs разделение на основе расширений

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

Нативное разделение vs pg_partman

Типы разделения таблиц в PostgreSQL

PostgreSQL поддерживает три основные стратегии разделения: Диапазон, Список и Хэш. Каждая из них имеет уникальные характеристики, подходящие для различных случаев использования.

Разделение по диапазону

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

Пример: Данные о ежемесячных продажах

CREATE TABLE sales (

    sale_id SERIAL,

    sale_date DATE NOT NULL,

    amount NUMERIC

) PARTITION BY RANGE (sale_date);

​

CREATE TABLE sales_2023_01 PARTITION OF sales

    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

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

• Эффективно для данных временных рядов, таких как логи или транзакции
• Поддерживает последовательные запросы, такие как извлечение данных за конкретные месяцы

Недостатки

• Требует заранее определенных диапазонов, что может привести к частым обновлениям схемы

Разделение по списку

Разделение по списку делит данные на основе дискретного набора значений, таких как регионы или категории.

Пример: Региональные заказы

CREATE TABLE orders (

    order_id SERIAL,

    region TEXT NOT NULL,

    amount NUMERIC

) PARTITION BY LIST (region);

​

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US');

CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('EU');

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

• Идеально подходит для наборов данных с конечным числом категорий (например, регионов, отделов)
• Прост в управлении для фиксированного набора разделов

Недостатки

• Не подходит для динамичных или расширяющихся категорий

Хэшированное разделение

Хэшированное разделение распределяет строки по набору разделов с использованием хэш-функции. Это обеспечивает равномерное распределение данных.

Пример: Учетные записи пользователей

CREATE TABLE users (

    user_id SERIAL,

    username TEXT NOT NULL

) PARTITION BY HASH (user_id);

​

CREATE TABLE users_partition_0 PARTITION OF users

    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

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

• Обеспечивает сбалансированное распределение по разделам, предотвращая горячие точки
• Подходит для равномерно распределенных рабочих нагрузок

Недостатки

• Не читаем для человека; разделы не могут быть определены интуитивно

pg_partman: Подробное руководство

pg_partman – это расширение PostgreSQL, упрощающее управление разделами, особенно для данных на основе времени и серийных номеров.

Установка и настройка

pg_partman требует установки как расширения в PostgreSQL. Он предоставляет набор функций для создания и управления разделенными таблицами динамически.

1. Установите с помощью вашего менеджера пакетов:
   Shell

   sudo apt-get install postgresql-pg-partman

2. Создайте расширение в вашей базе данных:
   SQL

    

   СОЗДАТЬ РАСШИРЕНИЕ pg_partman;

Настройка разделения

pg_partman поддерживает разделение по времени и по порядковым номерам, что особенно полезно для наборов данных с временными данными или последовательными идентификаторами.

Пример разделения по времени

CREATE TABLE logs (

    id SERIAL,

    log_time TIMESTAMP NOT NULL,

    message TEXT

);

​

SELECT partman.create_parent(

    p_parent_table := 'public.logs',

    p_control := 'log_time',

    p_type := 'time',

    p_interval := 'daily'

);

Эта конфигурация:

• Автоматически создает ежедневные разделы
• Упрощает запросы и обслуживание для данных журналов

Пример разделения по порядковым номерам

CREATE TABLE transactions (

    transaction_id BIGSERIAL PRIMARY KEY,

    details TEXT NOT NULL

);

​

SELECT partman.create_parent(

    p_parent_table := 'public.transactions',

    p_control := 'transaction_id',

    p_type := 'serial',

    p_interval := 100000

);

Это создает разделы каждые 100 000 строк, обеспечивая управляемость родительской таблицы.

Функции автоматизации

Автоматическое обслуживание

Используйте run_maintenance(), чтобы гарантировать предварительное создание будущих разделов:

SELECT partman.run_maintenance();

Политики удержания

Определите периоды удержания для автоматического удаления старых разделов:

UPDATE partman.part_config

SET retention = '12 months'

WHERE parent_table = 'public.logs';

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

1. Упрощает динамическое создание разделов
2. Автоматизирует очистку и обслуживание
3. Снижает необходимость в ручных обновлениях схемы

Практические примеры использования разделения таблиц

1. Управление журналами. Журналы высокой частоты разделены по дням для удобного архивирования и запросов.
2. Многорегиональные данные. Системы электронной коммерции делят заказы по регионам для улучшенной масштабируемости.
3. Данные временных рядов. Приложения Интернета вещей с разделенными данными телеметрии.

Управление журналами

Разделяйте журналы по дням или месяцам для эффективного управления данными с высокой частотой.

SELECT partman.create_parent(

    p_parent_table := 'public.server_logs',

    p_control := 'timestamp',

    p_type := 'time',

    p_interval := 'monthly'

);

Мульти-региональные данные

Разделяйте данные о продажах или запасах по регионам для лучшей масштабируемости.

CREATE TABLE sales (

    sale_id SERIAL,

    region TEXT NOT NULL

) PARTITION BY LIST (region);

Транзакции с высоким объемом

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

SELECT partman.create_parent(

    p_parent_table := 'public.transactions',

    p_control := 'transaction_id',

    p_type := 'serial',

    p_interval := 10000

);

Заключение

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

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