Essential SQL for exploring AlloyDB Columnar Engine.

In one of my previous blogs, I added shortcuts to the psql configuration to simplify and expedite interactions with AlloyDB, specifically focusing on the Columnar Store.

Check it out here → https://medium.com/google-cloud/enhancing-google-cloud-alloydb-and-postgresql-interaction-with-custom-psql-shortcuts-f0fdc3ce55c3

In this blog, I will be sharing same as SQL script that I frequently use in my work with AlloyDB, specifically leveraging its Columnar engine capabilities.

1. List Table within Columnar store.

List Table within columnar store along with size and additional details.

SELECT
 n.nspname as "Schema",
 c.relname as "Name",CASE
  c.relkind
  WHEN 'r' THEN 'table'
  WHEN 'v' THEN 'view'
  WHEN 'm' THEN 'materialized view'
  WHEN 'i' THEN 'index'
  WHEN 'S' THEN 'sequence'
  WHEN 't' THEN 'TOAST table'
  WHEN 'f' THEN 'foreign table'
  WHEN 'p' THEN 'partitioned table'
  WHEN 'I' THEN 'partitioned index'
 END as "Type",
 pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
 pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
 gcol.status as "ColumnarCache",
 pg_catalog.pg_size_pretty(gcol.size) as "ColCacheSizeMB",
 pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM
 pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
 INNER JOIN g_columnar_relations gcol on gcol.schema_name = n.nspname
 and gcol.relation_name = c.relname
 AND gcol.status = 'Usable'
WHERE
 c.relkind IN ('r', 'p', '')
 AND n.nspname <> 'pg_catalog'
 AND n.nspname !~ '^pg_toast'
 AND n.nspname <> 'information_schema'
 AND pg_catalog.pg_table_is_visible(c.oid)
 and gcol.database_name = current_database()
ORDER BY 1,2;

2. List Table along with Columns within Columnar store.

List all columns from a table within columnar store with it specific size.

SELECT
 gcols.relation_name as "TableName",
 a.attname,
 pg_catalog.format_type(a.atttypid, a.atttypmod),(
  SELECT
   pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
  FROM
   pg_catalog.pg_attrdef d
  WHERE
   d.adrelid = a.attrelid
   AND d.adnum = a.attnum
   AND a.atthasdef
 ) as "Default",
 a.attnotnull,
 gcols.status as ColumnarCache,
 round(gcols.size_in_bytes / 1024 / 1024, 2) as ColCacheSizeMB
FROM
 pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 left join pg_catalog.pg_attribute a on a.attrelid = c.oid
 INNER JOIN g_columnar_columns gcols on gcols.schema_name = n.nspname
 and gcols.relation_name = c.relname
 AND gcols.status = 'Usable'
 and gcols.column_name = a.attname
WHERE
 pg_catalog.pg_table_is_visible(c.oid)
 AND a.attnum > 0
 AND NOT a.attisdropped
 AND gcols.database_name = current_database()
ORDER BY
 a.attnum;

3. Columnar block units consumed along with invalidation to be refresh.

Identify blocks consumed by columnar store and highlight invalid blocks cause due to DML on table. It helps us to monitor if columnar store refresh is needed or not for a table.

select
        schema_name,
        relation_name,
        status,
        swap_status,
        sum(end_block - start_block) ttl_block,
        sum(invalid_block_count) invalid_block,
        round(
                100 * sum(invalid_block_count) / sum(end_block - start_block),
                1
        ) as invalid_block_perc,
        pg_size_pretty(sum(size)) ttl_size,
        pg_size_pretty(sum(cached_size_bytes)) ttl_cached_size
from
        g_columnar_units
where
        g_columnar_units.database_name = current_database()
group by
        schema_name,
        relation_name,
        status,
        swap_status;

4. Local Storage consumed by Google Columnar Tables.

It gives insight on local storage consumed and available along with
Swapped out columns details.

select
        pg_size_pretty(memory_total) as cc_allocated,
        pg_size_pretty(memory_total - memory_available) as cc_consumed,
        pg_size_pretty(memory_available) cc_available,
        google_columnar_engine_local_storage_used() as cc_local_storage_used_mb,
        google_columnar_engine_local_storage_available() as cc_local_storage_avail_mb,
        CASE
                WHEN google_columnar_engine_local_storage_used() IS NOT NULL THEN 'Swapped-out Column(s)'
                ELSE null
        end as "SwapOut",
        (
                select
                        CONCAT_WS(
                                '-',
                                STRING_AGG(distinct g_columnar_units.relation_name, '/'),
                                STATUS,
                                swap_status
                        )
                from
                        g_columnar_units
                group by
                        status,
                        swap_status
        ) as current_obj
from
        g_columnar_memory_usage
where
        memory_name = 'main_pool';

5. List all native AlloyDB related Flags changed from default.

SELECT
 s.name AS "Parameter",
 pg_catalog.current_setting(s.name) AS "Value"
FROM
 pg_catalog.pg_settings s
WHERE
 s.source <> 'default'
 AND s.setting IS DISTINCT
FROM
 s.boot_val
 AND lower(s.name) like '%google%'
ORDER BY
 1;

6. List all google_columnar_engine related flags.

SELECT
 s.name AS "Parameter",
 pg_catalog.current_setting(s.name) AS "Value"
FROM
 pg_catalog.pg_settings s
WHERE
 pg_catalog.lower(s.name) OPERATOR(pg_catalog.~) '^(google_columnar_engine..*)$' COLLATE pg_catalog.default
ORDER BY
 1;

7. Columnar Memory metric.

select
        memory_name,
        memory_total / 1024 / 1024 memory_total_MB,
        memory_available / 1024 / 1024 memory_available_MB,
        memory_available_percentage
from
        g_columnar_memory_usage;

8. List all AlloyDB related Configuration Flags.

SELECT
 s.name AS "Parameter",
 pg_catalog.current_setting(s.name) AS "Value"
FROM
 pg_catalog.pg_settings s
WHERE
 pg_catalog.lower(s.name) OPERATOR(pg_catalog.~) '^(alloydb.*)$' COLLATE pg_catalog.default
ORDER BY
 1;