1. Find the SQL to run

We write Django migrations in Python, but they ultimately end up running a series of SQL statements. To run a migration by hand, you need those SQL statements, so you can run them yourself.

You can display the SQL for a migration with Django's sqlmigrate command like:

$ ./manage.py sqlmigrate <app> <prefix>

This outputs each migration operation with a commented header describing what it does and then its actual statements. Replace <app> with the label of the app that the migration lives in. Replace <prefix> with a unique prefix of the migration's name - normally a four digit number like 0003.

For example, to show the SQL for the core app's migration with name starting "0003", you would run:

$ ./manage.py sqlmigrate core 0003
BEGIN;
--
-- Add field page_count to book
--
ALTER TABLE "core_book" ADD COLUMN "page_count" integer NULL;
COMMIT;

You'll only see the bookending with BEGIN and COMMIT on databases that support transactional schema changes (SQLite and PostgreSQL, of Django's built-in backends). These may be disabled per-migration, when necessary.

If any operations cannot be run as SQL, they will have the message THIS OPERATION CANNOT BE WRITTEN AS SQL (from Django 4.1, older versions have slightly different wording). This normally means use of the RunPython operation. For such operations, will need to figure out how to run them by hand separate to the process I'm covering here. You could also consider splitting them into their own migration.

A small note: it's best to run sqlmigrate against your production database. For certain operations, Django queries the database to find names of objects, patricularly index names when migrating older index definitions. Depending on how your various environments' databases were created and migrated over time, these names can be different. Thus, the SQL that Django generates on your staging server may be different to that on production. But on the other hand, using your production settings with an unexecuted migration isn't always easy, so you may just want to beware of this issue and adjust SQL when necessary.

2. Execute the SQL statement-by-statement

Open up your database's SQL shell on the target environment with Django's dbshell command:

$ ./manage.py dbshell

Here you can run the migration's SQL statements from sqlmigrate, one by one. Skip the comments from sqlmigrate (the lines starting --), and make sure you copy whole SQL statements that end with ;.

For example, running the above on PostgreSQL:

$ ./manage.py dbshell
psql (14.4, server 13.5 (Debian 13.5-1.pgdg110+1))
Type "help" for help.

example=# BEGIN;
BEGIN
example=*# ALTER TABLE "core_book" ADD COLUMN "page_count" integer NULL;
ALTER TABLE
example=*# COMMIT;
COMMIT
example=#

Whilst running the migration, you should keep an eye on your database's key metrics with whatever monitoring tools you use. You may also want to use a second dbshell to run some administrative commands, for example in my recent PostgreSQL post I covered finding and stopping queries that block an ALTER TABLE.

One note for databases supporting transactional schema changes (SQLite, PostgreSQL). If the migration has a single schema-changing statement like ALTER TABLE, you can drop BEGIN and COMMIT. This means less SQL to run, and you'll hold schema locks for slightly less time, reducing the risk of the migration affecting your busy production database.

3. Record migration as executed

After you've run your migration's statements by hand, you need to record the migration as executed. If you don't, the migrate command will try to again execute that migration, which could end catastrophically (but will normally just result in an error like "column already exists").

Django's migration systems keeps a record of executed migrations in a table called django_migrations. You can record a migration as executed with this query template:

INSERT INTO django_migrations (app, name, applied) VALUES (<app>, <name>, NOW());

Replace <app> with the label of the app that the migration lives in. Replace <name> with the full name of the migration, which is the migration's filename without the .py extension. (Be careful not to use just a prefix like 0003!)

For example, to mark our example migration as complete:

example=# INSERT INTO django_migrations (app, name, applied) VALUES ('core', '0003_book_page_count', NOW());
INSERT 0 1

You can check your entry looks right by visually comparing it with others:

SELECT * FROM django_migrations ORDER BY applied DESC;

…and just like that, you're done applying the migration!

$ ./manage.py sqlmigrate --backwards <app> <prefix>

DELETE FROM django_migrations WHERE app = <app> AND name = <name>;

It Costs… Money!

pgMustard is a paid product, with a free trial that allows you to explain five query plans (no credit card required). This doesn't include queries with no optimization suggestions.

At current it costs €95 (~$105) a year for a single developer, or €500 (~$525) a year for a team (no limit on members). Both plans allow you to explain an unlimited amount of queries. I think this works well, as optimization is something you may focus on only a few times a year.

There are free tools for visualizing EXPLAIN output; see the list on the PostgreSQL Wiki. I've used two of them before: explain.dalibo.com (the original PEV version) and explain.depesz.com. They're handy, but they're focused on visualizing, so they don't have many interpretation features. For me, pgMustard's prioritized explanations are the main feature. We'll revisit the free tools at the end of this post.

1. Use QuerySet.explain() and All the Options

You use the EXPLAIN statement by prefixing it to the target statement, like:

EXPLAIN SELECT * FROM auth_user;

Django's ORM provides the QuerySet.explain() method to do this for any data-fetching query. It returns the query plan as a string (on PostgreSQL). For example:

In [2]: User.objects.all().explain()
Out[2]: 'Seq Scan on auth_user  (cost=0.00..699.32 rows=25632 width=109)'

Note that you can only use .explain() with methods that return QuerySets. Thus if you want to explain a .count(), .aggregate(), .exists(), etc., try explaining the QuerySet without that last method. This slightly changes the query, but it's often accurate enough.

PostgreSQL has many options for EXPLAIN, of which pgMustard asks you to use at least:

• format json - output in JSON format for machine consumption, rather than the default text format intended for humans
• analyze - run the query and gain actual performance data, not just predicted
• buffers - track disk read metrics
• verbose - add some miscellaneous details

There are also two newer options that pgMustard can use the data from:

• setttings - PostgreSQL 12+, show settings that affect the query plan
• wal - PostgreSQL 13+, add data on the Write Ahead Log, an important data structure

You can pass these options as arguments to QuerySet.explain(), with the value True, except format="json":

User.objects.all().explain(
    format="json", analyze=True, buffers=True, verbose=True, settings=True, wal=True
)

You then want to copy the output, in order to paste it into pgMustard.

The output from explain() is a string, which is a bit fiddly for copying as you don't want the surrounding quote marks, and it may include escaping. You can print() the string to avoid these problems. Here's a function you can use to run explain with all the options and provide copy-able output:

def show_explain(qs):
    print(
        qs.explain(
            format="json",
            analyze=True,
            buffers=True,
            verbose=True,
            settings=True,
            wal=True,
        )
    )

(Remove wal if you're using PostgreSQL < 13, and remove settings if you're using PostgreSQL < 12.)

Pass the QuerySet you're interested in, and you'll get back the query plan as a big chunk of JSON:

In [39]: show_explain(User.objects.filter(email__endswith="@example.org"))
[{"Plan": {"Node Type": "Gather", "Parallel Aware": false, "Startup Cost": 1000.0, "Total Cost": 191384.91, "Plan Rows": 1031, "Plan Width": 72, "Actual Startup Time": 36.64, "Actual Total Time": 4307.309, "Actual Rows": 100001, "Actual Loops": 1, "Output": ["id", "password", "last_login", "is_superuser", "username", "first_name", "last_name", "email", "is_staff", "is_active", "date_joined"], "Workers Planned": 2, "Workers Launched": 2, "Single Copy": false, "Shared Hit Blocks": 16148, "Shared Read Blocks": 120436, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "WAL Records": 0, "WAL FPI": 0, "WAL Bytes": 0, "Plans": [{"Node Type": "Seq Scan", "Parent Relationship": "Outer", "Parallel Aware": true, "Relation Name": "auth_user", "Schema": "public", "Alias": "auth_user", "Startup Cost": 0.0, "Total Cost": 190281.81, "Plan Rows": 430, "Plan Width": 72, "Actual Startup Time": 130.523, "Actual Total Time": 3996.232, "Actual Rows": 33334, "Actual Loops": 3, "Output": ["id", "password", "last_login", "is_superuser", "username", "first_name", "last_name", "email", "is_staff", "is_active", "date_joined"], "Filter": "((auth_user.email)::text ~~ '%@example.org'::text)", "Rows Removed by Filter": 3300013, "Shared Hit Blocks": 16148, "Shared Read Blocks": 120436, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "WAL Records": 0, "WAL FPI": 0, "WAL Bytes": 0, "Workers": [{"Worker Number": 0, "Actual Startup Time": 177.384, "Actual Total Time": 3897.37, "Actual Rows": 31986, "Actual Loops": 1, "JIT": {"Functions": 4, "Options": {"Inlining": false, "Optimization": false, "Expressions": true, "Deforming": true}, "Timing": {"Generation": 12.21, "Inlining": 0.0, "Optimization": 20.075, "Emission": 147.532, "Total": 179.817}}, "Shared Hit Blocks": 5394, "Shared Read Blocks": 38399, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "WAL Records": 0, "WAL FPI": 0, "WAL Bytes": 0}, {"Worker Number": 1, "Actual Startup Time": 179.124, "Actual Total Time": 3900.698, "Actual Rows": 32096, "Actual Loops": 1, "JIT": {"Functions": 4, "Options": {"Inlining": false, "Optimization": false, "Expressions": true, "Deforming": true}, "Timing": {"Generation": 12.141, "Inlining": 0.0, "Optimization": 19.935, "Emission": 149.706, "Total": 181.782}}, "Shared Hit Blocks": 5358, "Shared Read Blocks": 38514, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "WAL Records": 0, "WAL FPI": 0, "WAL Bytes": 0}]}]}, "Settings": {"search_path": "\"$user\", public, tiger"}, "Planning": {"Shared Hit Blocks": 0, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0}, "Planning Time": 2.314, "Triggers": [], "JIT": {"Functions": 12, "Options": {"Inlining": false, "Optimization": false, "Expressions": true, "Deforming": true}, "Timing": {"Generation": 36.606, "Inlining": 0.0, "Optimization": 42.797, "Emission": 328.02, "Total": 407.423}}, "Execution Time": 4355.994}]

Boom, now you can select and copy query plan.

import ast
import json


def show_explain(qs):
    plan = qs.explain(
        format="json",
        analyze=True,
        buffers=True,
        verbose=True,
        settings=True,
        wal=True,
    )
    print(json.dumps(ast.literal_eval(plan)))

2. Analyze with pgMustard

pgMustard's "New plan" screen has a big text box to paste the query plan into:

After doing so and pushing "Review", you will see the visualization and recommendations:

For this example query, there are three recommendations, two of which have five stars, meaning a lot of potential for speeding up the query. Expanding the first one, it says:

5.0 ⭐️ Index Potential

100,002 out of 10,000,041 table rows are returned after filtering (1.0%)

Sequential scans are often slow when not all rows are used.

Adding an index may allow the planner to look up the rows required more efficiently. Consider adding an index, or investigating why the query planner considers any existing index(es) unhelpful.

The table being scanned is:

auth_user

The filter being used is:

((auth_user.email)::text ~~ '%@example.org'::text)

So here you may consider adding an index to the email field. Note that a vanilla index cannot help with this end-of-string matching. You could use a functional index to store the split-out email domain, and adjust the query accordingly to use the same function calls. (Specifically it would be Substr("email", StrIndex("email", Value("@"))).)

By default, pgMustard stores your analyzed query plans in your browser's local storage, keeping them private. Query plans can include sensitive data in table names, filters, etc. so it's nice that it defaults to keeping this secret. You also have the option to "publish" a plan, which stores it on their server and gives it a random public URL you can share with colleagues:

Here's the link for this plan: app.pgmustard.com/#/explore/aea44714-89ef-4de6-95b2-1da18d595ebc. Great stuff.

import json
from contextlib import contextmanager

from django.db import connection


class ExplainResult(Exception):
    pass


def explain_hook(execute, sql, params, many, context):
    sql = "EXPLAIN (ANALYZE, FORMAT JSON, BUFFERS, VERBOSE, SETTINGS, WAL) " + sql
    execute(sql, params, many, context)
    result = context["cursor"].fetchone()[0]
    raise ExplainResult(result)


@contextmanager
def explain():
    try:
        with connection.execute_wrapper(explain_hook):
            yield
    except ExplainResult as exc:
        print(json.dumps(exc.args[0]))

In [59]: with explain():
    ...:     User.objects.count()
[{"Plan": {"Node Type": "Aggregate", "Strategy": "Plain", "Partial Mode": "Finalize", "Parallel Aware": false, "Startup Cost": 191282.03, "Total Cost": 191282.04, "Plan Rows": 1, "Plan Width": 8, "Actual Startup Time": 4159.731, "Actual Total Time": 4178.435, "Actual Rows": 1, "Actual Loops": 1, "Output": ["count(*)"], "Shared Hit Blocks": 16145, "Shared Read Blocks": 120439, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "WAL Records": 0, "WAL FPI": 0, "WAL Bytes": 0, "Plans": [{"Node Type": "Gather", "Parent Relationship": "Outer", "Parallel Aware": false, "Startup Cost": 191281.81, "Total Cost": 191282.02, "Plan Rows": 2, "Plan Width": 8, "Actual Startup Time": 4156.926, "Actual Total Time": 4178.27, "Actual Rows": 3, "Actual Loops": 1, "Output": ["(PARTIAL count(*))"], "Workers Planned": 2, "Workers Launched": 2, "Single Copy": false, "Shared Hit Blocks": 16145, "Shared Read Blocks": 120439, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "WAL Records": 0, "WAL FPI": 0, "WAL Bytes": 0, "Plans": [{"Node Type": "Aggregate", "Strategy": "Plain", "Partial Mode": "Partial", "Parent Relationship": "Outer", "Parallel Aware": false, "Startup Cost": 190281.81, "Total Cost": 190281.82, "Plan Rows": 1, "Plan Width": 8, "Actual Startup Time": 3950.091, "Actual Total Time": 3950.119, "Actual Rows": 1, "Actual Loops": 3, "Output": ["PARTIAL count(*)"], "Shared Hit Blocks": 16145, "Shared Read Blocks": 120439, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "WAL Records": 0, "WAL FPI": 0, "WAL Bytes": 0, "Workers": [{"Worker Number": 0, "Actual Startup Time": 3846.571, "Actual Total Time": 3846.613, "Actual Rows": 1, "Actual Loops": 1, "JIT": {"Functions": 3, "Options": {"Inlining": false, "Optimization": false, "Expressions": true, "Deforming": true}, "Timing": {"Generation": 7.396, "Inlining": 0.0, "Optimization": 11.115, "Emission": 116.999, "Total": 135.51}}, "Shared Hit Blocks": 5405, "Shared Read Blocks": 38639, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "WAL Records": 0, "WAL FPI": 0, "WAL Bytes": 0}, {"Worker Number": 1, "Actual Startup Time": 3847.801, "Actual Total Time": 3847.84, "Actual Rows": 1, "Actual Loops": 1, "JIT": {"Functions": 3, "Options": {"Inlining": false, "Optimization": false, "Expressions": true, "Deforming": true}, "Timing": {"Generation": 7.155, "Inlining": 0.0, "Optimization": 10.949, "Emission": 118.094, "Total": 136.199}}, "Shared Hit Blocks": 5246, "Shared Read Blocks": 38227, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "WAL Records": 0, "WAL FPI": 0, "WAL Bytes": 0}], "Plans": [{"Node Type": "Seq Scan", "Parent Relationship": "Outer", "Parallel Aware": true, "Relation Name": "auth_user", "Schema": "public", "Alias": "auth_user", "Startup Cost": 0.0, "Total Cost": 179542.25, "Plan Rows": 4295825, "Plan Width": 0, "Actual Startup Time": 97.703, "Actual Total Time": 2546.515, "Actual Rows": 3333347, "Actual Loops": 3, "Shared Hit Blocks": 16145, "Shared Read Blocks": 120439, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "WAL Records": 0, "WAL FPI": 0, "WAL Bytes": 0, "Workers": [{"Worker Number": 0, "Actual Startup Time": 136.892, "Actual Total Time": 2509.312, "Actual Rows": 3223800, "Actual Loops": 1, "Shared Hit Blocks": 5405, "Shared Read Blocks": 38639, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "WAL Records": 0, "WAL FPI": 0, "WAL Bytes": 0}, {"Worker Number": 1, "Actual Startup Time": 138.042, "Actual Total Time": 2489.045, "Actual Rows": 3182375, "Actual Loops": 1, "Shared Hit Blocks": 5246, "Shared Read Blocks": 38227, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "WAL Records": 0, "WAL FPI": 0, "WAL Bytes": 0}]}]}]}]}, "Settings": {"search_path": "\"$user\", public, tiger"}, "Planning": {"Shared Hit Blocks": 0, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0}, "Planning Time": 4.296, "Triggers": [], "JIT": {"Functions": 11, "Options": {"Inlining": false, "Optimization": false, "Expressions": true, "Deforming": true}, "Timing": {"Generation": 24.741, "Inlining": 0.0, "Optimization": 23.089, "Emission": 251.59, "Total": 299.42}}, "Execution Time": 4190.409}]

Leveraging Python for secure medical image data transfer to the cloud - Ronald van 't Klooster (Quantib)

Usecase: they want to use python to connect a hospital to the cloud to upload radiology images securely. Imagery of patients, so it has to be really secure. And in their case also certified.

Radiology images are normally in the "DICOM" format. There's a special dicom protocol to transfer images, both for sending and getting images. A computer that implements the protocol is a "dicom node" and can be used by many applications.

The image format is mostly jpeg (actually a set of images: cross-sections of the brain, for instance) plus metadata.

They have an AI program to help diagnose images, with a microservices based architecture using docker+django. It used to run inside the hospital on their network. They wanted to move the AI server to the cloud. But how to get that working while still retaining their certification? This means that the actual certified AI server program cannot be changed.

The solution was to use an open source DIOCM node software called Orthanc. With a nice REST interface, so usable over https. They added one instance inside the network and one in the cloud, next to the AI server.

Dicom node can send/recieve to/from another dicom node, but how to manage it? This is where they used python. A daemon on both sides that monitores the REST api and the local orthanc databases and triggers the necessary "gets" and "sends". Also the sending/getting from the orthanc node to the actual node where the orthanc is placed next to.

Python helped them a lot. Requests, threading, abstract base classes, logging... everything is there. And with https and client certificates, the security is good. The node on the hospital side only does get and send, so nothing needs to be send to the hospital, as requesting an open port would be very hard :-)

Deployment is with docker in the cloud. In the hospitals, requesting a windows machine is easiest in practice, so they packaged the small orthanc node up as an executable that can be run as a service.

Developing Python apps on Kubernetes - Marcel Koek (Erasmus MC)

They slowly learned how to get a python app to run fine in kubernetes, including getting your local development environment to match the server environment as much as possible. And... making it as comfortable to work with as possible.

He demoed a small app build with fastapi as an example.

When using docker, there's an extra layer between you and your code. If you change code, you need to rebuild your docker image. Or you need to mount the dir with your code into your running docker. How to manage that? And you also want to make it resemble your production environment.

One step closer to production is to use kubernetes locally. He uses k3d to run a local kubernetes cluster: it runs kubernetes in your local docker daemon! Easy to run. It also includes a local docker registry, which you can push your image to.

For actually deploying the docker, he uses "helm", kind of a package manager for docker.

What really helps getting development working nice with kubernetes: tilt. Smart rebuilds, live updates, automatic docker rebuilding. His demo "tiltfile" even included an automatic pip install -r requirements if the requirements change.

Note: a tiltfile looks like a python file, but it is a "go" configuration file format...

Build and scale containerized applications on AWS - James Meakin (Radboud UMC)

He's working on https://grand-challenge.org/ , an open source project for AI and medical imaging. You can submit imagery ("a challenge") that can then be evaluated with various algorithms. You can test your algorithms this way.

Techs they use: django, celery, vuejs, htmlx, pydicom and many more. And it is containers everywhere with a multi-region deployment on AWS for really low-latency image access from the browser.

The platform is extensible with container images running, for instance, specific algorithms. That's a security challenge, as they're written by lots of people. And you've got to manage the allowable resources.

In AWs there are lots of ways to run your containers. The three most used ones:

• AWS app runner. Cheap and easy. Good if your apps have more than 25% idle time.
• Amazon kubernetes, EKS. For his taste, it is way too complex. "Yaml hell".
• Amazon elastic container service, ECS. It is AWS' opinionated way to run containers at scale. You have much less to understand: it mostly looks and handles like docker-compose.

You can use AWS fargate to run tasks on either EKS or ECS. Their software allows them to use "spot instances", instances that are cheap because they might get killed.

They use quite some AWS services. Simple queue service, eventbridge, elastic file storage. So they bought into the AWS ecosystem. But, as the actual software is all open source, they can move to other providers with some effort.

Simulating 6-axis industrial robots with Python - Thijs Damsma (CEAD)

He now works for a firm that builds 3d printing robots. 3d stuff that's up to 40 meter long :-) He's trying to use python to manage/steer them.

His current experiments are with https://pypi.org/project/roboticstoolbox-python/, which you can use comfortably in a jupyter notebook, including a 3d visualisation. He showed a nice demo.