Detailed explanation of how PostgreSQL can view the data footprint in database and tables

SELECT pg_size_pretty(pg_database_size('database_name'));

SELECT
    table_schema || '.' || table_name AS table,
    #Table data only    pg_size_pretty(pg_relation_size(table_schema || '.' || table_name)) AS size
    #Table data + index data    #pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS size
FROM information_schema.tables
WHERE 
    table_schema = 'public'
ORDER BY
    pg_relation_size(table_schema || '.' || table_name) DESC;
    #pg_total_relation_size(table_schema || '.' || table_name) DESC;

#Table data onlySELECT pg_size_pretty(pg_relation_size('schemal_test.table_test'));
#Table data + index dataSELECT pg_size_pretty(pg_total_relation_size('schemal_test.table_test'));

#Find postgresql tablespace sizeSELECT pg_size_pretty (pg_tablespace_size ('tablespace_name'));

#Name and size of all tablespacesSELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) as size from pg_tablespace;

#Find a single postgresql database sizeSELECT pg_size_pretty(pg_database_size('db_name'));

#Total size of all databases, displayed in easy-to-read formatSELECT pg_size_pretty(SUM(pg_database_size(datname))) FROM pg_database;


#View the list of all databases and their sizes (in GB), descending orderSELECT
	pg_database.datname as db_name,
	pg_database_size(pg_database.datname)/1024/1024/1024 as db_size
FROM pg_database ORDER by db_size DESC;

# or pg_size_pretty with modified sizeSELECT
	pg_database.datname as db_name,
	pg_size_pretty(pg_database_size(pg_database.datname)) as db_size
FROM pg_database ORDER by pg_database_size(pg_database.datname) DESC;

#View all database names, owners, and their respective sizesSELECT 
     AS db_name,
    pg_catalog.pg_get_userbyid() AS owner,
    CASE
        WHEN pg_catalog.has_database_privilege(, 'CONNECT') THEN
            pg_catalog.pg_size_pretty(pg_catalog.pg_database_size())
        ELSE 'No Access'
    END AS size
FROM pg_catalog.pg_database db
ORDER BY CASE
            WHEN pg_catalog.has_database_privilege(, 'CONNECT') THEN
                pg_catalog.pg_database_size()
        END;

#The size of all tables in the current schema or any schema, the size of the table-related objects, and the total table sizeSELECT  as table_name,
	pg_size_pretty(pg_relation_size()) as table_size,
	pg_size_pretty(pg_total_relation_size() - pg_relation_size()) as external_size,
	pg_size_pretty(pg_total_relation_size()) as total_table_size,
	stats.n_live_tup as live_rows
FROM pg_catalog.pg_statio_user_tables as statios
JOIN pg_stat_user_tables as stats
USING (relname)
WHERE  = 'schema_name'  -- Replace with pattern name
UNION ALL
SELECT 'TOTAL' as table_name,
   pg_size_pretty(sum(pg_relation_size())) AS table_size,
   pg_size_pretty(sum(pg_total_relation_size() - pg_relation_size())) AS external_size,
   pg_size_pretty(sum(pg_total_relation_size())) AS total_table_size,
   sum(stats.n_live_tup) AS live_rows
FROM pg_catalog.pg_statio_user_tables AS statios
JOIN pg_stat_user_tables AS stats
USING (relname)
WHERE  = 'schema_name'  -- Replace with pattern name
ORDER BY live_rows ASC;

#View the single table size of the postgresql database - excluding dependency size:SELECT pg_size_pretty(pg_relation_size('schema_test.table_name'));
#SELECT pg_size_pretty(pg_relation_size('table_name'));

#View the single table size of the postgresql database - including the dependency size:SELECT pg_size_pretty(pg_total_relation_size('schema_test.table_name'));
#SELECT pg_size_pretty(pg_total_relation_size('table_name'));

#Find the size of each table in the current database, including the indexSELECT 
    table_schema || '.' || table_name as table_name, 
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) as table_size 
FROM information_schema.tables 
ORDER BY 
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC

#Find each table and index size in the current database, including indexSELECT
    table_name,
    pg_size_pretty(pg_table_size(table_name)) as table_size,
    pg_size_pretty(pg_indexes_size(table_name)) as index_size, 
    pg_size_pretty(pg_total_relation_size(table_name)) as total_size
FROM (
    select ('"' || table_schema || '"."' || table_name || '"') as table_name FROM information_schema.tables) as tables
ORDER BY 4 DESC

#View table size and dependency sizeSELECT schemaname as schema_name,
	relname as table_name,
	pg_size_pretty(pg_total_relation_size(relid)) as table_size,
	pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as external_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

#View row count of all tablesselect relname as table_name, reltuples as rows from pg_class where relkind = ‘r' order by rowCounts desc

#single index size of postgresql database:SELECT pg_size_pretty(pg_indexes_size('index_name'));

#List the size of each index in the databaseSELECT indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes;

#PostgreSQL column value size, to find how much space is needed to store a specific value, you can use the pg_column_size() function, for example:select pg_column_size(5::smallint);
select pg_column_size(5::int);
select pg_column_size(5::bigint);

#Get OIDselect * from pg_class where relname='table_name';
select oid, datname from pg_database;

#View file addressselect pg_relation_filepath('table_name');

#Get the OID of the data tableselect oid,relname from pg_class where relname='table_name';
#Get the database OIDselect oid, datname from pg_database;
#Get the file path of the data tableselect pg_relation_filepath('table_name');