Database growth rate and info

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and report on the growth rate of databases within a PostgreSQL instance to help in capacity planning and management.
• Why: This job is important because it provides detailed insights into how rapidly databases are growing over time. By tracking these metrics, database administrators can proactively manage database sizes and plan for necessary storage or performance interventions. It helps prevent potential issues related to database capacity, which might affect application performance and uptime.
• Manual checking: To check this manually in the PostgreSQL database, you can issue the following SQL commands to view the current database size and recent growth history:

select datname as "Database name", round(pg_database_size(datname)/1024.0/1024.0,1) as "Database size in MB" from pg_database where datname not in (select ignore_db from dbw_database_growth_ignore_list);

and

select datname as "Database name", sum(size_change/1024.0/1024.0) as "Data change", date_trunc('day', histr_date) as "History date" from dbw_database_growth_histr where period > 0 and extract(epoch from histr_date) > extract(epoch from (current_time - interval '1 month')) and datname not in (select ignore_db from dbw_database_growth_ignore_list) group by datname, date_trunc('day', histr_date);

Job Configurations

• Object: dbw_database_growth_rate
• Description: Collects detailed growth rate information of databases.
• Version: 1.6
• Company: dbwatch.no
• Default Schedule: Twice daily at 05:10 and 17:10.

Data Retention and Exclusions

• Data Retained For: Data related to database growth is kept for 7 days to ensure timely relevance and management of disk space.
• Databases Ignored: By default, template0 and template1 databases are excluded from the monitoring to focus on user-defined and operational databases.

Dependencies

• This monitoring job relies on two key tables and one function for operation:
• dbw_database_growth_ignore_list: Stores database names that are ignored in reports and monitoring.

Reporting

• Report Title: Database Growth Rate and Info
• Included Charts and Data Points:
• Database size over time chart.

• Charts and Tables Details:
  |_. Chart/Report Title|_. Data Points Included|_. Chart Type| | Database size over time | Database name, Total size, Date | Line Chart | | Daily database growth last month | Database name, Data change, History date | Bar Chart | | Current database size | Database name, Database size in MB | Table |

Each presentation in the report focuses on providing a visual and tabular understanding of database sizes and changes, vital for assessing trends and making informed decisions regarding database management.