Arsitektur layanan mikro, seperti segala sesuatu di dunia ini, memiliki pro dan kontra. Beberapa proses menjadi lebih mudah dengan itu, yang lain lebih sulit. Dan demi kecepatan perubahan dan skalabilitas yang lebih baik, Anda perlu berkorban. Salah satunya adalah kompleksitas analitik. Jika dalam monolit semua analitik operasional dapat direduksi menjadi kueri SQL menjadi replika analitik, maka dalam arsitektur multilayanan, setiap layanan memiliki basisnya sendiri dan tampaknya satu kueri tidak cukup (atau mungkin akan?). Bagi mereka yang tertarik dengan bagaimana kami memecahkan masalah analitik operasional di perusahaan kami dan bagaimana kami belajar untuk hidup dengan solusi ini - selamat datang.

Nama saya Pavel Sivash, di DomClick saya bekerja dalam tim yang bertanggung jawab memelihara gudang data analitik. Secara konvensional, aktivitas kami dapat dikaitkan dengan rekayasa data, tetapi sebenarnya jangkauan tugasnya jauh lebih luas. Ada standar rekayasa data ETL/ELT, dukungan dan adaptasi alat analisis data dan pengembangan alatnya sendiri. Secara khusus, untuk pelaporan operasional, kami memutuskan untuk "berpura-pura" bahwa kami memiliki monolit dan memberikan satu database kepada analis yang akan berisi semua data yang mereka butuhkan.

Secara umum, kami mempertimbangkan opsi yang berbeda. Dimungkinkan untuk membangun repositori yang lengkap - kami bahkan mencoba, tetapi, sejujurnya, kami tidak dapat berteman dengan perubahan logika yang cukup sering dengan proses yang agak lambat dalam membangun repositori dan membuat perubahan padanya ( kalau ada yang berhasil tulis di komentar caranya). Anda dapat mengatakan kepada analis: "Teman-teman, pelajari python dan lanjutkan ke jalur analitik", tetapi ini adalah persyaratan rekrutmen tambahan, dan tampaknya ini harus dihindari jika memungkinkan. Kami memutuskan untuk mencoba menggunakan teknologi FDW (Foreign Data Wrapper): sebenarnya, ini adalah dblink standar, yang ada dalam standar SQL, tetapi dengan antarmuka yang jauh lebih nyaman. Atas dasar itu, kami membuat keputusan, yang akhirnya mengakar, kami menyelesaikannya. Detailnya adalah topik artikel terpisah, dan mungkin lebih dari satu, karena saya ingin membicarakan banyak hal: dari sinkronisasi skema database hingga kontrol akses dan depersonalisasi data pribadi. Perlu juga dicatat bahwa solusi ini bukan pengganti database dan repositori analitik nyata, ini hanya menyelesaikan masalah tertentu.

Di tingkat atas terlihat seperti ini:

Terdapat database PostgreSQL tempat pengguna dapat menyimpan data pekerjaan mereka, dan yang terpenting, replika analitik dari semua layanan terhubung ke database ini melalui FDW. Ini memungkinkan untuk menulis kueri ke beberapa database, dan tidak masalah apa itu: PostgreSQL, MySQL, MongoDB atau yang lainnya (file, API, jika tiba-tiba tidak ada pembungkus yang sesuai, Anda dapat menulis sendiri). Yah, semuanya tampak hebat! Putus?

Jika semuanya berakhir begitu cepat dan sederhana, maka, mungkin, artikel itu tidak akan ada.

Penting untuk memperjelas tentang bagaimana postgres menangani permintaan ke server jarak jauh. Ini tampaknya logis, tetapi seringkali orang tidak memperhatikannya: postgres membagi kueri menjadi bagian-bagian yang dieksekusi secara independen di server jarak jauh, mengumpulkan data ini, dan melakukan perhitungan akhir itu sendiri, sehingga kecepatan eksekusi kueri akan sangat bergantung pada bagaimana ada tertulis. Perlu juga diperhatikan: ketika data berasal dari server jarak jauh, mereka tidak lagi memiliki indeks, tidak ada yang akan membantu penjadwal, oleh karena itu, hanya kami sendiri yang dapat membantu dan menyarankannya. Dan itulah yang ingin saya bicarakan lebih detail.

Permintaan sederhana dan rencana dengan itu

Untuk menunjukkan bagaimana Postgres menanyakan tabel 6 juta baris pada server jarak jauh, mari kita lihat rencana sederhana.

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

Menggunakan pernyataan VERBOSE memungkinkan Anda untuk melihat kueri yang akan dikirim ke server jarak jauh dan hasilnya akan kami terima untuk diproses lebih lanjut (string RemoteSQL).

Mari melangkah lebih jauh dan menambahkan beberapa filter ke kueri kita: satu per satu boolean lapangan, satu demi satu timestamp per interval dan satu demi satu 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

Di sinilah letak momen yang perlu Anda perhatikan saat menulis kueri. Filter tidak ditransfer ke server jarak jauh, yang berarti untuk menjalankannya, postgres menarik semua 6 juta baris untuk memfilter secara lokal (garis Filter) dan melakukan agregasi nanti. Kunci suksesnya adalah menulis kueri sehingga filter dikirim ke mesin jarak jauh, dan kami hanya menerima dan menggabungkan baris yang diperlukan.

Itu beberapa booleanshit

Dengan bidang boolean, semuanya sederhana. Dalam kueri awal, masalahnya adalah karena operator is. Jika kita menggantinya dengan =, maka kita mendapatkan hasil sebagai berikut:

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

Seperti yang Anda lihat, filter terbang ke server jarak jauh, dan waktu eksekusi dikurangi dari 27 menjadi 19 detik.

Perlu dicatat bahwa operator is berbeda dengan operatornya = salah satu yang dapat bekerja dengan nilai Null. Itu artinya tidak benar di filter akan meninggalkan nilai False dan Null, sedangkan != Benar hanya akan meninggalkan nilai False. Karena itu, saat mengganti operator tidak Anda harus meneruskan dua ketentuan ke filter dengan operator ATAU, misalnya, DIMANA (col != Benar) ATAU (col adalah null).

Dengan boolean tahu, lanjutkan. Sementara itu, mari kembalikan filter dengan nilai boolean ke bentuk aslinya untuk mempertimbangkan efek perubahan lain secara mandiri.

stempel waktu? hz

Secara umum, Anda sering harus bereksperimen dengan cara menulis kueri dengan benar yang melibatkan server jarak jauh, dan baru kemudian mencari penjelasan mengapa hal ini terjadi. Sangat sedikit informasi tentang ini yang dapat ditemukan di Internet. Jadi, dalam percobaan, kami menemukan bahwa filter tanggal tetap terbang ke server jarak jauh dengan keras, tetapi ketika kami ingin menyetel tanggal secara dinamis, misalnya, sekarang() atau CURRENT_DATE, ini tidak terjadi. Dalam contoh kami, kami telah menambahkan filter sehingga kolom create_at berisi data tepat 1 bulan yang lalu (BETWEEN CURRENT_DATE - INTERVAL '7 month' AND CURRENT_DATE - INTERVAL '6 month'). Apa yang kami lakukan dalam kasus ini?

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

Kami meminta perencana untuk menghitung tanggal terlebih dahulu di subquery dan meneruskan variabel yang sudah disiapkan ke filter. Dan petunjuk ini memberi kami hasil yang bagus, kueri menjadi hampir 6 kali lebih cepat!

Sekali lagi, penting untuk berhati-hati di sini: tipe data dalam subkueri harus sama dengan bidang yang kita filter, jika tidak, perencana akan memutuskan bahwa karena tipenya berbeda dan pertama-tama perlu mendapatkan semua data dan memfilternya secara lokal.

Mari kembalikan filter berdasarkan tanggal ke nilai aslinya.

Freddy vs. jsonb

Secara umum, bidang dan tanggal boolean sudah cukup mempercepat kueri kami, tetapi ada satu tipe data lagi. Pertarungan dengan memfilternya, sejujurnya, masih belum berakhir, meski ada juga yang sukses di sini. Jadi, inilah cara kami berhasil melewati filter jsonb bidang ke server jauh.

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

Alih-alih memfilter operator, Anda harus menggunakan keberadaan satu operator. jsonb berbeda. 7 detik, bukan 29 aslinya. Sejauh ini, ini adalah satu-satunya opsi yang berhasil untuk mentransfer filter jsonb ke server jarak jauh, tetapi di sini penting untuk mempertimbangkan satu batasan: kami menggunakan database versi 9.6, tetapi pada akhir April kami berencana untuk menyelesaikan tes terakhir dan pindah ke versi 12. Segera setelah kami memperbarui, kami akan menulis bagaimana pengaruhnya, karena ada banyak perubahan yang memiliki banyak harapan: json_path, perilaku CTE baru, push down (ada dari versi 10). Saya sangat ingin segera mencobanya.

Habisi dia

Kami memeriksa bagaimana setiap perubahan memengaruhi kecepatan kueri satu per satu. Sekarang mari kita lihat apa yang terjadi jika ketiga filter ditulis dengan benar.

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

Ya, kuerinya terlihat lebih rumit, ini adalah harga yang dipaksakan, tetapi kecepatan eksekusinya 2 detik, lebih dari 10 kali lebih cepat! Dan kita berbicara tentang kueri sederhana pada kumpulan data yang relatif kecil. Atas permintaan nyata, kami menerima peningkatan hingga beberapa ratus kali lipat.

Singkatnya: jika Anda menggunakan PostgreSQL dengan FDW, selalu periksa apakah semua filter dikirim ke server jarak jauh dan Anda akan senang ... Setidaknya sampai Anda bergabung di antara tabel dari server yang berbeda. Tapi itu cerita untuk artikel lain.

Terima kasih atas perhatian Anda! Saya ingin mendengar pertanyaan, komentar, dan cerita tentang pengalaman Anda di komentar.

Sumber: www.habr.com