List Column Store Tables in Greenplum Database using Data Storage Types
It is common to store data in column store table among analytical data warehouses to achieve high performance on read queries. Pivotal Greenplum data warehouse also supports column oriented storage as well as row-store tables. Since the default storage option is shipped as row-oriented storage, SQL developers can require to check which tables are created in column-store and which are in row-store frequently. In this tutorial I want to show how to check default storage options for data warehouse SQL developers and how to list column-oriented tables in a Greenplum database.
Greenplum Database Default Storage Options
Pivotal Greenplum provides the gp_default_storage_options parameter to set default values used while creation of a database table. To display the gp_default_storage_options parameter value, SQL developers can execute below SHOW command.
show gp_default_storage_options;
The output of the above SHOW command could be similar to shown below
SQL programmers can realize that the default storage option for CREATE TABLE command is row-oriented which means if it is not explicitely stated all tables are created as classic row-store tables.
List Greenplum Database Tables Created as Column-Oriented
Since both row-store and column-store tables can be created on Greenplum data warehouse, it is sometimes required especially for performance considerations which tables are column-oriented and which tables are row-oriented.
To find the list of columnar tables created in a Greenplum database, pg_class table relstorage column value can be queried.
relstorage column stores information about the storage mode of any Greenplum database table.
Possible values for relstorage are as:
a, append-optimized,
c, column-oriented,
h, heap,
v, virtual, and
x, external table
Please execute following SQL query on your Greenplum database to list the column oriented tables created in that database. Just activate the WHERE clause criteria for "c.relstorage = 'c'"
select
n.nspname as schemaname,
c.relname as tablename,
case c.relstorage
when 'a' then ' append-optimized'
when 'c' then 'column-oriented'
when 'h' then 'heap'
when 'v' then 'virtual'
when 'x' then 'external table'
end as "data storage mode"
from pg_class as c
inner join pg_namespace as n
on c.relnamespace = n.oid
where
n.nspname not in ('gp_toolkit',
'information_schema',
'pg_catalog',
'pg_aoseg',
'pg_toast')
-- and c.relstorage = 'c' -- use to filter for column store tables only
order by n.nspname, c.relname ;
If Greenplum database administrators think they will frequently use above SQL query to identify a table is column-oriented or column-store or rowstore, they can create a view for this list.
Create View Greenplum_Table_Data_Orientation
as
select
n.nspname as schemaname,
c.relname as tablename,
c.relstorage,
case c.relstorage
when 'a' then ' append-optimized'
when 'c' then 'column-oriented'
when 'h' then 'heap'
when 'v' then 'virtual'
when 'x' then 'external table'
end as "data storage mode"
from pg_class as c
inner join pg_namespace as n
on c.relnamespace = n.oid
where
n.nspname not in ('gp_toolkit',
'information_schema',
'pg_catalog',
'pg_aoseg',
'pg_toast')
order by n.nspname, c.relname ;
And using SQL view, developers can query the data storage types of Greenplum tables as:
select * from Greenplum_Table_Data_Orientation where relstorage = 'c';
I hope this SQL script is useful for all Greenplum data professionals.