PolarDB：Gunakan indeks penyimpanan kolom untuk mempercepat analitik data deret waktu

Alur operasional

1. Penulisan data: Aplikasi bisnis menulis data deret waktu, seperti catatan transaksi, ke kluster PolarDB for PostgreSQL.

2. Indeks penyimpanan kolom: Buat indeks penyimpanan kolom pada tabel dasar. PolarDB for PostgreSQL secara otomatis memelihara data penyimpanan kolom dalam tabel tersebut. Penyimpanan kolom mengorganisasi data berdasarkan kolom, yang memberikan rasio kompresi lebih tinggi dan mengurangi konsumsi I/O selama kueri agregasi karena hanya kolom yang relevan yang perlu dibaca.

3. Akselerasi kueri: Kueri analitik, seperti agregasi candlestick, diarahkan oleh pengoptimal atau Hint untuk menggunakan indeks penyimpanan kolom. Mesin kueri kemudian menggunakan penyimpanan kolom dan pemrosesan paralel untuk memindai serta mengagregasi data dan mengembalikan hasilnya.

Keunggulan solusi

• Mudah digunakan: Tidak diperlukan modifikasi bisnis atau proses ekstrak, transformasi, dan muat (ETL) yang kompleks. Anda cukup membuat indeks penyimpanan kolom untuk tabel dasar guna mempercepat kueri analitik secara transparan.

• Fitur lengkap: Mendukung secara native tabel partisi dan menyediakan berbagai fungsi analisis deret waktu bawaan, seperti time_bucket, first, dan last, untuk menyederhanakan pengembangan SQL Anda.

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 lakukan dengan hati-hati.

2. Aktifkan fitur indeks penyimpanan kolom.

   Cara mengaktifkan fitur indeks penyimpanan kolom berbeda-beda 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 perlu tindakan apa pun. Anda dapat langsung menggunakan ekstensi tersebut.
   Alokasi sumber daya	Mesin penyimpanan kolom menggunakan semua sumber daya secara eksklusif dan dapat memanfaatkan seluruh 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 analitik (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) dikenakan biaya tambahan dan ditagih dengan tarif yang sama seperti node komputasi biasa.	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 peningkatan/penurunan spesifikasi 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 dalam kluster database. Sebelum Anda dapat menggunakan indeks penyimpanan kolom, Anda harus membuat ekstensi tersebut di database yang ditentukan.

   Catatan

   • Ekstensi polar_csi berlaku pada tingkat database. Untuk menggunakan indeks penyimpanan kolom di beberapa database dalam satu kluster, Anda harus membuat ekstensi polar_csi untuk masing-masing 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 membuka 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 Aksi. Pada kotak dialog Install Extension yang muncul, pilih Database Account target dan klik OK untuk menginstal ekstensi di database target.

      

   Instal dari command line

   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

1. Solusi ini menggunakan tabel catatan transaksi dan mensimulasikan pembuatan 100 juta entri data transaksi selama kurang lebih dua hari. Selama periode ini, transaksi terjadi setiap hari mulai pukul 08.00 hingga 16.00, dengan perkiraan volume data harian sekitar 40 juta entri.

   -- Tabel catatan transaksi
   CREATE TABLE market_trades (
       trade_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- Kunci primer auto-increment
       trade_ts   TIMESTAMP,                                        -- Timestamp transaksi
       market_id  VARCHAR,                                          -- ID pasar
       price      DECIMAL,                                          -- Harga transaksi
       amount     DECIMAL,                                          -- Jumlah transaksi  
       insert_ts  TIMESTAMP                                         -- Timestamp penulisan sistem
   );
   
   INSERT INTO market_trades(trade_ts, market_id, price, amount, insert_ts)
   SELECT
       trade_ts,
       market_id,
       price,
       amount,
       trade_ts + (random() * 500)::INT * INTERVAL '1 millisecond' AS insert_ts
   FROM (
       -- ========================
       -- 1. Puncak Hari 1: 2025-06-01 08:00 - 16:00, 40 juta entri
       -- ========================
       SELECT
           '2025-06-01 08:00:00'::TIMESTAMP +
           (random() * 28800)::INT * INTERVAL '1 second' +  -- 28800 detik = 8 jam
           (random() * 1000)::INT * INTERVAL '1 millisecond' AS trade_ts,
           CASE WHEN random() < 0.6 THEN 'BTC-USDT' ELSE 'ETH-USDT' END AS market_id,
           CASE WHEN random() < 0.6 THEN 30000 + (random() * 1000) ELSE 2000 + (random() * 100) END AS price,
           random() * 10 + 0.1 AS amount
       FROM generate_series(1, 40000000)
   
       UNION ALL
   
       -- ========================
       -- 2. Off-peak Hari 1: 2025-06-01 16:00 - 2025-06-02 08:00, 10 juta entri
       -- ========================
       SELECT
           CASE 
               WHEN random() < 0.5 THEN
                   -- 16:00 - 24:00
                   '2025-06-01 16:00:00'::TIMESTAMP + (random() * 28800)::INT * INTERVAL '1 second'
               ELSE
                   -- 00:00 - 08:00 (dini hari hari 2)
                   '2025-06-02 00:00:00'::TIMESTAMP + (random() * 28800)::INT * INTERVAL '1 second'
           END +
           (random() * 1000)::INT * INTERVAL '1 millisecond' AS trade_ts,
           CASE WHEN random() < 0.6 THEN 'BTC-USDT' ELSE 'ETH-USDT' END AS market_id,
           CASE WHEN random() < 0.6 THEN 30000 + (random() * 1000) ELSE 2000 + (random() * 100) END AS price,
           random() * 10 + 0.1 AS amount
       FROM generate_series(1, 10000000)
   
       UNION ALL
   
       -- ========================
       -- 3. Puncak Hari 2: 2025-06-02 08:00 - 16:00, 40 juta entri
       -- ========================
       SELECT
           '2025-06-02 08:00:00'::TIMESTAMP +
           (random() * 28800)::INT * INTERVAL '1 second' +
           (random() * 1000)::INT * INTERVAL '1 millisecond' AS trade_ts,
           CASE WHEN random() < 0.6 THEN 'BTC-USDT' ELSE 'ETH-USDT' END AS market_id,
           CASE WHEN random() < 0.6 THEN 30000 + (random() * 1000) ELSE 2000 + (random() * 100) END AS price,
           random() * 10 + 0.1 AS amount
       FROM generate_series(1, 40000000)
   
       UNION ALL
   
       -- ========================
       -- 4. Off-peak Hari 2: 2025-06-02 16:00 - 2025-06-03 08:00, 10 juta entri
       -- ========================
       SELECT
           CASE 
               WHEN random() < 0.5 THEN
                   -- 16:00 - 24:00
                   '2025-06-02 16:00:00'::TIMESTAMP + (random() * 28800)::INT * INTERVAL '1 second'
               ELSE
                   -- 00:00 - 08:00 (dini hari hari 3)
                   '2025-06-03 00:00:00'::TIMESTAMP + (random() * 28800)::INT * INTERVAL '1 second'
           END +
           (random() * 1000)::INT * INTERVAL '1 millisecond' AS trade_ts,
           CASE WHEN random() < 0.6 THEN 'BTC-USDT' ELSE 'ETH-USDT' END AS market_id,
           CASE WHEN random() < 0.6 THEN 30000 + (random() * 1000) ELSE 2000 + (random() * 100) END AS price,
           random() * 10 + 0.1 AS amount
       FROM generate_series(1, 10000000)
   ) AS data;

2. Buat indeks penyimpanan kolom untuk tabel catatan transaksi.

   CREATE INDEX idx_csi_market_trades ON market_trades USING CSI;

Langkah 3: Jalankan kueri agregasi candlestick

Skenario: Hitung data candlestick untuk setiap jendela waktu tetap.

Contoh: Hitung harga tertinggi, harga terendah, harga pembukaan, harga penutupan, dan total volume transaksi untuk setiap detik.

Contoh berikut menghitung data candlestick per detik, per menit, per jam, dan per hari.

-- Agregasi candlestick tingkat detik
/*+ SET (polar_csi.enable_query on) */
SELECT
    time_bucket('1 second', trade_ts) AS candle_ts,   -- Data dalam 1 detik
    market_id,
    MIN(price) AS low,                                -- Harga terendah dalam 1 detik
    MAX(price) AS high,                               -- Harga tertinggi dalam 1 detik
    FIRST(price ORDER BY trade_ts) AS open,           -- Harga pembukaan dalam 1 detik
    LAST(price ORDER BY trade_ts) AS close,           -- Harga penutupan dalam 1 detik
    SUM(amount) AS vol                                -- Total volume transaksi dalam 1 detik
FROM market_trades
WHERE trade_ts >= '2025-06-01 00:00:00' AND trade_ts <= '2025-06-02 00:00:00'
GROUP BY candle_ts, market_id
ORDER BY candle_ts, market_id;

-- Agregasi candlestick tingkat menit
/*+ SET (polar_csi.enable_query on) */
SELECT
    time_bucket('1 minute', trade_ts) AS candle_ts,   -- Data dalam 1 menit
    market_id,
    MIN(price) AS low,                                -- Harga terendah dalam 1 menit
    MAX(price) AS high,                               -- Harga tertinggi dalam 1 menit
    FIRST(price ORDER BY trade_ts) AS open,           -- Harga pembukaan dalam 1 menit
    LAST(price ORDER BY trade_ts) AS close,           -- Harga penutupan dalam 1 menit
    SUM(amount) AS vol                                -- Total volume transaksi dalam 1 menit
FROM market_trades
WHERE trade_ts >= '2025-06-01 00:00:00' AND trade_ts <= '2025-06-02 00:00:00'
GROUP BY candle_ts, market_id
ORDER BY candle_ts, market_id;

-- Agregasi candlestick tingkat jam
/*+ SET (polar_csi.enable_query on) */ 
SELECT
    time_bucket('1 hour', trade_ts) AS candle_ts,     -- Data dalam 1 jam
    market_id,
    MIN(price) AS low,                                -- Harga terendah dalam 1 jam
    MAX(price) AS high,                               -- Harga tertinggi dalam 1 jam
    FIRST(price ORDER BY trade_ts) AS open,           -- Harga pembukaan dalam 1 jam
    LAST(price ORDER BY trade_ts) AS close,           -- Harga penutupan dalam 1 jam
    SUM(amount) AS vol                                -- Total volume transaksi dalam 1 jam
FROM market_trades
WHERE trade_ts >= '2025-06-01 00:00:00' AND trade_ts <= '2025-06-02 00:00:00'
GROUP BY candle_ts, market_id
ORDER BY candle_ts, market_id;

-- Agregasi candlestick tingkat hari
/*+ SET (polar_csi.enable_query on) */ 
SELECT
    time_bucket('1 day', trade_ts) AS candle_ts,     -- Data dalam 1 hari
    market_id,
    MIN(price) AS low,                                -- Harga terendah dalam 1 hari
    MAX(price) AS high,                               -- Harga tertinggi dalam 1 hari
    FIRST(price ORDER BY trade_ts) AS open,           -- Harga pembukaan dalam 1 hari
    LAST(price ORDER BY trade_ts) AS close,           -- Harga penutupan dalam 1 hari
    SUM(amount) AS vol                                -- Total volume transaksi dalam 1 hari
FROM market_trades
WHERE trade_ts >= '2025-06-01 00:00:00' AND trade_ts <= '2025-06-02 00:00:00'
GROUP BY candle_ts, market_id
ORDER BY candle_ts, market_id;

Deskripsi SQL

• /*+ SET (polar_csi.enable_query on) */: Petunjuk ini memaksa kueri menggunakan rencana eksekusi indeks penyimpanan kolom. Dalam beberapa skenario, pengoptimal mungkin salah menentukan bahwa penyimpanan baris lebih optimal. Anda dapat menggunakan Hint ini untuk memastikan bahwa kueri menggunakan jalur penyimpanan kolom.

• time_bucket(bucket_width, ts): Fungsi yang disediakan oleh Time Series Database yang mengelompokkan timestamp (ts) berdasarkan interval waktu tertentu (bucket_width).