Django + Postgres: The Hunt for Long Running Queries

The Set-Up

The Incident

SELECT * FROM "table_1" INNER JOIN "table_2" ON ("table_1"."foreign_key_id" = "table_2"."id") WHERE (UPPER("table_2"."name"::text) LIKE UPPER(%Paul%) AND "table_1"."another_foreign_key_id" = 12345

What next?

The Hunt

from django.db import connection


def get_queries_active_for_duration(duration_in_minutes):
cursor = connection.cursor()
select_statement = """
SELECT
pid,query,
pg_stat_activity.query_start AS query_start,
now() - pg_stat_activity.query_start AS duration,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) >
interval '{duration_in_minutes} minutes'
AND state='active' ORDER BY duration;
""".format(duration_in_minutes=duration_in_minutes)
cursor.execute(select_statement)
row = cursor.fetchall()
return row

The Kill

try:
with transaction.atomic():
connection.cursor().execute(
"SET LOCAL statement_timeout = '60s';".format(timeout))
result = list(queryset) # evaluates and speaks to postgres
except OperationalError:
erorr_message = 'Cancelled search query after 60s.'
message += 'Query SQL:\n {}'.format(queryset.query)
# Logic for reporting this message to e.g. rollbar goes here.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store