PostgreSQL Built-In Functions

Functions

current_database

Name of current database

pg_table_size

disk space usage for the specified table, including TOAST, free space and visibility map

pg_column_size

Bytes required to store the value, perhaps with compression

pg_database_size

Bytes used by the database with the specified name/OID.
pg_database_size() essentially queries the filesystem for the size of $PGDATA/base/oid-of-database (the per-database data directory), plus the size of every such directory in non-default tablespaces added with CREATE TABLESPACE. If there are files in these directories that don't pertain to any relation , they will be counted too. https://dba.stackexchange.com/questions/121804/what-parts-make-up-pg-database-size

pg_indexes_size

disk space usage for all indexes attached to the specified table

pg_tablespace_size

total disk space usage for the specified tablespace

pg_size_pretty

Converts a size in bytes expressed as a numeric value into a human-readable format with size units.

pg_relation_size

disk space usage for the main fork of the specified table or index

pg_total_relation_size

total disk space usage for the specified table and associated indexes

Example SQL

Database Size

SELECT current_database() as db;
SELECT pg_database_size(current_database()) as size;
SELECT pg_size_pretty(pg_database_size(current_database())) as size;

SELECT SUM(pg_total_relation_size(oid)) AS dbsize,
           pg_database_size(current_database()) AS dbsizeondisk
FROM   pg_class
WHERE  relkind IN ('r','m','S')
  AND NOT relisshared;

Example Output

jira
1714780696
1635 MB

1731985408
1732098584