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.

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.