PolarDB：Gunakan indeks penyimpanan kolom pada tabel partisi

Langkah 1: Siapkan lingkungan

1. Pastikan versi dan konfigurasi kluster Anda memenuhi persyaratan berikut:

   • Versi kluster:

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

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

     Catatan

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

   • Tabel sumber harus memiliki kunci primer. Kolom kunci primer harus disertakan saat Anda membuat indeks penyimpanan kolom.

   • Parameter wal_level harus diatur ke logical. Hal ini menambahkan informasi yang diperlukan untuk mendukung replikasi logis 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.

2. Aktifkan fitur indeks penyimpanan kolom.

   Metode untuk mengaktifkan fitur indeks penyimpanan kolom 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-versi tersebut, tersedia dua metode. Perbedaannya diuraikan dalam tabel berikut. Anda dapat memilih metode yang paling sesuai dengan kebutuhan Anda.

   Item perbandingan	[Direkomendasikan] Tambahkan node read-only indeks penyimpanan kolom	Langsung gunakan ekstensi indeks penyimpanan kolom yang telah dipra-instal
   Metode	Anda dapat menambahkan node indeks penyimpanan kolom secara manual melalui antarmuka visual di Konsol.	Tidak diperlukan tindakan apa pun. Anda dapat langsung menggunakan ekstensi tersebut.
   Alokasi resource	Mesin penyimpanan kolom menggunakan seluruh resource secara eksklusif dan dapat memanfaatkan sepenuhnya memori yang tersedia.	Mesin penyimpanan kolom hanya dapat menggunakan 25% memori. Sisa memori dialokasikan ke mesin penyimpanan baris.
   Dampak bisnis	Beban kerja pemrosesan transaksional (TP) dan pemrosesan analitis (AP) diisolasi pada node yang berbeda dan tidak saling memengaruhi.	Beban kerja TP dan AP berjalan pada node yang sama dan saling memengaruhi.
   Biaya	Node read-only In-Memory Column Index (IMCI) dikenai biaya tambahan dan ditagih dengan tarif yang sama seperti node komputasi reguler.	Tidak ada biaya tambahan.

   Tambahkan node read-only indeks penyimpanan kolom

   Anda dapat menambahkan node read-only indeks penyimpanan kolom dengan salah satu dari dua cara berikut:

   Catatan

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

   Tambahkan di Konsol

   1. Login ke Konsol PolarDB dan pilih wilayah tempat kluster berada. 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 indeks penyimpanan kolom dan selesaikan pembayaran.

      1. Klik Add an IMCI Node dan pilih spesifikasi node.

      2. Pilih waktu Alih bencana.

      3. (Opsional) Tinjau Ketentuan Layanan Produk dan Perjanjian Tingkat Layanan.

      4. Klik Buy Now.

      

   4. Setelah pembayaran selesai, kembali ke halaman detail kluster dan tunggu hingga node read-only indeks penyimpanan kolom ditambahkan. Node tersebut berhasil ditambahkan ketika statusnya berubah menjadi Running.

   Tambahkan saat pembelian

   Pada halaman pembelian PolarDB, pilih jumlah Nodes untuk parameter 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-versi tersebut, indeks penyimpanan kolom diterapkan sebagai ekstensi polar_csi di kluster database. Sebelum Anda dapat menggunakan indeks penyimpanan kolom, Anda harus membuat ekstensi tersebut di database yang ditentukan.

   Catatan

   • Ekstensi polar_csi bersifat cakupan level database. Untuk menggunakan indeks penyimpanan kolom 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.

   Anda dapat menginstal ekstensi polar_csi dengan salah satu dari dua cara berikut.

   Instal dari Konsol

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

   2. Di panel navigasi sebelah kiri, pilih Settings and Management > Extension Management. Pada 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. Pada kotak dialog Install Extension yang muncul, pilih Database Account target dan klik OK untuk menginstal ekstensi di database target.

      

   Instal dari baris perintah

   Hubungkan ke kluster database dan jalankan pernyataan berikut di database target tempat Anda memiliki izin yang diperlukan untuk membuat ekstensi polar_csi.

   CREATE EXTENSION polar_csi;

Langkah 2: Siapkan data

Pada contoh ini, Anda akan membuat tabel partisi multi-level dan memasukkan 320 juta baris data simulasi (sekitar 16 GB). Kemudian, Anda akan melakukan analisis statistik berdasarkan kondisi partisi.

Skema tabel partisi uji adalah sebagai berikut:

• sales: Tabel utama.

• sales_2023: Dipartisi berdasarkan tahun.

  • sales_2023_a: Dipartisi berdasarkan bulan. Partisi ini berisi data untuk bulan 1 hingga 6.

  • sales_2023_b: Dipartisi berdasarkan bulan. Partisi ini berisi data untuk bulan 7 hingga 12.

• sales_2024: Dipartisi berdasarkan tahun.

  • sales_2024_a: Dipartisi berdasarkan bulan. Partisi ini berisi data untuk bulan 1 hingga 6.

  • sales_2024_b: Dipartisi berdasarkan bulan. Partisi ini berisi data untuk bulan 7 hingga 12.

1. Buat tabel partisi multi-level bernama sales. Gunakan kolom sale_date sebagai kunci partisi. Definisinya adalah 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 dan masukkan sekitar 16 GB data ke dalam tabel partisi.

   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 indeks penyimpanan kolom untuk tabel tersebut. Tambahkan bidang sale_id, product_id, sale_date, dan amount ke dalam indeks penyimpanan kolom.

   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 indeks penyimpanan kolom

  --- Aktifkan indeks penyimpanan kolom dan atur tingkat paralelisme kueri menjadi 4.
  SET polar_csi.enable_query to on;
  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;

• Nonaktifkan indeks penyimpanan kolom dan gunakan mesin penyimpanan baris

  --- Nonaktifkan indeks penyimpanan kolom, gunakan mesin penyimpanan baris, dan atur tingkat paralelisme kueri menjadi 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;