PolarDB:Use IMCI to accelerate materialized view refreshes

Step 1: Enable IMCI

The method for enabling IMCI depends on your minor engine version.

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

These versions support two methods. Choose based on your workload isolation and cost requirements:

	PolarDB purchase page[Recommended] Add a columnstore index read-only node	Use the pre-installed columnstore index extension
Setup	Add a node from the console	No setup required
Memory	The column store engine uses all available memory on the dedicated node	The column store engine is limited to 25% of node memory. The remaining memory is allocated to the row store engine.
Workload isolation	TP and AP workloads run on separate nodes and do not affect each other	TP and AP workloads share a node and may affect each other
Cost	Charged at the same rate as a regular compute node	No additional cost

Add a columnstore index read-only node

The cluster must already have at least one read-only node. Single-node clusters do not support adding a columnstore index read-only node.

Option A: Add from the console

1. Log on to the PolarDB console and select the region where your cluster is located. Open the Add/Remove Nodes wizard using one of these methods:

   • On the Basic Information page of your 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, configure the node and complete payment:

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

   2. Select a switchover time.

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

   4. Click Buy Now.

   

4. Return to the cluster details page and wait for the node status to change to Running.

   

Option B: Add during purchase

On the PolarDB purchase page, set the Columnstore Index Read-only Nodes count under the Number Of Nodes parameter.

CREATE EXTENSION polar_csi;

Step 2: Install supporting extensions

Install the pg_hint_plan extension, which lets you control the query optimizer using hints in SQL comments:

CREATE EXTENSION pg_hint_plan;

Install the pg_cron extension in the postgres system database for scheduled refresh tasks:

\c postgres;
CREATE EXTENSION pg_cron;

Step 3: Prepare the base tables

Switch to your business database and create the base tables with columnstore indexes.

This example uses a testdb database with customers and orders tables:

\c testdb;

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

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

About the refresh strategy

The refresh function described below uses a full-rebuild strategy: it creates a new materialized view, copies indexes and permissions from the old one, drops the old view, and renames the new one. This ensures IMCI hints are applied consistently on every refresh.

Create the refresh function

This function is provided as a reference implementation. Test it thoroughly in a staging environment before using it in production.

-- Parameters:
--   view_name:   Name of the materialized view to refresh
--   schema_name: Schema containing the materialized view (defaults to current_schema)
--   new_owner:   Owner to assign to the newly created view (optional)
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;

            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;
$$;

Function parameters