ApsaraDB RDS:Cara mempercepat kueri dengan DuckDB?

Langkah 1: Aktifkan akselerasi DuckDB

Untuk mengalihkan kueri SELECT ke DuckDB, gunakan salah satu metode berikut.

1. Metode 1: Gunakan petunjuk (tingkat pernyataan)

   Tambahkan petunjuk ke pernyataan SELECT untuk mempercepat hanya kueri tersebut. Metode ini ideal untuk validasi sementara atau mempercepat kueri lambat tertentu:

   /*+ set(rds_duckdb.execution on) */ SELECT * FROM my_table WHERE id = 1;

2. Metode 2: Atur parameter tingkat sesi

   Jalankan perintah berikut dalam sesi saat ini. Semua kueri yang memenuhi syarat dalam sesi ini kemudian akan dialihkan ke DuckDB:

   SET rds_duckdb.execution = on;

Langkah 2: Verifikasi pengalihan

Gunakan rencana eksekusi untuk memverifikasi apakah suatu pernyataan SQL dialihkan ke DuckDB.

1. Contoh 1: Kueri satu tabel dialihkan ke DuckDB

   /*+ set(rds_duckdb.execution on) */ EXPLAIN SELECT * FROM test_hint;

   Rencana yang diharapkan berisi Custom Scan (DuckDBScan) dan DuckDB Execution Plan:

                            QUERY PLAN
   ------------------------------------------------------------
    Custom Scan (DuckDBScan)  (cost=0.00..0.00 rows=0 width=0)
      DuckDB Execution Plan:
   
    ┌───────────────────────────┐
    │         SEQ_SCAN          │
    │    ────────────────────   │
    │      Table: test_hint     │
    │   Type: Sequential Scan   │
    │       Projections: a      │
    │                           │
    │          ~0 Rows          │
    └───────────────────────────┘

2. Contoh 2: Rencana kembali ke rencana PostgreSQL native setelah pengalihan dinonaktifkan

   /*+ set(rds_duckdb.execution off) */ EXPLAIN SELECT * FROM test_hint;

   Rencana yang diharapkan:

         QUERY PLAN
   -----------------------
    Seq Scan on test_hint
   (1 row)

Langkah 3: Lihat log DuckDB (opsional)

Parameter rds_duckdb.enable_log_warning mengontrol apakah pesan tingkat WARNING dikirim ke klien. Hal ini membantu Anda mengidentifikasi alasan suatu kueri tidak dialihkan ke DuckDB.

1. Aktifkan output WARNING. Perubahan ini langsung berlaku pada tingkat sesi.

   SET rds_duckdb.enable_log_warning = on;

2. Amati output klien. Saat enable_log_warning = on, sebuah WARNING ditampilkan dalam skenario berikut:

   • Kueri kembali menggunakan PostgreSQL: Fallback postgres due to ...

   • Operasi tulis tidak didukung: Operasi modifikasi pada tabel DuckDB saat ini tidak didukung; fallback ke PG.

   • Pernyataan tidak perlu diproses oleh DuckDB: Statements don't need to be handed over to DuckDB, fallback to PG.

Saat enable_log_warning = off (default), informasi ini ditulis ke log pada level DEBUG1 dan tidak muncul di klien.

4.1 Sinkronisasi DDL otomatis

Ekstensi rds_duckdb dapat secara otomatis menyinkronkan perubahan struktur tabel (DDL) dari ApsaraDB RDS for PostgreSQL ke DuckDB.

Jenis DDL yang didukung:

-- Tambah kolom
ALTER TABLE tbl ADD COLUMN extra_1 int;
-- Hapus kolom
ALTER TABLE tbl DROP COLUMN extra_1;
-- Tambah kolom dengan nilai default
ALTER TABLE tbl ADD COLUMN extra_1 int DEFAULT 0;
-- Hapus nilai default kolom
ALTER TABLE tbl ALTER COLUMN extra_1 DROP DEFAULT;
-- Ubah tipe data kolom
ALTER TABLE tbl ALTER COLUMN extra_1 TYPE varchar;
-- Ganti nama kolom
ALTER TABLE tbl RENAME COLUMN extra_1 TO extra_2;
-- Ganti nama tabel
ALTER TABLE tbl RENAME TO tbl_new;
-- DDL dalam transaksi
BEGIN;
ALTER TABLE tbl ADD COLUMN extra_3 int;
INSERT INTO tbl VALUES (..., 1);
COMMIT;
-- Roll back DDL dengan savepoint
BEGIN;
SAVEPOINT s1;
ALTER TABLE tbl DROP COLUMN extra_1;
ROLLBACK TO SAVEPOINT s1;
COMMIT;

Contoh operasi DDL yang tidak didukung dan memicu fallback:

-- Ubah skema tabel (memicu full refresh otomatis atau konflik, tergantung pengaturan fail_action)
ALTER TABLE tbl SET SCHEMA nsp1;

-- Hapus primary key (menyebabkan sinkronisasi berhenti dan kueri kembali menggunakan PostgreSQL)
ALTER TABLE tbl DROP CONSTRAINT tbl_pkey;

Setelah Anda menghapus primary key, status tabel di duckdb_sync_stat berubah menjadi not syncing, dan kueri tidak lagi diarahkan melalui DuckDB. Status ini tetap berlaku hingga Anda memulihkan primary key atau REPLICA IDENTITY dan merefresh tabel secara manual.

4.2 Sinkronisasi tabel partisi

Ekstensi rds_duckdb dapat menyinkronkan partitioned tables PostgreSQL, termasuk partisi tingkat tunggal dan multi-level, ke DuckDB.

Sinkronkan tabel partisi PostgreSQL: Untuk menyinkronkan tabel partisi, Anda harus membuat tabel DuckDB untuk partisi root dan semua partisi leaf. Contoh berikut menggunakan tabel partisi tingkat tunggal:

-- Buat tabel partisi PostgreSQL.
CREATE TABLE test_partition (
    id int,
    age int,
    primary key (id, age)
) PARTITION BY RANGE (age);

CREATE TABLE test_partition_a PARTITION OF test_partition FOR VALUES FROM (0) TO (18);
CREATE TABLE test_partition_b PARTITION OF test_partition FOR VALUES FROM (18) TO (30);
CREATE TABLE test_partition_c PARTITION OF test_partition FOR VALUES FROM (30) TO (60);

INSERT INTO test_partition SELECT i, i FROM generate_series(0, 59) i;

-- Sinkronkan partisi root dan semua partisi leaf ke DuckDB.
SELECT rds_duckdb.create_duckdb_tables('{test_partition, test_partition_a, test_partition_b, test_partition_c}');

Contoh kueri pada partisi root:

/*+ set(rds_duckdb.execution on) */ EXPLAIN SELECT * FROM test_partition WHERE age >= 10 AND age < 20;

Rencana yang diharapkan (dipangkas untuk hanya memindai partisi yang cocok):

 Custom Scan (DuckDBScan)  (cost=0.00..0.00 rows=0 width=0)
   DuckDB Execution Plan:

 ┌───────────────────────────┐
 │           UNION           ├──────────────┐
 └─────────────┬─────────────┘              │
 ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
 │         SEQ_SCAN          ││         SEQ_SCAN          │
 │           Table:          ││           Table:          │
 │      test_partition_a     ││      test_partition_b     │
 │   Type: Sequential Scan   ││   Type: Sequential Scan   │
 │       Projections:        ││       Projections:        │
 │          Filters:         ││          Filters:         │
 │     age>=10 AND age<20││     age>=10 AND age<20│
 └───────────────────────────┘└───────────────────────────┘

Tabel partisi multi-level juga didukung, seperti pada contoh berikut:

CREATE TABLE test_multi_partition (
    id serial,
    sale_id int NOT NULL,
    sale_date date NOT NULL,
    amount numeric(15,2) NOT NULL,
    primary key(sale_id, sale_date)
) PARTITION BY RANGE (sale_date);

CREATE TABLE test_multi_partition_a_l1 PARTITION OF test_multi_partition
    FOR VALUES FROM ('2024-1-1') TO ('2025-1-1') PARTITION BY RANGE (sale_date);
CREATE TABLE test_multi_partition_b_l1 PARTITION OF test_multi_partition
    FOR VALUES FROM ('2025-1-1') TO ('2026-1-1') PARTITION BY RANGE (sale_date);

CREATE TABLE test_multi_partition_a_l2_1 PARTITION OF test_multi_partition_a_l1
    FOR VALUES FROM ('2024-1-1') TO ('2024-7-1');
CREATE TABLE test_multi_partition_a_l2_2 PARTITION OF test_multi_partition_a_l1
    FOR VALUES FROM ('2024-7-1') TO ('2025-1-1');
CREATE TABLE test_multi_partition_b_l2_1 PARTITION OF test_multi_partition_b_l1
    FOR VALUES FROM ('2025-1-1') TO ('2025-7-1');
CREATE TABLE test_multi_partition_b_l2_2 PARTITION OF test_multi_partition_b_l1
    FOR VALUES FROM ('2025-7-1') TO ('2026-1-1');

INSERT INTO test_multi_partition (sale_id, sale_date, amount)
SELECT (random() * 100)::int, '2024-01-1'::date + i, (random() * 1000)::numeric(15,2)
FROM generate_series(1, 730) i;

-- Sinkronkan semua level partisi.
SELECT rds_duckdb.create_duckdb_tables('{
    test_multi_partition,
    test_multi_partition_a_l1, test_multi_partition_b_l1,
    test_multi_partition_a_l2_1, test_multi_partition_a_l2_2,
    test_multi_partition_b_l2_1, test_multi_partition_b_l2_2
}');

4.3 Pembuatan tabel DuckDB otomatis

Selain memanggil create_duckdb_table() secara manual, rds_duckdb mendukung pembuatan otomatis. Saat fitur ini diaktifkan, sistem secara otomatis membuat tabel DuckDB yang sesuai dan memulai sinkronisasi inkremental jika Anda menjalankan CREATE TABLE di PostgreSQL dan kondisi sinkronisasi terpenuhi.

-- Aktifkan pembuatan otomatis (tingkat USERSET, dapat dikontrol pada tingkat sesi).
SET rds_duckdb.auto_create_duckdb_table = on;

-- Buat tabel PostgreSQL. Tabel DuckDB dibuat secara otomatis.
CREATE TABLE auto_tbl(id int primary key, val text);
INSERT INTO auto_tbl VALUES (1, 'hello');

-- Langsung kueri data DuckDB.
/*+ set(rds_duckdb.execution on) */ SELECT * FROM auto_tbl;

Hasil yang diharapkan (jika sinkronisasi normal):

  sync_table     | sync_status_description | sync_error_description
-----------------+-------------------------+------------------------
 public.auto_tbl | data syncing            | no errors

Catatan perilaku:

• Fitur ini hanya berlaku untuk tabel reguler. Pembuatan otomatis untuk tabel partisi saat ini tidak didukung.

• Tabel harus memiliki primary key atau REPLICA IDENTITY. Jika tidak, tabel tidak dapat memasuki status data syncing.

• Pembuatan otomatis tidak dipicu jika belum ada tabel DuckDB yang dibuat di database saat ini.

4.4 Fallback otonom

Parameter rds_duckdb.enable_fallback mengontrol perilaku sistem ketika pernyataan SQL tidak dapat dieksekusi oleh DuckDB:

• on (default): Secara otomatis kembali menggunakan PostgreSQL untuk eksekusi. Anda dapat menggunakan enable_log_warning untuk melihat alasan fallback.

• off: Langsung melemparkan error, sehingga masalah harus segera ditangani.

Ekstensi ini juga mendukung fallback berdasarkan latensi sinkronisasi. Parameter rds_duckdb.wait_sync_timeout mengontrol berapa lama kueri menunggu posisi sinkronisasi inkremental sebelum timeout. Satuannya adalah milidetik.

Skenario pemicu: Di lingkungan dengan operasi tulis yang sering dan latensi sinkronisasi tinggi, kueri yang memerlukan konsistensi kuat dapat mengalami fallback akibat timeout.

Contoh log (enable_log_warning = on):

WARNING:  Fallback postgres due to waiting for incremental synchronization timeout

Jika enable_fallback = off, error dilaporkan:

ERROR:  RDS DuckDB: canceling statement due to waiting for incremental synchronization timeout

Periksa latensi:

-- Periksa latensi sinkronisasi.
SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_name LIKE 'rds_duckdb_slot%';

4.5 Ambang batas biaya eksekusi (plan_cost_threshold)

Parameter rds_duckdb.plan_cost_threshold memastikan bahwa hanya kueri dengan perkiraan biaya yang cukup tinggi yang diteruskan ke DuckDB untuk eksekusi, sehingga mencegah kueri simple melambat akibat overhead forwarding dan startup.

Kasus penggunaan khas:

• Workload Anda mencakup kueri SQL analitis kompleks (melibatkan join, agregasi, dan pemindaian besar) serta kueri simple seperti point query atau pemindaian rentang kecil. Kueri simple sering kali berjalan lebih cepat di PostgreSQL karena overhead tetap dari offloading dan inisialisasi koneksi di DuckDB.

• Mengatur ambang batas yang wajar, seperti 1000 atau 10000, memungkinkan kueri simple berbiaya rendah tetap berjalan di PostgreSQL, sementara hanya kueri berbiaya tinggi dan besar yang didorong ke DuckDB untuk akselerasi.

Contoh konfigurasi:

-- Atur ambang batas biaya ke 5000. Kueri dialihkan ke DuckDB hanya jika biaya rencana PostgreSQL-nya melebihi 5000.
SET rds_duckdb.plan_cost_threshold = 5000;

-- Contoh 1: Kueri point query simple dengan biaya rencana PostgreSQL rendah tetap di PostgreSQL.
/*+ set(rds_duckdb.execution on) */ EXPLAIN SELECT * FROM my_table WHERE id = 1;

-- Contoh 2: Kueri agregasi kompleks dengan biaya rencana PostgreSQL tinggi dialihkan ke DuckDB.
/*+ set(rds_duckdb.execution on) */ EXPLAIN SELECT count(*), avg(amount) FROM my_table GROUP BY region;

Mengapa kueri saya tidak dialihkan?

Gunakan parameter rds_duckdb.enable_fallback untuk menentukan alasan kueri tidak dialihkan ke DuckDB.

Langkah pemecahan masalah:

-- Langkah 1: Nonaktifkan fallback untuk mengungkap akar penyebab.
SET rds_duckdb.enable_fallback = off;

-- Langkah 2: Jalankan pernyataan SQL target.
/*+ set(rds_duckdb.execution on) */ EXPLAIN SELECT * FROM my_table;

Potensi error dan maknanya:

Rangkuman skenario fallback umum:

Anda dapat mengkueri tampilan rds_duckdb.duckdb_sync_stat untuk memeriksa status sinkronisasi setiap tabel DuckDB. Tindakan ini memerlukan akun istimewa. Contoh:

SELECT sync_table,
       sync_status_description,
       sync_error_description
FROM rds_duckdb.duckdb_sync_stat;

 sync_table        | sync_status_description | sync_error_description
-------------------+-------------------------+------------------------------------------
 test_schema.test1 | not syncing             | no primary key or replica identity index

Jika Anda ingin mengkueri data dari tabel yang belum disinkronkan secara inkremental, Anda dapat mengatur parameter rds_duckdb.query_syncing_table = off.