How to check the actual size of an AO Table

Products

• VMware Tanzu Greenplum

Issue/Introduction

Append Only tables might have compression. Below you will find an explanation on how to calculate the size of AO table with compression, indexes, and partitions.

Environment

Product Version: 4.3.30

Resolution

Note: In order to have accurate results, please run VACUUM and ANALYZE first on the tables. 

1. For a non partition tables:

For non-partition tables we can use get_ao_compression_ratio function and pg_relation_size. 

Example:

SELECT  pg_size_pretty(pg_relation_size('ao_compres_with_index')) data_disk_size, get_ao_compression_ratio('ao_compres_with_index') compresion_ratio, pg_size_pretty(round(pg_relation_size('ao_compres_with_index')*get_ao_compression_ratio('ao_compres_with_index'))::bigint) uncompresed_data_size, pg_size_pretty(pg_total_relation_size('ao_compres_with_index') - pg_relation_size('ao_compres_with_index')) index_and_headers_size,   pg_size_pretty(pg_total_relation_size('ao_compres_with_index')) total_table_disk_size;


 data_disk_size | compresion_ratio | uncompresed_data_size | index_and_headers_size | total_table_disk_size
----------------+------------------+-----------------------+------------+-----------------------
 33 MB          |             2.85 | 95 MB                 | 214 MB     | 247 MB
(1 row)

Columns:

data_disk_size:

The size of the data in the table. This do not take into account the bloat, only actual sizeof data. 

compresion_ratio: 

How well is the data compressed

uncompresed_data_size: 

Data size * compress ration

index_and_headers_size: 

If the column has index it is not compressed as the data. Also all tables have some small overhead for headers etc. 

total_table_disk_size: 

use pg_total_relation_size function so this is the size off data, indexes, headers etc, on the disk

If if want to know the exact size of indexes, you can use the following query:

SELECT schemaname, tablename, pg_size_pretty(sum(pg_relation_size(indexname))::bigint) index_size from pg_indexes where tablename = 'ao_compres_with_index' group by schemaname, tablename;
 schemaname |       tablename       | index_size
------------+-----------------------+------------
 public     | ao_compres_with_index | 213 MB
(1 row)

Please note that the data_disk_size might not translate directly to a disk size needed when you take a backup. This is due to a difference in database block size and Hard disk block allocation. 

2. For a partition tables:

For a partition table we need to use pg_partitions to get the information and then sum up all individual partitions.

SELECT   partitionschemaname,  tablename, pg_size_pretty(SUM(  pg_relation_size(partitionschemaname||'.'||partitiontablename) * get_ao_compression_ratio   )::bigint) uncompressed_size,  pg_size_pretty(SUM(Pg_relation_size(partitionschemaname||'.'||partitiontablename))::bigint) compressed_size FROM ( SELECT   partitionschemaname,  tablename, partitiontablename, get_ao_compression_ratio(partitionschemaname||'.'||partitiontablename), Pg_relation_size(partitionschemaname||'.'||partitiontablename) FROM     pg_partitions where partitionschemaname='public' and tablename in('t4p')  ) a GROUP BY partitionschemaname, tablename ;
 partitionschemaname | tablename | uncompressed_size | compressed_size
---------------------+-----------+-------------------+-----------------
 public              | t4p       | 3524 kB           | 1084 kB
(1 row)

Above result provide a summary for the entire table. If you would like to know results for the individual partitions please use:

SELECT   partitionschemaname,  tablename, partitiontablename, get_ao_compression_ratio(partitionschemaname||'.'||partitiontablename), pg_size_pretty(Pg_relation_size(partitionschemaname||'.'||partitiontablename)) FROM pg_partitions where partitionschemaname='public' and tablename in('t4p') ;
 partitionschemaname | tablename |    partitiontablename    | get_ao_compression_ratio | pg_size_pretty
---------------------+-----------+--------------------------+--------------------------+----------------
 public              | t4p       | t4p_1_prt_outlying_years |                       -1 | 0 bytes
 public              | t4p       | t4p_1_prt_1              |                       -1 | 0 bytes
 public              | t4p       | t4p_1_prt_2              |                     3.25 | 542 kB
 public              | t4p       | t4p_1_prt_3              |                     3.25 | 542 kB
(3 rows)