PolarDB:Use IMCI to accelerate materialized view refreshes

Prepare the environment

1. An eligible PolarDB for PostgreSQL (Compatible with Oracle) cluster.

2. Enable the columnstore index feature.

   The method for enabling IMCI varies depending on the minor engine version of your PolarDB for PostgreSQL (Compatible with Oracle) cluster:

   Oracle syntax compatibility 2.0 (2.0.14.17.35.0 or later)

   For PolarDB for PostgreSQL (Compatible with Oracle) clusters with these versions, two methods are available. The following table outlines the differences.

   Comparison item	[Recommended] Add an IMCI read-only node	Directly use the pre-installed columnstore index extension
   Method	You can add an IMCI read-only node manually in the console.	No action is required. You can use the extension directly.
   Resource allocation	The columnstore engine exclusively uses the node's resources, including all available memory.	The columnstore engine is limited to 25% of the node's 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 can affect each other.
   Costs	IMCI read-only nodes incur additional charges and are billed at the same rate as regular compute nodes.	No additional cost.

   Add an IMCI read-only node

   There are two ways to add an IMCI read-only node:

   Note

   The cluster must contain at least one read-only node. You cannot add an IMCI read-only node to a single-node cluster.

   Console

   1. Log on to the PolarDB console and select the cluster's region. You can open the Add/Remove Node wizard in one of the following ways:

      • On the Clusters page, 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 Read-only IMCI Node and click OK.

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

      1. Click Add a Read-only IMCI Node and select the node specifications.

      2. Select a switchover time.

      3. (Optional) Review the Product Terms of Service and Service Level Agreement.

      4. Click Buy Now.

   4. After the payment is complete, return to the cluster details page and wait for the IMCI read-only node to be added. The node is ready when its status changes to Running.

   During purchase

   On the PolarDB purchase page, in the Nodes section, specify the number of IMCI Read-Only Nodes.

   Oracle syntax compatibility 2.0 (2.0.14.10.20.0 to 2.0.14.17.35.0)

   For PolarDB for PostgreSQL (Compatible with Oracle) clusters with these versions, the IMCI feature is provided as the polar_csi extension. To use IMCI, you must first create the extension in the desired database.

   Note

   • The polar_csi extension is scoped at the database level. To use IMCI 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.

   There are two ways to install the polar_csi extension:

   Console

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

   2. In the left-side navigation pane, choose Settings and Management > Extension Management. On the Extension Management tab, select Uninstalled Extensions.

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

   CLI

   Connect to the database cluster and run the following statement in a database where you have sufficient permissions to create the polar_csi extension.

   CREATE EXTENSION polar_csi;

3. In your target database (business database), install the pg_hint_plan extension. This extension lets you use special comment-style hints to adjust query plans that have already been chosen.

   CREATE EXTENSION pg_hint_plan;

4. In the postgres system database, install the pg_cron (scheduled tasks) extension. This extension runs tasks automatically at a specified time or interval.

   1. Switch to the database.

      \c postgres;

   2. Install the extension.

      CREATE EXTENSION pg_cron;

Prepare the data

In your target database (business database), create the customers table and the orders table, build columnstore indexes on them, and insert test data.

1. Switch to the target database (business database). This example uses testdb.

   \c testdb;

2. Create the tables and insert the data.

   -- Create the customers table and its columnstore index.
   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 its columnstore index.
   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 the refresh function

The refresh process is wrapped in a function. We recommend the following function because it swaps in the new view safely while preserving its indexes and ownership.

-- view_name is the name of the materialized view. schema_name is the schema in which the view resides (defaults to current_schema). new_owner is the owner assigned to the newly created 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 fully qualified table names.
    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;

    -- Retrieve 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 old and new views.
    old_view_name := view_name;
    new_view_name := view_name || '_new';

    -- IMCI performance parameters.
    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 view permissions.
        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

Execute 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)