SQLite ou PostgreSQL ? Le choix est compliqué !

SQLite ou PostgreSQL ? Le choix est compliqué !

Chez Twilio, nous prenons les blogs très au sérieux. Pour nous aider à comprendre quel contenu fonctionne bien et lequel ne fonctionne pas sur notre blog, nous avons un tableau de bord qui combine les métadonnées que nous conservons pour chaque article, comme l'auteur, l'équipe, le produit, la date de publication, etc., avec des informations sur le trafic provenant de Google Analytics. Les utilisateurs peuvent demander de manière interactive des graphiques et des tableaux tout en filtrant et en regroupant les données de différentes manières.

J'ai choisi SQLite pour la base de données qui prend en charge ce tableau de bord. Au début de l'année 2021, lorsque j'ai créé ce système, cela me semblait le choix idéal pour ce que je pensais être une petite application de niche que mes collègues et moi-même pourrions utiliser afin d'améliorer nos blogs. Mais près d'un an et demi plus tard, cette application suit le trafic quotidien de près de 8000 articles sur les blogs de Twilio et de SendGrid, avec environ 6,5 millions d'enregistrements de trafic quotidien individuels, et une base d'utilisateurs qui compte désormais plus de 200 employés.

À un moment donné, je me suis rendu compte que certaines requêtes prenaient quelques secondes pour produire des résultats. J'ai donc commencé à me demander si une base de données plus robuste telle que PostgreSQL fournirait de meilleures performances. Ayant publiquement confessé mon aversion pour les tests de performance, j'ai résisté à l'envie de rechercher des comparaisons en ligne et décidé d'entreprendre moi-même une série d'expériences afin de mesurer avec précision les performances de ces deux bases de données pour les cas d'usage spécifiques de cette application. Ce qui suit est un compte rendu détaillé de mes efforts, des résultats de mes tests (y compris un revirement surprenant !), de mon analyse et de ma décision finale, laquelle s'est avérée plus complexe que prévu.

Portage de l'application vers PostgreSQL

La première mesure que j'ai prise a été de m'assurer que je pouvais facilement reconfigurer l'application pour utiliser SQLite ou PostgreSQL et effectuer ainsi des comparaisons côte à côte. J'ai donc installé PostgreSQL sur mon ordinateur portable Mac pour que cela fonctionne en développement.

L'adaptation du code pour qu'il soit compatible avec PostgreSQL a été relativement facile à réaliser, car cette application utilise SQLAlchemy, une librairie ORM pour Python qui permet au développeur de créer des schémas et des requêtes en utilisant principalement des constructions Python indépendantes de la base de données. En rendant l'URL de connexion à la base de données configurable, j'ai pu basculer entre les URL postgresql:// et sqlite:// . Il n'a pas été nécessaire de modifier le code Python, car les différences entre ces bases de données ont été gérées en interne par SQLAlchemy. SQLAlchemy a même créé toutes les tables et tous les index sur ma nouvelle base de données PostgreSQL, exactement comme dans SQLite.

Pour permettre à l'application de fonctionner pleinement avec PostgreSQL, j'ai également dû migrer les données actuellement stockées dans la base de données SQLite. J'ai utilisé pgloader, un script open source assez complet qui importe les données de plusieurs sources dans une base de données PostgreSQL. Heureusement, SQLite est l'une des sources de données prises en charge.

Le moyen le plus simple d'importer des données avec pgloader est de créer un fichier de configuration qui définit la source, la destination et les options. Voici celui que j'ai créé :

La seule option que j'ai utilisée est data only, qui indique à pgloader de ne pas créer de tables ou d'index, uniquement de déplacer les données de la base de données source vers la base de données de destination. C'est important, car je voulais que le schéma de la base de données soit le plus proche possible de celui de SQLite One, et la meilleure façon de le faire était de laisser SQLAlchemy créer toutes les tables et tous les index.

Pour effectuer la migration des données, pgloader est appelé avec le nom du fichier de configuration comme seul argument :

Après quelques essais, le processus d'importation a fonctionné sans problème. Les premières tentatives infructueuses m'ont alerté sur quelques problèmes potentiellement graves que j'ai rencontrés lors de la conception de ma base de données, que SQLite a décidé de ne pas signaler ou de ne pas prendre en compte :

• Pour les slugs des articles, j'ai défini une colonne VARCHAR de 256 caractères. La migration des données a échoué pour quatre articles dont la longueur des slugs dépassait la longueur maximale. SQLite, avec son système de typage inhabituel, ne s'en est pas soucié et a stocké les slugs plus longs sans rechigner. En revanche, PostgreSQL a appliqué strictement ses types de colonne, si bien que ces quatre publications n'ont pas pu être migrées. La solution a été d'augmenter la colonne slug à 512 caractères et de réessayer.
• Toujours concernant le typage, l'une de mes tables disposait initialement de clés primaires entières, mais à un moment donné, j'ai décidé de remplacer ces clés par des chaînes UUID. Lorsque j'ai effectué cette modification dans SQLite, j'ai migré la table manuellement, mais je ne me suis pas rendu compte que j'avais laissé la colonne de clés primaires de cette table définie sur integer. Une fois de plus, SQLite a stocké mes UUID dans cette colonne et les a même indexés. Mais pour les transférer à PostgreSQL, j'ai dû corriger le schéma et déclarer correctement ces clés en tant que chaînes.

À ce stade, de façon relativement simple, je pouvais passer d'une base de données SQLite à une base de données PostgreSQL équivalente en modifiant l'URL de la base de données dans un fichier de configuration et en redémarrant le serveur Python.

J'ai testé manuellement certaines requêtes sur mon ordinateur portable de développement et j'ai eu l'impression que tout était plus rapide avec la base de données PostgreSQL, ce qui a été un premier constat encourageant.

Création d'un test de performances

Ma prochaine tâche a été de créer un test automatisable et reproductible afin de pouvoir tester le système sous les deux bases de données et de déterminer laquelle était plus performante. Simple, non ?

Malheureusement, il est très difficile de concevoir un test couvrant toutes les façons dont ce système peut être utilisé en raison du grand nombre de possibilités, car je l'ai conçu pour permettre à l'utilisateur de créer un grand nombre de requêtes différentes. Il permet par exemple à l'utilisateur d'obtenir des statistiques de trafic sur une période pouvant couvrir des jours, des semaines, voire des années, et de classer les résultats par article, auteur, équipe, produit, langage informatique, langue écrite ou année de publication. L'utilisateur peut également répartir les résultats du trafic par pays d'origine. Lors de la récupération des articles, l'utilisateur peut choisir de voir toutes les traductions d'un article regroupées avec l'original. Pour toutes les requêtes, l'utilisateur peut filtrer une section de l'ensemble de données selon n'importe quelle combinaison d'auteurs, d'équipes, de produits, de langages informatiques, de langues écrites ou de dates de publication. Et tout cela peut être fait pour les blogs Twilio ou SendGrid, ou pour les deux blogs combinés.

Pour éviter de m'aventurer dans une voie inconnue, j'ai décidé de me concentrer sur les cas d'usage les plus courants. J'ai donc créé une liste de requêtes API les représentant. Voici la liste que j'ai établie :

Ces URL doivent ensuite être complétées par des arguments de chaîne de requête supplémentaires. Les arguments start et end sont requis dans toutes les requêtes, car ils spécifient la période de temps demandée. J'ai constaté que cela avait une grande influence sur les temps de réponse, c'est pourquoi j'ai essayé différentes durées.

Il existe un grand nombre d'arguments de chaîne de requêtes qui peuvent être utilisés pour implémenter des filtres. L'argument blog que j'ai inclus dans les URL ci-dessus sélectionne le blog Twilio, qui est celui ayant le plus de trafic. J'ai décidé de ne pas compliquer mes tests avec d'autres filtres, car en examinant les journaux d'utilisation, j'ai remarqué que la plupart des requêtes des utilisateurs n'en comportaient aucun.

Avec cette liste, j'ai créé une courte fonction Python qui exécute ces requêtes et enregistre la durée de chacune d'entre elles dans un dictionnaire results :

La fonction test() effectue une copie de la liste requests, la réorganise de manière aléatoire, puis exécute la demande avec curl en tant que sous-processus. Elle utilise la fonction timeit() de la librairie standard Python pour mesurer le temps nécessaire à chaque requête pour renvoyer une réponse, puis ajoute cette mesure au dictionnaire results, sous l'URL correspondante.

La raison pour laquelle je randomise la liste est que j'ai l'intention d'exécuter plusieurs instances de cette fonction en parallèle pour simuler des clients simultanés. Il est pratique que la fonction passe en revue les requêtes dans un ordre aléatoire, car cela garantit que la base de données dispose d'une variété de requêtes à tout moment, au lieu de recevoir plusieurs instances de la même requête.

La fonction reçoit la partie domaine de l'URL en tant qu'argument server. Je voulais que cela soit facile à configurer, afin de pouvoir passer de mon système de développement à mon système de production. Une clé API est également transmise à cette fonction afin que les demandes puissent être authentifiées. Enfin, les arguments start et end requis de la chaîne de requêtes sont transmis et ajoutés à l'URL avant d'effectuer la demande.

Le dictionnaire results utilise les URL de la liste requests en tant que clés. Pour chaque clé, je stocke un tableau de résultats et non une seule valeur, car cela me permet d'enregistrer plusieurs exécutions pour chaque requête et d'obtenir une moyenne.

Pour terminer mon script de test, j'ai ajouté une fonction run_test() et un analyseur de ligne de commande :

La fonction run_test() lance un ou plusieurs threads en fonction de l'argument num_threads. Tous les threads sont configurés pour exécuter la fonction test() en parallèle. Cela simule la charge provenant de plusieurs clients simultanés, chacun parcourant la liste des URL dans son propre ordre aléatoire, afin de créer une certaine imprévisibilité.

Une fois tous les threads terminés, run_test() indique le temps de demande moyen pour chaque URL de demande, ainsi qu'une moyenne de toutes les requêtes combinées, qui est l'indicateur que j'ai décidé d'utiliser pour mon analyse.

Les arguments de la ligne de commande me permettent de transmettre la racine du serveur, la clé API, les dates de début et de fin de la requête et la simultanéité. Grâce à ces commandes, je peux tester différents scénarios.

Le script de test est maintenant prêt, il est donc temps d'obtenir des indicateurs !

Test sur le système de développement

Le système de développement sur lequel je travaille est un ordinateur portable Mac avec 6 cœurs hyperthreads et 16 Go de RAM. L'environnement de production de ce tableau de bord est un serveur virtuel Linode avec 1 vCPU et 2 Go de RAM.

Grâce à mes expériences passées en matière de benchmarking, je sais que les résultats d'un système rapide ne sont pas toujours les mêmes que ceux d'un système plus lent. Mon objectif final est donc de tester le système de production et de prendre des décisions en fonction des résultats obtenus sur cette plateforme.

Mais avant cela, j'ai voulu effectuer une première série de tests pratiques sur mon ordinateur portable pour m'assurer que le script de test fonctionnait correctement, mais aussi parce que j'étais curieux de voir comment ces deux bases de données se comportaient sur une plateforme assez puissante.

La méthodologie de test que j'ai décidé d'utiliser est la suivante : tester le système fonctionnant sous les deux bases de données, pour des requêtes avec des périodes d'une semaine, d'un mois, d'un trimestre et d'un an, toutes les requêtes ayant le 01/01/2021 comme date de début ; répéter les tests avec 1, 2 et 4 clients simultanés ; pour chaque test individuel, exécuter le script trois fois et enregistrer le meilleur des trois ; et utiliser la moyenne totale de toutes les requêtes comme indicateur.

Avec ce plan, j'ai atteint 24 points de données (2 bases de données x 4 périodes de requête x 3 niveaux de simultanéité). Le graphique suivant indique le temps de réponse pour PostgreSQL (bleu) et SQLite (rouge), avec un seul client.

Il apparaît clairement que PostgreSQL renvoie les résultats plus rapidement et qu'il est particulièrement efficace pour les requêtes plus courtes. Les temps de réponse de PostgreSQL augmentent de manière plus ou moins linéaire au fur et à mesure que la période de requête s'allonge, mais il est intéressant de noter que le temps de réponse de SQLite est resté pratiquement stable lors des trois premiers tests, avant de s'effondrer lors du dernier test.

Vous trouverez ci-dessous les graphiques pour le même test avec 2 et 4 clients simultanés.

J'ai trouvé intéressant le fait que la modification de l'accès simultané ne semble pas avoir un grand impact sur les temps de réponse pour la plupart des tests, probablement en raison des multiples cœurs que j'ai sur cet ordinateur portable. Le seul cas où l'on constate une différence concerne les requêtes annuelles, où PostgreSQL semble souffrir davantage que SQLite.

Pour vous aider à visualiser cela, vous trouverez ci-dessous un graphique qui montre les temps de réponse des tests PostgreSQL, en comparant les trois niveaux de simultanéité pour chacune des périodes de requête.

Cela confirme que pour les requêtes impliquant un nombre petit ou moyen de lignes, une simultanéité de quatre clients maximum n'affecte pas les temps de réponse de manière significative. En revanche, lorsque le nombre de lignes impliquées augmente, une simultanéité plus élevée commence à ralentir les choses.

Voici le même graphique pour SQLite :

C'est en fait une surprise, car cela montre que SQLite est bien plus apte à gérer des charges simultanées que PostgreSQL sur ce système. Bien sûr, les temps de réponse sont plus longs, mais le fait que la simultanéité ait moins d'impact sur ces temps de réponse est très intéressant.

La plateforme de production donnera-t-elle des résultats similaires ? Je pense qu'un seul processeur, par rapport aux 6 de mon ordinateur portable, fera de la simultanéité un facteur beaucoup plus important dans les résultats.

Test sur le système de production

Armé d'un script de benchmarking désormais bien testé, j'ai démarré un nouveau Linode avec un clone du système de production, j'ai répété toutes les étapes pour migrer la base de données SQLite vers PostgreSQL, et je me suis assuré de pouvoir, une fois de plus, changer facilement de base de données en modifiant le fichier de configuration et en redémarrant le service.

Pour ce test, j'ai décidé d'exécuter le script de test sur mon propre ordinateur portable, de façon à ce que toutes les latences impliquées dans le trafic transitant sur Internet, le chiffrement et le proxy soient incluses dans les résultats. J'aurais pu choisir d'exécuter les tests sur le même hôte que le service et éviter ces coûts supplémentaires, mais au final, mon objectif est d'améliorer les performances du service pour les utilisateurs, et ces derniers n'ont pas d'autre choix que de passer par toutes ces couches sans lien apparent.

Voici le graphique que j'ai obtenu en utilisant une simultanéité de un :

C'est un revirement totalement inattendu. Les lignes se croisent !

PostgreSQL surpasse SQLite sur les requêtes courtes et moyennes, mais rencontre beaucoup de problèmes avec la requête annuelle, qui prend presque deux fois plus de temps qu'avec SQLite. Au cas où vous penseriez que c'est un hasard, sachez que j'ai répété ces tests plusieurs fois et je vous confirme que ces résultats sont corrects.

Voici les graphiques pour 2 et 4 clients sur ce serveur de production.

Bien que les formes des courbes soient quasiment identiques à tous les niveaux de simultanéité, il suffit de regarder l'échelle des axes Y pour voir que sur cette plateforme, les temps de réponse sont beaucoup plus affectés par la simultanéité que sur mon ordinateur portable. Pour la requête annuelle avec quatre clients simultanés, les temps de réponse étaient extrêmement lents, environ 14 secondes pour PostgreSQL et 8 secondes pour SQLite.

PostgreSQL et SQLite sont-ils affectés de la même façon par la simultanéité ? Examinons-les séparément pour le savoir. Le graphique suivant montre les performances de PostgreSQL sur les quatre périodes avec 1, 2 et 4 clients simultanés.

Cela montre que pour cette base de données, les choses deviennent incontrôlables pour les requêtes volumineuses, mais que pour les requêtes de petite et moyenne taille, les temps de réponse sont relativement stables.

Vous trouverez ci-dessous le même graphique pour SQLite :

Rien à voir, n'est-ce pas ? Avec un seul processeur, la simultanéité joue un rôle dans SQLite, y compris pour les requêtes de petite et moyenne taille. Les temps de réponse augmentent de manière non linéaire, mais ils augmentent toujours, même sur les tests les plus petits.

Pour mieux visualiser ces différences, j'ai créé une autre série de graphiques. Pour chaque période de requête que j'ai testée, les graphiques suivants comparent les deux bases de données à chaque point de simultanéité.

Ces graphiques montrent clairement que PostgreSQL peut gérer les petites et moyennes requêtes bien mieux que SQLite, au point que l'augmentation de la simultanéité a un très faible impact sur les temps de réponse, ce qui est excellent. SQLite est affecté plus directement par la simultanéité pour toutes les tailles de requêtes, et bien qu'il soit moins performant pour les requêtes de petite et moyenne taille, il est nettement supérieur à PostgreSQL pour les requêtes plus longues.

Ajustement pour des performances optimales

Jusqu'à présent, je ne me suis pas préoccupé de la configuration des bases de données, j'ai simplement supposé que la configuration par défaut serait suffisante. Dans le cas de SQLite , il n'y a pas grand-chose à configurer, mais PostgreSQL dispose de quelques leviers de réglage.

Après avoir parcouru les options de configuration liées à l'utilisation de la mémoire, j'ai trouvé l'option work_mem qui configure la quantité de mémoire que chaque requête peut utiliser avant de commencer à stocker des données dans des fichiers de disque temporaires. La valeur par défaut de cette option est 4 Mo. Je l'ai remplacée par 16 Mo :

Les tests de la semaine, du mois et du trimestre ont eu des temps de réponse similaires à ceux des tests précédents. Mais ce changement a permis d'améliorer les requêtes plus longues. Vous trouverez ci-dessous les graphiques pour 1, 2 et 4 clients après avoir refait les tests PostgreSQL pour une année :

Avec cette modification, les lignes se sont décroisées et PostgreSQL a commencé à retourner plus rapidement que SQLite, même pour les requêtes les plus longues. Pour le test le plus lourd, avec des requêtes annuelles et quatre clients, les temps de réponse sont passés de 14 à 6 secondes avec le serveur de base de données à mémoire optimisée. Ces requêtes sont toujours plus lentes que je ne le voudrais, mais comme j'utilise une plateforme d'entrée de gamme, je pense qu'elles sont raisonnables.

Conclusion

Comme vous vous en doutez certainement, au vu des résultats obtenus, j'ai décidé de passer à PostgreSQL. Après avoir mis à niveau le système de production, j'ai interrogé de manière informelle quelques utilisateurs sur les performances et tous ont estimé que le système répondait beaucoup plus rapidement qu'auparavant. Ma mission est donc accomplie.

Si vous ne devez retenir qu'une chose de cet article, j'espère que vous retiendrez que les seuls critères de référence valables sont ceux qui fonctionnent sur votre propre plateforme, avec votre propre pile, avec vos propres données et avec votre propre logiciel. Et même dans ce cas, il se peut que vous deviez ajouter des optimisations personnalisées pour obtenir les meilleures performances.

Pour améliorer encore les performances, cet exercice de test me laisse deux pistes :

• Si j'obtiens le budget, je sais maintenant que l'ajout de processeurs pourrait améliorer les tests plus longs. Le passage à un serveur dédié doté de 4 processeurs devrait faire une différence considérable et rapprocher les temps de réponse de ceux que j'ai mesurés sur mon ordinateur portable.
• Sans injecter d'argent supplémentaire, le principal domaine d'amélioration concerne les longues requêtes, qui pourraient être simplifiées si la base de données stockait des totaux mensuels précalculés en plus des données quotidiennes. Une année de trafic pourrait alors être récupérée à partir de 12 lignes par article au lieu de 365. Une partie des économies sera compensée par une logique plus complexe lorsque les requêtes ne sont pas alignées sur les limites mensuelles, mais globalement, cela devrait réduire les temps de requête plus longs.

J'espère que vous avez trouvé de nouvelles idées dans cet article. Je vous encourage à comparer et à optimiser vos propres projets !

Miguel Grinberg est ingénieur informatique principal pour le contenu technique chez Twilio. Contactez-le à l'adresse mgrinberg [at] twilio [dot] com si vous avez un projet sympa que vous souhaitez partager sur ce blog !