Пошук уроків, статей та іншого контенту
Визначайте, коли контрольоване дублювання даних виправдане заради продуктивності читання.
Денормалізація — це навмисне дублювання або попереднє обчислення даних у базі, щоб пришвидшити читання.
У нормалізованій схемі дані зберігаються без зайвих повторів. Наприклад:
клієнт зберігається в customers;
замовлення — в orders;
позиції замовлення — в order_items.
Щоб отримати статистику клієнтів, потрібно об’єднати кілька таблиць і виконати агрегацію:
SELECT
c.id,
c.email,
COUNT(o.id) AS orders_count,
COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.email;Якщо такий запит виконується часто, а таблиці містять мільйони рядків, його виконання може бути дорогим. Денормалізація дозволяє зберегти результат або його частину окремо, щоб читати вже підготовлені дані.
Ціна цього підходу — складніше оновлення та ризик отримати застарілі або неузгоджені дані.
Контрольоване дублювання варто розглядати, якщо:
читання відбуваються значно частіше за записи;
один і той самий складний запит виконується регулярно;
агрегація обробляє велику кількість рядків;
допустима затримка оновлення даних;
потрібен стабільний час відповіді для звітів або дашбордів;
запит має чіткий і стабільний шаблон.
Прикладами можуть бути:
щоденна статистика продажів;
кількість замовлень і загальна сума витрат клієнта;
підсумки за категоріями товарів;
готова структура даних для адміністративної панелі.
Денормалізація зазвичай не є першим кроком оптимізації. Спочатку варто:
перевірити план виконання через EXPLAIN (ANALYZE, BUFFERS);
створити потрібні індекси;
перевірити умови з’єднання та фільтрації;
зменшити обсяг даних, які обробляє запит;
оцінити частоту виконання запиту.
Якщо після цього запит залишається вузьким місцем, можна розглядати денормалізацію.
У PostgreSQL матеріалізоване представлення (MATERIALIZED VIEW) зберігає результат запиту фізично.
На відміну від звичайного представлення, яке виконує запит під час кожного читання, матеріалізоване представлення читає вже збережений результат.
Нижче наведено повний приклад, який можна виконати в PostgreSQL:
DROP MATERIALIZED VIEW IF EXISTS customer_order_stats;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id),
total_amount numeric(12, 2) NOT NULL CHECK (total_amount >= 0),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id),
product_name text NOT NULL,
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0)
);
CREATE INDEX orders_customer_id_idx
ON orders (customer_id);
INSERT INTO customers (email)
VALUES
('anna@example.com'),
('bohdan@example.com'),
('iryna@example.com');
INSERT INTO orders (customer_id, total_amount, created_at)
VALUES
(1, 1250.00, '2026-08-01 10:00:00+00'),
(1, 300.00, '2026-08-05 12:30:00+00'),
(2, 800.00, '2026-08-03 09:15:00+00');
INSERT INTO order_items (
order_id,
product_name,
quantity,
unit_price
)
VALUES
(1, 'Keyboard', 1, 1000.00),
(1, 'Cable', 1, 250.00),
(2, 'Mouse', 1, 300.00),
(3, 'Monitor', 1, 800.00);
CREATE MATERIALIZED VIEW customer_order_stats AS
SELECT
c.id AS customer_id,
c.email,
COUNT(o.id)::integer AS orders_count,
COALESCE(SUM(o.total_amount), 0)::numeric(12, 2) AS total_spent,
MAX(o.created_at) AS last_order_at
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.email;
CREATE UNIQUE INDEX customer_order_stats_customer_id_idx
ON customer_order_stats (customer_id);
SELECT
customer_id,
email,
orders_count,
total_spent,
last_order_at
FROM customer_order_stats
ORDER BY total_spent DESC;Результат містить уже обчислені значення:
кількість замовлень;
загальну суму покупок;
дату останнього замовлення.
Читання з матеріалізованого представлення простіше:
SELECT *
FROM customer_order_stats
WHERE total_spent >= 500
ORDER BY total_spent DESC;У цьому запиті більше не потрібно щоразу об’єднувати customers та orders і виконувати GROUP BY.
Матеріалізоване представлення не оновлюється автоматично після зміни базових таблиць.
Після додавання нового замовлення статистика залишиться старою:
INSERT INTO orders (customer_id, total_amount)
VALUES (3, 450.00);
SELECT *
FROM customer_order_stats
WHERE customer_id = 3;Щоб перебудувати дані, потрібно виконати:
REFRESH MATERIALIZED VIEW customer_order_stats;Після цього нове замовлення з’явиться у статистиці.
REFRESH MATERIALIZED VIEW customer_order_stats;Під час такого оновлення матеріалізоване представлення може бути недоступним для читання. Це часто прийнятно для нічних звітів або внутрішньої статистики.
Якщо потрібно дозволити читання під час оновлення, можна використати:
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_order_stats;Для цього матеріалізоване представлення має мати унікальний індекс, який охоплює всі рядки однозначно. Саме тому в прикладі створено:
CREATE UNIQUE INDEX customer_order_stats_customer_id_idx
ON customer_order_stats (customer_id);REFRESH MATERIALIZED VIEW CONCURRENTLY потрібно виконувати окремою командою, не всередині явного блоку транзакції.
Стратегія залежить від вимог до актуальності даних.
Підходить для:
аналітичних звітів;
статистики за день;
адміністративних сторінок;
даних, яким допустимо бути застарілими кілька хвилин або годин.
Перевага — просте обслуговування.
Недолік — читач може побачити не найновіший результат.
Замість періодичної перебудови можна підтримувати окрему таблицю підсумків одночасно з основною операцією запису.
Наприклад, під час створення замовлення можна в тій самій транзакції оновити:
кількість замовлень клієнта;
загальну суму покупок;
дату останнього замовлення.
Перевага — дані майже завжди актуальні.
Недоліки:
кожен запис стає складнішим;
зростає навантаження на запис;
потрібно правильно обробляти зміну або видалення замовлення;
помилка в логіці оновлення може порушити узгодженість.
Додаток може накопичувати зміни, а окремий процес — періодично оновлювати денормалізовані дані.
Це компроміс між миттєвою актуальністю та продуктивністю запису. У такій моделі потрібно явно визначити допустиму затримку, наприклад: статистика може відставати не більше ніж на одну хвилину.
Дублювати можна не лише результат агрегації, а й окремі значення.
Наприклад, у orders можна зберігати копію електронної адреси клієнта:
ALTER TABLE orders
ADD COLUMN customer_email_snapshot text;Під час створення замовлення туди записується поточна адреса клієнта.
Це може бути виправдано не лише продуктивністю. Для історичних документів часто потрібно зберегти дані такими, якими вони були на момент операції. Якщо клієнт пізніше змінить email, старе замовлення все одно має містити адресу, яка використовувалася під час покупки.
Важливо розрізняти:
поточне значення — має відповідати рядку в customers;
історичний знімок — навмисно не змінюється після створення замовлення.
Назва customer_email_snapshot допомагає явно показати цю семантику.
Інший приклад — збереження підсумку замовлення в orders.total_amount, хоча його можна обчислити з order_items. У такому разі застосунок або база даних повинні гарантувати, що підсумок перераховується під час кожної зміни позицій.
До і після зміни потрібно порівняти реальні запити:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
c.id,
c.email,
COUNT(o.id) AS orders_count,
COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.email;Після створення матеріалізованого представлення:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
customer_id,
email,
orders_count,
total_spent
FROM customer_order_stats
ORDER BY total_spent DESC;Порівнювати потрібно не лише час одного запуску, а й:
кількість операцій читання;
обсяг даних, який обробляється;
частоту виконання;
навантаження на запис;
час оновлення денормалізованих даних;
допустимість застарілих результатів.
Денормалізація виправдана, якщо загальна система стає кращою, а не лише один запит.
Для кожного дубльованого значення потрібно заздалегідь визначити:
де зберігається джерело істини;
хто оновлює копію;
коли саме вона оновлюється;
наскільки довго копія може бути застарілою;
як перевірити та відновити узгодженість.
Наприклад, для customer_order_stats:
джерелом істини є customers і orders;
копією є матеріалізоване представлення;
оновлення виконується через REFRESH MATERIALIZED VIEW;
допустима застарілість залежить від вимог звіту;
відновлення виконується повторним оновленням представлення.
Корисно також періодично порівнювати денормалізовані дані з повторно обчисленим результатом. Це допомагає виявити помилки в процесі оновлення.
Дублювання даних саме по собі не гарантує прискорення. Якщо запит і так виконується швидко, додаткова складність не виправдана.
Якщо незрозуміло, яка копія правильна, оновлення швидко призведе до суперечливих значень.
Якщо підсумок змінюється тільки після INSERT, але не після UPDATE або DELETE, він поступово стане неправильним.
Користувачі повинні знати, чи статистика є актуальною на поточний момент, чи відображає стан кілька хвилин тому.
Кожна копія збільшує:
обсяг збережених даних;
кількість логіки оновлення;
кількість сценаріїв для тестування;
ризик розходження значень.
Денормалізована структура теж може потребувати індексів. У прикладі унікальний індекс на customer_id потрібен і для пошуку, і для конкурентного оновлення матеріалізованого представлення.
Денормалізація — це контрольоване дублювання або попереднє обчислення даних.
Її застосовують переважно для пришвидшення частих і дорогих операцій читання.
У PostgreSQL матеріалізоване представлення зберігає результат запиту фізично.
Матеріалізовані представлення потрібно оновлювати, тому вони можуть містити застарілі дані.
Денормалізація окремих колонок може мати історичний сенс, наприклад для збереження знімка адреси клієнта.
Перед денормалізацією потрібно виміряти проблему та перевірити індекси й план виконання.
Для кожної копії слід визначити джерело істини, спосіб оновлення та допустиму затримку.
Якщо вартість підтримки копії більша за виграш у продуктивності, денормалізація не виправдана.