Quelques détails après l'interview

Par un heureux hasard, je me suis retrouvé à être interviewé par l'auteur du livre Mastering PostgreSQL in Application Development. Comme l'exercice de l'interview ne se prête pas complètement aux détails techniques, j'en profite pour compléter ici un peu ce que nous avons changés sur nos django.

Premier exemple assez trivial, nous avions du code qui vérifiait si un objet n'avait pas été vu depuis un certain temps. L'approche précédente était de faire cette vérification en python :

    def is_seen(self, now=None):
        if self.last_seen is None:
            return False
        
        if now is None:
            now = timezone.now()
        return now - self.last_seen < LOST_DELTA

La micro-optimisation sur la variable now n'est pas très importante, sauf quand cette méthode est appelée sur des milliers d'objets différents.

Ce n'est pas très spectaculaire, mais nous avons ajouté une simple méthode sur le manager de l'objet :

    def add_connected(self):
        return self.annotate(connected=Case(When(last_seen__gt=(timezone.now() - LOST_DELTA),
                                                 then=Value(True)),
                                            default=Value(False),
                                            output_field=BooleanField()))

On a ainsi directement un attribut booléen sur tous les objets quand nous en avons besoin.

Second exemple, nous avons dans notre modèle de base de données des lignes ADSL. Ces lignes ADSL sont liées à des sites (un site pouvant avoir N lignes). Nous souhaitons savoir très rapidement si un site a une ligne ADSL ou pas. Le code existant utilisait la solution standard en Django en utilisant un JOIN avec un DISTINCT :

Site.objects.filter(line__isnull=False).order_by("pk").distinct("pk")

Cela générait ce genre de requête SQL :

SELECT DISTINCT ON ("appli_site"."id") "appli_site"."id", "appli_site"."name"
FROM "appli_site" INNER JOIN "appli_line" ON ("appli_site"."id" = "appli_line"."site_id")
WHERE "appli_line"."id" IS NOT NULL ORDER BY "appli_site"."id"

Ce n'est malheureusement pas très efficace. Il y a d'ailleurs un bug ouvert à ce sujet sur le projet Django.

Nous l'avons remplacé par un EXIST, environ quatre fois plus efficace sur notre application :

SELECT "appli_site"."id", "appli_site"."name" FROM "appli_site"
WHERE (EXISTS (SELECT 1 FROM "appli_line" WHERE "appli_line"."site_id" = "appli_site"."id"

En attendant que la fonctionnalité existe chez Django, nous avons également rajouté une méthode sur le manager de l'objet :

def has_line(self): 
    return self.extra(where=['EXISTS (SELECT 1 FROM "appli_line" WHERE "appli_line"."site_id" = "  appli_site"."id")'])

Dernier exemple, nous voulions compter les points d'accès WiFi joignables et non joignables, par site. Tout comme les lignes, les objets points d'accès sont liés aux sites par un ForeignKey.

Le code précédent itérait sur l'ensemble des points d'accès, et s'occupait du comptage en python dans un dictionnaire (le code est simplifié, notamment le nom des variables est réduit pour simplifier la lecture) :

for site_id, is_reachable in AP.objects.values_list('site', 'is_reachable'):
    key = 'ok' if is_reachable else 'down'
    counters[site_id][key] += 1

Quand le nombre d'objets AP est très important, cela génère beaucoup de lignes de résultats à transférer (et encore, on ne génère pas d'objets python en utilisant values_list). Une meilleure solution tout en SQL :

SELECT "appli_site"."id",
       (COUNT("appli_ap"."mac") FILTER (WHERE is_reachable = true)) AS "ap_oks",
       (COUNT("appli_ap"."mac") FILTER (WHERE is_reachable = false)) AS "ap_downs"
FROM "appli_site" LEFT OUTER JOIN "appli_ap" ON ("appli_site"."id" = "appli_ap"."site_id")
GROUP BY "appli_site"."id"

Quand le nombre de site est faible par rapport au nombre de points d'accès (ce qui est très vrai dans notre cas), cette requête va beaucoup plus vite que l'évaluation en python. À noter que cette requête est possible entièrement en Django à partir de la version 2.0, grâce au niveau paramètre filter ajouté aux aggrégations.

Ce ne sont pas les seules optimisations, mais avec d'autres petits changements (un jour de développement), on a réussi à arriver au résultat cité dans l'interview, à savoir une diminution par cinq des données transférées entre la base de données et l'application Django.