Database Design Mistakes I Made So You Don't Have To \u2014 TXT1.ai

Il y a trois ans, j'ai regardé la base de données de notre startup s'arrêter à 2h47 du matin le Black Friday. Nous avions 50 000 utilisateurs simultanés, 2 millions de dollars de transactions en attente et une requête qui prenait 45 secondes pour renvoyer la disponibilité des produits. Notre CTO hurlait dans Slack. Nos investisseurs appelaient. Et je regardais un schéma que j'avais conçu six mois auparavant, réalisant que chaque décision "maligne" que j'avais prise nous coûtait maintenant environ 8000 $ par minute en revenus perdus.

Je suis Marcus Chen, et j'ai passé les douze dernières années en tant qu'architecte de base de données, travaillant avec tout le monde, des startups SaaS désordonnées aux entreprises du Fortune 500. J'ai conçu des schémas pour des systèmes traitant 500 millions de transactions quotidiennes, optimisé des requêtes qui ont réduit de 200 ms les chemins critiques, et oui—j'ai fait pratiquement toutes les erreurs de conception de base de données possibles. Cet incident du Black Friday ? Il m'a appris plus sur la conception de bases de données que tout mon diplôme en informatique.

Aujourd'hui, je suis l'architecte principal de base de données chez TXT1.ai, où nous traitons plus de 3 milliards de messages texte par an au sein de notre plateforme de communication alimentée par l'IA. Mais je suis arrivé ici en échouant de manière constructive, et je veux partager les leçons coûteuses que j'ai apprises afin que vous puissiez éviter les attaques de panique à 2 heures du matin et les appels d'investisseurs en colère.

Le désastre des "je vais juste utiliser des UUID partout"

Laissez-moi commencer par ce que j'appelle mon erreur à 40 000 $. En 2019, je concevais un système de gestion de la relation client pour une entreprise de commerce électronique de taille moyenne. Je venais de lire un article de blog sur la façon dont les UUID étaient la manière "moderne" de gérer les clés primaires—fini les entiers à auto-incrément, fini l'exposition séquentielle des ID, parfait pour les systèmes distribués. J'étais convaincu.

Alors, j'ai fait de chaque clé primaire dans le système un UUID. Table des utilisateurs ? UUID. Table des commandes ? UUID. Éléments de ligne de commande ? Vous l'avez deviné—UUID. Je me sentais comme un génie. Le schéma avait l'air propre, il n'y avait aucune vulnérabilité d'ID séquentiel, et je pouvais générer des ID côté client si nécessaire. Que pourrait-il mal se passer ?

Tout. Absolument tout a mal tourné.

En six mois, notre taille de base de données avait explosé à 340 Go alors qu'elle aurait dû être d'environ 180 Go. Les performances des requêtes se détérioraient semaine après semaine. Nos tailles d'index étaient énormes—l'index de la table des commandes à lui seul faisait 12 Go. Les jointures entre les commandes et les lignes de produit qui auraient dû prendre 50 ms prenaient 800 ms. La base de données passait un temps énorme sur les I/O disque, et nos coûts AWS RDS avaient presque doublé.

Voici ce que j'ai appris à mes dépens : les UUID font 128 bits (16 octets) par rapport à un entier de 4 octets ou un bigint de 8 octets. C'est 4x le stockage pour chaque clé primaire. Mais le véritable problème n'est pas le stockage—c'est la fragmentation des index. Les UUID sont aléatoires, ce qui signifie que chaque insertion provoque des écritures aléatoires dans vos index B-tree. Avec des entiers séquentiels, les nouvelles lignes s'ajoutent à la fin de l'index. Avec les UUID, la base de données doit constamment rééquilibrer toute la structure de l'index.

Nous avons mesuré l'impact : insérer 100 000 lignes avec des ID entiers a pris 8 secondes. La même opération avec des UUID a pris 34 secondes. C'est une pénalité de performance de 4,25x juste à cause du choix de la clé primaire. Lorsque vous traitez 50 000 commandes par jour, ça s'accumule rapidement.

La solution nous a coûté trois semaines de temps de développement et a nécessité une migration soigneusement orchestrée pendant une fenêtre de maintenance. Nous sommes passés à des clés primaires bigint pour les tables à fort volume et avons conservé les UUID uniquement pour les tables où nous avions réellement besoin d'identifiants uniques au niveau mondial dans des systèmes distribués—ce qui s'est révélé être exactement deux tables sur quarante-sept.

Ma règle maintenant : utilisez des entiers ou des bigints à auto-incrément pour les clés primaires, sauf si vous avez une raison spécifique et documentée d'utiliser des UUID. Et "ça semble plus moderne" n'est pas une raison documentée.

Normalisation prématurée : quand la troisième forme normale devient votre ennemi

Tout juste sorti de l'université, j'étais obsédé par la normalisation. J'avais mémorisé toutes les formes normales, je pouvais réciter les règles de Codd dans mon sommeil, et je croyais qu'une base de données correctement normalisée était le summum de l'excellence en matière de conception. Donc, lorsque j'ai conçu mon premier système de production—une plateforme de gestion de contenu—j'ai tout normalisé à la troisième forme normale et au-delà.

"Chaque décision 'maligne' de base de données que vous prenez aujourd'hui est une crise potentielle à 2 heures du matin dans six mois. Conception pour le système que vous aurez, pas le système que vous voulez."

J'avais une table des utilisateurs, une table des adresses_utilisateurs (car les utilisateurs peuvent avoir plusieurs adresses), une table des numéros_de_téléphone_utilisateurs (plusieurs téléphones !), une table des préférences_utilisateurs, une table des paramètres_utilisateurs et une table des métadonnées_utilisateurs. Charger le profil d'un seul utilisateur nécessitait de joindre six tables. J'étais si fier de à quel point tout cela avait l'air "propre".

Puis nous avons lancé. La page du profil utilisateur—la page la plus fréquemment consultée de toute l'application—mettait 1,2 seconde à charger. Nous faisions six jointures pour chaque vue de page, et avec 10 000 utilisateurs actifs par jour, cela signifiait 60 000 jointures par jour juste pour les vues de profil. Le CPU de la base de données était constamment au-dessus de 70%.

Le coup de grâce est venu lorsque notre développeur en chef m'a pris à part et m'a montré le plan d'exécution de la requête. "Marcus," a-t-il dit, "nous joignons six tables pour afficher le nom, l'e-mail et le numéro de téléphone d'un utilisateur. C'est insensé." Il avait raison. J'avais optimisé pour la pureté théorique au lieu de la performance pratique.

Nous avons dénormalisé de manière stratégique. L'adresse principale de l'utilisateur est revenue dans la table des utilisateurs. Leur numéro de téléphone principal ? Même chose. Nous avons conservé les tables séparées pour les adresses et numéros de téléphone supplémentaires, mais 94 % de nos utilisateurs n'avaient qu'un seul de chaque. Ce changement unique a réduit notre temps moyen de requête de page de profil de 1,2 seconde à 180 ms—une amélioration de 85 %.

Voici ce que j'ai appris : la normalisation est un outil, pas une religion. La troisième forme normale est un excellent point de départ, mais la performance dans le monde réel nécessite souvent une dénormalisation stratégique. Maintenant, je suis ce que j'appelle la "règle de dénormalisation 80/20"—si 80 % des requêtes nécessitent des données provenant de plusieurs tables, ces données appartiennent probablement à une seule table. J'évalue les modèles de requêtes en production et je dénormalise en fonction de l'utilisation réelle, pas de la pureté théorique.

La clé est de savoir quand dénormaliser. Les tables à forte lecture et faible écriture sont des candidates idéales. Profils d'utilisateurs, catalogues de produits, données de configuration—ce sont toutes de bonnes opportunités pour dénormaliser. Les tables de transactions avec des volumes d'écriture élevés ? Gardez-les normalisées pour éviter les anomalies de mise à jour.

Le cauchemar NULL : quand l'optionnel devient impossible

J'aimais autrefois les colonnes nullable. Elles semblaient si flexibles, si accommodantes. Un utilisateur pourrait ne pas avoir de deuxième prénom ? Rendre ça nullable. Une commande pourrait ne pas avoir de code de réduction ? Nullable. Un produit pourrait ne pas avoir de poids ? Vous avez compris l'idée.

Dans un projet particulièrement désastreux, j'ai conçu un système de gestion des stocks où environ 60 % des colonnes de toutes les tables étaient nullable. Cela semblait raisonnable—tous les champs n'auraient pas toujours de données, n'est-ce pas ? Pourquoi forcer des valeurs par défaut quand NULL communique clairement "pas de valeur" ?

Les problèmes ont commencé immédiatement. Les requêtes sont devenues un champ de mines de vérifications de NULL. Vous voulez trouver tous les produits sans poids ? Vous penseriez que "WHERE weight IS NULL" fonctionnerait, mais qu'en est-il des produits où le poids était explicitement fixé à zéro ? Maintenant, vous avez besoin de "WHERE weight IS NULL OR weight = 0". Vous voulez faire la somme des totaux de commandes ? Mieux vaut utiliser COALESCE ou votre SUM pourrait renvoyer NULL si une valeur individuelle est NULL.

B