Table and index scan collector

Job details

Parameters

Job Summary

• Purpose: This job is designed to collect table and index scan statistics across all schemas within each database on a PostgreSQL instance.
• Why: This job is vital for maintaining database performance by examining access patterns that could influence indexing strategies and other optimization efforts. Collecting and analyzing these metrics helps in understanding whether the database is more often accessing data via full table scans or using indexed methods, which directly affects performance. Monitoring these statistics can indicate if a change in database configuration or query optimization might be needed.
• Manual checking: To verify similar statistics manually, you could use the following SQL command in the PostgreSQL environment:

SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch FROM pg_stat_all_tables WHERE schemaname NOT IN ('pg_catalog','pg_toast');

Detailed Description

This job works specifically with databases that are not system templates and excludes certain defaults like the ‘rdsadmin’ database for services such as Amazon RDS. Additionally, it ignores databases listed in a configurable “ignore_databases” parameter to focus on the relevant databases.

• Execution Pattern: The monitoring job is scheduled to run at the 10th and 40th minutes of each hour.
• Timeout Setting: The maximum duration for the job is set to 180 seconds (3 minutes), after which it will timeout if not completed.

Compatibility and Installation

The job filter ensures that this specific task is only executed on PostgreSQL instances, ensuring that there is no attempt to run irrelevant or incompatible database types, thereby preserving system resources and avoiding errors.

• Compatibility: Exclusive to PostgreSQL database instances.
• Installation: The job specifies an express installation attribute, allowing it to be quickly set up and integrated into the dbWatch framework.

Report Generation and Presentation

The job includes a template for generating reports, which consist of detailed visuals and data tables:

• Report Version: Utilizes version 2 of the dbWatch reporting template.
• Job Details Section: Provides a concise representation of collected database statistics, focusing on critical metrics like sequence and index scans in tabular formats.
• Data Plots: The reports generated likely include tables summarizing the statistical data, which helps in easy visual analysis and periodical review.
• Key Performance Metrics: Emphasizes high-sequence scans and high-index scans, which are critical for performance tuning and optimizations in PostgreSQL databases.

Error Handling and Notifications

Throughout the job’s execution, various checks and balances are implemented to handle errors effectively. For instance, if the system does not find the necessary database table for collecting statistics, an appropriate error message and status are set, helping in troubleshooting and immediate rectification.

Overall, the “Table and Index Scan Collector” job within the dbWatch Control Center is a crucial tool for database administrators aiming to enhance the performance and efficiency of PostgreSQL databases through proactive monitoring and metric collection.