Feature request: implement log rotation for execution_log in database

[johnbizokk]

Summary:

As the execution_log table in the database managed by pg_timetable grows continuously, it leads to an increase in the overall database size, which currently stands at 42 GB for the execution_log alone (3+ years of the continuous scheduled task execution) for our production database. This growth poses potential performance and storage scalability issues.

Current Behavior:

The execution_log table does not implement any form of log rotation, resulting in unbounded growth of the log data stored within the database. The current configuration for pg_timetable is as follows:

pg_timetable --upgrade --sslmode=disable --log-level=debug --log-database-level=none --log-file-format=json --cron-workers=4 --interval-workers=4

Problem:

The continuous growth of the execution_log table without a rotation or cleanup mechanism significantly increases the database's size, affecting the performance and leading to higher storage costs.

Proposed Solution:

Introduce a log rotation mechanism for the execution_log table. This could involve:

• Implementing a retention policy where logs older than a certain period (e.g., 30 days) are automatically purged.
• Introducing a partitioning scheme where logs are stored in different partitions based on the date, and older partitions can be dropped according to a set schedule.

Expected Benefits:

• Reduced Database Size: By regularly purging old logs, the total size of the database will be controlled, preventing excessive growth.
• Improved Performance: With smaller database size, the performance of database queries and maintenance operations could improve.
• Cost Efficiency: Reduces storage costs by keeping only necessary logs in the database.

SQL Query to Analyze Current Log Sizes:

SELECT schemaname AS table_schema,
       relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS data_size,
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS external_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC, pg_relation_size(relid) DESC
LIMIT 10;

The query shows that the execution_log table is a significant contributor to the database size.

Additional Context:

Implementing this feature would align with best practices for database management and ensure the sustainability of the system as the volume of executed tasks increases.

[pashagolub]

Thanks.

I can implement log rotation in application. Right now you can have a special chain that will delete/truncate execution_log.

Do you think it's worth to add new functionality or just adding maintenance chain by default is the easiest solution?

[pashagolub]

This is not feature you requested but it solves your issue: #682