PolarDB：Gunakan IMCI untuk mempercepat pembaruan tampilan yang di-materialisasi

Siapkan lingkungan

1. Siapkan kluster PolarDB untuk PostgreSQL yang memenuhi prasyarat.

2. Aktifkan IMCI. Metode untuk mengaktifkannya bervariasi berdasarkan versi mesin minor kluster PolarDB untuk 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 versi ini, kluster PolarDB untuk PostgreSQL mendukung dua metode untuk mengaktifkan fitur ini. Tabel berikut menggambarkan perbedaan-perbedaannya. Anda dapat memilih metode sesuai kebutuhan.

   Item	[Direkomendasikan] Tambahkan node indeks penyimpanan kolom read-only	Gunakan langsung ekstensi indeks penyimpanan kolom pra-instal
   Operasi	Tambahkan node indeks penyimpanan kolom secara manual dari konsol.	Tidak ada operasi yang diperlukan. Anda dapat menggunakan ekstensi secara langsung.
   Alokasi Sumber Daya	Mesin penyimpanan kolom menggunakan semua sumber daya secara eksklusif dan dapat sepenuhnya memanfaatkan semua memori yang tersedia.	Mesin penyimpanan kolom hanya dapat menggunakan 25% dari memori. Sisa memori dialokasikan ke mesin penyimpanan baris.
   Dampak Bisnis	Pemrosesan transaksional (TP) dan pemrosesan analitik (AP) diisolasi pada node yang berbeda dan tidak saling memengaruhi.	TP dan AP berjalan pada node yang sama dan saling memengaruhi.
   Biaya	Anda akan dikenakan biaya tambahan untuk node indeks penyimpanan kolom read-only. Biayanya sama dengan node komputasi reguler.	Tidak ada biaya tambahan.

   Tambahkan node indeks penyimpanan kolom read-only

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

   Catatan

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

   Tambahkan di konsol

   1. Masuk ke Konsol PolarDB dan pilih wilayah tempat kluster Anda berada. Anda dapat mengakses wizard Add/Remove Nodes dengan salah satu cara berikut:

      • Di halaman Clusters, temukan kluster target dan klik Add/Remove Node di kolom Actions.

        

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

        

   2. Pilih Add Columnstore Index Read-only Node dan klik OK.

   3. Di halaman peningkatan/penurunan spesifikasi kluster, tambahkan node indeks penyimpanan kolom read-only dan selesaikan pembayaran.

      1. Klik +Add a Columnstore Index Read-only Node dan pilih spesifikasi node.

      2. Pilih waktu alih bencana.

      3. (Opsional) Lihat Ketentuan Layanan dan Perjanjian Tingkat Layanan produk.

      4. Klik Buy Now.

      

   4. Setelah Anda menyelesaikan pembayaran, kembali ke halaman detail kluster dan tunggu hingga node indeks penyimpanan kolom read-only ditambahkan. Status node berubah menjadi Running.

   Tambahkan selama pembelian

   Di halaman Pembelian PolarDB, pilih jumlah Columnstore Index Read-only Nodes untuk parameter Number Of 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 versi ini dari kluster PolarDB untuk PostgreSQL, indeks penyimpanan kolom diterapkan sebagai ekstensi polar_csi di kluster database. Sebelum Anda dapat menggunakannya, Anda harus membuat ekstensi di database yang ditentukan.

   Catatan

   • Ekstensi polar_csi dibatasi pada tingkat database. Untuk menggunakan indeks penyimpanan kolom di beberapa database dalam 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 cara berikut.

   Instal dari konsol

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

   2. Di panel navigasi di sebelah kiri, pilih Configuration and Management > Extension Management. Di tab Manage Extensions, pilih Uninstalled Extensions.

   3. Di sudut kanan atas halaman, pilih database target. Temukan ekstensi polar_csi dan klik Install di kolom Tindakan. Dalam 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 dengan izin yang diperlukan untuk membuat ekstensi polar_csi.

   CREATE EXTENSION polar_csi;

3. Instal ekstensi pg_hint_plan di sistem bisnis Anda. Ekstensi ini memungkinkan Anda menyesuaikan rencana eksekusi menggunakan petunjuk dalam komentar khusus.

   CREATE EXTENSION pg_hint_plan;

4. Buat ekstensi pg_cron (tugas terjadwal) di database sistem postgres. Ekstensi ini memungkinkan Anda menjalankan tugas secara otomatis pada waktu atau interval tertentu.

   1. Beralih ke database.

      \c postgres;

   2. Instal ekstensi.

      CREATE EXTENSION pg_cron;

Siapkan data

Di sistem bisnis Anda, buat tabel customers dan orders, buat IMCI untuk mereka, dan masukkan data uji.

1. Beralih ke sistem bisnis Anda. Contoh ini menggunakan testdb.

   \c testdb;

2. Buat tabel dan masukkan data.

   -- Buat tabel customers dan IMCI.
   CREATE TABLE customers (
       customer_id SERIAL PRIMARY KEY,
       customer_name VARCHAR(100),
       email VARCHAR(100)
   );
   CREATE INDEX idx_customers_csi ON customers USING csi;
   
   -- Buat tabel orders dan IMCI.
   CREATE TABLE orders (
       order_id SERIAL PRIMARY KEY,
       order_date DATE,
       amount DECIMAL(10, 2),
       customer_id INT REFERENCES customers(customer_id)
   );
   CREATE INDEX idx_orders_csi ON orders USING csi;
   
   -- Masukkan data ke tabel customers.
   INSERT INTO customers (customer_name, email) VALUES 
   ('Alice', 'alice@example.com'),
   ('Bob', 'bob@example.com'),
   ('Charlie', 'charlie@example.com');
   
   -- Masukkan data ke tabel orders.
   INSERT INTO orders (order_date, amount, customer_id) VALUES 
   ('2025-06-01', 200.00, 1),
   ('2025-06-02', 150.00, 2),
   ('2025-06-03', 300.00, 1),
   ('2025-06-04', 100.00, 3);

Buat fungsi untuk memperbarui tampilan yang di-materialisasi

Kapsulasi proses pembaruan dalam sebuah fungsi. Kami merekomendasikan Anda menggunakan fungsi berikut untuk pembaruan karena dapat mengganti tampilan lama dengan aman sambil tetap mempertahankan indeks dan kepemilikannya.

-- view_name adalah nama tampilan yang di-materialisasi, schema_name adalah skema tempat tampilan yang di-materialisasi berada (default adalah current_schema), dan new_owner adalah pemilik dari tampilan yang di-materialisasi yang baru dibuat.
CREATE OR REPLACE FUNCTION refresh_materialized_view_safely_using_csi(
    view_name TEXT,
    schema_name TEXT DEFAULT NULL,
    new_owner TEXT DEFAULT NULL
)
RETURNS BOOL
LANGUAGE plpgsql
AS $$
DECLARE
    view_definition TEXT;
    new_view_name TEXT;
    old_view_name TEXT;
    index_record RECORD;
    index_creation_sql TEXT;
    explain_result TEXT;
    target_schema TEXT;
    qualified_old_name TEXT;
    qualified_new_name TEXT;
    current_owner TEXT;
    grant_record RECORD;
BEGIN
    -- Tentukan skema target (gunakan parameter input atau skema saat ini).
    IF schema_name IS NULL THEN
        target_schema := current_schema();
    ELSE
        target_schema := schema_name;
    END IF;
    
    -- Buat nama tabel yang memenuhi syarat sepenuhnya.
    qualified_old_name := format('%I.%I', target_schema, view_name);
    qualified_new_name := format('%I.%I', target_schema, view_name || '_new');
    
    RAISE NOTICE 'Beroperasi di skema: %', target_schema;

    -- Verifikasi bahwa tampilan yang di-materialisasi ada.
    IF NOT EXISTS (
        SELECT 1 FROM pg_matviews 
        WHERE matviewname = view_name 
        AND schemaname = target_schema
    ) THEN
        RAISE EXCEPTION 'Tampilan yang di-materialisasi "%" tidak ada di skema "%"', view_name, target_schema;
    END IF;

    -- Dapatkan definisi dan pemilik saat ini dari tampilan yang di-materialisasi.
    SELECT m.definition, p.rolname INTO view_definition, current_owner
    FROM pg_matviews m
    JOIN pg_class c ON m.matviewname = c.relname AND m.schemaname = target_schema
    JOIN pg_roles p ON c.relowner = p.oid
    WHERE m.matviewname = view_name
    AND m.schemaname = target_schema;
    
    IF view_definition IS NULL THEN
        RAISE EXCEPTION 'Gagal mengambil definisi untuk tampilan yang di-materialisasi "%"', view_name;
    END IF;

    -- Atur nama untuk tampilan baru dan lama.
    old_view_name := view_name;
    new_view_name := view_name || '_new';

    -- Parameter performa untuk IMCI.
    SET LOCAL polar_csi.cost_threshold = 0;

    -- Cetak rencana kueri.
    RAISE NOTICE 'Rencana kueri untuk pembaruan tampilan yang di-materialisasi:';
    FOR explain_result IN EXECUTE format('/*+ SET(polar_csi.enable_query on) */ EXPLAIN CREATE MATERIALIZED VIEW %s AS %s', qualified_new_name, view_definition) LOOP
        RAISE NOTICE '%', explain_result;
    END LOOP;
    
    BEGIN
        -- Buat tampilan yang di-materialisasi baru.
        EXECUTE format('/*+ SET(polar_csi.enable_query on) */ CREATE MATERIALIZED VIEW %s AS %s', qualified_new_name, view_definition);

        -- Jika pemilik baru ditentukan, atur pemilik.
        IF new_owner IS NOT NULL THEN
            -- Verifikasi bahwa pengguna ada.
            IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = new_owner) THEN
                RAISE EXCEPTION 'Role "%" tidak ada', new_owner;
            END IF;
            
            EXECUTE format('ALTER MATERIALIZED VIEW %s OWNER TO %I', qualified_new_name, new_owner);
            RAISE NOTICE 'Mengubah pemilik dari "%" ke "%"', current_owner, new_owner;
        END IF;
        
        -- Salin semua indeks dari tampilan lama ke tampilan baru.
        FOR index_record IN 
            SELECT indexname, indexdef 
            FROM pg_indexes 
            WHERE tablename = old_view_name 
            AND schemaname = target_schema
        LOOP
            -- Ganti nama tampilan lama dengan nama tampilan baru.
            index_creation_sql := regexp_replace(
                index_record.indexdef, 
                ' ON ' || target_schema || '.' || old_view_name || ' ', 
                ' ON ' || target_schema || '.' || new_view_name || ' ', 
                'i'
            );
            
            -- Tangani kasus khusus indeks UNIQUE.
            index_creation_sql := regexp_replace(
                index_creation_sql, 
                'INDEX ' || index_record.indexname || ' ON', 
                'INDEX ' || index_record.indexname || '_new ON', 
                'i'
            );

            RAISE NOTICE 'Membuat indeks: %', index_creation_sql;
            EXECUTE index_creation_sql;
        END LOOP;

        -- Salin izin dari tampilan lama.
        RAISE NOTICE 'Memulihkan izin ke tampilan baru %.%', target_schema, new_view_name;
        FOR grant_record IN
        SELECT 
            (acl).grantee::regrole::text AS grantee,
            (acl).privilege_type
        FROM 
            pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        CROSS JOIN aclexplode(c.relacl) AS acl
        WHERE 
            n.nspname = target_schema
        AND c.relname = old_view_name
        LOOP
            CONTINUE WHEN grant_record.grantee IS NULL;
            -- RAISE NOTICE 'Memberikan % ON %.% TO %',
            --     grant_record.privilege_type, target_schema, new_view_name, grant_record.grantee;

            EXECUTE format(
                'GRANT %s ON %I.%I TO %s', 
                grant_record.privilege_type,
                target_schema,
                new_view_name,
                quote_ident(grant_record.grantee) 
            );
        END LOOP;
        
        -- Hapus tampilan yang di-materialisasi lama.
        EXECUTE format('DROP MATERIALIZED VIEW %s', qualified_old_name);
        
        -- Ubah nama tampilan yang di-materialisasi baru menjadi nama aslinya.
        EXECUTE format('ALTER MATERIALIZED VIEW %s RENAME TO %I', qualified_new_name, old_view_name);
        
        -- Ubah nama indeks (hapus akhiran _new).
        FOR index_record IN 
            SELECT indexname 
            FROM pg_indexes 
            WHERE tablename = old_view_name 
            AND schemaname = target_schema
        LOOP
            IF position('_new' in index_record.indexname) > 0 THEN
                EXECUTE format(
                    'ALTER INDEX %I.%I RENAME TO %I', 
                    target_schema,
                    index_record.indexname, 
                    replace(index_record.indexname, '_new', '')
                );
            END IF;
        END LOOP;
        
        RETURN TRUE;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE EXCEPTION 'Gagal memperbarui tampilan yang di-materialisasi: %', SQLERRM;
            RETURN FALSE;
    END;
END;
$$;

Parameter

Lakukan pembaruan

SELECT refresh_materialized_view_safely_using_csi('mv_customer_orders');

SELECT * FROM cron.job;

jobid  |  schedule   |                                           command                                            | nodename | nodeport | database | username | active |          jobname           
-------+-------------+----------------------------------------------------------------------------------------------+----------+----------+----------+----------+--------+----------------------------
     1 | */5 * * * * | SELECT refresh_materialized_view_safely_using_csi('mv_customer_orders', 'public', 'polarpg') | /data/.  |     3000 | testdb   | polarpg  | t      | refresh_mv_customer_orders
(1 row)

SELECT cron.unschedule('refresh_my_materialized_view');

SELECT * FROM cron.job_run_details;

 jobid | runid | job_pid | database | username |                                           command                                            |  status   | return_message |          start_time           |           end_time            
-------+-------+---------+----------+----------+----------------------------------------------------------------------------------------------+-----------+----------------+-------------------------------+-------------------------------
     1 |     1 |   76537 | testdb   | polarpg  | SELECT refresh_materialized_view_safely_using_csi('mv_customer_orders', 'public', 'polarpg') | succeeded | 1 row          | 2025-08-27 08:35:00.007231+00 | 2025-08-27 08:35:00.024946+00
(1 rows)