postgres_metrics.metrics module
- class postgres_metrics.metrics.AvailableExtensions(ordering=None)[source]
Bases:
Metric
PostgreSQL can be extended by installing extensions with the CREATE EXTENSION command. The list of available extensions on each database is shown below.
- description = '<p>PostgreSQL can be extended by installing extensions with the CREATE EXTENSION command. The list of available extensions on each database is shown below.</p>'
- get_record_style(record)[source]
Given a single record from
MetricResult
, decide how to style it. Most likely to be used with the template tagrecord_style()
.By default, django-postgres-metrics supports for styling classes:
ok
warning
critical
info
Override this method and return one of the above strings or
None
to apply the given style to the entire record. In the Django Admin this will highlight the entire row.
- label = 'Available Extensions'
The label is what is used in the Django Admin views. Consider making this string translateable.
- ordering = '1'
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
.
- permission_key = 'postgres_metrics.can_view_metric_available_extensions'
- permission_name = 'can_view_metric_available_extensions'
- slug = 'available-extensions'
A URL safe representation of the label and unique across all metrics.
- sql = '\n SELECT\n name,\n default_version,\n installed_version,\n comment\n FROM\n pg_available_extensions\n {ORDER_BY}\n ;\n '
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 seeget_order_by_clause()
.
- class postgres_metrics.metrics.CacheHits(ordering=None)[source]
Bases:
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/)
- description = '<p>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%.</p>\n\n<p>(Source: <a href="http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/">http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/</a>)</p>'
- get_record_item_style(record, item, index)[source]
Given a single record from
MetricResult
, the value of the current item within, and the current item’s index, decide how to style it. Most likely to be used with the template tagrecord_item_style()
.By default, django-postgres-metrics supports for styling classes:
ok
warning
critical
info
Override this method and return one of the above strings or
None
to apply the given style to the entire record. In the Django Admin this will highlight the entire row.
- header_labels = ['Reads', 'Hits', 'Ratio']
A list of strings used as column headers in the admin. Consider making the strings translateable. If the attribute is undefined, the column names returned by the database will be used.
- label = 'Cache Hits'
The label is what is used in the Django Admin views. Consider making this string translateable.
- permission_key = 'postgres_metrics.can_view_metric_cache_hits'
- permission_name = 'can_view_metric_cache_hits'
- slug = 'cache-hits'
A URL safe representation of the label and unique across all metrics.
- 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 {ORDER_BY}\n ;\n "
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 seeget_order_by_clause()
.
- class postgres_metrics.metrics.DetailedIndexUsage(ordering=None)[source]
Bases:
Metric
A metric similar to “Index Usage” but broken down by index.
The “index scan over sequential scan” column shows how frequently an index was used in comparison to the total number of sequential and index scans on the table.
Similarly, the “index scan on table” shows how often an index was used compared to the other indexes on the table.
- description = '<p>A metric similar to "Index Usage" but broken down by index.</p>\n\n<p>The "index scan over sequential scan" column shows how frequently an index was used in comparison to the total number of sequential and index scans on the table.</p>\n\n<p>Similarly, the "index scan on table" shows how often an index was used compared to the other indexes on the table.</p>'
- header_labels = ['Table', 'Index', 'Index Scan over Sequential Scan', 'Index Scan on table']
A list of strings used as column headers in the admin. Consider making the strings translateable. If the attribute is undefined, the column names returned by the database will be used.
- label = 'Detailed Index Usage'
The label is what is used in the Django Admin views. Consider making this string translateable.
- ordering = '1.2'
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
.
- permission_key = 'postgres_metrics.can_view_metric_detailed_index_usage'
- permission_name = 'can_view_metric_detailed_index_usage'
- slug = 'detailed-index-usage'
A URL safe representation of the label and unique across all metrics.
- sql = '\n SELECT\n t.relname,\n i.indexrelname,\n CASE t.seq_scan + t.idx_scan\n WHEN 0\n THEN round(0.0, 2)\n ELSE\n round(\n (100::float * i.idx_scan / (t.seq_scan + t.idx_scan))::numeric,\n 2::int\n )\n END,\n CASE t.idx_scan\n WHEN 0\n THEN round(0.0, 2)\n ELSE\n round((100::float * i.idx_scan / t.idx_scan)::numeric, 2::int)\n END\n FROM\n pg_stat_user_tables t\n INNER JOIN\n pg_stat_user_indexes i\n ON t.relid = i.relid\n {ORDER_BY}\n ;\n '
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 seeget_order_by_clause()
.
- class postgres_metrics.metrics.IndexSize(ordering=None)[source]
Bases:
Metric
- description = ''
- header_labels = ['Table', 'Index', 'Size']
A list of strings used as column headers in the admin. Consider making the strings translateable. If the attribute is undefined, the column names returned by the database will be used.
- label = 'Index Size'
The label is what is used in the Django Admin views. Consider making this string translateable.
- ordering = '1.2'
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
.
- permission_key = 'postgres_metrics.can_view_metric_index_size'
- permission_name = 'can_view_metric_index_size'
- slug = 'index-size'
A URL safe representation of the label and unique across all metrics.
- sql = '\n SELECT\n relname,\n indexrelname,\n pg_size_pretty(index_size)\n FROM (\n SELECT\n relname,\n indexrelname,\n pg_relation_size(indexrelid) AS index_size\n FROM\n pg_stat_user_indexes\n {ORDER_BY}\n ) AS t\n ;\n '
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 seeget_order_by_clause()
.
- class postgres_metrics.metrics.IndexUsage(ordering=None)[source]
Bases:
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/)
- description = '<p>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.</p>\n\n<p>(Source: <a href="http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/">http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/</a>)</p>'
- get_record_style(record)[source]
Given a single record from
MetricResult
, decide how to style it. Most likely to be used with the template tagrecord_style()
.By default, django-postgres-metrics supports for styling classes:
ok
warning
critical
info
Override this method and return one of the above strings or
None
to apply the given style to the entire record. In the Django Admin this will highlight the entire row.
- header_labels = ['Table', 'Index used (in %)', 'Num rows']
A list of strings used as column headers in the admin. Consider making the strings translateable. If the attribute is undefined, the column names returned by the database will be used.
- label = 'Index Usage'
The label is what is used in the Django Admin views. Consider making this string translateable.
- ordering = '2'
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
.
- permission_key = 'postgres_metrics.can_view_metric_index_usage'
- permission_name = 'can_view_metric_index_usage'
- slug = 'index-usage'
A URL safe representation of the label and unique across all metrics.
- sql = '\n SELECT\n relname,\n round(\n (100::float * idx_scan / (seq_scan + idx_scan))::numeric,\n 2::int\n ),\n n_live_tup\n FROM\n pg_stat_user_tables\n WHERE\n seq_scan + idx_scan > 0\n {ORDER_BY}\n ;\n '
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 seeget_order_by_clause()
.
- 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’surlize()
method to create<a></a>
HTML tags around links.
- classmethod can_view(user)[source]
Check that the given a user instance has access to the metric.
This requires the user instance to have the
django.contrib.auth.models.User.is_superuser
anddjango.contrib.auth.models.User.is_staff
flags as well as thedjango.contrib.auth.models.User.has_perm()
method.Users with
is_superuser=True
will always have access to a metric. Users withis_staff=True
will have access if and only if the user has the permission for a metric.
- full_sql
The
sql
formatted withget_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:
- 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
ordering
as1.5.-3.-2.6
return a stringORDER BY 1 ASC, 5 ASC, 3 DESC, 2 DESC, 6 ASC
.Ensures that each column (excluding the
-
prefix) is an integer by callingint()
on it.
- get_record_item_style(record, item, index)[source]
Given a single record from
MetricResult
, the value of the current item within, and the current item’s index, decide how to style it. Most likely to be used with the template tagrecord_item_style()
.By default, django-postgres-metrics supports for styling classes:
ok
warning
critical
info
Override this method and return one of the above strings or
None
to apply the given style to the entire record. In the Django Admin this will highlight the entire row.
- get_record_style(record)[source]
Given a single record from
MetricResult
, decide how to style it. Most likely to be used with the template tagrecord_style()
.By default, django-postgres-metrics supports for styling classes:
ok
warning
critical
info
Override this method and return one of the above strings or
None
to apply the given style to the entire record. In the Django Admin this will highlight the entire row.
- header_labels = None
A list of strings used as column headers in the admin. Consider making the strings translateable. If the attribute is undefined, the column names returned by the database will be used.
- headers
A wrapper around the
header_labels
to make the tables in the admin sortable.
- label = ''
The label is what is used in the Django Admin views. Consider making this string translateable.
- max_pg_version = None
- min_pg_version = None
- 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
ordering
as1.5.-3.-2.6
return a list 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 seeget_order_by_clause()
.
- 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 orderFalse
otherwise
- 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.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.
- property sorted
All registered metrics ordered by their label.
- 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.
- records
The rows returned by a metric for the given database.
- holds_data = True
- class postgres_metrics.metrics.NoMetricResult(connection, reason)[source]
Bases:
MetricResult
Internal class to pass an error message to the template when a metric is not available.
- holds_data = False
- class postgres_metrics.metrics.SequenceUsage(ordering=None)[source]
Bases:
Metric
Show the sequence usage within a PostgreSQL database. A usage over 75% will be marked as red, and a usage over 50% will be marked as yellow.
- description = '<p>Show the sequence usage within a PostgreSQL database. A usage over 75% will be marked as red, and a usage over 50% will be marked as yellow.</p>'
- get_record_style(record)[source]
Given a single record from
MetricResult
, decide how to style it. Most likely to be used with the template tagrecord_style()
.By default, django-postgres-metrics supports for styling classes:
ok
warning
critical
info
Override this method and return one of the above strings or
None
to apply the given style to the entire record. In the Django Admin this will highlight the entire row.
- header_labels = ['Table', 'Column', 'Sequence', 'Last value', 'Max value', 'Used (in %)']
A list of strings used as column headers in the admin. Consider making the strings translateable. If the attribute is undefined, the column names returned by the database will be used.
- label = 'Sequence Usage'
The label is what is used in the Django Admin views. Consider making this string translateable.
- min_pg_version = 100000
- ordering = '-6.1.2.3'
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
.
- permission_key = 'postgres_metrics.can_view_metric_sequence_usage'
- permission_name = 'can_view_metric_sequence_usage'
- slug = 'sequence-usage'
A URL safe representation of the label and unique across all metrics.
- sql = "\n SELECT\n tabcls.relname,\n attrib.attname,\n seqcls.relname,\n seq.last_value,\n seq.max_value,\n round(\n (\n 100::float * COALESCE(seq.last_value, 0)\n / (seq.max_value - seq.start_value + 1)\n )::numeric,\n 2::int\n )\n FROM\n pg_class AS seqcls\n INNER JOIN\n pg_sequences AS seq\n ON seqcls.relname = seq.sequencename\n INNER JOIN\n pg_depend AS dep\n ON seqcls.relfilenode = dep.objid\n INNER JOIN\n pg_class AS tabcls\n ON dep.refobjid = tabcls.relfilenode\n INNER JOIN\n pg_attribute AS attrib\n ON\n attrib.attnum = dep.refobjsubid\n AND attrib.attrelid = dep.refobjid\n WHERE\n seqcls.relkind = 'S'\n {ORDER_BY}\n ;\n "
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 seeget_order_by_clause()
.
- class postgres_metrics.metrics.TableSize(ordering=None)[source]
Bases:
Metric
The “size” of a table in PostgreSQL can be different, depending on what to include when calculating “the size”.
The “Total size” for a relation is equal to the “Table size” plus all indexes.
The “size of * fork” refers to the “main” data fork, the Free Space Map (fsm), Visibility Map (vm), and the initialization fork.
See also the PostgreSQL documentation on the physical storage: https://www.postgresql.org/docs/current/storage.html
- description = '<p>The "size" of a table in PostgreSQL can be different, depending on what to include when calculating "the size".</p>\n\n<p>The "Total size" for a relation is equal to the "Table size" plus all indexes.</p>\n\n<p>The "size of * fork" refers to the "main" data fork, the Free Space Map (fsm), Visibility Map (vm), and the initialization fork.</p>\n\n<p>See also the PostgreSQL documentation on the physical storage: <a href="https://www.postgresql.org/docs/current/storage.html">https://www.postgresql.org/docs/current/storage.html</a></p>'
- header_labels = ['Table', 'Total size', 'Table size', "Size of 'main' fork", "Size of 'fsm' fork", "Size of 'vm' fork", "Size of 'init' fork"]
A list of strings used as column headers in the admin. Consider making the strings translateable. If the attribute is undefined, the column names returned by the database will be used.
- label = 'Table Size'
The label is what is used in the Django Admin views. Consider making this string translateable.
- ordering = '1'
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
.
- permission_key = 'postgres_metrics.can_view_metric_table_size'
- permission_name = 'can_view_metric_table_size'
- slug = 'table-size'
A URL safe representation of the label and unique across all metrics.
- sql = "\n SELECT\n relname,\n pg_size_pretty(total_size),\n pg_size_pretty(table_size),\n pg_size_pretty(relation_size_main),\n pg_size_pretty(relation_size_fsm),\n pg_size_pretty(relation_size_vm),\n pg_size_pretty(relation_size_init)\n FROM (\n SELECT\n relname,\n pg_total_relation_size(relid) AS total_size,\n pg_table_size(relid) AS table_size,\n pg_relation_size(relid, 'main') AS relation_size_main,\n pg_relation_size(relid, 'fsm') AS relation_size_fsm,\n pg_relation_size(relid, 'vm') AS relation_size_vm,\n pg_relation_size(relid, 'init') AS relation_size_init\n FROM\n pg_stat_user_tables\n {ORDER_BY}\n ) AS t\n ;\n "
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 seeget_order_by_clause()
.