Database Object Size Functions
Database object size functions calculate the actual disk space used by database objects.
pg_column_size(any)
Description: Specifies the number of bytes used to store a particular value (possibly compressed).
Return type: int
Note: pg_column_size displays the space for storing an independent data value.
1 2 3 4 5 |
gaussdb=# SELECT pg_column_size(1); pg_column_size ---------------- 4 (1 row) |
pg_database_size(oid)
Description: Specifies the disk space used by the database with the specified OID.
Return type: bigint
pg_database_size(name)
Description: Specifies the disk space used by the database with the specified name.
Return type: bigint
Note: pg_database_size receives the OID or name of a database and returns the disk space used by the corresponding object.
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_database_size('testdb'); pg_database_size ------------------ 51590112 (1 row) |

The query time of the pg_database_size(oid/name) function is linearly related to the number of object files in the database corresponding to the OID/name. To evaluate the time required, perform the following steps:
- Since the time required is related to the software and hardware environments, the test result of a certain database (the number of object files in the selected database should not be too large) is used as the benchmark. Calculate the time consumed by a single object file (database_size_t) based on the query time and the number of object files in the database.
- Obtain the OID of the database to be queried using the command SELECT oid, * FROM pg_database WHERE datname = 'database';. In the base and pg_tblspc tablespace directories, count the total number of files (database_obj_num) in the directory corresponding to the OID. If the directory contains subdirectories, you need to enter it to view the number of object files. You can run ls | wc -l to query the number of files in the directory.
- Calculate the estimated total time consumption based on database_size_t (time consumption of a single file) and database_obj_num (number of all object files in the database) in the benchmark test result. The total time consumption is equal to database_size_t multiplied by database_obj_num. If the estimated latency is too long, consider adjusting the execution timeout threshold to avoid unnecessary alarms.
pg_relation_size(oid)
Description: Specifies the disk space used by the table with a specified OID or index.
Return type: bigint
get_db_source_datasize()
Description: Estimates the total size of non-compressed data in the current database.
Return type: bigint
Note: ANALYZE must be performed before this function is called.
Example:
1 2 3 4 5 6 7 |
gaussdb=# analyze; ANALYZE gaussdb=# SELECT get_db_source_datasize(); get_db_source_datasize ------------------------ 35384925667 (1 row) |
pg_relation_size(text)
Description: Specifies the disk space used by the table with a specified name or index. The table name can be schema-qualified.
Return type: bigint
pg_relation_size(relation regclass, fork text)
Description: Specifies the disk space used by the specified bifurcating tree ('main', 'fsm', or 'vm') of a certain table or index.
Return type: bigint
pg_relation_size(relation regclass)
Description: Is an abbreviation of pg_relation_size(..., 'main').
Return type: bigint
Note: pg_relation_size receives the OID or name of a table, an index, or a compressed table, and returns the size.
pg_partition_size(oid, oid)
Description: Specifies the disk space used by the partition with a specified OID. The first oid is the OID of the table and the second oid is the OID of the partition.
Return type: bigint
pg_partition_size(text, text)
Description: Specifies the disk space used by the partition with a specified name. The first text is the table name and the second text is the partition name.
Return type: bigint
pg_partition_indexes_size(oid, oid)
Description: Specifies the disk space used by the index of the partition with a specified OID. The first oid is the OID of the table and the second oid is the OID of the partition.
Return type: bigint
pg_partition_indexes_size(text, text)
Description: Specifies the disk space used by the index of the partition with a specified name. The first text is the table name and the second text is the partition name.
Return type: bigint
pg_indexes_size(regclass)
Description: Specifies the total disk space used by the index appended to the specified table.
Return type: bigint
pg_size_pretty(bigint)
Description: Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units.
Return type: text
pg_size_pretty(numeric)
Description: Converts a size in bytes expressed as a numeric value into a human-readable format with size units.
Return type: text
Note: pg_size_pretty formats the results of other functions into a human-readable format. KB, MB, GB, and TB can be used.
pg_table_size(regclass)
Description: Specifies the disk space used by the specified table, excluding indexes (but including TOAST, free space mapping, and visibility mapping).
Return type: bigint
pg_tablespace_size(oid)
Description: Specifies the disk space used by the tablespace with a specified OID.
Return type: bigint
pg_tablespace_size(name)
Description: Specifies the disk space used by the tablespace with a specified name.
Return type: bigint
Note:
pg_tablespace_size receives the OID or name of a database and returns the disk space used by the corresponding object.
pg_total_relation_size(oid)
Description: Specifies the disk space used by the table with a specified OID, including the index and the compressed data.
Return type: bigint
pg_total_relation_size(regclass)
Description: Specifies the total disk space used by the specified table, including all indexes and TOAST data.
Return type: bigint
pg_total_relation_size(text)
Description: Specifies the disk space used by the table with a specified name, including the index and the compressed data. The table name can be schema-qualified.
Return type: bigint
Note: pg_total_relation_size receives the OID or name of a table or a compressed table, and returns the sizes of the data, related indexes, and the compressed table in bytes.
datalength(any)
Description: Specifies the number of bytes used by an expression of a specified data type (data management space, data compression, or data type conversion is not considered).
Return type: int
Note: datalength is used to calculate the space of an independent data value.
Example:
gaussdb=# SELECT datalength(1);
datalength
------------
4
(1 row)
The following table lists the supported data types and calculation methods.
Data Type |
Storage Space |
||
---|---|---|---|
Numeric data types |
Integer types |
TINYINT |
1 |
SMALLINT |
2 |
||
INTEGER |
4 |
||
BINARY_INTEGER |
4 |
||
BIGINT |
8 |
||
Arbitrary precision types |
DECIMAL |
Every four decimal digits occupy two bytes. The digits before and after the decimal point are calculated separately. |
|
NUMERIC |
Every four decimal digits occupy two bytes. The digits before and after the decimal point are calculated separately. |
||
NUMBER |
Every four decimal digits occupy two bytes. The digits before and after the decimal point are calculated separately. |
||
Serial integers |
SMALLSERIAL |
2 |
|
SERIAL |
4 |
||
BIGSERIAL |
8 |
||
LARGESERIAL |
Every four decimal digits occupy two bytes. The digits before and after the decimal point are calculated separately. |
||
Floating point types |
FLOAT4 |
4 |
|
DOUBLE PRECISION |
8 |
||
FLOAT8 |
8 |
||
BINARY_DOUBLE |
8 |
||
FLOAT[(p)] |
Every four decimal digits occupy two bytes. The digits before and after the decimal point are calculated separately. |
||
DEC[(p[,s])] |
Every four decimal digits occupy two bytes. The digits before and after the decimal point are calculated separately. |
||
INTEGER[(p[,s])] |
Every four decimal digits occupy two bytes. The digits before and after the decimal point are calculated separately. |
||
Boolean data types |
Boolean type |
BOOLEAN |
1 |
Character data types |
Character types |
CHAR |
n |
CHAR(n) |
n |
||
CHARACTER(n) |
n |
||
NCHAR(n) |
n |
||
VARCHAR(n) |
n |
||
CHARACTER |
Actual number of bytes of a character |
||
VARYING(n) |
Actual number of bytes of a character |
||
VARCHAR2(n) |
Actual number of bytes of a character |
||
NVARCHAR(n) |
Actual number of bytes of a character |
||
NVARCHAR2(n) |
Actual number of bytes of a character |
||
TEXT |
Actual number of bytes of a character |
||
CLOB |
Actual number of bytes of a character |
||
Time data types |
Time types |
DATE |
8 |
TIME |
8 |
||
TIMEZ |
12 |
||
TIMESTAMP |
8 |
||
TIMESTAMPZ |
8 |
||
SMALLDATETIME |
8 |
||
INTERVAL DAY TO SECOND |
16 |
||
INTERVAL |
16 |
||
RELTIME |
4 |
||
ABSTIME |
4 |
||
TINTERVAL |
12 |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.