Get best of the week

Analisis operasional dalam arsitektur layanan mikro: membantu dan menyarankan Postgres FDW

Arsitektur microservice, seperti semua yang ada di dunia ini, memiliki pro dan kontra. Beberapa proses dengannya menjadi lebih mudah, yang lain lebih rumit. Dan demi kecepatan perubahan dan skalabilitas yang lebih baik, pengorbanan harus dilakukan. Salah satunya adalah komplikasi analitik. Jika dalam monolit semua analitik operasional dapat direduksi menjadi SQL queries untuk replika analitik, maka dalam arsitektur multiservice, setiap layanan memiliki basisnya sendiri dan tampaknya satu query tidak dapat ditiadakan (atau dapatkah dihilangkan?). Bagi mereka yang tertarik pada 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 DomKlik saya bekerja di sebuah tim yang bertanggung jawab untuk menjaga gudang data analitis. Secara konvensional, kegiatan kami dapat dikaitkan dengan tanggal rekayasa, tetapi, pada kenyataannya, berbagai tugas jauh lebih luas. Ada standar ETL / ELT untuk rekayasa tanggal, dukungan dan adaptasi alat untuk analisis data dan pengembangan alat mereka sendiri. Khususnya, untuk pelaporan operasional, kami memutuskan untuk "berpura-pura" bahwa kami memiliki monolit dan memberikan analis satu basis di mana mereka akan memiliki semua data yang mereka butuhkan.

Secara umum, kami mempertimbangkan opsi yang berbeda. Dimungkinkan untuk membangun repositori lengkap - kami bahkan mencoba, tetapi jujur ​​saja, kami gagal menjalin pertemanan yang cukup sering dalam logika dengan proses yang agak lambat dalam membangun repositori dan membuat perubahan padanya (jika seseorang berhasil, tuliskan di komentar caranya). Orang bisa mengatakan kepada analis: "Guys, pelajari python, dan ikuti petunjuk analitik", tetapi ini adalah persyaratan tambahan untuk perekrutan staf, dan tampaknya ini harus dihindari jika mungkin. Kami memutuskan untuk mencoba menggunakan teknologi FDW (Foreign Data Wrapper): pada kenyataannya, ini adalah standar dblink, yang ada dalam standar SQL, tetapi dengan antarmuka yang jauh lebih nyaman. Atas dasar itu, kami membuat keputusan, yang akhirnya diselesaikan, kami berhenti di atasnya. Detailnya adalah subjek dari artikel terpisah, atau mungkin bukan satu,karena saya ingin berbicara tentang banyak hal: mulai dari sinkronisasi skema basis data hingga kontrol akses dan penganoniman data pribadi. Anda juga perlu membuat reservasi bahwa solusi ini bukan pengganti untuk database dan repositori analitis nyata, itu hanya memecahkan masalah tertentu.

Tingkat atas terlihat seperti ini:


Ada database PostgreSQL, di mana pengguna dapat menyimpan data kerja mereka, dan yang paling penting, replika analitis semua layanan terhubung ke database ini melalui FDW. Ini memungkinkan untuk menulis kueri ke beberapa basis data, tidak peduli apa itu: PostgreSQL, MySQL, MongoDB atau yang lainnya (file, API, jika tiba-tiba tidak ada pembungkus yang cocok, Anda dapat menulis sendiri). Yah, semuanya tampak super! Apakah kita terpecah?

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

Penting untuk memahami dengan jelas bagaimana postgres menangani permintaan ke server jarak jauh. Ini tampaknya logis, tetapi seringkali mereka tidak memperhatikannya: postgres membagi permintaan menjadi bagian-bagian yang dilakukan pada server jarak jauh secara independen, mengumpulkan data ini, dan perhitungan akhir dilakukan dengan sendirinya, sehingga kecepatan permintaan akan sangat tergantung pada bagaimana ditulisnya. Perlu juga dicatat: ketika data berasal dari server jauh, mereka tidak lagi memiliki indeks, tidak ada yang dapat membantu penjadwal, oleh karena itu, hanya kami yang bisa membantu dan menyarankannya. Dan saya ingin memberi tahu Anda lebih banyak tentang itu.

Permintaan dan rencana sederhana dengan itu


Untuk memperlihatkan bagaimana postgres mengeksekusi kueri pada 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 instruksi VERBOSE memungkinkan Anda untuk melihat permintaan yang akan dikirim ke server jarak jauh dan hasil yang akan kami terima untuk diproses lebih lanjut (baris RemoteSQL).

Mari kita melangkah lebih jauh dan menambahkan beberapa filter ke kueri kami: satu dengan bidang boolean , satu oleh timestamp dalam interval, dan satu oleh 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 saat terletak bahwa Anda perlu memperhatikan saat menulis pertanyaan. Filter tidak ditransfer ke server jarak jauh, yang berarti untuk menjalankannya, postgres memperluas semua 6 juta baris, hanya untuk memfilternya secara lokal nanti (Filter line) dan melakukan agregasi. Kunci kesuksesan adalah menulis permintaan sehingga filter ditransfer ke mesin jarak jauh, dan kami hanya menerima dan mengagregasi baris yang diperlukan.

Itu booleanshit


Dengan bidang boolean, semuanya sederhana. Dalam permintaan awal, masalahnya adalah karena pernyataan is . Jika kita menggantinya dengan = , maka kita mendapatkan hasil 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 jauh, dan runtime dikurangi dari 27 menjadi 19 detik.

Perlu dicatat bahwa operator is berbeda dari operator = di mana ia dapat bekerja dengan nilai Null. Ini berarti bahwa tidak Benar dalam filter akan meninggalkan False dan Null, sementara ! = Benar hanya akan meninggalkan False. Oleh karena itu, ketika mengganti bukan operator, dua kondisi dengan operator ATAU harus diteruskan ke filter, misalnya, WHERE (col! = True) ATAU (col adalah nol) .

Dengan boolean disortir, lanjutkan. Sementara itu, kembalikan filter dengan nilai Boolean ke bentuk aslinya, untuk secara independen mempertimbangkan efek perubahan lainnya.

timestamptz? hz


Secara umum, kita sering harus bereksperimen dengan cara menulis kueri yang melibatkan server jauh, dan hanya kemudian mencari penjelasan mengapa ini terjadi. Sangat sedikit informasi tentang ini yang dapat ditemukan di Internet. Jadi, dalam percobaan, kami menemukan bahwa filter berdasarkan tanggal tetap terbang ke server jauh dengan bang, tetapi ketika kami ingin mengatur tanggal secara dinamis, misalnya sekarang () atau CURRENT_DATE, ini tidak terjadi. Dalam contoh kami, kami menambahkan filter sehingga kolom Created_at berisi data tepat untuk 1 bulan yang lalu (BETWEEN CURRENT_DATE - INTERVAL '7 bulan' DAN CURRENT_DATE - INTERVAL '6 bulan'). Apa yang telah 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 mendorong penjadwal untuk menghitung tanggal di subquery di muka dan meneruskan variabel yang sudah jadi ke filter. Dan petunjuk ini memberi kami hasil yang sangat baik, permintaan menjadi hampir 6 kali lebih cepat!

Sekali lagi, penting untuk berhati-hati di sini: tipe data dalam subquery harus sama dengan bidang yang kita filter, jika tidak, penjadwal akan memutuskan bahwa karena tipe berbeda dan Anda harus terlebih dahulu mendapatkan semua data dan memfilternya secara lokal.

Kembalikan filter berdasarkan tanggal ke nilai aslinya.

Freddy vs. Jsonb


Secara umum, bidang dan tanggal Boolean telah mempercepat kueri kami, tetapi ada satu tipe data lagi. Pertempuran dengan penyaringan di atasnya, terus terang, masih belum berakhir, meskipun ada keberhasilan. Jadi, di sini adalah bagaimana kami berhasil melewati bidang filter dengan jsonb ke server jarak 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 operator yang memiliki satu jsonb di yang lain. 7 detik alih-alih 29 awal. Sejauh ini, ini adalah satu-satunya opsi yang berhasil mentransfer filter melalui jsonb ke server jarak jauh, tetapi penting untuk mempertimbangkan satu batasan: kami menggunakan versi database 9.6, namun kami berencana untuk menyelesaikan tes terbaru dan pindah ke versi 12 pada akhir April. Saat kami memperbarui, kami akan menulis bagaimana pengaruhnya, karena ada banyak perubahan yang ada banyak harapan: json_path, perilaku CTE baru, tekan ke bawah (ada dari versi 10). Saya ingin segera mencobanya.

Habisi dia


Kami memeriksa bagaimana setiap perubahan memengaruhi kecepatan permintaan secara individual. Sekarang mari kita lihat apa yang terjadi ketika 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, permintaan terlihat lebih rumit, ini adalah papan yang dipaksakan, tetapi kecepatan eksekusi adalah 2 detik, yang lebih dari 10 kali lebih cepat! Dan kita berbicara tentang permintaan sederhana pada kumpulan data yang relatif kecil. Atas permintaan nyata, kami menerima pertumbuhan hingga beberapa ratus kali.

Untuk meringkas: jika Anda menggunakan PostgreSQL dengan FDW, selalu periksa bahwa semua filter dikirim ke server jarak jauh, dan Anda akan senang ... Setidaknya sampai Anda masuk ke gabungan antara tabel dari server yang berbeda. Tapi ini adalah cerita untuk artikel lain.

Terimakasih atas perhatiannya! Saya akan senang mendengar pertanyaan, komentar, serta cerita tentang pengalaman Anda dalam komentar.

More articles:


All Articles