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_TB
FROM information_schema.tables
WHERE table_type IN('DIMENSION' , 'FACT')
),
indexes_sizes AS materialized(
SELECT
SUM((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_sizes
CROSS JOIN indexes_sizes
Execute this insert once a day to make sure the data is up to date.