timescaledb_information.job_stats

Shows information and statistics about jobs run by the automation framework. This includes jobs set up for user defined actions and jobs run by policies created to manage data retention, continuous aggregates, compression, and other automation policies. (See policies). The statistics include information useful for administering jobs and determining whether they ought be rescheduled, such as: when and whether the background job used to implement the policy succeeded and when it is scheduled to run next.

Available Columns

NameDescription
hypertable_schema(NAME) Schema name of the hypertable
hypertable_name(NAME) Table name of the hypertable
job_id(INTEGER) The id of the background job created to implement the policy
last_run_started_at(TIMESTAMP WITH TIME ZONE) Start time of the last job
last_successful_finish(TIMESTAMP WITH TIME ZONE) Time when the job completed successfully
last_run_status(TEXT) Whether the last run succeeded or failed
job_status(TEXT) Status of the job. Valid values are ‘Running’, ‘Scheduled’ and ‘Paused’
last_run_duration(INTERVAL) Duration of last run of the job
next_scheduled_run(TIMESTAMP WITH TIME ZONE) Start time of the next run
total_runs(BIGINT) The total number of runs of this job
total_successes(BIGINT) The total number of times this job succeeded
total_failures(BIGINT) The total number of times this job failed

Sample Usage

Get job success/failure information for a specific hypertable.

  1. SELECT job_id, total_runs, total_failures, total_successes 
  2.   FROM timescaledb_information.job_stats
  3.   WHERE hypertable_name = 'test_table';
  5.  job_id | total_runs | total_failures | total_successes 
  6. --------+------------+----------------+-----------------
  7.    1001 |          1 |              0 |               1
  8.    1004 |          1 |              0 |               1
  9. (2 rows)

Get information about continuous aggregate policy related statistics

  1. SELECT  js.* FROM
  2.   timescaledb_information.job_stats js, timescaledb_information.continuous_aggregates cagg
  3.   WHERE cagg.view_name = 'max_mat_view_timestamp' 
  4.   and cagg.materialization_hypertable_name = js.hypertable_name;
  6. -[ RECORD 1 ]----------+------------------------------
  7. hypertable_schema      | _timescaledb_internal
  8. hypertable_name        | _materialized_hypertable_2
  9. job_id                 | 1001
  10. last_run_started_at    | 2020-10-02 09:38:06.871953-04
  11. last_successful_finish | 2020-10-02 09:38:06.932675-04
  12. last_run_status        | Success
  13. job_status             | Scheduled
  14. last_run_duration      | 00:00:00.060722
  15. next_scheduled_run     | 2020-10-02 10:38:06.932675-04
  16. total_runs             | 1
  17. total_successes        | 1
  18. total_failures         | 0