Пошук уроків, статей та іншого контенту
Розділення великої таблиці на менші частини для покращення продуктивності та керування даними
Партиціювання — це розділення однієї логічної таблиці на кілька фізичних таблиць, які називаються партиціями.
Для застосунку така структура виглядає як одна таблиця:
SELECT *
FROM measurements;А PostgreSQL фактично зберігає дані в окремих партиціях, наприклад:
measurements_2025_01;
measurements_2025_02;
measurements_2025_03.
Партиціювання корисне, коли таблиця містить дуже багато рядків і дані природно можна розділити за певною ознакою:
датою;
регіоном;
категорією;
діапазоном числових значень.
Основні переваги:
PostgreSQL може читати лише потрібні партиції;
старі дані можна видаляти або архівувати окремими партиціями;
обслуговування великих обсягів даних стає простішим;
індекси кожної партиції можуть бути меншими за індекс усієї таблиці.
Водночас партиціювання не є універсальним способом прискорити будь-який запит. Найбільша користь з’являється тоді, коли умови запиту використовують ключ партиціювання.
У PostgreSQL спочатку створюють батьківську таблицю із зазначенням стратегії партиціювання:
CREATE TABLE table_name (
column_name data_type
) PARTITION BY RANGE (column_name);Батьківська таблиця описує структуру даних, але рядки зберігаються в її партиціях.
Під час вставки PostgreSQL сам визначає, до якої партиції належить рядок:
INSERT INTO table_name (...)
VALUES (...);Для вибірки можна звертатися до батьківської таблиці:
SELECT *
FROM table_name;У цьому випадку PostgreSQL аналізує умови запиту й за можливості виключає непотрібні партиції. Цей механізм називається partition pruning — відсікання партицій.
PostgreSQL підтримує три основні стратегії.
Партиції визначають діапазони значень. Це найпоширеніший варіант для дат.
Наприклад:
січень 2025 року;
лютий 2025 року;
березень 2025 року.
CREATE TABLE events (
id bigint,
occurred_at timestamptz NOT NULL,
event_type text NOT NULL
) PARTITION BY RANGE (occurred_at);Партиції визначають конкретні значення або списки значень.
Цей варіант підходить, наприклад, для регіонів:
CREATE TABLE customers (
id bigint,
region text NOT NULL
) PARTITION BY LIST (region);PostgreSQL розподіляє рядки між партиціями за хешем ключа.
Цей варіант підходить, коли потрібно рівномірно розподілити дані, але немає природних діапазонів:
CREATE TABLE user_events (
id bigint,
user_id bigint NOT NULL
) PARTITION BY HASH (user_id);Для HASH-партиціювання зазвичай визначають кількість партицій і номер залишку:
CREATE TABLE user_events_0
PARTITION OF user_events
FOR VALUES WITH (MODULUS 4, REMAINDER 0);Розглянемо повний приклад для таблиці подій. Дані розділятимуться за місяцями.
DROP TABLE IF EXISTS events CASCADE;
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
occurred_at timestamptz NOT NULL,
event_type text NOT NULL,
payload jsonb NOT NULL DEFAULT '{}'::jsonb
) PARTITION BY RANGE (occurred_at);
CREATE TABLE events_2025_01
PARTITION OF events
FOR VALUES FROM ('2025-01-01 00:00:00+00')
TO ('2025-02-01 00:00:00+00');
CREATE TABLE events_2025_02
PARTITION OF events
FOR VALUES FROM ('2025-02-01 00:00:00+00')
TO ('2025-03-01 00:00:00+00');
CREATE TABLE events_2025_03
PARTITION OF events
FOR VALUES FROM ('2025-03-01 00:00:00+00')
TO ('2025-04-01 00:00:00+00');
INSERT INTO events (occurred_at, event_type, payload)
VALUES
('2025-01-10 12:00:00+00', 'login', '{"user_id": 10}'),
('2025-01-20 08:30:00+00', 'purchase', '{"amount": 49.99}'),
('2025-02-05 15:45:00+00', 'login', '{"user_id": 11}'),
('2025-03-12 09:15:00+00', 'logout', '{"user_id": 10}');
SELECT tableoid::regclass AS actual_partition,
id,
occurred_at,
event_type
FROM events
ORDER BY occurred_at;У стовпці actual_partition буде видно фізичну партицію, у якій зберігається кожен рядок.
Межі діапазонів мають нижню включну та верхню невключну межу:
FROM ('2025-01-01') TO ('2025-02-01')Це означає:
дата 2025-01-01 належить партиції;
дата 2025-01-31 належить партиції;
дата 2025-02-01 вже не належить цій партиції.
Такий підхід запобігає перекриттю сусідніх місячних діапазонів.
Запит із фільтром за ключем партиціювання може працювати лише з потрібною партицією:
EXPLAIN
SELECT *
FROM events
WHERE occurred_at >= '2025-02-01 00:00:00+00'
AND occurred_at < '2025-03-01 00:00:00+00';У плані виконання PostgreSQL зазвичай покаже звернення лише до events_2025_02.
Натомість запит без умови за occurred_at має перевірити всі доступні партиції:
EXPLAIN
SELECT *
FROM events
WHERE event_type = 'login';Тому ключ партиціювання потрібно вибирати з урахуванням типових запитів. Якщо більшість запитів фільтрує дані за датою, дата часто є хорошим ключем.
Індекс, створений на батьківській таблиці, створює відповідні індекси на партиціях:
CREATE INDEX events_occurred_at_idx
ON events (occurred_at);
CREATE INDEX events_event_type_idx
ON events (event_type);Такі індекси підтримують структуру партиційної таблиці. Для кожної партиції фактично існує окремий індекс.
Можна також створити індекс безпосередньо на конкретній партиції:
CREATE INDEX events_2025_02_payload_user_id_idx
ON events_2025_02 ((payload ->> 'user_id'));У більшості випадків індекси варто створювати на батьківській таблиці, щоб однакова структура застосовувалася до всіх партицій.
Якщо вставити рядок, для якого не існує відповідної партиції, PostgreSQL поверне помилку:
INSERT INTO events (occurred_at, event_type)
VALUES ('2025-04-10 10:00:00+00', 'login');Для тимчасового зберігання таких рядків можна створити партицію DEFAULT:
CREATE TABLE events_default
PARTITION OF events DEFAULT;Тепер рядки за межами визначених діапазонів потраплятимуть до events_default.
Це зручно для захисту від помилок під час вставки, але потребує контролю. Якщо пізніше створюється партиція для нового діапазону, PostgreSQL перевіряє, чи немає в DEFAULT рядків, які мають належати новій партиції.
Наприклад:
CREATE TABLE events_2025_04
PARTITION OF events
FOR VALUES FROM ('2025-04-01 00:00:00+00')
TO ('2025-05-01 00:00:00+00');Якщо в events_default уже є квітневі рядки, така операція може завершитися помилкою. Перед створенням нової партиції ці рядки потрібно перемістити.
Партиції можна додавати, від’єднувати та видаляти незалежно від інших.
CREATE TABLE events_2025_04
PARTITION OF events
FOR VALUES FROM ('2025-04-01 00:00:00+00')
TO ('2025-05-01 00:00:00+00');DROP TABLE events_2025_01;Видалення партиції видаляє всі дані, які в ній зберігаються. Перед такою операцією потрібно переконатися, що дані більше не потрібні.
Іноді потрібно перестати керувати таблицею як партицією, але зберегти її дані:
ALTER TABLE events
DETACH PARTITION events_2025_01;Після цього events_2025_01 залишається звичайною таблицею, а запити до events більше не читають її дані.
Це може бути корисно для архівування або окремого обслуговування старих даних.
За допомогою LIST можна створити окремі партиції для груп значень:
DROP TABLE IF EXISTS orders CASCADE;
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY,
region text NOT NULL,
created_at timestamptz NOT NULL,
total numeric(12, 2) NOT NULL
) PARTITION BY LIST (region);
CREATE TABLE orders_eu
PARTITION OF orders
FOR VALUES IN ('PL', 'DE', 'FR');
CREATE TABLE orders_us
PARTITION OF orders
FOR VALUES IN ('US', 'CA');
CREATE TABLE orders_other
PARTITION OF orders DEFAULT;
INSERT INTO orders (region, created_at, total)
VALUES
('PL', now(), 100.00),
('US', now(), 250.00),
('UA', now(), 75.00);
SELECT tableoid::regclass AS actual_partition,
id,
region,
total
FROM orders
ORDER BY id;У цьому прикладі:
PL, DE і FR потрапляють до orders_eu;
US і CA потрапляють до orders_us;
інші значення потрапляють до orders_other.
Партиціювання впливає на проєктування обмежень.
Первинний ключ або унікальне обмеження на партиційній таблиці повинні містити ключ партиціювання. Наприклад, для таблиці, розділеної за occurred_at, така конструкція не відповідає цій вимозі:
CREATE TABLE events (
id bigint PRIMARY KEY,
occurred_at timestamptz NOT NULL
) PARTITION BY RANGE (occurred_at);Унікальність id не можна гарантувати незалежно в кожній партиції, якщо ключ партиціювання не входить до обмеження. Варіант, що відповідає вимозі:
CREATE TABLE events (
id bigint,
occurred_at timestamptz NOT NULL,
PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);Зовнішні ключі та інші обмеження також потрібно перевіряти для конкретної версії PostgreSQL і моделі даних. Партиціювання не скасовує необхідність явно проєктувати цілісність даних.
Хороший ключ партиціювання має такі властивості:
часто використовується у фільтрах;
має зрозумілий розподіл значень;
дозволяє визначити межі або групи партицій;
не створює надто багато маленьких партицій.
Для журналів, подій і транзакцій часто використовують дату або час. Розмір періоду залежить від обсягу даних:
рік — для невеликої кількості даних;
місяць — для великих журналів;
день — для дуже великих потоків даних.
Надмірна кількість партицій також шкодить продуктивності: планувальнику потрібно аналізувати більше об’єктів, а адміністрування стає складнішим.
Якщо не створити партицію для нового періоду й не визначити DEFAULT, вставка завершиться помилкою.
Рішення:
створювати майбутні партиції заздалегідь;
або використовувати DEFAULT і регулярно його перевіряти.
Якщо таблицю розділено за датою, але запити фільтрують лише за іншим стовпцем, PostgreSQL може звертатися до всіх партицій.
Рішення — аналізувати реальні запити та перевіряти їх за допомогою EXPLAIN.
Для RANGE важливо уважно визначати межі:
FROM ('2025-01-01') TO ('2025-02-01')
FROM ('2025-02-01') TO ('2025-03-01')Межа 2025-02-01 належить лише другій партиції.
Створення окремої партиції для кожного дня або кожного значення може ускладнити планування запитів і обслуговування.
Розмір партицій потрібно визначати на основі обсягу даних і способу їх використання.
Партиціювання не замінює індекси. Якщо запити фільтрують або сортують дані за стовпцем, який не є ключем партиціювання, для нього може знадобитися індекс.
Команда:
DELETE FROM events
WHERE occurred_at < '2025-01-01';може працювати довше, ніж видалення старої партиції, оскільки видаляє рядки окремо.
Якщо дані вже розділені за періодами, для повного видалення періоду краще видалити або від’єднати відповідну партицію.
Перед створенням партиційної таблиці варто:
Визначити, чи справді таблиця достатньо велика для партиціювання.
Проаналізувати запити та знайти спільний стовпець для фільтрації.
Вибрати стратегію RANGE, LIST або HASH.
Визначити розумний розмір партицій.
Створити майбутні партиції.
Передбачити поведінку для значень поза відомими діапазонами.
Додати необхідні індекси.
Перевірити плани запитів через EXPLAIN.
Визначити процедуру архівування та видалення старих партицій.
Партиціювання розділяє одну логічну таблицю на кілька фізичних партицій.
Декларативне партиціювання створюється через PARTITION BY.
RANGE добре підходить для дат і числових діапазонів.
LIST підходить для окремих категорій або груп значень.
HASH рівномірно розподіляє рядки між партиціями.
PostgreSQL може пропускати непотрібні партиції під час виконання запиту.
Для ефективного відсікання запит має містити умову за ключем партиціювання.
Партиції спрощують видалення та архівування великих періодів даних.
Потрібно заздалегідь планувати межі партицій, індекси та обмеження цілісності.