Checking Database Object Sizes and Disk Space

Checking Database Object Sizes and Disk Space

The gp_size_* family of views can be used to determine the disk space usage for a distributed Greenplum Database, schema, table, or index. The following views calculate the total size of an object across all primary segments (mirrors are not included in the size calculations).

The table and index sizing views list the relation by object ID (not by name). To check the size of a table or index by name, you must look up the relation name (relname) in the pg_class table. For example:

SELECT relname as name, sotdsize as size, sotdtoastsize as 
toast, sotdadditionalsize as other 
FROM gp_size_of_table_disk as sotd, pg_class 
WHERE sotd.sotdoid=pg_class.oid ORDER BY relname;

gp_size_of_all_table_indexes

This view shows the total size of all indexes for a table. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access.

Table 1. gp_size_of_all_table_indexes view
Column Description
soatioid The object ID of the table
soatisize The total size of all table indexes in bytes
soatischemaname The schema name
soatitablename The table name

gp_size_of_database

This view shows the total size of a database. This view is accessible to all users, however non-superusers will only be able to see databases that they have permission to access.

Table 2. gp_size_of_database view
Column Description
sodddatname The name of the database
sodddatsize The size of the database in bytes

gp_size_of_index

This view shows the total size of an index. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access.

Table 3. gp_size_of_index view
Column Description
soioid The object ID of the index
soitableoid The object ID of the table to which the index belongs
soisize The size of the index in bytes
soiindexschemaname The name of the index schema
soiindexname The name of the index
soitableschemaname The name of the table schema
soitablename The name of the table

gp_size_of_partition_and_indexes_disk

This view shows the size on disk of partitioned child tables and their indexes. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access..

Table 4. gp_size_of_partition_and_indexes_disk view
Column Description
sopaidparentoid The object ID of the parent table
sopaidpartitionoid The object ID of the partition table
sopaidpartitiontablesize The partition table size in bytes
sopaidpartitionindexessize The total size of all indexes on this partition
Sopaidparentschemaname The name of the parent schema
Sopaidparenttablename The name of the parent table
Sopaidpartitionschemaname The name of the partition schema
sopaidpartitiontablename The name of the partition table

gp_size_of_schema_disk

This view shows schema sizes for the public schema and the user-created schemas in the current database. This view is accessible to all users, however non-superusers will be able to see only the schemas that they have permission to access.

Table 5. gp_size_of_schema_disk view
Column Description
sosdnsp The name of the schema
sosdschematablesize The total size of tables in the schema in bytes
sosdschemaidxsize The total size of indexes in the schema in bytes

gp_size_of_table_and_indexes_disk

This view shows the size on disk of tables and their indexes. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access.

Table 6. gp_size_of_table_and_indexes_disk view
Column Description
sotaidoid The object ID of the parent table
sotaidtablesize The disk size of the table
sotaididxsize The total size of all indexes on the table
sotaidschemaname The name of the schema
sotaidtablename The name of the table

gp_size_of_table_and_indexes_licensing

This view shows the total size of tables and their indexes for licensing purposes. The use of this view requires superuser permissions.

Table 7. gp_size_of_table_and_indexes_licensing view
Column Description
sotailoid The object ID of the table
sotailtablesizedisk The total disk size of the table
sotailtablesizeuncompressed If the table is a compressed append-optimized table, shows the uncompressed table size in bytes.
sotailindexessize The total size of all indexes in the table
sotailschemaname The schema name
sotailtablename The table name

gp_size_of_table_disk

This view shows the size of a table on disk. This view is accessible to all users, however non-superusers will only be able to see tables that they have permission to access

Table 8. gp_size_of_table_disk view
Column Description
sotdoid The object ID of the table
sotdsize The size of the table in bytes. The size is only the main table size. The size does not include auxiliary objects such as oversized (toast) attributes, or additional storage objects for AO tables.
sotdtoastsize The size of the TOAST table (oversized attribute storage), if there is one.
sotdadditionalsize Reflects the segment and block directory table sizes for append-optimized (AO) tables.
sotdschemaname The schema name
sotdtablename The table name

gp_size_of_table_uncompressed

This view shows the uncompressed table size for append-optimized (AO) tables. Otherwise, the table size on disk is shown. The use of this view requires superuser permissions.

Table 9. gp_size_of_table_uncompressed view
Column Description
sotuoid The object ID of the table
sotusize The uncomressed size of the table in bytes if it is a compressed AO table. Otherwise, the table size on disk.
sotuschemaname The schema name
sotutablename The table name

gp_disk_free

This external table runs the df (disk free) command on the active segment hosts and reports back the results. Inactive mirrors are not included in the calculation. The use of this external table requires superuser permissions.

Table 10. gp_disk_free external table
Column Description
dfsegment The content id of the segment (only active segments are shown)
dfhostname The hostname of the segment host
dfdevice The device name
dfspace Free disk space in the segment file system in kilobytes