Postgres deadlocks in the scheduler when running reindexDB.pl while the scheduler is active

Products

• Autosys Workload Automation

Issue/Introduction

Attempting to execute the reindexDB.pl script while the AutoSys Scheduler is active can cause PostgreSQL deadlocks. These deadlocks interfere with the scheduler's ability to perform database transactions, leading to performance degradation or service instability.

Environment

• Database: PostgreSQL

• Component: AutoSys Scheduler 24.x / Database Maintenance

Resolution

Per the official documentation for reindexDB.pl, the script must be executed when system activity is at a minimum. If high activity persists or the scheduler is processing jobs, AutoSys may experience significant slowdowns or timeout conditions during database transactions.

To mitigate this:

1. Schedule Maintenance: It is highly recommended to run the reindexDB.pl script during a maintenance window when the scheduler is stopped or activity is negligible.

2. PostgreSQL Optimization: Additional research indicates that PostgreSQL supports a CONCURRENTLY option for reindexing. This option allows the database to rebuild indexes without acquiring locks that block concurrent inserts, updates, or deletes on the table.

Additional Information For further technical details on reindexing strategies and the CONCURRENTLY parameter, refer to the official PostgreSQL documentation: https://www.postgresql.org/docs/current/sql-reindex.html