How to monitor Heroku Postgres using heroku-pg-extras

Michael Yin

Last updated on January 06 2022

Table of Contents

Django Heroku Tutorial Series:

  1. Heroku vs AWS Which is Best for Your Django project
  2. How to deploy Django project to Heroku using Docker
  3. How to deploy Python project to Heroku in Gitlab CI
  4. How to use Heroku Pipeline
  5. Heroku Logs Tutorial
  6. How to monitor Heroku Postgres using heroku-pg-extras

Django Dokku Tutorial Series:

  1. How to deploy Django project to Dokku
  2. How to deploy Django project to Dokku with Docker

Introduction

In this Heroku tip, I will talk about how to use heroku-pg-extras plugin to monitor Heroku Postgres.

Background

As you know, Heroku would send some Postgres metric data to the log stream but it is not handy for people to check if the Postgres db is working fine.

heroku-pg-extras can be used to obtain performance data about a Heroku Postgres db, which is very useful when solving the performance issue.

You can check index, lock, connection, cache and other statistics using this plugin.

Postgres has built-in statistics collector which automatically aggregates most of these metrics internally, heroku-pg-extras would help you build SQL query to get the metric value from the predefined statistics views.

Install heroku-pg-extras

$ heroku plugins:install heroku-pg-extras

Please note that this plugin is installed to your Heroku CLI instead of some specific Heroku app, which means, after you install it, you can use it with different Heroku apps in local.

NOTE: If you want to know more about some command, just type $ heroku help pg:<command>

Command list

pg:cache-hit

$ heroku pg:cache-hit

      name      |         ratio
----------------|------------------------
 index hit rate | 0.99978961173382966536
 table hit rate | 0.99968176561451181147
(2 rows)

This command provides information on the efficiency of the buffer cache, for both index reads (index hit rate) as well as table reads (table hit rate).

If the hit ratio is low, then you might need to consider upgrade your Postgres plan.

pg:index-usage

                   relname                    | percent_of_times_index_used | rows_in_table
----------------------------------------------|-----------------------------|---------------
 django_session                               | 99                          |          3505
 auth_permission                              | 98                          |           451
 django_migrations                            | Insufficient data           |           419

This command provides information on the efficiency of indexes, represented as what percentage of query used the index.

A low percentage can indicate under indexing, or wrong data being indexed.

pg:locks

$ heroku pg:locks

This command displays queries that have taken out an exlusive lock on a relation. Exclusive locks typically prevent other operations on that relation from taking place, and can be a cause of "hung" queries that are waiting for a lock to be granted.

pg:outliers

$ heroku pg:outliers

This command displays statements, obtained from pg_stat_statements, ordered by the amount of time to execute in aggregate. This includes the statement itself, the total execution time for that statement, the proportion of total execution time for all statements that statement has taken up, the number of times that statement has been called, and the amount of time that statement spent on synchronous I/O (reading/writing from the filesystem).

Typically, an efficient query will have an appropriate ratio of calls to total execution time, with as little time spent on I/O as possible. Queries that have a high total execution time but low call count should be investigated to improve their performance. Queries that have a high proportion of execution time being spent on synchronous I/O should also be investigated.

pg:calls

$ heroku pg:calls

This command is much like pg:outliers, but ordered by the number of times a statement has been called

pg:blocking

$ heroku pg:blocking

This command displays statements that are currently holding locks that other statements are waiting to be released. This can be used in conjunction with pg:locks to determine which statements need to be terminated in order to resolve lock contention.

pg:seq-scans

$ heroku pg:seq-scans

                     name                     | count
----------------------------------------------|-------
 django_content_type                          | 30819
 blog_blogcategory                            | 18928
 base_socialmediasettings                     | 17926
 blog_blogpageblogcategory                    | 15959
 blog_blogpagegalleryimage                    | 14716
 base_defaulthomepage                         | 11568

This command displays the number of sequential scans recorded against all tables, descending by count of sequential scans. Tables that have very high numbers of sequential scans may be underindexed, and it may be worth investigating queries that read from these tables.

pg:long-running-queries

$ heroku pg:long-running-queries

  pid  |    duration     |                                      query
-------|-----------------|---------------------------------------------------------------------------------------
 19578 | 02:29:11.200129 | EXPLAIN SELECT  "students".* FROM "students"  WHERE "students"."id" = 1450645 LIMIT 1
 19465 | 02:26:05.542653 | EXPLAIN SELECT  "students".* FROM "students"  WHERE "students"."id" = 1889881 LIMIT 1
 19632 | 02:24:46.962818 | EXPLAIN SELECT  "students".* FROM "students"  WHERE "students"."id" = 1581884 LIMIT 1

This command displays currently running queries, that have been running for longer than 5 minutes, descending by duration.

Conclusion

You can go to heroku-pg-extras homepage to learn more commands.

Django Heroku Tutorial Series:

  1. Heroku vs AWS Which is Best for Your Django project
  2. How to deploy Django project to Heroku using Docker
  3. How to deploy Python project to Heroku in Gitlab CI
  4. How to use Heroku Pipeline
  5. Heroku Logs Tutorial
  6. How to monitor Heroku Postgres using heroku-pg-extras

Django Dokku Tutorial Series:

  1. How to deploy Django project to Dokku
  2. How to deploy Django project to Dokku with Docker

Launch Products Faster with Django

SaaS Hammer helps you launch products in faster way. It contains all the foundations you need so you can focus on your product.

Michael Yin

Michael is a Full Stack Developer from China who loves writing code, tutorials about Django, and modern frontend tech.

He has published some ebooks on leanpub and tech course on testdriven.io.

He is also the founder of the AccordBox which provides the web development services.

Table of Contents

Django SaaS Template

It aims to save your time and money building your product, developed by Michael Yin

Learn More

This book will teach you how to build a SPA (single-page application) with React and Wagtail CMS

Read More

Subscribe

Get notified about new great Web Development Tutorial