PolarDB:Use o IMCI para acelerar atualizações de materialized views

Prepare o ambiente

1. Um cluster PolarDB for PostgreSQL elegível.

2. Ative o recurso de índice columnstore.

   O método para ativar o IMCI varia conforme a versão secundária do mecanismo do seu cluster PolarDB for PostgreSQL:

   PostgreSQL 16 (2.0.16.9.8.0 ou posterior) ou PostgreSQL 14 (2.0.14.17.35.0 ou posterior)

   Para clusters PolarDB for PostgreSQL com essas versões, dois métodos estão disponíveis. A tabela a seguir destaca as diferenças.

   Item de comparação	[Recomendado] Adicionar um nó somente leitura IMCI	Usar diretamente a extensão de índice columnstore pré-instalada
   Método	Adicione manualmente um nó somente leitura IMCI no console.	Nenhuma ação é necessária. Use a extensão diretamente.
   Alocação de recursos	O mecanismo columnstore usa exclusivamente os recursos do nó, incluindo toda a memória disponível.	O mecanismo columnstore fica limitado a 25% da memória do nó. A memória restante é alocada para o mecanismo row store.
   Impacto nos negócios	As cargas de trabalho de processamento transacional (TP) e processamento analítico (AP) ficam isoladas em nós diferentes e não se afetam mutuamente.	As cargas de trabalho TP e AP são executadas no mesmo nó e podem afetar umas às outras.
   Custos	Nós somente leitura IMCI geram cobranças adicionais e são faturados à mesma taxa dos nós de computação regulares.	Sem custo adicional.

   Adicionar um nó somente leitura IMCI

   Existem duas maneiras de adicionar um nó somente leitura IMCI:

   Nota

   O cluster deve conter pelo menos um nó somente leitura. Não é possível adicionar um nó somente leitura IMCI a um cluster de nó único.

   Console

   1. Faça login no console do PolarDB e selecione a região do cluster. Abra o assistente Add/Remove Node de uma das seguintes formas:

      • Na página Clusters, clique em Add/Remove Node na coluna Actions.

      • Na página Basic Information do cluster desejado, clique em Add/Remove Node na seção Database Nodes.

   2. Selecione Add Read-only IMCI Node e clique em OK.

   3. Na página de upgrade/downgrade do cluster, adicione o nó somente leitura IMCI e conclua o pagamento.

      1. Clique em Add a Read-only IMCI Node e selecione as especificações do nó.

      2. Escolha um horário para o switchover.

      3. (Opcional) Revise os Termos de Serviço do Produto e o Acordo de Nível de Serviço.

      4. Clique em Buy Now.

   4. Após a conclusão do pagamento, retorne à página de detalhes do cluster e aguarde a adição do nó somente leitura IMCI. O nó estará pronto quando seu status mudar para Running.

   During purchase

   Na página de compra do PolarDB, na seção Nodes, especifique o número de IMCI Read-Only Nodes.

   PostgreSQL 16 (2.0.16.8.3.0 a 2.0.16.9.8.0) ou PostgreSQL 14 (2.0.14.10.20.0 a 2.0.14.17.35.0)

   Em clusters PolarDB for PostgreSQL com essas versões, o recurso IMCI é fornecido como a extensão polar_csi. Para usar o IMCI, crie primeiro a extensão no banco de dados desejado.

   Nota

   • A extensão polar_csi tem escopo no nível do banco de dados. Para usar o IMCI em vários bancos de dados dentro de um cluster, crie a extensão polar_csi para cada banco de dados.

   • A conta de banco de dados usada para instalar a extensão deve ser uma conta privilegiada.

   Há duas maneiras de instalar a extensão polar_csi:

   Console

   1. Faça login no console do PolarDB. No painel de navegação à esquerda, clique em Clusters. Selecione a região onde seu cluster está localizado e clique no ID do cluster para abrir a página de detalhes do cluster.

   2. No painel de navegação à esquerda, escolha Settings and Management > Extension Management. Na aba Extension Management, selecione Uninstalled Extensions.

   3. No canto superior direito da página, selecione o banco de dados alvo. Na linha da extensão polar_csi, clique em Install na coluna Actions. Na caixa de diálogo Install Extension, selecione a Database Account alvo e clique em OK para instalar a extensão no banco de dados alvo.

   CLI

   Conecte-se ao cluster de banco de dados e execute a seguinte instrução em um banco de dados onde você tenha permissões suficientes para criar a extensão polar_csi.

   CREATE EXTENSION polar_csi;

3. No banco de dados alvo (banco de dados de negócios), instale a extensão pg_hint_plan. Essa extensão permite usar hints especiais no estilo de comentários para ajustar planos de consulta já escolhidos.

   CREATE EXTENSION pg_hint_plan;

4. No banco de dados de sistema postgres, instale a extensão pg_cron (tarefas agendadas). Essa extensão executa tarefas automaticamente em um horário ou intervalo especificado.

   1. Mude para o banco de dados.

      \c postgres;

   2. Instale a extensão.

      CREATE EXTENSION pg_cron;

Prepare os dados

No banco de dados alvo (banco de dados de negócios), crie a tabela customers e a tabela orders, crie índices columnstore nelas e insira dados de teste.

1. Mude para o banco de dados alvo (banco de dados de negócios). Este exemplo usa testdb.

   \c testdb;

2. Crie as tabelas e insira os dados.

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

/*+ SET(polar_csi.enable_query on) SET(polar_csi.cost_threshold 0) SET(polar_csi.max_parallel_workers 6) SET(polar_csi.memory_limit 10240) */CREATE MATERIALIZED VIEW mv_customer_orders AS
SELECT
    c.customer_name AS customer_name,
    o.order_date AS order_date,
    o.amount AS amount
FROM
    orders o
JOIN
    customers c ON o.customer_id = c.customer_id;

Crie a função de atualização

O processo de atualização é encapsulado em uma função. Recomendamos a função a seguir porque ela substitui a nova view com segurança, preservando seus índices e propriedade.

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

Parâmetros