How to track database storage size

Help Center
1

To monitor the total size of a database during the month, you can create a table, inserting the total size of the tables + aggregating indexes in the database once a day, to track the total size + costs.

The first step is to create a table that will store the calculations:

CREATE FACT TABLE IF NOT EXISTS calculations (Total_dimension_tables_size_in_db_TB DOUBLE PRECISION NOT NULL ,Total_fact_tables_size_in_db_TB DOUBLE PRECISION NOT NULL ,Total_indexes_size_in_db_TB DOUBLE PRECISION NOT NULL ,Total_size_in_db_TB DOUBLE PRECISION NOT NULL ,date_time TIMESTAMP NOT NULL) PRIMARY INDEX date_time


Then, insert the SUM of the size of the tables + aggregating indexes and the current time:

INSERT INTO calculations WITH tables_sizes AS materialized(SELECT SUM(CASE WHEN table_type = 'DIMENSION' THEN size/1024/1024/1024/1024 END) AS Total_dimension_tables_size_in_db_TB,SUM(CASE WHEN table_type = 'FACT' THEN size/1024/1024/1024/1024 END) AS Total_fact_tables_size_in_db_TBFROM information_schema.tables WHERE table_type IN('DIMENSION' , 'FACT')),indexes_sizes AS materialized(SELECTSUM((index_compressed_size/1024/1024/1024/1024)) AS Total_indexes_size_in_db_TB FROM information_schema.indexes WHERE index_type = 'aggregating')SELECT tables_sizes.Total_dimension_tables_size_in_db_TB , tables_sizes.Total_fact_tables_size_in_db_TB , indexes_sizes.Total_indexes_size_in_db_TB , tables_sizes.Total_dimension_tables_size_in_db_TB + tables_sizes.Total_fact_tables_size_in_db_TB + indexes_sizes.Total_indexes_size_in_db_TB AS Total_size_in_db_TB , NOW()FROM tables_sizesCROSS JOIN indexes_sizes

Execute this insert once a day to make sure the data is up to date.