PostgreSQL vs MariaDB pour les applications CRUD transactionnelles

Quand une application CRUD dépasse le stade du prototype

Une application CRUD prototype paraît généralement rapide parce que les données sont peu volumineuses, l'équipe est petite et le trafic prévisible. On peut s'en sortir avec des requêtes simples, quelques index et des ajustements manuels du schéma. Puis l'application a de vrais utilisateurs, de vrais workflows et de vraies échéances.

La croissance change la charge. Les listes et tableaux de bord s'ouvrent toute la journée. Plusieurs personnes modifient les mêmes enregistrements. Des jobs en arrière-plan commencent à écrire par lots. C'est là que « ça marchait hier » se transforme en pages lentes, timeouts aléatoires et attentes de verrous aux heures de pointe.

Vous avez probablement franchi la ligne si vous voyez des pages de liste qui ralentissent après la page 20, des releases qui incluent des backfills (pas seulement de nouvelles colonnes), plus de « champs flexibles » pour des métadonnées ou des payloads d'intégration, ou des tickets de support qui indiquent « l'enregistrement prend une éternité » pendant les périodes chargées.

C'est à ce moment que comparer PostgreSQL et MariaDB cesse d'être une préférence de marque et devient une question pragmatique. Pour les charges CRUD transactionnelles, les détails qui décident généralement sont les options d'indexation à mesure que les requêtes se complexifient, la sécurité des migrations quand les tables sont grandes, le stockage et l'interrogation JSON, et les fonctionnalités de requête qui réduisent le travail côté application.

Cet article reste centré sur ces comportements de base de données. Il n'entre pas profondément dans le dimensionnement des serveurs, la tarification cloud ou les contrats fournisseurs. Ces sujets comptent, mais ils sont souvent plus faciles à changer plus tard qu'un schéma et un style de requêtes dont dépend votre produit.

Commencez par les besoins de votre application, pas par la marque de la base

Le meilleur point de départ n'est pas « PostgreSQL vs MariaDB ». C'est le comportement au quotidien de votre application : créer des enregistrements, mettre à jour quelques champs, lister des résultats filtrés et rester correct quand beaucoup de personnes cliquent en même temps.

Notez ce que font vos écrans les plus chargés. Combien de lectures par écriture ? Quand surviennent les pics (connexions du matin, clôtures de fin de mois, gros imports) ? Capturez les filtres et tris exacts dont vous dépendez, car ce sont eux qui orienteront plus tard la conception des index et les patterns de requêtes.

Puis définissez vos non-négociables. Pour beaucoup d'équipes, cela signifie une cohérence stricte pour l'argent ou les stocks, une piste d'audit pour « qui a changé quoi », et des requêtes de reporting qui ne s'effondrent pas dès que le schéma évolue.

La réalité opérationnelle compte autant que les fonctionnalités. Décidez si vous utiliserez une base gérée ou auto-hébergée, à quelle vitesse vous devez restaurer des sauvegardes et quelle est votre tolérance aux fenêtres de maintenance.

Enfin, définissez « assez rapide » avec quelques cibles claires. Par exemple : latence API p95 en charge normale (200 à 400 ms), p95 en concurrence de pointe (peut-être 2x la normale), attente maximale acceptable sur verrous lors des mises à jour (moins de 100 ms), et limites de temps pour sauvegarde et restauration.

Principes d'indexation qui déterminent la vitesse CRUD

La plupart des apps CRUD semblent rapides jusqu'à ce que les tables atteignent des millions de lignes et que chaque écran devienne « une liste filtrée avec tri ». À ce stade, l'indexation fait la différence entre une requête à 50 ms et un timeout à 5 s.

Les index B-tree sont l'outil par défaut dans PostgreSQL comme dans MariaDB. Ils aident quand vous filtrez sur une colonne, joignez des clés ou que votre ORDER BY correspond à l'ordre de l'index. La vraie différence de performance vient souvent de la sélectivité (combien de lignes correspondent) et de la capacité de l'index à satisfaire à la fois le filtrage et le tri sans parcourir des lignes supplémentaires.

À mesure que l'application mûrit, les index composites comptent plus que ceux sur une seule colonne. Un pattern courant est le filtrage multi-tenant plus un statut plus un tri temporel, par exemple (tenant_id, status, created_at). Mettez en premier le filtre le plus constant (souvent tenant_id), puis le filtre suivant, puis la colonne de tri. Cela bat souvent des index séparés que l'optimiseur ne peut pas combiner efficacement.

Les différences apparaissent avec des index « plus intelligents ». PostgreSQL prend en charge les index partiels et les index sur expressions, utiles pour des écrans ciblés (par exemple indexer seulement les tickets « ouverts »). Ils sont puissants, mais peuvent surprendre si les requêtes ne correspondent pas exactement au prédicat.

Les index ne sont pas gratuits. Chaque insert et update doit aussi mettre à jour chaque index, il est donc facile d'améliorer un écran et de ralentir silencieusement chaque écriture.

Une façon simple de rester discipliné :

• Ajoutez un index seulement pour un vrai chemin de requête (un écran ou un appel API que vous pouvez nommer).
• Préférez un bon index composite plutôt que plusieurs qui se chevauchent.
• Révisez les index après les changements de fonctionnalité et supprimez le poids mort.
• Prévoyez de la maintenance : PostgreSQL a besoin de vacuum/analyze réguliers pour éviter le bloat ; MariaDB dépend aussi de bonnes statistiques et d'un nettoyage occasionnel.
• Mesurez avant et après, plutôt que de faire confiance à l'intuition.

Indexation pour les écrans réels : listes, recherche et pagination

La plupart des apps CRUD passent leur temps sur quelques écrans : une liste avec filtres, une boîte de recherche et une page de détail. Le choix de base de données compte moins que la concordance des index avec ces écrans, mais les deux moteurs offrent des outils différents quand les tables grossissent.

Pour les pages de liste, pensez dans cet ordre : filtrer d'abord, trier ensuite, paginer enfin. Un pattern courant est « tous les tickets pour le compte X, status dans (open, pending), les plus récents d'abord ». Un index composite commençant par les colonnes de filtrage et se terminant par la colonne de tri gagne généralement.

La pagination mérite une attention particulière. La pagination par offset (page 20 avec OFFSET 380) ralentit à mesure que vous descendez car la base doit toujours parcourir les lignes précédentes. La pagination par keyset est plus stable : on passe la dernière valeur vue (comme created_at et id) et on demande « 20 suivants plus anciens que ça ». Elle réduit aussi les doublons et les trous quand de nouvelles lignes arrivent pendant le défilement.

PostgreSQL propose une option utile pour les écrans de liste : les index « covering » avec INCLUDE, qui permettent des index-only scans quand la visibility map le permet. MariaDB peut aussi faire des lectures couvrantes, mais vous y arrivez généralement en incluant directement les colonnes nécessaires dans la définition de l'index. Cela peut rendre les index plus larges et plus coûteux à maintenir.

Vous avez probablement besoin de meilleurs index si un endpoint de liste ralentit avec la croissance de la table alors qu'il ne renvoie que 20 à 50 lignes, si le tri devient lent à moins de supprimer le ORDER BY, ou si l'I/O augmente pendant des filtres simples. Les requêtes longues tendent aussi à augmenter les attentes de verrous aux périodes chargées.

Exemple : un écran de commandes qui filtre par customer_id et status et trie par created_at bénéficie généralement d'un index commençant par (customer_id, status, created_at). Si vous ajoutez plus tard « recherche par numéro de commande », il s'agit généralement d'un index séparé, pas quelque chose que vous greffez sur l'index de liste.

Migrations : garder les releases sûres quand les données augmentent

Les migrations cessent vite d'être « changer une table ». Une fois que de vrais utilisateurs et une vraie histoire existent, il faut aussi gérer les backfills, resserrer des contraintes et nettoyer d'anciens formats de données sans casser l'application.

Un défaut sûr est expand, backfill, contract. Ajoutez ce dont vous avez besoin sans perturber le code existant, copiez ou calculez les données en petites étapes, puis retirez l'ancien chemin seulement quand vous êtes confiant.

En pratique, cela signifie souvent ajouter une nouvelle colonne nullable ou une table, backfiller par lots en gardant les écritures cohérentes, valider ensuite avec des contraintes comme NOT NULL, des clés étrangères et des règles d'unicité, puis seulement après supprimer les anciennes colonnes, index et chemins de code.

Tous les changements de schéma ne se valent pas. Ajouter une colonne est souvent peu risqué. Créer un index peut être coûteux sur de grandes tables, planifiez-le sur des périodes de faible trafic et mesurez. Changer le type d'une colonne est souvent le plus risqué car cela peut réécrire des données ou bloquer les écritures. Un pattern plus sûr courant : créer une nouvelle colonne avec le nouveau type, backfiller, puis basculer lectures et écritures.

Les rollbacks changent aussi de sens à grande échelle. Revenir en arrière sur le schéma est parfois facile ; revenir sur des données souvent ne l'est pas. Soyez explicite sur ce que vous pouvez annuler, surtout si une migration inclut des suppressions destructrices ou des transformations avec perte.

JSON : champs flexibles sans douleur future

Les champs JSON sont tentants car ils permettent d'expédier plus vite : champs de formulaire supplémentaires, payloads d'intégration, préférences utilisateur et notes de systèmes externes tiennent sans changer le schéma. L'astuce est de décider ce qui appartient au JSON et ce qui mérite de vraies colonnes.

Dans PostgreSQL comme dans MariaDB, le JSON marche le mieux quand il est rarement filtré et surtout affiché, stocké pour le debug, conservé comme un blob de « paramètres » par utilisateur ou tenant, ou utilisé pour de petits attributs optionnels qui ne pilotent pas le reporting.

C'est sur l'indexation du JSON que les équipes se font surprendre. Interroger une clé JSON une fois est facile. Filtrer et trier dessus à travers de grandes tables, c'est là que la performance peut s'effondrer. PostgreSQL propose de bonnes options pour indexer des chemins JSON, mais il faut de la discipline : choisissez quelques clés vraiment filtrées et indexez-les, et laissez le reste en payload non indexé. MariaDB peut aussi interroger du JSON, mais des patterns complexes de « recherche dans le JSON » deviennent souvent fragiles et plus difficiles à maintenir rapides.

Le JSON affaiblit aussi les contraintes. Il est plus difficile d'imposer « doit être l'une de ces valeurs » ou « toujours présent » à l'intérieur d'un blob non structuré, et les outils de reporting préfèrent généralement des colonnes typées.

Une règle qui scale : commencez par JSON pour l'inconnu, mais normalisez en colonnes ou tables enfants lorsque vous (1) filtrez ou triez dessus, (2) avez besoin de contraintes, ou (3) le voyez apparaître dans des tableaux de bord chaque semaine. Stocker la réponse complète d'une API d'expédition pour une commande en JSON est souvent acceptable. Des champs comme delivery_status et carrier méritent généralement des colonnes réelles dès que le support et le reporting en dépendent.

Fonctions de requête qui apparaissent dans les apps mûres

Au début, la plupart des apps CRUD tournent sur des SELECT, INSERT, UPDATE et DELETE simples. Plus tard, vous ajoutez des feeds d'activité, des vues d'audit, des rapports d'admin et une recherche qui doit être instantanée. C'est là où le choix devient une question de compromis de fonctionnalités.

Les CTE et sous-requêtes gardent les requêtes lisibles. Ils sont utiles quand vous construisez un résultat en étapes (filtrer les commandes, joindre les paiements, calculer des totaux). Mais la lisibilité peut cacher le coût. Quand une requête devient lente, il peut être nécessaire de réécrire un CTE en sous-requête ou jointure puis de re-vérifier le plan d'exécution.

Les fonctions de fenêtre sont utiles dès qu'on demande « classer les clients par dépenses », « montrer des totaux cumulés » ou « dernier statut par ticket ». Elles remplacent souvent des boucles applicatives maladroites et réduisent le nombre de requêtes.

Les écritures idempotentes sont une autre exigence adulte. Quand des retries arrivent (réseaux mobiles, jobs en arrière-plan), les upserts permettent d'écrire sans créer de doublons :

• PostgreSQL : INSERT ... ON CONFLICT
• MariaDB : INSERT ... ON DUPLICATE KEY UPDATE

La recherche est la fonctionnalité qui surprend les équipes. La recherche full-text intégrée peut suffire pour des catalogues produits, bases de connaissances et notes de support. La recherche de type trigramme est utile pour l'auto-complétion et la tolérance aux fautes de frappe. Si la recherche devient centrale (classement complexe, beaucoup de filtres, trafic important), un moteur de recherche externe peut valoir la peine malgré la complexité supplémentaire.

Exemple : un portail de commandes commence par « lister les commandes ». Un an plus tard, il doit « montrer la dernière commande de chaque client, classer par dépense mensuelle et chercher par noms mal orthographiés ». Ce sont des capacités de base de données, pas seulement du travail UI.

Transactions, verrous et concurrence sous charge

Quand le trafic est faible, la plupart des bases semblent correctes. Sous charge, la différence tient souvent à la façon dont vous gérez les modifications concurrentes des mêmes données, pas à la vitesse brute. PostgreSQL et MariaDB peuvent tous deux gérer une charge CRUD transactionnelle, mais il faut concevoir pour la contention.

Isolation en langage simple

Une transaction est un ensemble d'étapes qui doivent réussir ensemble. L'isolation contrôle ce que les autres sessions peuvent voir pendant l'exécution. Une isolation plus forte évite des lectures surprenantes, mais peut augmenter les temps d'attente. Beaucoup d'apps commencent avec les valeurs par défaut et renforcent l'isolation seulement pour les flux qui en ont vraiment besoin (comme débiter une carte et mettre à jour une commande).

Ce qui cause vraiment la douleur des verrous

Les problèmes de verrou dans les apps CRUD viennent généralement de quelques coupables récurrents : des lignes « chaudes » que tout le monde met à jour, des compteurs qui changent à chaque action, des queues de jobs où plusieurs workers essaient de récupérer le même « job suivant », et des transactions longues qui gardent des verrous pendant que d'autres travaux (ou l'utilisateur) se déroulent.

Pour réduire la contention, gardez les transactions courtes, mettez à jour seulement les colonnes nécessaires et évitez les appels réseau à l'intérieur d'une transaction.

Une habitude utile : retenter en cas de conflit. Si deux agents support enregistrent des modifications sur le même ticket en même temps, n'échouez pas silencieusement. Détectez le conflit, rechargez la ligne la plus récente et demandez à l'utilisateur de réappliquer ses changements.

Pour détecter les problèmes tôt, surveillez les deadlocks, les transactions de longue durée et les requêtes qui passent du temps à attendre plutôt qu'à s'exécuter. Faites des logs des requêtes lentes une routine, surtout après des releases qui ajoutent de nouveaux écrans ou jobs en arrière-plan.

Opérations qui deviennent importantes après le lancement

Après le lancement, vous n'optimisez plus seulement la vitesse des requêtes. Vous optimisez la récupération, le changement sûr et la performance prévisible.

Une étape commune est d'ajouter une réplique. Le primaire gère les écritures et une réplique peut servir les pages en lecture lourdes comme les tableaux de bord ou rapports. Cela change la façon de penser la fraîcheur : certaines lectures peuvent être en retard de quelques secondes, donc votre application doit savoir quels écrans doivent lire sur le primaire (par exemple « commande juste passée ») et lesquels peuvent tolérer des données légèrement plus anciennes (par exemple des résumés hebdomadaires).

Les sauvegardes ne sont que la moitié du travail. Ce qui compte, c'est de pouvoir restaurer rapidement et correctement. Planifiez des restaurations de test régulières dans un environnement séparé, puis validez l'essentiel : l'app peut se connecter, les tables clés existent et les requêtes critiques retournent des résultats attendus. Les équipes découvrent souvent trop tard qu'elles ont sauvegardé la mauvaise chose, ou que le temps de restauration dépasse largement leur budget d'indisponibilité.

Les mises à jour cessent aussi d'être « cliquer et espérer ». Planifiez une fenêtre de maintenance, lisez les notes de compatibilité et testez le chemin de mise à niveau avec une copie des données de production. Même des versions mineures peuvent changer les plans d'exécution ou le comportement autour des index et des fonctions JSON.

Une observabilité simple rapporte tôt. Commencez par les logs de requêtes lentes et le top des requêtes par temps total, la saturation des connexions, la latence de réplication (si vous utilisez des répliques), le taux de hit du cache et la pression I/O, ainsi que les attentes de verrous et les événements de deadlock.

Comment choisir : un processus d'évaluation pratique

Si vous êtes bloqué, arrêtez de lire des listes de fonctionnalités et faites un petit essai avec votre propre charge de travail. L'objectif n'est pas un benchmark parfait mais d'éviter les surprises quand les tables atteignent des millions de lignes et que votre cycle de release s'accélère.

1) Construisez un mini-test proche de la production

Choisissez un extrait de votre application qui représente la vraie douleur : une ou deux tables clés, quelques écrans et les chemins d'écriture qui les soutiennent. Récupérez vos requêtes principales (celles derrière les pages de liste, pages de détail et jobs en arrière-plan). Chargez des volumes réalistes (au moins 100x vos données de prototype, avec une forme similaire). Ajoutez les index que vous pensez nécessaires, puis exécutez les mêmes requêtes avec les mêmes filtres et tris et capturez les temps. Répétez pendant que des écritures ont lieu (un simple script insérant et mettant à jour des lignes suffit).

Un exemple rapide : une liste « Clients » qui filtre par statut, cherche par nom, trie par dernière activité et pagine. Cet écran unique révèle souvent si votre indexation et le comportement du planner vieilliront bien.

2) Répétez les migrations comme une vraie release

Créez une copie de staging du dataset et entraînez-vous aux changements à venir : ajouter une colonne, changer un type, backfiller des données, ajouter un index. Mesurez le temps nécessaire, si cela bloque les écritures et ce que signifie réellement un rollback lorsque les données ont déjà changé.

3) Utilisez une carte de score simple

Après les tests, notez chaque option sur la performance pour vos requêtes réelles, la correction et la sécurité (contraintes, transactions, cas limites), le risque de migration (verrouillage, downtime, options de récupération), l'effort ops (sauvegarde/restaure, réplication, monitoring) et le confort de l'équipe.

Choisissez la base qui réduit le risque pour vos 12 prochains mois, pas celle qui gagne un micro-test.

Erreurs courantes et pièges

Les problèmes de base de données les plus coûteux commencent souvent par des « gains rapides ». Les deux bases peuvent exécuter une application CRUD transactionnelle, mais de mauvaises habitudes nuiront à l'une comme à l'autre quand le trafic et les données augmentent.

Un piège fréquent est de traiter le JSON comme un raccourci pour tout. Un champ « extras » flexible va pour des données vraiment optionnelles, mais des champs principaux comme le statut, les timestamps et les clés étrangères doivent rester de vraies colonnes. Sinon, vous vous retrouvez avec des filtres lents, une validation maladroite et des refactorings douloureux quand le reporting devient prioritaire.

L'indexation a son propre piège : ajouter un index pour chaque filtre visible sur un écran. Les index accélèrent les lectures mais ralentissent les écritures et alourdissent les migrations. Indexez ce que les utilisateurs utilisent réellement, puis validez sous charge mesurée.

Les migrations peuvent mordre quand elles verrouillent des tables. Les changements en mode big-bang comme réécrire une grande colonne, ajouter un NOT NULL avec une valeur par défaut ou créer un gros index peuvent bloquer les écritures pendant des minutes. Scindez les changements risqués en étapes et planifiez-les quand l'app est calme.

Aussi, ne vous fiez pas indéfiniment aux valeurs par défaut de l'ORM. Quand une vue de liste passe de 1 000 à 10 millions de lignes, il faut lire les plans de requête, repérer les index manquants et corriger les jointures lentes.

Signes d'alerte rapides : champs JSON utilisés pour le filtrage et le tri principal, un nombre d'index qui augmente sans mesurer l'impact sur les écritures, des migrations qui réécrivent de grandes tables en un seul déploiement, et une pagination sans ordre stable (ce qui conduit à des lignes manquantes ou dupliquées).

Checklist rapide avant de vous engager

Avant de choisir un camp, faites un contrôle de réalité basé sur vos écrans les plus chargés et votre processus de release.

• Vos écrans principaux restent-ils rapides en charge de pointe ? Testez la page de liste la plus lente avec de vrais filtres, tris et pagination, et confirmez que vos index correspondent exactement à ces requêtes.
• Pouvez-vous livrer des changements de schéma en sécurité ? Rédigez un plan expand-backfill-contract pour le prochain changement majeur.
• Avez-vous une règle claire JSON vs colonnes ? Décidez quelles clés JSON doivent être recherchables ou triables et lesquelles sont vraiment flexibles.
• Dépendrez-vous de fonctions de requête spécifiques ? Vérifiez le comportement d'upsert, des fonctions de fenêtre, du CTE et si vous avez besoin d'index fonctionnels ou partiels.
• Pouvez-vous l'exploiter après le lancement ? Prouvez que vous pouvez restaurer une sauvegarde, mesurer les requêtes lentes et baseliner latence et attentes de verrous.

Exemple : du simple suivi de commandes à un portail client chargé

Imaginez un portail client qui commence simplement : les clients se connectent, consultent des commandes, téléchargent des factures et ouvrent des tickets. La première semaine, presque n'importe quelle base transactionnelle suffit. Les pages chargent vite et le schéma est petit.

Quelques mois plus tard, les moments de croissance apparaissent. Les clients demandent des filtres comme « commandes expédiées les 30 derniers jours, payées par carte, avec remboursement partiel ». Le support veut des exports CSV rapides pour des revues hebdomadaires. La finance veut une piste d'audit : qui a changé le statut d'une facture, quand et de quoi à quoi. Les patterns de requête deviennent plus larges et plus variés que les écrans initiaux.

C'est là que la décision porte sur des fonctionnalités spécifiques et leur comportement en charge réelle.

Si vous ajoutez des champs flexibles (instructions de livraison, attributs personnalisés, métadonnées de ticket), le support JSON importe parce que vous voudrez éventuellement interroger à l'intérieur de ces champs. Soyez honnête sur la capacité de votre équipe à indexer des chemins JSON, valider des formes et garder des performances prévisibles à mesure que le JSON grossit.

Le reporting est un autre point de tension. Dès que vous joignez commandes, factures, paiements et tickets avec beaucoup de filtres, vous tiendrez compte des index composites, du planner et de la facilité à faire évoluer des index sans downtime. Les migrations ne sont plus "exécuter un script le vendredi" mais font partie de chaque release, car un petit changement de schéma peut toucher des millions de lignes.

Une approche pratique : écrivez cinq écrans réels et exports que vous attendez dans six mois, incluez tôt des tables d'historique d'audit, benchmarquez avec une taille de données réaliste en utilisant vos requêtes les plus lentes (pas un hello-world CRUD), et documentez les règles d'équipe pour l'usage du JSON, l'indexation et les migrations.

Si vous voulez aller vite sans construire chaque couche à la main, AppMaster (appmaster.io) peut générer des backends prêts pour la production, des apps web et mobiles natives à partir d'un modèle visuel. Il vous incite aussi à traiter écrans, filtres et processus métier comme de vraies charges de requêtes tôt, ce qui aide à détecter les risques d'indexation et de migration avant qu'ils n'atteignent la production.