Ringkasan Mode Eksekusi DML untuk Distributed Table PolarDB - PolarDB

Dalam kluster PolarDB for PostgreSQL (Distributed Edition), Anda dapat menggunakan perintah Data Manipulation Language (DML) PostgreSQL standar, seperti INSERT, UPDATE, dan DELETE, untuk mengelola data. Meskipun sintaksnya konsisten dengan PostgreSQL standar, perilaku eksekusi dan performanya sangat bergantung pada cara data didistribusikan di berbagai node. Untuk memastikan operasi DML Anda benar dan efisien, pahami prinsip-prinsip inti berikut.

Prinsip inti dan batasan operasi DML

Tentukan kolom distribusi untuk operasi INSERT: Pernyataan INSERT harus menyertakan kolom distribusi. Sistem menggunakan nilai kolom distribusi untuk menghitung nilai hash yang menentukan shard fisik tujuan bagi data baru. Jika kolom distribusi dihilangkan, operasi tersebut gagal.
Contoh: Asumsikan id adalah kolom distribusi. Jika Anda tidak menyertakan kolom id dalam operasi INSERT, kesalahan akan dikembalikan.
```
INSERT INTO t (data) VALUES ('TEST');
```
Hasil berikut dikembalikan:
```
ERROR:  cannot perform an INSERT without a partition column value
```
Jangan ubah nilai kolom distribusi: Setelah sebuah baris ditulis, lokasi fisiknya ditentukan oleh nilai kolom distribusinya. Oleh karena itu, Anda tidak dapat mengubah nilai kolom distribusi dalam pernyataan UPDATE atau INSERT ... ON CONFLICT DO UPDATE. Operasi jenis ini tidak didukung karena akan memerlukan pemindahan data antar node fisik.
Contoh: Asumsikan id adalah kolom distribusi. Jika Anda memasukkan baris dengan id=1 lalu mencoba mengubah nilai kolom distribusi tersebut, kesalahan akan terjadi.
```
-- Insert the original data
INSERT INTO t (id, data) VALUES (1, 'TEST');
-- Attempt to modify the distribution column value
UPDATE t SET id = 10 WHERE id = 1;
```
Hasil berikut dikembalikan:
```
ERROR:  modifying the partition value of rows is not allowed
```
Catatan mengenai konsistensi transaksi lintas node: PolarDB for PostgreSQL (Distributed Edition) menggunakan protokol two-phase commit (2PC) untuk memastikan atomisitas transaksi terdistribusi. Namun, pada tingkat isolasi Read Committed bawaan, Snapshot Isolation (SI) lintas node tidak didukung.
Artinya, untuk transaksi yang melibatkan beberapa node data (DN), waktu commit pada setiap node mungkin sedikit berbeda. Selama interval singkat ini, kueri SELECT konkuren mungkin membaca keadaan antara yang tidak konsisten. Misalnya, sebuah kueri mungkin melihat data baru di satu node tetapi data lama di node lain. Jika aplikasi Anda memerlukan konsistensi data lintas node yang ketat, Anda harus menerapkan penanganan khusus di lapisan aplikasi, seperti menggunakan lock atau menghindari pembacaan data yang baru saja ditulis.

Operasi DML umum

`INSERT`: Memasukkan data

Anda dapat melakukan insert baris tunggal maupun multi-baris. Anda juga dapat menggunakan klausa ON CONFLICT untuk menangani konflik data.

Insert baris tunggal

-- Sintaks kolom implisit. Memasukkan data sesuai urutan kolom dalam tabel.
INSERT INTO t VALUES (1, 'TEST');
-- Sintaks kolom eksplisit. Secara jelas menentukan kolom mana yang akan diisi data.
INSERT INTO t (id, data) VALUES (1, 'TEST');

Penyisipan Batch

INSERT INTO t VALUES (1, 'TEST'), (2, 'TEST'), (3, 'TEST');

INSERT...SELECT: Mengimpor data dari tabel sumber ke tabel target

Buat tabel sumber dan tabel target.

-- Tabel sumber
CREATE TABLE source (id int, data text);
SELECT create_distributed_table('source', 'id');
-- Tabel target
CREATE TABLE target (id int, data text);
SELECT create_distributed_table('target', 'id');

Impor data tersebut.

-- Ketika tabel sumber dan target memiliki skema yang sama
INSERT INTO target SELECT * FROM source;
-- Menentukan secara manual himpunan kolom target dan himpunan kolom data
INSERT INTO target(id, data) SELECT id, data FROM source;

ON CONFLICT: Menangani konflik data
- Masalah: Konflik dapat terjadi jika tabel target untuk operasi INSERT memiliki kendala PRIMARY KEY, UNIQUE, atau EXCLUDE. Kesalahan serupa berikut dikembalikan:
```
ERROR:  duplicate key value violates unique constraint "xxx"
```
- Solusi:
  - ON CONFLICT DO NOTHING: Jika terjadi konflik, abaikan data baru.
```
INSERT INTO target VALUES (1, 'NEW_DATA') ON CONFLICT(id) DO NOTHING;
```
  - ON CONFLICT DO UPDATE: Jika terjadi konflik, perbarui kolom yang ditentukan. Selain itu, PolarDB for PostgreSQL (Distributed Edition) mendukung penggunaan EXCLUDED untuk mereferensikan nilai baru yang ditolak akibat konflik tersebut.
```
-- Jika terjadi konflik, perbarui kolom yang ditentukan ke nilai statis, seperti 'ERROR'.
INSERT INTO target VALUES (1, 'NEW_DATA') ON CONFLICT(id) DO UPDATE SET data='ERROR';
```
```
-- Gunakan EXCLUDED untuk mereferensikan nilai baru yang dikecualikan akibat konflik
INSERT INTO target VALUES (1, 'NEW_DATA') ON CONFLICT(id) DO UPDATE SET data = EXCLUDED.data;
```

`UPDATE`/`DELETE`: Memperbarui dan menghapus data

Sintaks perintah ini identik dengan PostgreSQL standar. Gunakan klausa WHERE untuk menemukan data target secara efisien.

Memperbarui data:

UPDATE t SET data = 'CHANGED' WHERE id = 1;

Menghapus data:
```
DELETE FROM t WHERE id = 1;
```

`COPY`: Memuat data secara batch

Anda dapat menggunakan perintah COPY untuk memuat data dalam jumlah besar dari file secara efisien. Perhatikan perbedaan antara perintah COPY sisi server dan meta-perintah sisi klien \COPY di psql:

COPY ... FROM 'file_path': Membaca file dari sistem file node database, seperti Coordinator Node (CN) atau node data (DN).
```
COPY target FROM '/path/to/data.csv' WITH CSV HEADER;
```
\COPY ... FROM 'file_path': Membaca file dari mesin klien tempat perintah psql dijalankan dan mengalirkannya ke database.
```
\COPY target FROM '/path/on/client/data.csv' WITH CSV HEADER;
```

Optimalisasi performa DML

Operasi DML kompleks, terutama pernyataan INSERT...SELECT dan pernyataan UPDATE atau DELETE multi-tabel, dapat dieksekusi dalam beberapa mode yang memiliki perbedaan performa signifikan. Anda dapat menggunakan perintah EXPLAIN untuk melihat rencana eksekusi dan menentukan mode eksekusi yang digunakan.

Mode eksekusi DML

Mode eksekusi	Peringkat performa	Kondisi pemicu	Konsep inti
Eksekusi paralel yang dipush-down	★★★★★	Hanya melibatkan satu tabel terdistribusi. Ketika melibatkan beberapa tabel terdistribusi, semua kondisi berikut harus terpenuhi: Terdiri dari beberapa tabel terdistribusi atau tabel replikasi. Tabel terdistribusi termasuk dalam kelompok colocation yang sama. Tabel terdistribusi yang terlibat dalam perintah `UPDATE/DELETE` di-join berdasarkan kolom distribusinya.	Komputasi sepenuhnya dipush-down ke setiap node data untuk eksekusi paralel. Tidak ada transfer data antar node.
Eksekusi dengan repartisi	★★	Dalam pernyataan `INSERT...SELECT`, kunci distribusi tabel sumber dan target tidak cocok.	Mendistribusikan ulang data tabel sumber di antara node agar sesuai dengan aturan distribusi tabel target.
Eksekusi yang ditarik ke atas dan dialihkan kembali	★	Pernyataan DML mengandung klausa seperti `ORDER BY`, `LIMIT`, `OFFSET`, atau `GROUP BY (jika kunci pengelompokan tidak mencakup kolom distribusi)`.	Menarik hasil antara ke node koordinator untuk diproses, lalu mengirimkan hasilnya kembali ke node target. Hal ini menyebabkan overhead jaringan yang tinggi.

Eksekusi paralel yang dipush-down

Operasi DML hanya melibatkan satu tabel terdistribusi.

-- Pernyataan insert sederhana
INSERT INTO t VALUES (1, 'TEST'), (2, 'TEST');
-- Pernyataan update sederhana
UPDATE t SET data = 'CHANGE' WHERE id = 1;
-- Pernyataan delete sederhana
DELETE FROM t WHERE id = 1;

Operasi DML melibatkan beberapa tabel terdistribusi.

Contoh:

Asumsikan tabel source dan target ada dengan skema tabel berikut. Kedua tabel berada dalam kelompok colocation yang sama, dan kolom distribusinya adalah `id`:

-- Tabel sumber
CREATE TABLE source (id int, data text);
SELECT create_distributed_table('source', 'id');
-- Tabel target
CREATE TABLE target (id int, data text);
SELECT create_distributed_table('target', 'id');

Lihat informasi kelompok colocation.

SELECT logicalrelid, colocationid FROM pg_dist_partition;

Hasil berikut dikembalikan:

 logicalrelid | colocationid
--------------+--------------
 target       |            1
 source       |            1

Ketika operasi DML berikut dijalankan, operasi tersebut dapat dilakukan dalam satu node dan karenanya dapat dipush-down untuk eksekusi paralel:

-- Contoh pernyataan insert
INSERT INTO target(id, data) SELECT id, data FROM source;
-- Contoh pernyataan update
UPDATE target SET data = source.data FROM source WHERE target.id = source.id;
-- Contoh pernyataan delete
DELETE FROM target USING source WHERE target.id = source.id;

Ambil perintah INSERT...SELECT sebagai contoh. Perintah khas yang dapat dipush-down menghasilkan beberapa sub-tugas independen yang juga dapat dipush-down:

EXPLAIN INSERT INTO target(id, data) SELECT id, data FROM source;

Hasil berikut dikembalikan:

                                           QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Custom Scan (PolarCluster Adaptive)  (cost=0.00..0.00 rows=0 width=0)
   Task Count: 4 -- Tugas didistribusikan di 4 shard untuk eksekusi
   Tasks Shown: One of 4 -- Setiap shard menjalankan INSERT...SELECT secara lokal
   ->  Task
         Node: host=10.xxx.xxx.xxx port=3006 dbname=testdb
         ->  Insert on target_102105 polar_cluster_table_alias  (cost=0.00..22.70 rows=0 width=0)
               ->  Seq Scan on source_102101 source  (cost=0.00..22.70 rows=1264 width=36)
                     Filter: (id IS NOT NULL)
(8 rows)

Eksekusi dengan repartisi

Jika aturan distribusi tabel sumber dan target dalam pernyataan INSERT...SELECT tidak konsisten, operasi DML tidak dapat dilakukan dalam satu node. Hal ini memicu repartisi data. Selama repartisi, node sumber mengemas data yang diperlukan dan meneruskannya ke node tujuan yang sesuai agar perintah dapat dieksekusi.

Contoh:

Asumsikan tabel source2 dan target2 ada dengan skema tabel berikut:

-- Tabel sumber
CREATE TABLE source2 (id int, t_id int, data text);
SELECT create_distributed_table('source2', 'id');
-- Tabel target
CREATE TABLE target2 (id int, data text);
SELECT create_distributed_table('target2', 'id');

Ketika Anda menjalankan perintah INSERT...SELECT berikut, repartisi dipicu. Rencana eksekusi menunjukkan bahwa perintah dijalankan menggunakan metode repartition. Hal ini terjadi karena pernyataan INSERT menargetkan kolom id, tetapi pernyataan SELECT menggunakan kolom t_id untuk mengisi kolom id.

EXPLAIN INSERT INTO target2(id, data) SELECT t_id, data FROM source2;

Hasil berikut dikembalikan:

                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Custom Scan (PolarCluster INSERT ... SELECT)  (cost=0.00..0.00 rows=0 width=0)
   INSERT/SELECT method: repartition -- Secara jelas menunjukkan bahwa repartisi data terjadi
   ->  Custom Scan (PolarCluster Adaptive)  (cost=0.00..0.00 rows=100000 width=36)
         Task Count: 4
         Tasks Shown: One of 4
         ->  Task
               Node: host=10.188.91.26 port=3006 dbname=testdb
               ->  Seq Scan on source2_102117 source2  (cost=0.00..22.00 rows=1200 width=36)
(8 rows)

Eksekusi yang ditarik ke atas dan dialihkan kembali

Ketika pernyataan DML mengandung klausa yang tidak dapat dipush-down, seperti ORDER BY, LIMIT, OFFSET, atau GROUP BY (jika kunci pengelompokan tidak mencakup kolom distribusi), data harus terlebih dahulu ditarik ke Coordinator Node (CN). Setelah CN menyelesaikan operasi penggabungan, hasilnya dikirim kembali ke node target. Proses ini dapat menciptakan bottleneck di CN dan menghasilkan lalu lintas jaringan yang besar.

Contoh:

Asumsikan tabel source3 dan target3 ada dengan skema tabel berikut:

-- Tabel sumber
CREATE TABLE source3 (id int, t_id int, data text);
SELECT create_distributed_table('source3', 'id');
-- Tabel target
CREATE TABLE target3 (id int, data text);
SELECT create_distributed_table('target3', 'id');

Ketika perintah INSERT...SELECT berikut dijalankan, eksekusi yang ditarik ke atas dan dialihkan kembali dipicu. Rencana eksekusi menunjukkan bahwa metode eksekusinya adalah pull to coordinator.

EXPLAIN INSERT INTO target3(id, data) SELECT t_id, data FROM source3 LIMIT 1;

Hasil berikut dikembalikan:

                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Custom Scan (PolarCluster INSERT ... SELECT)  (cost=0.00..0.00 rows=0 width=0)
   INSERT/SELECT method: pull to coordinator -- Secara jelas menunjukkan bahwa data ditarik ke node koordinator
   ->  Limit  (cost=0.00..0.00 rows=1 width=36)
         ->  Custom Scan (PolarCluster Adaptive)  (cost=0.00..0.00 rows=100000 width=36)
               Task Count: 4
               Tasks Shown: One of 4
               ->  Task
                     Node: host=10.188.91.26 port=3006 dbname=testdb
                     ->  Limit  (cost=0.00..0.02 rows=1 width=36)
                           ->  Seq Scan on source3_102125 source3  (cost=0.00..22.00 rows=1200 width=36)
(10 rows)

Prinsip inti dan batasan operasi DML

Operasi DML umum

INSERT: Memasukkan data

UPDATE/DELETE: Memperbarui dan menghapus data

COPY: Memuat data secara batch

Optimalisasi performa DML

Mode eksekusi DML

Eksekusi paralel yang dipush-down

Eksekusi dengan repartisi

Eksekusi yang ditarik ke atas dan dialihkan kembali

`INSERT`: Memasukkan data

`UPDATE`/`DELETE`: Memperbarui dan menghapus data

`COPY`: Memuat data secara batch