Архівація бази даних: чому довгострокові рішення кращі за короткострокові

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

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

Чому важлива архівація даних

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

Інтенсивність користування системою також прямо пропорційно впливає на об’єм даних. Але не всі дані однаково корисні. Одна справа, коли ми провели платіж і записали інформацію, а через декілька місяців клієнт вирішив зробити рефанд (повернути гроші покупцю). У такому випадку ми раціонально використовуємо дані з першого платежу.

Але, якщо транзакція була проведена більше 2-х років тому, то вона просто «лежить мертвим тягарем». Повернення грошей за такими платежами не проводяться, а уся супутня інформація лише збільшує об’єм даних і створює складнощі в обслуговуванні БД. Це можна помітити зі сторони бекенду та девопсу — уповільнюється обробка запитів, зростає розмір бекапів і час відновлення, збільшується навантаження на диск, виникає потреба у побудові нових індексів та збільшенні інстансів.

Щоб пришвидшити роботу платіжного шлюзу, потрібне логічне рішення ситуації — вилучення застарілих даних і переміщення їх до іншого сховища. Дана стаття саме про архівацію таких даних.

Поширеним рішенням оптимізації БД є використання індексів. Ось тут можна прочитати про степи по бусту продуктивності бази, в тому числі і за допомогою використання індексу.

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

3 кроки до оптимальної архівації БД

Щоб зрозуміти наш шлях, спочатку варто розповісти про ввідні задачі, аналіз ситуації і робочий процес, а вже у наступному розділі буде гайд зі створення самої системи архівації даних.

Варто зауважити, що даний кейс ми вирішуємо для AWS PostgreSQL. Отже, 3 етапи:

1 крок. Технічне завдання: вимоги та обмеження

а) Процес архівації не повинен навантажувати продову БД, яку використовує система. Запити на вибірку та перенос даних не мають впливати на проведення платежів.

б) Фізичний обсяг БД повинен стати меншим. Завдяки цьому зменшуються витрати на зберігання даних, прискорюється перенесення БД, швидше створюються бекапи і зменшується час їх розгортки.

в) Дані старіші 2-х років повинні архівуватися. Періодична архівація запобігає надмірному збільшенню БД.

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

ґ) Потрібна можливість верифікації результатів скрипта архівації. Це важливо, оскільки ми працюємо безпосередньо з продовою БД і помилки неприпустимі.

2 крок. Пошук рішення. Від чого і чому відмовилися

Підхід 1.

Можна зробити воркер, який буде обирати батчами старі дані, а потім вставляти в архівну БД та видаляти з поточної. Але це сумнівний підхід. Чому він не спрацює:

  • постійне навантаження на БД;
  • для зменшення фактичного розміру потрібно робити full vacuum, а це призводить до локів;
  • відсутня можливість перевірки результату роботи воркера (тільки за фактом видалення).

Підхід 2.

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

Ми розділили дані за часом створення транзакції, отримавши 24 таблиці (за умови архівації всіх даних, старіших за два роки). Щоб таким чином архівувати дані, треба витягувати стару таблицю і проводити архівацію. Але в нашому випадку і цей підхід не працює. Ми відмовилися від такого рішення через низку недоліків:

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

3 крок. Рішення, яке задовольняє всі вимоги

Після низки експериментів та брейнштормів ми все ж таки знайшли рішення, яке відповідає всім нашим потребам. Складається воно з таких частин:

1. Підготовка

У першу чергу для роботи нам потрібна репліка продуктової бази даних. Репліку ми використовуємо для того, щоб не зачепити роботу продуктової бази даних.

Далі створюємо нову БД. У майбутньому саме з нею будуть працювати додатки.

В новій БД створюється схема даних і готується тригер. Тригер створюється, щоб розливати вхідні дані на 2 таблиці — операційні дані (2 роки) і архівні дані (більше 2-х років).

Наступним кроком налаштовуємо і запускаємо переливання даних за допомогою DMS від AWS (докладніше про Database Migration Service).

Важливо зауважити, що на першому етапі перенесення даних в нову БД треба вимикати всі наявні тригери, крім новоствореного. Це потрібно, щоб дані переливалися без мутації і сайд ефектів «as is». Коли DMS перейде в режим синхронізації, тригери варто знову увімкнути. Для цього ми використовуємо механізм Postgres — session_replication_role

Коли основний етап переливки даних завершився і таска DMS перейшла в режим синхронізації, приходить час наступного кроку.

2. Архівація

На цьому моменті в нашій новій базі дані вже розділені на операційні і архівні за таблицями. Тому ми переносимо дані з архівних таблиць в окреме сховище і видаляємо архівні таблиці.

3. Перевірка

На цьому етапі нова база даних ідентична до продуктової (за винятком відсутності архівних даних). Зараз саме час в цьому переконатися. Базу можна підключити до окремого нового інстанса додатка, запустити тести і переконатися, що все готово до роботи на продуктовому оточенні.

4. Перемикання

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

Після того, як всі програми почали працювати з новою БД, ми перевіряємо, що більше ніхто не підключений до старої БД. Потім зупиняємо таски DMS і вимикаємо її. Через N часу ми її видаляємо.

Переваги такого рішення:

  • не торкаємося поточної продової БД;
  • маємо час на перевірку нової БД;
  • дропаємо таблиці з застарілими даними;
  • зменшуємо фактичний розмір БД.

В принципі усе відносно просто і саме таке рішення ми вважаємо оптимальним.

Гайд зі створення системи архівації даних

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

Скрипт був написаний на golang з використанням патерну Chain of responsibility. При першому запуску у нас є івент, який містить в собі поточний стан системи. Далі він передається від команди до команди, продукуючи нову базу даних. Створюється актуальна схема, виконуються міграції, вмикається тригер і запускається завдання DMS.

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

  1. Створює нову БД і робить підготовку схеми, запускає DMS таски, чекає переходу переливки в режим доливання та переносить дані в архівну БД.
  2. Перемикає додатки на роботу з новою БД шляхом оновлення енвів і рестарту додатків. Також ця команда видаляє інстанси, що створені на 1-му кроці для запуску DMS.

Між першою та другою командами ми проводимо регресивне тестування додатків при роботі з новою БД. Якщо все ок, то приймаємо рішення перемикатися і запускаємо 2-гу команду.

Все це ми будемо проводити раз на місяць і таким чином не допускати збільшення нашої операційної бази.

Підсумок та порада

Більшість людей не аналізує реальну доцільність зберігання тієї чи іншої інформації у БД. Зростання бази негативно впливає на продуктивність. В результаті постійного додавання апаратних ресурсів ми врешті-решт зіткнемося з низкою проблем: брак коштів; відсутність місця для обслуговування БД; бекапи будуть тривати годинами, тощо. Індекси борються лише з повільними запитами, але не вирішують корінь проблеми.

Тому після всіх протестованих підходів я рекомендую оцінювати архівацію БД як довгострокове рішення. Цей підхід завжди працює і допомагає побудувати ефективну систему для безперебійного скейлінгу продукту.

Дякую за увагу. Буду вдячним, якщо поділитеся в коментах своїми думками по темі.

👍НравитсяПонравилось17
В избранноеВ избранном11
LinkedIn
Допустимые теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Допустимые теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter

Реляционки для больших олтп систем это тупик. Там 2/3 места будут занимать айдишки, которых почти нет в документалках. Хочется обхватить двумя ладонями лицо и спросить за что.
Не хочете документалки, хотябы колоночное.

Бл*
«О очевидных вещах люди думают реже всего» © Макс Вебер

А как стендбай с таким подходом? Каждый раз новый? А если ... , пока он делается с новой работающей и уже принимающей финансовые транзакции базы.

Спасибо, что подняли интересную тему!

Скажите, какой у вас размер базы?
И какой процент данных вы архивируете за один прогон?

Исходный размер бд ~2TB

Сугубо имхо, 2тб не есть vldb чтобы утверждать партиционирование не взлетит.
Рекомендовал б нанять грамотного дба, или дейта архитекта.
Зы Вы умолчали

И какой процент данных вы архивируете за один прогон?

?

Странное решение, держать два инстанса, натравить DMS и утверждать он не создает нагрузку на рабочий инстанс. Потом переключать аппликейшны, получить эффект eventual consistency
Молодцы что запиляли но как то громоздко.

По второму варианту

первинне розподілення даних на партиції — не менш складна задача;

Ну как бы да, только это единоразовая задача а поднимать горы для слива архивных данных — нет

на кожну таблицю буде 25 партицій. Вибірка даних сповільнюється, оскільки потрібно шукати в кожній партиції (ключ партиціювання created_at не бере участі в умовах вибірки);

Да хоть и 1к партиций. Ключ партиции не обязателен быть в выборке. В чем разница таблица партиционирована и не нет. Делаем скен/сик в обоих случаях, такая драматическая разница?

обмеження унікальності працює лише в рамках однієї партиції, що ускладнює підтримку консистентності даних

Можно пример, какой юник констрейнт невозможен на таблице ?

Плюсую тему с партиционированием. Я тоже не совсем понял, почему были откинуты два самых популярных варианта.

Підхід 1.

-постійне навантаження на БД;
-для зменшення фактичного розміру потрібно робити full vacuum, а це призводить до локів;
-відсутня можливість перевірки результату роботи воркера (тільки за фактом видалення).

1. Не факт, что нагрузка будет такой уж значительной. Надо измерять.
2. Зачем уменьшать фактический размер того, что неминуемо вырастет? Достаточно того, что место свободно для новых вставок и переиспользуется приложением.
3. Не совсем понимаю почему работу нельзя проверить.

Підхід 2.

-первинне розподілення даних на партиції — не менш складна задача;
-на кожну таблицю буде 25 партицій. Вибірка даних сповільнюється, оскільки потрібно шукати в кожній партиції (ключ партиціювання created_at не бере участі в умовах вибірки);
-обмеження унікальності працює лише в рамках однієї партиції, що ускладнює підтримку консистентності даних.

1. Да, это правда, но делается один раз.
2. Можно обойти если добавить created_at как часть первичного ключа. (понимаю, что на данном этапе приложения это уже может быть невозможно).
3. см. п. 2

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

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

Цікавий кейс. Зберіг в закладки

А почему не используете шардинг?

В Постгре свого рідного наскільки я пам’ятаю немає. Модно було б самим зробити — таблиця-місяць наприклад.

Пока не вижу, как можно решить задачу свертки бд с помощью шардирования. Можете подробнее описать свою мысль?

У вас изначально проблема какая? Бесконечный рост бд и замедление её работы. Шардинг как раз для этого.
Свертку это вы уже сами себе придумали

шардинг же для скейлинга вширь, а не вглубь. Даже не представляю, как вы предполагаете шардировать по дате.

Таблиця — місяць. ORM чи рівень доступу до даних збирає потрібні SQL запити. Задача чистики вирішується простим видаленням таблиці з БД і словника. Перформанс вирішується тим що оперативні запити йдуть до малих таблиць.
І так, колись я щось подібне реалізував.

Так а зачем для этого шардинг, если здесь как раз партиционирование больше подходит?

Шардинг — это частный случай партицирования. Почитайте матчасть.
Но главное я так и не услышал — чем вам горизонтальное масштабирование не устраивает? Оно же решает ваши проблемы с ростом данных и замедлением работы

Это типа такой способ слиться? Ок, засчитано)

Пьете?
Ну как скажете, МарьИванна. ))
Только последите, кому и на что вы отвечаете.
А за «почитайте матчасть» я бы и в морду дал.

По шардингу я отвечал вам. Вторая часть была для автора поста, переслал уже.
Матчасть перечитать никогда не помешает, а в вашу морду попасть проще будет 😄

Шардинг — это частный случай партицирования. Почитайте матчасть.

Ну, измените в дискуссии шардинг на «физическое партицирование»). Выше же задали вопрос

как вы предполагаете шардировать по дате

На который ответа пока не видно, чтобы утверждать, что

Оно же решает ваши проблемы с ростом данных и замедлением работы

Шардинг удобен, если есть, как бы так правильнее выразиться, деноминатор более высокого уровня, подходящий под весь домен (например, для multi-tenant систем можно шардировать по tenant’у).

Ну и, если следовать Вашему способу коммуникации и отправлять «читать матчасть», хотя все всё понимают, то горизонтальное масштабирование — это не только шардинг, а и реплицирование.

Абсолютно верно. Однако из ответов топик стартера не очевидно, что он всё понимает. Больше похоже на то, что он изобрел очень сложный велосипед вместо того, чтоб использовать горизонтальное масштабирование, которое вероятно решило бы его задачу оптимизации

это явно партиционирование, а не шардинг

Даже не представляю, как вы предполагаете шардировать по дате.

Я такое сделал для продовского Data Warehouse, и как не странно производительность при этом стала максимальной.

Я вам даже верю. N инстансов очевидно дадут прирост производительности по сравнению с одним. Но было ли это подходящим решением? Стоило ли оно того? Не было ли другого более простого и дешевого решения?

Я такое сделал для продовского Data Warehouse

Data Warehouse — всё же частный случай.

Но главное я так и не услышал — чем вам горизонтальное масштабирование не устраивает? Оно же решает ваши проблемы с ростом данных и замедлением работы

А ви хоч архів шифруєте? Якось захищаєте? Бо якщо чесно від прочитаного волосся стоїть дибки...

Але, якщо транзакція була проведена більше 2-х років тому, то вона просто «лежить мертвим тягарем».

Фінтех який не знає терміну retention period і в усій статті нема навіть слова такого «безпека».
Шановні адміни, а можна все таки додати смайлики? Ну хоч фейспалм?

Тобто, за вашою логікою, якщо автор не згадав про безпеку в статті, то про неї не подумали зовсім?

за моєю логікою, а також за вимогами як мінімум PCI DSS БД треба чистити саме з міркувань безпеки. І 2 роки то завеликий retention period.

А можна пруф про PCI DSS вимоги стосовно data retention? В якій версії стандарту це описано?

Кхгм... Я так розумію що питання про версію це понтами постукати бо є одна версія дійсна зараз — 3.2.1. В якій є requirement 3.1:

3.1 Keep cardholder data storage to a minimum by implementing data retention and disposal policies, procedures and processes that include at least the following for all cardholder data (CHD) storage:
• Limiting data storage amount and retention time to that which is required for legal, regulatory, and/or
business requirements

• Specific retention requirements for cardholder data

• Processes for secure deletion of data when no longer needed

• A quarterly process for identifying and securely deleting stored cardholder data that exceeds defined retention.

Не розумію, про які ви там «понти», але з цієї цитати я можу зробити будь-який висновок про будь-який retention period — все залежить від вимог бізнесу. Також зауважу, що в статті не йде мова про cardholder data (принаймі про це ніде явно не сказано). Тому 2 роки — це ні добре, ні погано, а так як того вимагає бізнес. І це ніяк не протиречить стандарту PCI DSS.

А хіба я написав що PCI DSS вимагає менше? Я сказав що 2 роки то забагато на мій погляд і думаю на погляд будь якого нормального аудитора. В статті згадано що в БД інформації про фінансові транзакції і навіть якщо там не CHD в чистому вигляді такі дані також дуже чутливі.

Добре, то дайте будь ласка визначення терміну «нормальний аудитор», щоб вже закрити питання.

Ох... ну хоча б такі що мають сертифікації CISSP, CISM, CISA і працюють в західних компаніях з відповідними ліцензіями. Я з такими два рази на рік спілкуюсь.

Можливо, ви зплутали БД з логами?

Ох... Ні, не переплутав. З логами навпаки є вимога мінімального терміну зберігання і доступності.

10.7 Retain audit trail history for at least one year, with a minimum of three months immediately available for analysis (for example, online, archived, or restorable from backup).

Дякую за статтю.

Питання:
1. допустимо на 3 кроці знайшли невідповідність, тоді робота команди припиняється? архів зроблений на 2 кроці дропаєм, фіксимо багу, і запускаємо все з кроку 1? можливо є сенс кроки 2 і 3 поміняти місцями?
2. чи часто буває що на 3 кроці знаходяться помилки? з чим як правило пов"язані?

1 Нам важливо отримати апрув що нова бд ідентична і готова до роботи. Після тестування не бажано проводити якісь додаткові операції, які можуть вплинути на бд. Самі архівні дані можуть не відразу потрапляти в архівну БД, можна зливати дампами, розміщувати у тимчасовому сховищі, можна додатково обробляти їх, якщо є в цьому необхідність. Це вже все на ваш розсуд.
Можна для спокою запускати тести і до і після архівації.
2 В момент налагодження зловили цікавий кейс з тригерами, але ця проблема елегантно вирішилась за допомогою session_replication_role. В Основному проблеми можуть бути пов’язані з невідповідністю схеми продуктової і нової бд.

Подписаться на комментарии