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

Reference Documentation

AppConfig

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

Bases: django.apps.config.AppConfig

name = 'postgres_metrics'
verbose_name = 'PostgreSQL Metrics'

Metrics

class postgres_metrics.metrics.MetricRegistry[source]

Bases: object

register(metric)[source]

Given a class (not class instance) of Metric, adds it to the available list of metrics to show it to a user.

sorted

All registered metrics ordered by their label.

unregister(slug)[source]

Remove the metric slug from the registry. Raises a KeyError if the metric isn’t registered.

class postgres_metrics.metrics.Metric(ordering=None)[source]

Bases: object

The superclass for all Metric implementations. Inherit from this to define your own metric.

If you want to implement your own metric, here’s a gist:

from django.utils.translation import ugettext_lazy as _
from postgres_metrics.metrics import Metric, registry


class DjangoMigrationStatistics(Metric):
    """
    Count the number of applied Django migrations per app and sort by
    descending count and ascending app name.
    """
    label = _('Migration Statistics')
    slug = 'django-migration-statistics'
    ordering = '-2.1'
    sql = '''
        SELECT
            app, count(*)
        FROM
            django_migrations
        GROUP BY
            app
        {ORDER_BY}
        ;
    '''


registry.register(DjangoMigrationStatistics)
description

Don’t define this value directly. Instead define a docstring on the metric class.

The docstring will be processed by Python internals to trim leading white spaces and fix newlines. '\r\n' and '\r' line breaks will be normalized to '\n'. Two or more consecutive occurances of '\n' mark a paragraph which will be escaped and wrapped in <p></p> HTML tags. Further, each paragraph will call into Django’s urlize() method to create <a></a> HTML tags around links.

full_sql

The sql formatted with get_order_by_clause().

get_data()[source]

Iterate over all configured PostgreSQL database and execute the full_sql there.

Returns:Returns a list of MetricResult instances.
Return type:list
get_order_by_clause()[source]

Turn an ordering string like 1.5.-3.-2.6 into the respective SQL.

SQL’s column numbering starts at 1, so do we here. Given self.ordering as 1.5.-3.-2.6 return a string ORDER BY 1 ASC, 5 ASC, 3 DESC, 2 DESC, 6 ASC.

Ensures that each column (excluding the - prefix) is an integer by calling int() on it.

label = ''

The label is what is used in the Django Admin views. Consider marking this string as translateable.

ordering = ''

The default ordering that should be applied to the SQL query by default. This needs to be a valid ordering string as defined on parsed_ordering.

parsed_ordering

Turn an ordering string like 1.5.-3.-2.6 into the respective abstraction.

Given self.ordering as 1.5.-3.-2.6 return a lis of 2-tuples like [('', 1), ('', 5), ('-', 3), ('-', 2), ('', 6)].

slug = ''

A URL safe representation of the label and unique across all metrics.

sql = ''

The actual SQL statement that is being used to query the database. In order to make use of the ordering, include the string {ORDER_BY} in the query as necessary. For details on that value see get_order_by_clause().

class postgres_metrics.metrics.MetricResult(connection, records)[source]

Bases: object

Hold a metric’s data for a single database.

alias

The alias under which a database connection is known to Django.

dsn

The PostgreSQL connection string per psycopg2.

records

The rows returned by a metric for the given database.

class postgres_metrics.metrics.MetricHeader(name, index, ordering)[source]

Bases: object

A single column header; mostly takes care of a column’s sorting status.

ascending

True if the column is in ascending order False otherwise

static join_ordering(ordering)[source]
sort_priority

The priority of the columns order. 1 (high), n(low). Default 0.

url_primary

Querystring value making this the primary sorting header.

url_remove

Querystring value removing this column from sorting.

url_toggle

Querystring value toggling ascending/descending for this header.

class postgres_metrics.metrics.AvailableExtensions[source]

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

class postgres_metrics.metrics.CacheHitsMetric[source]

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/)

class postgres_metrics.metrics.IndexUsageMetric[source]

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/)

URLs

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

Views

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

Changelog

0.3.0

  • Added description to setup.py
  • The metric results can now be sorted on any column

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