Extend Postgres Overview dashboard

[onprem]

We have identified some panels that would be a great addition to the Postgres dashboard, giving more insights into how it's performing. These dashboards are especially useful with tobs as the metrics are important for debugging ingest performance issues with Promscale and TimescaleDB.

• Tuples inserted

  rate(pg_stat_database_tup_inserted{datname="tsdb",cloud_timescale_com_service_id="$db_service_id", container="metrics"}[$rate_interval])
• Tuples fetched and returned

  rate(pg_stat_database_tup_fetched{cloud_timescale_com_service_id="$db_service_id", datname="tsdb", container="metrics"}[5m]) rate(pg_stat_database_tup_returned{cloud_timescale_com_service_id="$db_service_id", datname="tsdb", container="metrics"}[5m])
• DB read & write times

  rate(pg_stat_database_blk_read_time{cloud_timescale_com_service_id="$db_service_id", datname="tsdb", container="metrics"}[$rate_interval]) rate(pg_stat_database_blk_write_time{cloud_timescale_com_service_id="$db_service_id", datname="tsdb", container="metrics"}[$rate_interval])
• Transaction max duration (seconds)

  pg_stat_activity_max_tx_duration{serviceid="$db_service_id", container="metrics", datname="tsdb", state="active"}
• Transaction rollbacks

  rate(pg_stat_database_xact_rollback{serviceid="$db_service_id", datname="tsdb", container="metrics"}[5m])
• Number of PG locks

  sum by (mode) (pg_locks_count{serviceid="$db_service_id", container="metrics", datname="tsdb"})
• % of requested checkpoints

  rate(pg_stat_bgwriter_checkpoints_req{serviceid="$db_service_id", container="metrics"}[5m]) / (rate(pg_stat_bgwriter_checkpoints_req{serviceid="$db_service_id", container="metrics"}[5m]) + rate(pg_stat_bgwriter_checkpoints_timed{serviceid="$db_service_id", container="metrics"}[5m])) * 100
• Timed and requested checkpoints

  idelta(pg_stat_bgwriter_checkpoints_timed{serviceid="$db_service_id"}[1m]) idelta(pg_stat_bgwriter_checkpoints_req{serviceid="$db_service_id"}[1m])
• Buffers Cleaned
  • BGwriter cleaned

    rate(pg_stat_bgwriter_buffers_clean{serviceid="$db_service_id", container="metrics"}[5m])
  • Backend cleaned

    rate(pg_stat_bgwriter_buffers_backend{serviceid="$db_service_id", container="metrics"}[5m])
  • Checkpoint cleaned

    rate(pg_stat_bgwriter_buffers_checkpoint{serviceid="$db_service_id", container="metrics"}[5m])
• BGwriter written 5m

  (rate(pg_stat_bgwriter_buffers_clean{serviceid="$db_service_id", container="metrics"}[5m]) + rate(pg_stat_bgwriter_buffers_backend{serviceid="$db_service_id", container="metrics"}[5m])+rate(pg_stat_bgwriter_buffers_checkpoint{serviceid="$db_service_id", container="metrics"}[5m]))*8192