Query to list all tables and hypertables in a Postgresql / TimescaleDB database with their total size

timescaledb_tables_and_hypertables_sizes.sql

WITH chunks AS (
    SELECT 
        ht.schema_name AS hypertable_schema,
        ht.table_name AS hypertable_name,
        ck.table_name AS chunk_name
    FROM _timescaledb_catalog.hypertable ht
    JOIN _timescaledb_catalog.chunk ck
    ON ht.id = ck.hypertable_id
),

tables_and_chunks AS (
    select 
        CASE WHEN c.hypertable_name IS NOT NULL THEN 'Hypertable' ELSE 'Table' END AS table_type,
        COALESCE(c.hypertable_schema, t.table_schema) AS table_schema,
        COALESCE(c.hypertable_name, t.table_name) AS table_name,
        pg_table_size('"'||t.table_schema||'"."'||t.table_name||'"') AS table_or_chunk_table_size,
        pg_indexes_size('"'||t.table_schema||'"."'||t.table_name||'"') AS table_or_chunk_indexes_size,
        pg_total_relation_size('"'||t.table_schema||'"."'||t.table_name||'"') AS table_or_chunk_total_size
    from information_schema.tables t
    LEFT JOIN chunks c
    ON c.chunk_name = t.table_name
)

SELECT
    table_type,
    table_schema,
    table_name,
    pg_size_pretty(SUM(table_or_chunk_table_size)) AS table_size,
    pg_size_pretty(SUM(table_or_chunk_indexes_size)) AS indexes_size,
    pg_size_pretty(SUM(table_or_chunk_total_size)) AS total_size
FROM tables_and_chunks
WHERE table_schema NOT IN ('pg_catalog', 'information_schema', '_timescaledb_catalog', 'timescaledb_information')
GROUP BY table_type, table_schema, table_name
ORDER BY SUM(table_or_chunk_total_size) DESC;

Sample output:

table_type	table_schema	table_name	table_size	indexes_size	total_size
Hypertable	public	locations	5169 MB	7515 MB	12 GB
Table	public	messages	2414 MB	879 MB	3292 MB
Hypertable	public	comments	1590 MB	673 MB	2264 MB
Table	public	actions	666 MB	280 kB	667 MB
Table	public	pages	46 MB	180 MB	225 MB
Table	public	agents	89 MB	108 MB	197 MB