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?

  • Perform deep analysis on why this specific query is so slow and attempt to optimise it.
  • Restructure our authorisation framework to play more nicely with these kind of searches.
  • Limit the kind of searches a user can perform — for example, can we move away from expensive LIKE type queries?

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

  1. The timeout applies only to queries coming from the search. We do not want a global timeout to be applied across all database queries (since some actions are expected to take longer than others).
  2. We want to be alerted when the query does time out and we want that alert to contain information on what the query was for.
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.

--

--

--

Python Software Developer based in Copenhagen, Denmark

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Functions and Relations

Build a microservices system from scratch — commit 7

Ready, Aim, FIRE! with the latest update from Sansar

GDPR: Impact to Your Data Management Landscape: Part 3

Docker Basic Commands — Part 3

How MongoDB Atlas Helps TripCloud Move at Startup Speed

Building your first chatbot using Dialogflow and C# — PART II (Implementation)

Announcing The Corda Public Beta

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
Paul Gilmartin

Paul Gilmartin

Python Software Developer based in Copenhagen, Denmark

More from Medium

Django — Extended pagination

Handle MEDIA files in production — Django

floppy disks and Django media

An Introduction to GraphQL in Django

How to Make a Webhook Receiver in Django