Як ми змігрували 300 мільйонів рядків у PostgreSQL

Привіт, мене звати Олександр Прокопович, я Go Engineer у Solidgate — українській продуктовій фінтех-компанії, що працює в ніші пейментів і допомагає розбудовувати платіжну інфраструктуру для інтернет-бізнесів.

Ця стаття була написана як ретроспектива нашого досвіду мігрування великої кількості даних. Ми дослідимо різні підходи до міграції, вимоги, типи даних, а також розкажемо, як ми з’ясували, що UPDATE даних суттєво складніший за INSERT чи DELETE. Якщо ви плануєте проводити великі міграції даних або хочете поглибити свої знання в PostgreSQL — цей матеріал для вас.

Передумови

Під час мажорного рефакторингу одного з наших сервісів, ми помітили деякі неоптимальні патерни доступу до даних нашої бази. Розглянемо приклад, який імітує поведінку, що у нас була. Існує три сутності — user, transaction і transaction_details. Сутність деталей транзакції прилінкована до транзакції, а транзакція до користувача. Тож, за наявності деталей транзакції, щоб дістати з бази дані про користувача, потрібно спочатку з деталей дістати транзакцію, і лише тоді з транзакції взяти user_id.

Для нас оптимізація подібних патернів доступу суттєво спрощувала життя — замість трьох запитів могли робити лише один. Тому вирішили денормалізувати дані деяких таблиць з метою підвищення швидкості доступу до ряду сутностей. Наша міграція оновлювала багато полів, але задля спрощення розглядатимемо в прикладах запитів лише оновлення поля user_id в таблиці transaction_details шляхом перенесення його значення з таблиці transactions.

Короткий опис того, що ми мали, і якими були наші вимоги до міграції:

• База, в якій проводилася міграція, знаходилася під постійним навантаженням і обслуговувала live-трафік ~20rps.

• Важливо було збалансувати навантаження на базу, аби уникнути downtime критичних ендпоїнтів, які обробляють платежі. Водночас ми не мали жорстких часових обмежень виконання міграції (допоки міграція не потребувала використання робочого часу інших інженерів). Швидке рішення було для нас краще за те, що було б ідеальним рішенням, але при цьому залучало б більший людський ресурс.
• У нас не було змоги апргейднути, а потім даунгрейнднути базу, але ми додали максимальну кількість provisioned IOPS для нашого диска.
• Поля, які ми планували оновити (user_id), модифікувалися в target-table (transaction_details) лише під час створення рядка і потім лишалися незмінними, а в origin-table (transactions) були іммутабельними, тож нам не потрібно було хвилюватися за data race.
• Zero data loss — ми не могли зупинити запис даних, змігрувати їх і потім знову запустити міграцію.

Підготовка

1. Перш за все ми почали заповнювати новостворені поля даними. Таким чином зафіксувавли загальну кількість рядків усіх таблиць, які нам потрібно було оновити на позначці близько 320 мільйонів, з найбільшою таблицею, яка містила 140 мільйонів записів.
2. Деякі конфігураційні дані, які нам були потрібні, лежали в іншій базі. Оскільки ці дані були іммутабельні, а нові записи нам були нецікаві (бо вони вже автоматично донасичувалися даними), для прискорення міграції ми повністю скопіювали потрібну таблицю з однієї бази в іншу.

Дослідження підходів до міграції

AWS DMS

Ми використовуємо AWS як cloud-provider для наших сервісів. Одним із продуктів AWS є сервіс для міграції даних між базами — DMS (Data Migration Service). AWS DMS дозволяє зробити міграцію між гомогенними базами — Full Load and CDC, яка полягає в копіюванні основного чанку даних у базі й наступного застосування всіх змін, які відбулися в базі через CDC. Під час цієї міграції можна налаштувати тригери, які модифікуватимуть дані — у результаті ми отримаємо готову копію початкової бази, але з усіма потрібними змінами.

Переваги:

• швидкість виконання скрипта міграції;
• якщо в планах є апгрейд бази, можна одночасно виконати і апгрейд, і міграцію, зекономивши час.

Недоліки:

• потрібно ретельно протестувати всі компоненти, оскільки висока ймовірність втрати даних на якомусь з етапів або ж навпаки наявності даних після міграції, що мали б бути видалені в початковій таблиці;
• якщо ми захочемо оновити таблиці в декілька ітерацій, то кожна міграція DMS залучатиме суттєву кількість ресурсу інженерів інфраструктурної команди, який у нас обмежений;
• підготувати міграцію всіх таблиць разом важче, оскільки у випадку якоїсь помилки під час міграції доведеться починати її повністю з початку.

Own wheel-reinvented DMS

Якщо підхід із міграцією основного чанка і дозаповненням даних через CDC працює, але при цьому апгрейдити базу не потрібно, то навіщо тоді DMS, якщо можна виконати ті самі операції над наявною базою? Розглянемо приклад, як могла б виглядати така міграція:

-- запамʼятовуємо latest_updated_at;
SELECT updated_at FROM origin_table ORDER BY updated_at DESC LIMIT 1;
-- копіюємо таблицю або наступним способом, або через pg_dump/pg_restore, тут відбуваються всі наші модифікації даних;
CREATE TABLE origin_table_copy FROM (SELECT * FROM origin_table);
-- створюємо індекс на id
-- створємо тригер на insert/update/delete який писатиме з origin_table в origin_table_copy;
-- інкрементально відновлюємо всі змінені дані;
UPDATE origin_table_copy SET x=y, ... WHERE updated_at BETWEEN latest_updated_at AND latest_updated_at + (10 * interval '1 minute');
-- відновлюємо всі констрейнти, створюємо індекси, рестартуємо sequences;
-- перемикаємо трафік між таблицями

Переваги:

• швидкість виконання скрипта міграції;
• легше реплікувати середовище проведення міграції, і, як наслідок, легше протестувати окремі компоненти, ніж у DMS;
• менше залученості інфраструктурних інженерів.
• немає проблем з окремою міграцією таблиці.

Недоліки:

• водночас компонентів, які потрібно протестувати, суттєво більше, ніж у DMS. Також значно складніша логіка самої міграції, відповідно — більше шансів на помилку.

Batch Updates

Ідея в оновленні даних батчами полягає в тому, що дає змогу швидко проводити міграцію за умови вільного ресурсу бази та добре оптимізованого запиту. Зокрема, цей спосіб найпростіший із погляду overhead на інженерів, оскільки він інтуїтивний у розумінні процесу міграції. А значить сприяє недопущенню помилок.

Спочатку ми спробували доволі тривіальний і прямолінійний підхід — в одній sql-транзакції агрегувати всі дані та одразу оновлювати таргетну таблицю:

CREATE INDEX tx_details_user_id_null_idx
    ON transaction_details (id)
    WHERE user_id IS NULL

UPDATE transaction_details td
SET user_id = cte.user_id
FROM (SELECT t.user_id, td.id
      FROM transactions t
  JOIN transaction_details td ON td.transaction_id=t.id
      WHERE td.user_id IS NULL
      LIMIT _limit
) AS cte
WHERE td.id = cte.id;

Це працювало дуже повільно, швидкість була приблизно 3k rows/min. Нескладна математика підказує — у такому темпі міграція однієї таблиці зайняла б близько 50 днів (за умови, що ми вимикали б міграцію на ніч). Перша проблема запиту вище в тому, що ми витрачаємо час на виконання JOIN. Щоб уникнути цього, створили допоміжну таблицю, в яку додали преагреговані дані:

CREATE TABLE tmp AS
SELECT ROW_NUMBER() OVER() AS row_id,
       td.id AS td_id,
       t.id AS t_id
FROM transaction_details td
JOIN transactions t ON td.transaction_id = t.id
WHERE td.user_id IS NULL;

CREATE INDEX tmp_idx ON tmp(row_id);
-- і відповідно міграція:
UPDATE transaction_details td
SET user_id = COALESCE(td.user_id, cte.user_id)
FROM (SELECT user_id,
             td_id
      FROM tmp
      WHERE row_id > 200000 AND row_id <= 210000) AS cte
WHERE td.td_id = cte.td_id;

Друга проблема, з якою ми зіткнулися, була менш очевидною, але не менш вагомою. Річ у тім, що індекс tx_details_user_id_null_idx не пришвидшує, а сповільнює UPDATE, оскільки при оновленні рядків таблиці цей індекс також потрібно оновлювати. Відтак переваги швидкого читання таблиці нівелюються повільним оновленням індексу. Тому ми його дропнули. Тепер оновлення даних відбувалося зі швидкістю ~5k rows/min, проте ця швидкість нас все ще не влаштовувала.

Ми спробували ще декілька доволі сміливих ідей: підчищати dead tuples вакуумом після кожної ітерації (це майже ні на що не вплинуло), погратися з налаштуваннями постгресу temp_buffers, work_mem, effective_cache_size (також мінімум імпакту). Врешті-решт, через декілька ітерацій змін ми прийшли до фінальної версії, швидкість якої нас задовольняла — 30k rows/min. Але про все по черзі.

Спочатку створюємо таблицю, в якій агрегуємо всі потрібні нам дані:

-- пре-агреговуємо дані, потрібні для апдейту
CREATE TABLE tmp AS
SELECT td.id         AS td_id,
       t.user_id         AS user_id,
       td.updated_at AS updated_at
FROM transaction_details td
JOIN transactions t ON t.id = td.transaction_id
WHERE td.user_id IS NULL;
-- створюємо індекс, аби швидко вичитувати перші N рекордів і потім дропати їх
CREATE INDEX tmp_idx ON tmp (updated_at, td_id) INCLUDE (user_id)

Ми зʼясували, що важливий аспект — це сортування індексу по updated_at. Під час оновлення даних наш ідеальний сценарій — читати всі дані з таблиці, які будуть оновлені, в тій послідовності, в якій вони лежать на диску. Це дає змогу зменшити overhead на нього. В PostgreSQL операція UPDATE — це INSERT+DELETE, тому при апдейті нові версії рядків можуть бути записані в інший регіон памʼяті, ніж поточні. Як наслідок — ймовірність того, що рядки з близьким значенням updated_at лежатимуть близько, вище за будь-які два довільних рядки, відсортовані за UUID.

Варто звернути увагу, що це має бути саме updated_at, а не created_at. Якщо fillfactor таблиці — 100% (default value), то апдейти даних із високою ймовірністю потраплятимуть на іншу table page, і дані, що лежать близько, ризикують знаходитися далеко один від одного після створення. Також ми робили спроби сортувати за ctid, втім, як не дивно, це виявилося значно повільнішим за updated_at.

При оновленні даних оновлюються й індекси, які референсять стару версію цих даних. Відтак, якщо є змога, доцільно дропнути якомога більше індексів таблиці, яка оновлюється, і відновити їх після міграції. Ми помітили приблизно 10%-30% покращення у швидкості UPDATE операції після видалення декількох застарілих індексів.

Для прикладу швидкість виконання запитів на клоні з та без жодного індексу:

Щоб зменшити overhead апдейтів, MVCC може використовувати HOT (Heap-Only-Tuples) оптимізацію. Для цього потрібно, аби апдейт відповідав певним умовам:

• апдейт не модифікує колонки, які використовуються в індексах таблиці;
• на table page, яка містить стару версію рядка, достатньо місця для нової версії рядка.

За другий пункт відповідає fillfactor — тому якщо в роботі з базою переважає update-heavy workload, доцільно поміркувати про створення таблиць із fillfactor <70-90%. Це покращить швидкість оновлення даних і під час міграції.

Наша міграція виглядала наступним чином:

DO $$
    DECLARE
        _id int := 0;
        _rowsLimit INT := 3000;
        _updatedTotal INT := 0;
        _updatedInBatch INT;
        start_time timestamp := CLOCK_TIMESTAMP();
        update_time INT;
        execution_time INT;
    BEGIN
        -- це налаштування нам потрібно було для того аби вимкнути тригери на таргетній таблиці
        SET session_replication_role='replica';

        LOOP
            RAISE NOTICE 'Started Iteration: %', _id;
            -- ці рядки з cte можна було записати в змінну і перевикористати під час delete-операції, але ми вчасно не звернули на це увагу, бо delete-операції було доволі далеко до нашого bottleneck-у.
            WITH cte AS (SELECT td_id, user_id
                         FROM tmp
                         ORDER BY updated_at
                         LIMIT _rowsLimit
            )
            UPDATE transaction_details td
            SET user_id = COALESCE(td.user_id, cte.user_id)
            FROM cte
            WHERE td.id = cte.td_id;

            COMMIT;

            GET DIAGNOSTICS _updatedInBatch = ROW_COUNT;
            _updatedTotal := _updatedTotal + _updatedInBatch;
            update_time := EXTRACT(EPOCH FROM clock_timestamp() - start_time);
            RAISE NOTICE 'UPDATE executed time: % sec.', update_time;

            WITH cte AS (SELECT td_id, updated_at
                         FROM tmp
                         ORDER BY updated_at
                         LIMIT _rowsLimit
            )
            DELETE FROM tmp t
                USING cte
            -- порівняння по `updated_at` потрібно для того аби підхопився індекс
            WHERE t.updated_at = cte.updated_at
              AND t.td_id = cte.td_id;

            execution_time := EXTRACT(EPOCH FROM clock_timestamp() - start_time);
            RAISE NOTICE 'Finished Iteration: %, updated total: %, ALL time: % sec.', _id, _updatedTotal, execution_time;

            COMMIT;

            IF _updatedInBatch = _rowsLimit THEN
                PERFORM pg_sleep(0.5);
            ELSE
                RAISE NOTICE 'All IDs were updated. Exit.';
                EXIT;
            END IF;

            _id := _id+1;
        END LOOP;
    END $$;

Тестування

Query planner враховує стан таблиці й бази, тому відповідно будь-який аналіз запитів має виконуватися на реальних даних у продовій базі. Оскільки на проді завжди є ризик щось зламати, ми підняли повноцінний клон нашого прода і виконували дослідження всіх гіпотез саме на ньому.

Крім того, ми протестували фрагменти міграції через функціональні тести в коді, аби переконатися, що вони виконують саме те, що нам потрібно. Перед початком міграції на проді ми запустили її на наших тестових середовищах і верифікували, що дані мігруються правильно.

Моніторинг та Performance Tuning

Під час міграції важливо памʼятати про domain-specific data access patterns.

• Чи характерні для сервісів, які працюють з базою спайки трафіку? Якщо так, потрібно поміркувати над зменшенням швидкості міграції даних.
• Чи трапляються вночі синхронізації даних, які створюють додаткове навантаження на базу? Можливо, варто вимикати міграцію на ніч.
• Як та коли відбувається вивантаження даних для DWH чи кешу і чи може міграція вплинути на це? У нас була певна логіка CDC, побудована на тригерах — її довелося вимкнути, аби не покласти сервіси, що нею користуються (кожного дня оновлювалося приблизно 20 мільйонів рядків).
• За скільки буде виконуватися апдейт одного батчу? Оскільки ми оновлюємо в батчі тисячі значень, кожен рядок буде залоканий FOR UPDATE. Чи може це негативно вплинути на конкурентні запити?
• Чи є сутності, які користуються базою/таблицею, даунтайм яких напряму впливає на виручку компанії? Для нас такими сутностями були ендпоїнти, які відповідають за створення платежів. Під час міграції важливо визначати такі чутливі місця і відстежувати, чи не падають запити по таймауту.
• Хто і як моніторить процес міграції та хто сидить біля kill switch скрипта на випадок загрози інциденту?
• Після міграції утвориться значна кількість dead tuples і гарною практикою буде їх підчистити за допомогою VACUUM ANALYZE.

Висновки

З часом може виникнути потреба в донасичуванні старих даних новими, але з ростом бази даних ця задача стає дедалі нетривіальнішою. Increased query latency, disk degradation, data loss/data races — всі ці проблеми постають перед інженерами в іншому світлі, коли мова заходить про велику кількість даних, а для виконання звичних маніпуляцій потрібно залучати все більш креативні підходи та виділяти більше часу на підготовку.

Важливо ретельніше та прискіпливіше проводити дослідження того, як ви плануєте проводити міграцію — починаючи від того, який скрипт запускатимете, до того, на які показники зможете дивитися, якщо сервіси, залежні від бази, почнуть деградувати.

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті