Schema growth collector

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to collect growth rate statistics for all schemas in all databases on PostgreSQL platforms.
• Why: This job is important because it helps in monitoring the expansion of database storage requirements over time. By tracking schema growth, organizations can plan for resource allocation, optimize performance, and prevent potential issues arising from unmanaged data increases.
• Manual checking: You can check this manually in the database by issuing the following SQL commands:

SELECT nspname, current_database() AS db, SUM(relpages), relkind::varchar 
FROM pg_class c 
JOIN pg_namespace n ON n.oid = c.relnamespace 
GROUP BY db, nspname, relkind;

Technical Details

• Job Name: Schema growth collector
• Version: 1.51
• Company: dbwatch.no
• Category: Capacity
• Database Compatibility: PostgreSQL (specifically for instances newer than version 9.2 and not template databases).
• Express Install: Enabled
• Default Schedule: Executes daily at 6:10 AM
• Timeout: 180 seconds

Job Execution Process

• A preliminary check ensures that a specific table named ‘dbw_schema_growth_base’ exists; if missing, the job will not proceed to collect data.
• It clears the aforementioned table to ensure fresh statistics are stored in each run.
• The job iterates over all databases (excluding specified ‘ignore_databases’) and collects data related to schema size (sum of relation pages) and type.
• Data for each schema is then inserted back into the ‘dbw_schema_growth_base’, marking the date and time of the data capture.
• The process constructs a dynamic message log on successful data collection or errors encountered during the job execution.

Output Analysis

The outcome of this job is a detailed log of each schema’s growth within every monitored database, updating the table with current statistics. This allows for historical tracking and growth analysis, essential for database capacity planning and management.

• Ignored Databases and Reasons: The job supports ignoring certain databases using the ‘ignore_databases’ parameter. If any databases are ignored, information about them is logged.
• Successful Operations: For each eligible database, an entry detailing the successful update is logged with the timestamp.
• Error Handling: Any issues like missing rights, absence of necessary objects, or execution halts due to exceptions are logged with an error status and message.

Job Execution Results Representation

• This job uses the dbWatch report template version 2.
• Report Title and Description: “Schema growth collector” focused on collecting schematics growth details.
• Scheduled Output Representation: Details are presented in tabulated formats drawing information directly from the ‘schema_growth collector’ task, enhancing readability and inspection.