PolarDB:Gunakan IMCI pada tabel partisi

Langkah 1: Siapkan lingkungan

1. Verifikasi bahwa versi dan konfigurasi kluster Anda memenuhi persyaratan berikut:

   • Versi kluster:

     • PostgreSQL 14 (versi mesin minor 2.0.14.10.20.0 atau lebih baru)

     • PostgreSQL 15 (versi mesin minor 2.0.15.15.7.0 atau lebih baru)

     • PostgreSQL 16 (versi mesin minor 2.0.16.8.3.0 atau lebih baru)

     • PostgreSQL 17 (versi mesin minor 2.0.17.7.5.0 atau lebih baru)

     Catatan

     Anda dapat melihat versi mesin minor di Konsol atau dengan menjalankan pernyataan SHOW polardb_version;. Jika versi mesin minor tidak memenuhi persyaratan, tingkatkan versi mesin minor.

   • Parameter wal_level harus diatur ke logical. Pengaturan ini menambahkan informasi yang diperlukan untuk logical decoding ke dalam write-ahead logging (WAL).

     Catatan

     Anda dapat mengatur parameter wal_level di Konsol. Mengubah parameter ini akan me-restart kluster. Rencanakan operasi bisnis Anda dengan sesuai dan lanjutkan dengan hati-hati.

   • Tabel sumber harus memiliki kunci primer, dan kolom kunci primer harus disertakan saat Anda membuat indeks penyimpanan kolom. Disarankan menggunakan tipe data SERIAL atau BIGSERIAL untuk kunci primer karena secara signifikan meningkatkan efisiensi sinkronisasi data.

   • Anda hanya dapat membuat satu indeks penyimpanan kolom untuk setiap tabel.

2. Aktifkan fitur IMCI.

   Metode untuk mengaktifkan IMCI bervariasi tergantung pada versi mesin minor kluster PolarDB for PostgreSQL Anda:

   PostgreSQL 16 (2.0.16.9.8.0 atau lebih baru) atau PostgreSQL 14 (2.0.14.17.35.0 atau lebih baru)

   Untuk kluster PolarDB for PostgreSQL dengan versi tersebut, tersedia dua metode. Tabel berikut menjelaskan perbedaannya.

   Item Perbandingan	[Direkomendasikan] Tambahkan Node Read-Only IMCI	Langsung Gunakan Ekstensi Indeks Penyimpanan Kolom Pra-instal
   Metode	Anda dapat menambahkan node read-only IMCI secara manual di Konsol.	Tidak perlu tindakan apa pun. Anda dapat langsung menggunakan ekstensi tersebut.
   Alokasi Sumber Daya	Mesin penyimpanan kolom secara eksklusif menggunakan resource node, termasuk seluruh memori yang tersedia.	Mesin penyimpanan kolom dibatasi hingga 25% dari memori node. Sisa memori dialokasikan ke mesin penyimpanan baris.
   Dampak Bisnis	Beban kerja transactional processing (TP) dan analytical processing (AP) diisolasi pada node yang berbeda dan tidak saling memengaruhi.	Beban kerja TP dan AP berjalan pada node yang sama dan dapat saling memengaruhi.
   Biaya	Node read-only IMCI dikenai biaya tambahan dan ditagih dengan tarif yang sama seperti node komputasi reguler.	Tidak ada biaya tambahan.

   Tambahkan node read-only IMCI

   Ada dua cara untuk menambahkan node read-only IMCI:

   Catatan

   Kluster harus memiliki setidaknya satu node read-only. Anda tidak dapat menambahkan node read-only IMCI ke kluster single-node.

   1. Login ke Konsol PolarDB dan pilih wilayah kluster. Anda dapat membuka wizard Add/Remove Node dengan salah satu cara berikut:

      • Pada halaman Clusters, klik Add/Remove Node di kolom Actions.

      • Pada halaman Basic Information kluster target, klik Add/Remove Node di bagian Database Nodes.

   2. Pilih Add Read-only IMCI Node dan klik OK.

   3. Pada halaman upgrade/downgrade kluster, tambahkan node read-only IMCI dan selesaikan pembayaran.

      1. Klik Add a Read-only IMCI Node dan pilih spesifikasi node.

      2. Pilih waktu failover.

      3. (Opsional) Tinjau Ketentuan Layanan Produk dan Service Level Agreement.

      4. Klik Buy Now.

   4. Setelah pembayaran selesai, kembali ke halaman detail kluster dan tunggu hingga node read-only IMCI ditambahkan. Node siap digunakan saat statusnya berubah menjadi Running.

   Pada halaman pembelian PolarDB, di bagian Nodes, tentukan jumlah IMCI Read-Only Nodes.

   PostgreSQL 16 (2.0.16.8.3.0 hingga 2.0.16.9.8.0) atau PostgreSQL 14 (2.0.14.10.20.0 hingga 2.0.14.17.35.0)

   Untuk kluster PolarDB for PostgreSQL dengan versi tersebut, fitur IMCI disediakan sebagai ekstensi polar_csi. Untuk menggunakan IMCI, Anda harus terlebih dahulu membuat ekstensi tersebut di database yang diinginkan.

   Catatan

   • Ekstensi polar_csi bersifat cakupan level database. Untuk menggunakan IMCI di beberapa database dalam satu kluster, Anda harus membuat ekstensi polar_csi untuk setiap database.

   • Akun database yang digunakan untuk menginstal ekstensi harus merupakan akun istimewa.

   Ada dua cara untuk menginstal ekstensi polar_csi:

   Konsol

   1. Login ke Konsol PolarDB. Di panel navigasi kiri, klik Clusters. Pilih wilayah tempat kluster Anda berada, lalu klik ID kluster untuk membuka halaman detail kluster.

   2. Di panel navigasi kiri, pilih Settings and Management > Extension Management. Di tab Extension Management, pilih Uninstalled Extensions.

   3. Di pojok kanan atas halaman, pilih database target. Pada baris ekstensi polar_csi, klik Install di kolom Actions. Di kotak dialog Install Extension, pilih Database Account target dan klik OK untuk menginstal ekstensi di database target.

   CLI

   Sambungkan ke kluster database dan jalankan pernyataan berikut di database tempat Anda memiliki izin yang cukup untuk membuat ekstensi polar_csi.

   CREATE EXTENSION polar_csi;

Langkah 2: Siapkan data

Kasus ini membuat tabel partisi multi-level, memasukkan sekitar 320 juta baris (~16 GB) data simulasi, lalu menjalankan analisis statistik berdasarkan kondisi partisi.

Skema tabel partisi pengujian adalah sebagai berikut:

• sales: tabel utama.

• sales_2023: dipartisi berdasarkan tahun.

  • sales_2023_a: dipartisi berdasarkan bulan; bulan 1 hingga 6 didefinisikan sebagai partisi a.

  • sales_2023_b: dipartisi berdasarkan bulan; bulan 7 hingga 12 didefinisikan sebagai partisi b.

• sales_2024: dipartisi berdasarkan tahun.

  • sales_2024_a: dipartisi berdasarkan bulan; bulan 1 hingga 6 didefinisikan sebagai partisi a.

  • sales_2024_b: dipartisi berdasarkan bulan; bulan 7 hingga 12 didefinisikan sebagai partisi b.

1. Buat tabel partisi multi-level bernama sales, dengan kolom waktu sale_date sebagai kunci partisi. Definisinya sebagai berikut:

   CREATE TABLE sales (
       sale_id serial,
       product_id int NOT NULL,
       sale_date date NOT NULL,
       amount numeric(10,2) NOT NULL,
       primary key(sale_id, sale_date)
   ) PARTITION BY RANGE (sale_date);
   
   CREATE TABLE sales_2023 PARTITION OF sales
       FOR VALUES FROM ('2023-1-1') TO ('2024-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2023_a PARTITION OF sales_2023
       FOR VALUES FROM ('2023-1-1') TO ('2023-7-1');
   CREATE TABLE sales_2023_b PARTITION OF sales_2023
       FOR VALUES FROM ('2023-7-1') TO ('2024-1-1');
   
   CREATE TABLE sales_2024 PARTITION OF sales
       FOR VALUES FROM ('2024-1-1') TO ('2025-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2024_a PARTITION OF sales_2024
       FOR VALUES FROM ('2024-1-1') TO ('2024-7-1');
   CREATE TABLE sales_2024_b PARTITION OF sales_2024
       FOR VALUES FROM ('2024-7-1') TO ('2025-1-1');

2. Hasilkan data dan masukkan ke dalam tabel partisi (sekitar 16 GB).

   INSERT INTO sales (product_id, sale_date, amount)
   SELECT
     (random()*100)::int AS product_id,
     '2023-01-1'::date + i/3200000*7 AS sale_date,
     (random()*1000)::numeric(10,2) AS amount
   FROM
     generate_series(1, 320000000) i;

3. Buat IMCI pada tabel dan sertakan kolom sale_id, product_id, sale_date, dan amount dalam IMCI.

   CREATE INDEX ON sales USING CSI(sale_id, product_id, sale_date, amount);

Langkah 3: Jalankan kueri

Jalankan kueri menggunakan mesin eksekusi yang berbeda. Tiga kueri (Q1, Q2, dan Q3) dibuat berdasarkan kondisi partisi yang berbeda.

• Gunakan IMCI.

  --- Aktifkan IMCI dan atur tingkat paralelisme kueri ke 4.
  SET polar_csi.enable_query to on;
  // Versi kernel tinggi
  SET polar_csi.max_parallel_workers to 4;
  // Versi kernel rendah
  SET polar_csi.exec_parallel to 4;
  
  --- Q1
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-3-1' AND amount > 100 GROUP BY sale_date;
  --- Q2
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-9-1' AND amount > 100 GROUP BY sale_date;
  --- Q3
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2024-3-1' AND amount > 100 GROUP BY sale_date;

  Catatan

  Parameter polar_csi.max_parallel_workers sebelumnya bernama polar_csi.exec_parallel pada versi kernel sebelumnya. Untuk versi kernel yang tidak mendukung polar_csi.max_parallel_workers, gunakan polar_csi.exec_parallel sebagai gantinya.

  • PostgreSQL 14:

    • Gunakan polar_csi.exec_parallel pada versi 2.0.14.20.42.0 dan sebelumnya.

    • Gunakan polar_csi.max_parallel_workers pada versi 2.0.14.20.43.0 dan seterusnya.

  • PostgreSQL 16:

    • Gunakan polar_csi.exec_parallel pada versi 2.0.16.11.15.0 dan sebelumnya.

    • Gunakan polar_csi.max_parallel_workers pada versi 2.0.16.13.16.0 dan seterusnya.

• Nonaktifkan IMCI dan gunakan mesin penyimpanan baris.

  --- Nonaktifkan IMCI, gunakan mesin penyimpanan baris, dan atur tingkat paralelisme kueri ke 4.
  SET polar_csi.enable_query to off;
  SET max_parallel_workers_per_gather to 4;
  
  --- Q1
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-3-1' AND amount > 100 GROUP BY sale_date;
  --- Q2
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-9-1' AND amount > 100 GROUP BY sale_date;
  --- Q3
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2024-3-1' AND amount > 100 GROUP BY sale_date;