PolarDB:Use IMCI to accelerate materialized view refreshes

Prepare the environment

1. Prepare a PolarDB for PostgreSQL cluster that meets the prerequisites.

2. Enable IMCI. The method for enabling it varies based on the minor engine version of your PolarDB for PostgreSQL cluster:

   PostgreSQL 16 (2.0.16.9.8.0 or later) or PostgreSQL 14 (2.0.14.17.35.0 or later)

   For these versions, PolarDB for PostgreSQL clusters support two methods to enable this feature. The following table describes the differences. You can choose a method as needed.

   Item	[Recommended] Add a columnstore index read-only node	Directly use the pre-installed columnstore index extension
   Operation	Add a columnstore index node manually from the console.	No operation is required. You can use the extension directly.
   Resource allocation	The column store engine exclusively uses all resources and can fully utilize all available memory.	The column store engine can use only 25% of the memory. The remaining memory is allocated to the row store engine.
   Business impact	Transactional processing (TP) and analytical processing (AP) workloads are isolated on different nodes and do not affect each other.	TP and AP workloads run on the same node and affect each other.
   Cost	You are charged an extra fee for the columnstore index read-only node. The fee is the same as that for a regular compute node.	No additional cost.

   Add a columnstore index read-only node

   You can add a columnstore index read-only node in either of the following ways:

   Note

   The cluster must contain a read-only node. You cannot add a columnstore index read-only node to a single-node cluster.

   Add in the console

   1. Log on to the PolarDB console and select the region where your cluster is located. You can access the Add/Remove Nodes wizard in one of the following ways:

      • On the Clusters page, find the target cluster and click Add/Remove Node in the Actions column.

        

      • On the Basic Information page of the target cluster, click Add/Remove Node in the Database Nodes section.

        

   2. Select Add Columnstore Index Read-only Node and click OK.

   3. On the cluster upgrade/downgrade page, add a columnstore index read-only node and complete the payment.

      1. Click +Add a Columnstore Index Read-only Node and select the node specifications.

      2. Select a switchover time.

      3. (Optional) View the product's Terms of Service and Service Level Agreement.

      4. Click Buy Now.

      

   4. After you complete the payment, return to the cluster details page and wait until the columnstore index read-only node is added. The node status changes to Running.

   Add during purchase

   On the PolarDB purchase page, select the number of Columnstore Index Read-only Nodes for the Number Of Nodes parameter.

   

   PostgreSQL 16 (2.0.16.8.3.0 to 2.0.16.9.8.0) or PostgreSQL 14 (2.0.14.10.20.0 to 2.0.14.17.35.0)

   For these versions of PolarDB for PostgreSQL clusters, the columnstore index is deployed as the polar_csi extension in the database cluster. Before you can use it, you must create the extension in the specified database.

   Note

   • The polar_csi extension is scoped to the database level. To use the columnstore index in multiple databases within a cluster, you must create the polar_csi extension for each database.

   • The database account used to install the extension must be a privileged account.

   You can install the polar_csi extension in either of the following ways.

   Install from the console

   1. Log on to the PolarDB console. In the navigation pane on the left, click Clusters. Select the region where your cluster is located, and then click the ID of the target cluster to open the cluster details page.

   2. In the navigation pane on the left, choose Configuration and Management > Extension Management. On the Manage Extensions tab, select Uninstalled Extensions.

   3. In the upper-right corner of the page, select the target database. Find the polar_csi extension and click Install in the Actions column. In the Install Extension dialog box that appears, select the target Database Account and click OK to install the extension in the target database.

      

   Install from the command line

   Connect to the database cluster and run the following statement in the target database with the required permissions to create the polar_csi extension.

   CREATE EXTENSION polar_csi;

3. Install the pg_hint_plan extension in your business system. This extension lets you adjust execution plans using hints in special comments.

   CREATE EXTENSION pg_hint_plan;

4. Create the pg_cron (scheduled task) extension in the postgres system database. This extension lets you automatically run tasks at specified times or intervals.

   1. Switch to the database.

      \c postgres;

   2. Install the extension.

      CREATE EXTENSION pg_cron;

Prepare the data

In your business system, create the customers and orders tables, create an IMCI for them, and insert test data.

1. Switch to your business system. This example uses testdb.

   \c testdb;

2. Create tables and insert data.

   -- Create the customers table and an 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;
   
   -- Create the orders table and an 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;
   
   -- Insert data into the customers table.
   INSERT INTO customers (customer_name, email) VALUES 
   ('Alice', 'alice@example.com'),
   ('Bob', 'bob@example.com'),
   ('Charlie', 'charlie@example.com');
   
   -- Insert data into the orders table.
   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);

Create a function to refresh the materialized view

Encapsulate the refresh process in a function. We recommend that you use the following function for refreshes because it can safely replace the old view while preserving its indexes and ownership.

-- view_name is the name of the materialized view, schema_name is the schema where the materialized view resides (default is current_schema), and new_owner is the owner of the newly created materialized view.
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
    -- Determine the target schema (use the input parameter or the current schema).
    IF schema_name IS NULL THEN
        target_schema := current_schema();
    ELSE
        target_schema := schema_name;
    END IF;
    
    -- Construct the fully qualified table name.
    qualified_old_name := format('%I.%I', target_schema, view_name);
    qualified_new_name := format('%I.%I', target_schema, view_name || '_new');
    
    RAISE NOTICE 'Operating in schema: %', target_schema;

    -- Verify that the materialized view exists.
    IF NOT EXISTS (
        SELECT 1 FROM pg_matviews 
        WHERE matviewname = view_name 
        AND schemaname = target_schema
    ) THEN
        RAISE EXCEPTION 'Materialized view "%" does not exist in schema "%"', view_name, target_schema;
    END IF;

    -- Get the definition and current owner of the materialized view.
    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 'Failed to retrieve definition for materialized view "%"', view_name;
    END IF;

    -- Set the names for the new and old views.
    old_view_name := view_name;
    new_view_name := view_name || '_new';

    -- Performance parameters for IMCI.
    SET LOCAL polar_csi.cost_threshold = 0;

    -- Print the query plan.
    RAISE NOTICE 'Query plan for materialized view refresh:';
    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
        -- Create the new materialized view.
        EXECUTE format('/*+ SET(polar_csi.enable_query on) */ CREATE MATERIALIZED VIEW %s AS %s', qualified_new_name, view_definition);

        -- If a new owner is specified, set the owner.
        IF new_owner IS NOT NULL THEN
            -- Verify that the user exists.
            IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = new_owner) THEN
                RAISE EXCEPTION 'Role "%" does not exist', new_owner;
            END IF;
            
            EXECUTE format('ALTER MATERIALIZED VIEW %s OWNER TO %I', qualified_new_name, new_owner);
            RAISE NOTICE 'Changed owner from "%" to "%"', current_owner, new_owner;
        END IF;
        
        -- Copy all indexes from the old view to the new view.
        FOR index_record IN 
            SELECT indexname, indexdef 
            FROM pg_indexes 
            WHERE tablename = old_view_name 
            AND schemaname = target_schema
        LOOP
            -- Replace the old view name with the new view name.
            index_creation_sql := regexp_replace(
                index_record.indexdef, 
                ' ON ' || target_schema || '.' || old_view_name || ' ', 
                ' ON ' || target_schema || '.' || new_view_name || ' ', 
                'i'
            );
            
            -- Handle the special case of UNIQUE indexes.
            index_creation_sql := regexp_replace(
                index_creation_sql, 
                'INDEX ' || index_record.indexname || ' ON', 
                'INDEX ' || index_record.indexname || '_new ON', 
                'i'
            );

            RAISE NOTICE 'Creating index: %', index_creation_sql;
            EXECUTE index_creation_sql;
        END LOOP;

        -- Copy permissions from the old view.
        RAISE NOTICE 'Restoring permissions to new view %.%', 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 'Granting % 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;
        
        -- Drop the old materialized view.
        EXECUTE format('DROP MATERIALIZED VIEW %s', qualified_old_name);
        
        -- Rename the new materialized view to the original name.
        EXECUTE format('ALTER MATERIALIZED VIEW %s RENAME TO %I', qualified_new_name, old_view_name);
        
        -- Rename the indexes (remove the _new suffix).
        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 'Failed to refresh materialized view: %', SQLERRM;
            RETURN FALSE;
    END;
END;
$$;

Parameters

Perform the refresh

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)