Transaction statistics

Job details

Name:	Transaction statistics
Platform:	Postgres
Category:	Performance
Description:	This procedure collects and stores historical snapshots of the xact_commit and xact_rollback counters from the pg_stat_database view.
Long description:	This procedure collects and stores historical snapshots of the xact_commit and xact_rollback counters from the pg_stat_database view. By capturing these values over time, it enables tracking of transaction activity trends per database, including both successful commits and rollbacks.
Version:	1.81
Default schedule:	0,10,20,30,40,50 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’]

Parameters

Name	Default value	Description
keep data for	7	The number of days to keep the data for.
ignore database	template0, template1	Databases you which to ignore both in gathering of statistics and displaying in report
max databases per graph	5	The maximum number of databases visualized in the report graphs.

Job Summary

“Purpose”: The purpose of this job is to monitor and report on transactions within PostgreSQL databases, specifically focusing on commits and rollbacks. It captures and retains data over the specified period to help in analyzing trends in database transaction performance.
“Why”: This job is important as it allows database administrators to understand transaction patterns and identify any irregularities or issues in committing and rolling back transactions, which could affect the performance and reliability of the database.
“Manual checking”: To manually check the transaction statistics, the following SQL commands can be used:

SELECT datname, commits, rollbacks FROM pg_stat_database WHERE datname NOT IN (SELECT datname FROM dbw_transaction_statistics_ignore);

Implementations and Implementations Details

The job involves several SQL and PL/pgSQL commands to create necessary tables and a function to process the transaction statistics:
Tables like dbw_transaction_statistics_histr and dbw_transaction_statistics_ignore are created to store historical data of transactions and to manage the databases that should not be included in the monitoring.

Dependencies and Cleanup

The job has dependencies on its own tables and functions to ensure historical data is correctly recorded and the current data processing is performed on the latest available information.
“Cleanup-on-fail” is set to true for all dependencies, meaning that if an operation fails, associated interim data gets cleaned up to avoid corruption or inaccuracies.

Scheduling and Execution

“Default Schedule”: The job is scheduled to run every 20 minutes as represented in the XML data as “0,20,40 * * *”, although the provided report-template cites a different schedule (hourly). This discrepancy should be aligned based on actual needs.

Reporting Structure

The job comes equipped with a report-template that includes two presentations:
“Commits per minute”: This report element uses a line chart to depict the average number of commits per minute for each recorded history date.

Versioning and Upgrades

Detailed upgrade paths have been provided for versions from 1.3 to 1.7 to ensure that the functionality remains consistent and up-to-date across various releases of this job.

This job setup offers a comprehensive approach to monitoring transaction statistics effectively in PostgreSQL environments, ensuring high levels of data integrity and performance understanding.