Welcome to django-postgres-metrics’s documentation!

Background. Why? What?

The original idea for this library comes from Craig Kerstiens’s talk “Postgres at any scale” at PyCon Canada 2017. In his talk, Craig pointed out a bunch of metrics one should look at to understand why a PostgreSQL database could be “slow” or not perform as expected.

In many cases, the root cause for “PostgreSQL being slow” is the lack of memory, full table scans, and others. For example, a cache hit ratio of less than 99% means that each of cache miss queries needs to go to the filesystem. Filesystem access is significantly slower than RAM access. Giving the PostgreSQL instance more memory will very likely improve performance.

This project aims at showing these and other metrics in the Django Admin making such bottlenecks visible.

Contributing

Issuing a new Release

  • Install bumpversion with:

    $ pip install git+ssh://git@github.com:MarkusH/bumpversion.git@sign#egg=bumpversion

  • Install twine with:

    $ pip install twine

  • Determine next version number from the changelog.rst (ensuring to follow SemVer)

  • Ensure changelog.rst is representative with new version number and commit possible changes.

  • Update the version number with bumpversion:

    $ bumpversion $part

    (instead of $part you can use major, minor, or patch.

  • git push --tags origin master

  • Check for TravisCI to complete. If TravisCI fails due to code errors, go back to the start and bump the $part with patch

  • Build artifacts with:

    $ python setup.py sdist bdist_wheel

  • Upload artifacts with:

    $ twine upload -s dist/*$newver*

  • Add likely new version to at the top of changelog.rst

postgres_metrics

postgres_metrics package

Submodules

postgres_metrics.apps module

class postgres_metrics.apps.PostgresMetrics(app_name, app_module)[source]

Bases: django.apps.config.AppConfig

name = 'postgres_metrics'
verbose_name

postgres_metrics.metrics module

class postgres_metrics.metrics.AvailableExtensions[source]

Bases: postgres_metrics.metrics.Metric

PostgreSQL can be extended by installing extensions with the CREATE EXTENSION command. The list of available extensions on each database is shown below.

label = 'Available Extensions'
slug = 'available-extensions'
sql = '\n SELECT\n name,\n default_version,\n installed_version,\n comment\n FROM\n pg_available_extensions\n ORDER BY\n name\n ;\n '
class postgres_metrics.metrics.CacheHitsMetric[source]

Bases: postgres_metrics.metrics.Metric

The typical rule for most applications is that only a fraction of its data is regularly accessed. As with many other things data can tend to follow the 80/20 rule with 20% of your data accounting for 80% of the reads and often times its higher than this. Postgres itself actually tracks access patterns of your data and will on its own keep frequently accessed data in cache. Generally you want your database to have a cache hit rate of about 99%.

(Source: http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/)

label = 'Cache Hits'
slug = 'cache-hits'
sql = "\n WITH cache AS (\n SELECT\n sum(heap_blks_read) heap_read,\n sum(heap_blks_hit) heap_hit,\n sum(heap_blks_hit) + sum(heap_blks_read) heap_sum\n FROM\n pg_statio_user_tables\n ) SELECT\n heap_read,\n heap_hit,\n CASE\n WHEN heap_sum = 0 THEN 'N/A'\n ELSE (heap_hit / heap_sum)::text\n END ratio\n FROM\n cache\n ;\n "
class postgres_metrics.metrics.IndexUsageMetric[source]

Bases: postgres_metrics.metrics.Metric

While there is no perfect answer, if you’re not somewhere around 99% on any table over 10,000 rows you may want to consider adding an index. When examining where to add an index you should look at what kind of queries you’re running. Generally you’ll want to add indexes where you’re looking up by some other id or on values that you’re commonly filtering on such as created_at fields.

(Source: http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/)

label = 'Index Usage'
slug = 'index-usage'
sql = '\n SELECT\n relname,\n 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,\n n_live_tup rows_in_table\n FROM\n pg_stat_user_tables\n WHERE\n seq_scan + idx_scan > 0\n ORDER BY\n percent_of_times_index_used DESC\n ;\n '
class postgres_metrics.metrics.Metric[source]

Bases: object

description
label = ''
slug = ''
sql = ''
class postgres_metrics.metrics.MetricRegistry[source]

Bases: object

register(metric)[source]
sorted

postgres_metrics.urls module

postgres_metrics.urls.re_path(route, view, kwargs=None, name=None, *, Pattern=<class 'django.urls.resolvers.RegexPattern'>)

postgres_metrics.views module

class postgres_metrics.views.MetricResult(connection, name)[source]

Bases: object

alias
dsn
postgres_metrics.views.metrics_view(request, name)[source]

Module contents

Changelog

0.2.0

  • Switched to Read The Docs theme for docs.
  • Added “Available Extensions” metric.
  • Fixed table styling in metric views. The tables now look more like Django Admin’s ChangeList

0.1.1

  • Excluded tests from built packages.

0.1.0

  • Inital Release

Indices and tables