Created
November 9, 2024 20:57
-
-
Save edzme/e5151b4acae35fb54321edfb5234e523 to your computer and use it in GitHub Desktop.
Returns the size (in megabytes) of all tables in a database that may include normal tables and timescaledb hypertables.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH hypertable_info AS ( | |
| SELECT hypertable_schema, hypertable_name | |
| FROM timescaledb_information.hypertables | |
| ) | |
| SELECT | |
| schemaname as schema_name, | |
| relname as table_name, | |
| pg_size_pretty( | |
| CASE | |
| WHEN EXISTS ( | |
| SELECT 1 | |
| FROM hypertable_info | |
| WHERE hypertable_schema = schemaname | |
| AND hypertable_name = relname | |
| ) THEN ( | |
| SELECT sum(pg_total_relation_size(format('%I.%I', chunk_schema, chunk_name)::regclass)) | |
| FROM timescaledb_information.chunks ch | |
| WHERE ch.hypertable_schema = schemaname | |
| AND ch.hypertable_name = relname | |
| ) | |
| ELSE pg_total_relation_size(relid) | |
| END | |
| ) as total_size, | |
| round( | |
| CASE | |
| WHEN EXISTS ( | |
| SELECT 1 | |
| FROM hypertable_info | |
| WHERE hypertable_schema = schemaname | |
| AND hypertable_name = relname | |
| ) THEN ( | |
| SELECT sum(pg_total_relation_size(format('%I.%I', chunk_schema, chunk_name)::regclass)) | |
| FROM timescaledb_information.chunks ch | |
| WHERE ch.hypertable_schema = schemaname | |
| AND ch.hypertable_name = relname | |
| ) | |
| ELSE pg_total_relation_size(relid) | |
| END / 1024.0 / 1024.0, | |
| 2 | |
| ) as size_mb, | |
| CASE | |
| WHEN EXISTS ( | |
| SELECT 1 | |
| FROM hypertable_info | |
| WHERE hypertable_schema = schemaname | |
| AND hypertable_name = relname | |
| ) THEN 'Hypertable' | |
| ELSE 'Regular Table' | |
| END as table_type | |
| FROM pg_catalog.pg_statio_user_tables | |
| WHERE schemaname NOT IN ('_timescaledb_internal', '_timescaledb_catalog', 'information_schema', 'pg_catalog') | |
| ORDER BY | |
| CASE | |
| WHEN EXISTS ( | |
| SELECT 1 | |
| FROM hypertable_info | |
| WHERE hypertable_schema = schemaname | |
| AND hypertable_name = relname | |
| ) THEN ( | |
| SELECT sum(pg_total_relation_size(format('%I.%I', chunk_schema, chunk_name)::regclass)) | |
| FROM timescaledb_information.chunks ch | |
| WHERE ch.hypertable_schema = schemaname | |
| AND ch.hypertable_name = relname | |
| ) | |
| ELSE pg_total_relation_size(relid) | |
| END DESC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment