Columnar engine setup quickstart

Stay organized with collections Save and categorize content based on your preferences.

This page shows steps to quickly enable the columnar engine on an AlloyDB primary instance, and then to automatically populate the engine's column store with the columns that most improve SQL query performance.

  1. Enable the columnar engine.

  2. Let the engine's recommendation feature observe your workload and gather query statistics.

  3. Size the engine's column store based on the recommendation feature's analysis.

  4. Enable automatic population of the column store by the recommendation feature.

  5. Let the recommendation feature observe your workload and automatically add columns to the column store.

Enable the columnar engine

To enable the columnar engine, you set the google_columnar_engine.enabled database flag on the instance and then create the google_columnar_engine extension in the database.

  1. Set the google_columnar_engine.enabled database flag:
    1. In the Google Cloud console, go to the Clusters page.

      Go to Clusters

    2. Click a cluster in the Resource Name column.
    3. In the Overview page, go to Instances in your cluster and then click Edit primary in the box for the primary instance.
    4. In the Edit primary instance panel, click Add flag.
    5. Select the google_columnar_engine.enabled flag from the New database flag list.
    6. Set the value to on.
    7. Click Done.
    8. Click Update instance.

    When you set this flag, the instance restarts.

  2. Create the google_columnar_engine extension:
    1. Connect a psql client to the cluster's primary instance, as described in Connect a psql client to an instance.

    2. At the psql command prompt, connect to the database and create the extension:
      \c DB_NAME
      CREATE EXTENSION IF NOT EXISTS google_columnar_engine;
      

Let the recommendation feature gather statistics

To let the columnar engine's recommendation feature gather statistics, simply let your applications run for a time so that the recommendation feature can observe the workload on the primary instance.

Size the column store

To size the column store, you get a recommendation and then set the google_columnar_engine.memory_size_in_mb database flag to the recommended value.

  1. Get a recommendation:
    1. Connect a psql client to the cluster's primary instance.
    2. At the psql command prompt, connect to the database and run a recommendation:
      \c DB_NAME
      SELECT google_columnar_engine_run_recommendation(
        half_of_primary_instance_memory_in_mb,
        'PERFORMANCE_OPTIMAL'
      );
      

      For half_of_primary_instance_memory_in_mb, enter half of the primary instance's memory, measured in MB.

      The columnar engine generates and displays a recommendation, including a recommended size for the column store.

  2. Set the google_columnar_engine.memory_size_in_mb database flag:
    1. In the Google Cloud console, go to the Clusters page.

      Go to Clusters

    2. Click a cluster in the Resource Name column.
    3. In the Overview page, go to Instances in your cluster and then click Edit primary in the box for the primary instance.
    4. In the Edit primary instance panel, click Add flag.
    5. Select the google_columnar_engine.memory_size_in_mb flag from the New database flag list.
    6. Set the value to the size provided by the recommendation.
    7. Click Done.
    8. Click Update instance.

    When you set this flag, the instance restarts.

Enable automatic population of the column store

To enable automatic population of the column store, you create a policy that lets the columnar engine's recommendation feature automatically add and manage columns to the column store on a scheduled, recurring basis.

  1. Connect a psql client to the cluster's primary instance.
  2. At the psql command prompt, connect to the database and create a policy:
    SELECT google_columnar_engine_add_policy(
      'RECOMMEND_AND_POPULATE_COLUMNS',
      'EVERY', 1, 'HOURS'
    );
    

Let the recommendation feature populate the column store

After the policy you created runs, you can track what's in the column store using the g_columnar_relations view, and you can use the EXPLAIN statement to verify usage of the columnar engine in SQL queries.