Мікросервісна архітектура, як і все у цьому світі, має свої плюси та свої мінуси. Одні процеси з нею стають простішими, інші — складнішими. І для швидкості змін і кращої масштабованості потрібно приносити свої жертви. Одна з них – ускладнення аналітики. Якщо в моноліті всю оперативну аналітику можна звести до SQL запитів до аналітичної репліки, то мультисервісної архітектурі в кожного сервісу своя база і, здається, одним запитом не обійтися (а може обійтися?). Для тих, кому цікаво, як ми вирішили проблему оперативної аналітики у себе в компанії та як навчилися жити із цим рішенням – welcome.

Мене звуть Павло Сиваш, у Будинку Кліку я працюю в команді, яка відповідає за супровід аналітичного сховища даних. Умовно нашу діяльність можна віднести до дати інженерії, але, насправді, спектр завдань набагато ширший. Є стандартні для дата інженерії ETL/ELT, підтримка та адаптація інструментів для аналізу даних та розробка своїх інструментів. Зокрема, для оперативної звітності ми вирішили «удати», що в нас моноліт і дати аналітикам одну базу, в якій будуть усі необхідні їм дані.

Взагалі ми розглядали різні варіанти. Можна було збудувати повноцінне сховище — ми навіть пробували, але, якщо чесно, так і не вдалося подружити досить часті зміни в логіці з досить повільним процесом побудови сховища та внесення до нього змін (якщо комусь вийшло, напишіть у коментарях як). Можна було сказати аналітикам: «Хлопці, навчайте python і ходіть в аналітичні репліки», але це додаткова вимога до підбору персоналу, і здавалося, що цього варто уникнути, якщо це можливо. Вирішили спробувати використовувати технологію FDW (Foreign Data Wrapper): по суті це стандартний dblink, який є в стандарті SQL, але зі своїм набагато зручнішим інтерфейсом. На базі її ми зробили рішення, яке зрештою і прижилося, на ньому ми зупинилися. Його подробиці — тема окремої статті, а може й не однієї, оскільки розповісти хочеться багато про що: від синхронізації схем баз до управління доступом та знеособлення персональних даних. Також слід зазначити, що це рішення не є заміною реальним аналітичним базам і сховищам, воно вирішує лише конкретне завдання.

Верхньорівнево це виглядає так:

Є база PostgreSQL, там користувачі можуть зберігати свої робочі дані, а найважливіше – до цієї бази через FDW підключено аналітичні репліки всіх сервісів. Це дає можливість написати запит до кількох баз, причому неважливо, що це: PostgreSQL, MySQL, MongoDB або ще щось (файл, API, якщо раптом немає враппера, можна написати свій). Ну начебто все, супер! Розходимося?

Якби все закінчувалося так швидко і просто, то, напевно, статті не було б.

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

Простий запит та план з ним

Щоб показати як постгрес виконує запит до таблиці на 6 мільйонів рядків на віддаленому сервері, подивимося на простий план.

explain analyze verbose  
SELECT count(1)
FROM fdw_schema.table;

Aggregate  (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table
Planning time: 0.986 ms
Execution time: 3857.436 ms

Використання інструкції VERBOSE дозволяє побачити запит, який буде надіслано на віддалений сервер і результати якого ми отримаємо для подальшої обробки (рядок RemoteSQL).

Підемо трохи далі і додамо до нашого запиту кілька фільтрів: один по boolean полю, один за входженням відмітка часу в інтервал та один по jsonb.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 5046843
        Remote SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Planning time: 0.665 ms
Execution time: 27474.118 ms

Саме тут і криється момент, на який необхідно звертати увагу при написанні запитів. Фільтри не передалися на віддалений сервер, а це означає, що для його виконання постгрес витягує всі 6 мільйонів рядків, щоб вже потім локально відфільтрувати (рядок Filter) і зробити агрегацію. Запорука успіху – це написати запит так, щоб фільтри передавалися на віддалену машину, а ми отримували та агрегували лише потрібні рядки.

That's some booleanshit

З boolean полями – все просто. У вихідному запиті проблема виникала через оператора is. Якщо замінити його на =, то ми отримаємо наступний результат:

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=508010.14..508010.15 rows=1 width=8) (actual time=19064.314..19064.314 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..507988.44 rows=8679 width=0) (actual time=33.035..18951.278 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 3567989
        Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planning time: 0.834 ms
Execution time: 19064.534 ms

Як бачите, фільтр відлетів на віддалений сервер, а час виконання скоротився з 27 до 19 секунд.

Варто зазначити, що оператор is відрізняється від оператора = тим, що вміє працювати із значенням Null. Це означає, що is not True у фільтрі залишить значення False та Null, тоді як != True залишить лише значення False. Тому при заміні оператора НЕ слід передавати у фільтр дві умови з оператором OR, наприклад, WHERE (col! = True) OR (col is null).

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

timestamptz? hz

Взагалі часто доводиться експериментувати з тим, як правильно написати запит, в якому беруть участь віддалені сервери, а вже потім шукати пояснення, чому відбувається саме так. Дуже мало інформації щодо цього можна знайти в Інтернеті. Так, в експериментах ми виявили, що фільтр за фіксованою датою відлітає на віддалений сервер на ура, а коли ми хочемо задати дату динамічно, наприклад, now() або CURRENT_DATE, такого не відбувається. У нашому прикладі ми додали такий фільтр, щоб стовпець created_at містив у собі дані рівно за 1 місяць у минулому (BETWEEN CURRENT_DATE - INTERVAL '7 month' AND CURRENT_DATE - INTERVAL '6 month'). Що ж ми зробили в цьому випадку?

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta->>'source' = 'test';

Aggregate  (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
        Rows Removed by Filter: 76934
        Remote SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Planning time: 0.703 ms
Execution time: 4789.379 ms

Ми підказали планувальнику заздалегідь обчислити дату в підзапиті і передати готову змінну у фільтр. І ця підказка дала нам чудовий результат, запит став швидшим майже в 6 разів!

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

Повернемо фільтр за датою у вихідне значення.

Freddy vs. Jsonb

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

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=1100.00..245459.90 rows=1478 width=0) (actual time=16.213..6634.794 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 619961
        Remote SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.747 ms
Execution time: 6727.815 ms

Замість операторів фільтрації необхідно використовувати оператор наявності одного jsonb в іншому. 7 секунд замість вихідних 29. Поки що це єдиний успішний варіант передачі фільтрів по jsonb на віддалений сервер, але тут важливо зважити на одне обмеження: ми використовуємо версію бази 9.6, проте до кінця квітня плануємо завершити останні тести і переїхати на 12 версію. Як оновимося, напишемо, як це вплинуло, адже змін, на які багато надій досить багато: json_path, нова поведінка CTE, push down (що існує з 10 версії). Дуже хочеться скоріше спробувати.

Finish him

Ми перевірили, як кожна зміна впливає на швидкість запиту окремо. Давайте подивимося, що буде, коли всі три фільтри будуть написані правильно.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone)) AND ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 ms

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

Підіб'ємо підсумки: якщо ви використовуєте PostgreSQL з FDW, завжди перевіряйте, чи всі фільтри відправляються на віддалений сервер, і буде вам щастя… Принаймні поки ви не дійдете до джойнів між таблицями з різних серверів. Але це вже історія ще однієї статті.

Дякую за увагу! Радий почути питання, коментарі, а також історії про ваш досвід у коментарях.

Джерело: habr.com