Postgres::Vacuum::Monitor

Postgres::Vacuum::Monitor provides queries that provide information about the number of dead tuples and long running queries. This information helps to diagnose and monitor two things: 1) That the current auto vacuum settings are working and keeping up. 2) That there are no long running queries affecting the auto vacuuming daemon.

Installation

Add this line to your application's Gemfile:

gem 'postgres-vacuum-monitor'

And then execute:

$ bundle install

Or install it yourself as:

$ gem install postgres-vacuum-monitor

Usage

The job itself needs a class to report the information and can be configured by doing:

Postgres::Vacuum::Monitor.configure do |config|
  config.monitor_reporter_class_name = 'MetricsReporter'
end

The class needs to follow this interface:

class MetricsReporter
  def report_event(name, attributes)
  end
end

For long running queries, the event name is LongQueries and the attributes are:

{
  database_name: # The name of the database.
  start_time: # When the query started .
  running_time: # How long has it been running in seconds.
  application_name: # What's the application name that is running the query.
  query: # The offending query.
}

For auto vacuum the attributes are the following:

{
  database_name: # The name of the database.
  table: # Table name.
  table_size: # How big is the table.
  dead_tuples: # How many dead tuples are in the table.
  tuples_over_limit: # How many dead tuples are over the auto vacuumer threshold.
}

Testing

To run the rspec tests, you need to create a DB called postgres_vacuum_monitor_test.

New relic queries

I use New relic and use the following NRQL to create dashboards:

Tuples over limit

SELECT percentile(tuples_over_limit, 95) from AutoVacuumLagging facet table where appName = 'my-app' TIMESERIES 30 minutes since 1 day ago

Dead tuples

SELECT percentile(dead_tuples) FROM AutoVacuumLagging facet table where appName = 'my-app' SINCE 1 DAY AGO TIMESERIES

Long running queries

SELECT application_name, query, running_time, start_time FROM LongQueries

Tables that need to be vacuumed

SELECT uniques(table) FROM AutoVacuumLagging where appName = 'my-app' since 30 minutes ago

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/salsify/postgres-vacuum-monitor.