PostgreSQL Built-In Functions

PostgreSQL

PostgreSQL Schema

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

Bibliography

https://help.heroku.com/THTJADE4/how-can-i-query-the-size-of-my-postgres-databasehttp://www.postgresonline.com/journal/archives/233-How-big-is-my-database-and-my-other-stuff.htmlhttps://dba.stackexchange.com/questions/121804/what-parts-make-up-pg-database-size

Page updated

Google Sites

Report abuse