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 calculationsWITH tables_sizes AS materialized(SELECTSUM(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.tablesWHERE table_type IN('DIMENSION' , 'FACT')),indexes_sizes AS materialized(SELECTSUM((index_compressed_size/1024/1024/1024/1024)) AS Total_indexes_size_in_db_TBFROM information_schema.indexesWHERE index_type = 'aggregating')SELECTtables_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.