Pour effectuer des traitements par lots on a parfois besoin d'effectuer des requêtes SQL qui renvoient un grand nombre d'enregistrements. Malheureusement dans ces cas là, si on effectue une requête classique, l'utilisation de la mémoire par le programme appelant peut devenir trop importante. Voici à quoi pourrait ressembler un code de ce type en Python :

cursor = connection.cursor()
cursor.execute("SELECT * FROM table")
for record in cursor:
    do_something_with(record)

Appliqué à une table contenant quelques dizaines de milliers d'enregistrements, ce code a une consommation mémoire très importante :

PID  USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+   COMMAND
1630 alex      20   0 3328m 2.9g  11m S 38.1 37.6    0:10.61 python

L'explication nous en est donnée dans la doc de psycopg2 :

Lorsque une requête de base de données est exécutée, le `cursor` Psycopg récupère habituellement tous les enregistrements renvoyés par le serveur et les transfère au processus client. Si la requête renvoie une énorme quantité de données, une quantité de mémoire de taille proportionnelle est allouée par le client.

Au lieu de charger toutes les données en mémoire d'un seul coup, il serait préférable de les consommer au fur et à mesure, sous forme de flux. C'est la raison d'être des curseurs PostgreSQL :

pgsql=# BEGIN;
BEGIN
pgsql=# DECLARE mon_curseur CURSOR FOR SELECT uid FROM ma_table;
DECLARE CURSOR
pgsql=# FETCH 10 mon_curseur;
                 uid              
----------------------------------
 000061788a874398821c8625e0fd24fb
 00007e1c94ca43f28dd50165a739d65c
 00031b13609d44db94b666b13e85baea
 0004a1f90bf94a12bd028a40b102c8f3
 0006623267e345e9af791cb8c0ec9021
 00067c3g6197413db58bc9736838515f
 0007bf39966f4d53baf149016bfdd74c
 0008a1b01aca4ab18a197d09e2f2ecae
 0008c1c8077a40759f18ea3d99d5ddbd
 00095c7cbef04a0baca9cedaa69d15fb
(10 rows)

pgsql=# END;
COMMIT

La commande DECLARE permet de déclarer un curseur en lui donnant un nom. On peut ensuite utiliser FETCH pour récupérer un nombre donné d'enregistrements.

Psycopg facilite grandement cette procédure, il suffit en effet de passer un nom de curseur en argument de la fonction cursor pour que le curseur côté serveur soit automatiquement créé. Le code Python devient donc:

cursor = connection.cursor(name="mon_curseur")
cursor.execute("SELECT * FROM table")
for record in cursor:
    do_something_with(record)

Et la consommation de mémoire redescend à un niveau raisonnable :

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
727 alex      20   0  543m 173m  11m S 44.5  2.2   0:08.14 python

Voilà comment on peut très simplement optimiser l'utilisation mémoire de nos programmes effectuant des requêtes renvoyant beaucoup de données.

Si cet article vous a intéressé, vous serez peut-être curieux de découvrir notre formation base de données PostgreSQL / PostGIS.