Schema growth statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to collect and analyze schema growth statistics for PostgreSQL databases, focusing on the size and growth rate of database schemas.
• Why: This job is important as it helps in identifying the growth trends of database schemas, which is crucial for capacity planning and performance optimization. If certain schemas grow unexpectedly, it may lead to performance degradation or space issues, impacting the overall health of the database.
• Manual checking: You can check this manually in the database by issuing the following SQL commands:

select schema_name, db_name, data_size, index_size, trans_log_size, total_size, histr_date from dbw_schema_growth_histr;

Key Components and Operations

• Tables and Constructors:
  • Tables like dbw_schema_growth_histr, dbw_schema_growth_snap, and dbw_schema_growth_base are crucial as they store different statistics related to schema sizes and changes over time.
  • The function dbw_schema_growth_rate calculates the growth rate of each schema, manages data retention, and updates necessary tables with new snapshots for comparison in future runs.

• Dependency Management:
  • This job has dependencies on several internal procedural and table objects like dbw_schema_growth_histr, dbw_schema_growth_rank, and others to ensure that the operations concerning data handling and growth determinations are consistent and accurate.

• Data Retention and Ignorance:
  • The job uses parameters such as “keep data for”, which specifies the number of days to retain the data, and has options to ignore certain schemas or databases making the reports more relevant by excluding unimportant data.

• Reporting:
  • It utilizes a dbWatch report template which potentially generates presentations and reports regarding schema growth statistics, such as “Schema growth statistics”, “Schema growth rate”, and “Daily schema growth last month”. These are visualized using charts and tables.

Scheduled Operations

• Default Schedules:
  • The job typically runs daily, designed as per the ‘default-schedule’ provided in the XML, ensuring regular monitoring and update to the schema growth data.

• Upgrade Path:
  • The job includes an upgrade script which adjusts database elements or dependencies required due to changes in job behavior from earlier versions (from version 1.7 in this case).

• Install Conditions:
  • The job installation has conditions specified that ensure it only installs on PostgreSQL instances with certain versions, making it reliant on compatibility checks.

Additional Operations

• Cleanup Operations:
  • Performs cleanup on failed operations, as specified by setting cleanup-on-fail attributes to true in dependencies, ensuring that the system remains in a consistent state even after unexpected failures during the job’s execution.

• Job Implementation Specifics:
  • Several SQL table creation and procedural creation commands are provided in ‘implementation’ and ‘upgrade’ sections indicating the setup necessary for the proper function of this job. These statements prepare the database environment by creating necessary schemas, tables, and functions tailored to track and analyze schema size changes over time.

Overall, this job orchestrates a comprehensive approach to monitor schema growth within PostgreSQL databases, providing insights through scheduled reports and handling exceptions for a smooth operational framework within the dbWatch environment.