Base de données PrestaShop : routine de maintenance et nettoyage automatisé

Table des matières :

1. Cartographier la base PrestaShop : où la dette s’accumule vraiment
2. Pré-requis non négociables : sauvegarde, fenêtre de maintenance, et sécurité
3. Nettoyage “safe” côté PrestaShop : ce qu’on peut purger sans casser le métier
4. Optimisation InnoDB : statistiques, fragmentation, et risques de verrouillage
5. Automatiser proprement : cron système, verrou applicatif, exécution en batch
6. Routine type (hebdo/mensuelle) et effets mesurables sur performance, UX et SEO

Cartographier la base PrestaShop : où la dette s’accumule vraiment

Sur PrestaShop (8.1.x et 9.1.x au moment d’écrire, PHP 8.1–8.5 supporté côté 9.1), la “maintenance base de données” ne veut pas dire “vider au hasard des tables”. Ça consiste à contrôler la croissance des tables à forte volatilité (paniers, connexions, index de recherche, logs), à réduire la fragmentation et à stabiliser les temps de réponse des requêtes critiques (listing produit, recherche, back-office). La plupart des boutiques qui ralentissent “sans raison” ont une base qui grossit sur des tables non critiques métier.

Trois symptômes sont récurrents : (1) des tables InnoDB avec beaucoup de pages libres (espace “data_free”) donc un stockage gonflé, (2) des index peu sélectifs ou obsolètes qui coûtent cher sur des tables qui prennent des millions de lignes, (3) des opérations cron/back-office (indexation, stats, export) qui déclenchent des scans complets et saturent l’I/O. Ce n’est pas spécifique à PrestaShop, mais PrestaShop a une particularité : il écrit énormément dans ps_connections, ps_connections_source, ps_guest, ps_cart*, et certains modules ajoutent leurs propres “log tables” sans politique de rétention.

Avant de parler nettoyage, il faut poser un diagnostic simple et mesurable. Un point de départ : identifier les tables les plus volumineuses et celles qui ont le plus de “bloat”.

Note : le préfixe n’est pas toujours ps_. Si votre boutique utilise un autre préfixe, adaptez les requêtes (ou travaillez table par table via information_schema).

-- MySQL/MariaDB
SELECT
  table_name,
  engine,
  table_rows,
  ROUND((data_length+index_length)/1024/1024, 1) AS size_mb,
  ROUND(data_free/1024/1024, 1) AS free_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length+index_length) DESC
LIMIT 30;

Si ps_connections* ou ps_cart* dominent, vous avez un candidat direct au nettoyage. Si free_mb est élevé sur des tables critiques (ex. ps_product, ps_product_shop, ps_search_index), la routine doit inclure une stratégie d’optimisation/rebuild (avec des précautions de verrouillage).

Pour rendre le diagnostic plus “actionable”, ajoutez deux contrôles très simples :

1) Taux de bloat (approx.) sur les tables qui grossissent :

SELECT
  table_name,
  ROUND(data_free / NULLIF((data_length+index_length),0) * 100, 1) AS free_pct,
  ROUND((data_length+index_length)/1024/1024, 1) AS size_mb,
  ROUND(data_free/1024/1024, 1) AS free_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY free_pct DESC, free_mb DESC
LIMIT 30;

2) Tables “chaudes” (celles qui reçoivent des écritures tous les jours) : vous pouvez rapidement repérer des dérapages en mesurant les volumes par période (quand une colonne date existe). Exemple sur les connexions :

SELECT
  DATE(date_add) AS d,
  COUNT(*) AS connections
FROM ps_connections
WHERE date_add >= (CURDATE() - INTERVAL 14 DAY)
GROUP BY DATE(date_add)
ORDER BY d DESC;

Mini-scénario typique : une boutique B2C hébergée en Europe (par exemple sur un cloud managé en région Paris) avec des pics d’acquisition (soldes, campagnes Ads) voit ps_connections passer de quelques millions à plusieurs dizaines de millions de lignes en quelques mois. Le front “tient”, mais le back-office devient lent sur tout ce qui touche aux stats/exports, et la fenêtre de sauvegarde s’allonge. La cartographie ci-dessus permet de cibler la dette (plutôt que de “tuner MySQL” à l’aveugle).

Pré-requis non négociables : sauvegarde, fenêtre de maintenance, et sécurité

Tout script de nettoyage automatisé est une opération destructrice. Sur une boutique en production, on pose d’abord des garde-fous : sauvegarde restaurable, exécution hors pics, journalisation, et rollback (au moins partiel). En pratique, pour MySQL/MariaDB : mysqldump --single-transaction (InnoDB) pour un export logique, ou snapshot/disque/volume si vous avez une infra virtualisée. Le point important : tester une restauration sur un environnement de staging. Sans test de restore, vous n’avez pas de sauvegarde, vous avez un fichier.

Deuxième point : fenêtre de maintenance et impact applicatif. Un DELETE massif sur une table InnoDB déclenche : génération de redo/undo, purge thread, possible contention, et parfois une pression I/O durable. Un OPTIMIZE TABLE sur InnoDB revient souvent à un rebuild (donc gros I/O + locks selon version et contexte). Si vous ne pouvez pas prendre de risque sur la disponibilité, planifiez les opérations lourdes la nuit et introduisez une exécution “en tranches” (batch) avec limite.

Troisième point : la surface d’attaque. Un cron SQL mal protégé devient une porte d’entrée (accès URL, injection de paramètres, fuite de credentials). Les prérequis de base : exécuter côté serveur (cron système), utiliser un compte DB à privilèges minimaux (souvent DELETE, SELECT, parfois ALTER pour l’optimisation), stocker les secrets hors webroot, et activer une politique de logs/alerting. Pour le cadre global, voir l’article interne : Sécurité PrestaShop 2026 : risques majeurs et protections professionnelles.

Enfin, sur le plan “qualité d’exécution”, n’exécutez pas ce type de routines avec une configuration PHP approximative si vous passez par un script PHP CLI : gestion des erreurs, timeouts, memory limit, etc. Le rappel utile est dans : Gestion d’erreur PHP : bonnes pratiques et configuration développement/production.

Deux ajouts importants (souvent négligés) :

• Cadre RGPD (conservation limitée) : les tables de tracking (connections, guest, certaines tables modules) contiennent fréquemment des identifiants, IP, user-agent ou autres données pouvant relever des données personnelles. Le principe de minimisation/conservation limitée s’applique :

« […] conservées sous une forme permettant l’identification des personnes concernées pendant une durée n’excédant pas celle nécessaire au regard des finalités […] » — Règlement (UE) 2016/679 (RGPD), article 5(1)(e)

Traduction opérationnelle : documentez une durée de rétention (ex. 90 jours / 180 jours) et tenez-vous-y via automatisation.

• Pré-vol “anti-catastrophe” avant suppression : sur chaque table, faites un SELECT COUNT(*) (ou un SELECT MIN(date_add), MAX(date_add)) pour confirmer le périmètre. Exemple :

  SELECT COUNT(*) AS to_delete
  FROM ps_connections
  WHERE date_add < (NOW() - INTERVAL 90 DAY);

Cette étape réduit drastiquement les erreurs de paramétrage (mauvais préfixe, fuseau horaire, colonne non indexée, etc.).

Nettoyage “safe” côté PrestaShop : ce qu’on peut purger sans casser le métier

Dans PrestaShop, le nettoyage doit respecter une règle : ne jamais toucher à ce qui porte une valeur légale ou comptable (commandes, factures, avoirs, écritures, etc.) sans cadrage juridique et métier. Par contre, une grosse part du volume provient de données transitoires : sessions de navigation, paniers abandonnés, index de recherche, logs techniques, et tables de stats anciennes.

Les candidats classiques (core) à rétention finie :

• ps_connections, ps_connections_source, ps_guest : tracking de visite / sources (fort débit d’écriture).
• ps_cart, ps_cart_product : paniers, dont une part énorme ne se convertit jamais.
• ps_log : logs applicatifs (erreurs, événements back-office).
• ps_search_index, ps_search_word, ps_search_word_shop : index de recherche interne (reconstruisible).
• tables de modules de stats/analytics (selon modules installés) : souvent volumineuses et non indispensables à l’exécution du front.

Pour éviter les purges “au feeling”, une grille de décision simple aide à prioriser (exemple indicatif, à adapter) :

Zone	Tables typiques	Risque métier	Reconstructible	Rétention fréquente
Tracking technique	connections*, guest	Faible	Oui	30–180 jours
Paniers abandonnés	cart* (hors commandes)	Faible à modéré (marketing)	Partiel	90–365 jours
Logs applicatifs	log	Faible	Oui (mais utile pour debug)	30–90 jours
Recherche native	search_*	Modéré (UX)	Oui	rebuild périodique
Modules stats	dépend du module	Variable	Souvent oui	selon besoin BI

Exemple de purge raisonnable des connexions (conserver 90 jours). Attention : adaptez le seuil à vos obligations internes et à vos usages (reporting, anti-fraude, etc.).

-- Conserver 90 jours de connexions
DELETE FROM ps_connections
WHERE date_add < (NOW() - INTERVAL 90 DAY);

-- Les tables associées peuvent contenir des FK/liaisons : purger par jointure
DELETE cs FROM ps_connections_source cs
LEFT JOIN ps_connections c ON c.id_connections = cs.id_connections
WHERE c.id_connections IS NULL;

DELETE g FROM ps_guest g
LEFT JOIN ps_connections c ON c.id_guest = g.id_guest
WHERE c.id_guest IS NULL;

Dans la pratique, sur les gros volumes, faites la même logique en batch (cf. section automatisation), sinon vous risquez une transaction très coûteuse. Autre point utile : si date_add n’est pas indexée sur vos tables les plus volumineuses (certains modules…), la purge devient un scan complet. Dans ce cas, on commence par indexer la colonne de date (avec méthode online si besoin), puis on purge.

Pour les paniers, la purge doit exclure tout panier lié à une commande. Dans le schéma PrestaShop, ps_orders.id_cart fait le lien. On supprime donc uniquement les paniers “orphelins” et anciens.

-- Purger les paniers abandonnés de plus de 180 jours, non liés à une commande
DELETE cp FROM ps_cart_product cp
INNER JOIN ps_cart c ON c.id_cart = cp.id_cart
LEFT JOIN ps_orders o ON o.id_cart = c.id_cart
WHERE o.id_order IS NULL
  AND c.date_add < (NOW() - INTERVAL 180 DAY);

DELETE c FROM ps_cart c
LEFT JOIN ps_orders o ON o.id_cart = c.id_cart
WHERE o.id_order IS NULL
  AND c.date_add < (NOW() - INTERVAL 180 DAY);

Deux précautions supplémentaires côté paniers :

• Marketing / relance paniers : si vous utilisez un module de relance qui exploite des paniers anciens, une purge trop agressive peut réduire l’historique exploitable. Une approche équilibrée consiste à purger par paliers (ex. 365 jours), puis à réduire progressivement (180 jours) après validation.
• Nettoyage complet : il peut exister d’autres tables liées aux paniers selon modules (ex. tables de “quote”, “wishlist”, “abandoned cart pro”, etc.). Avant d’automatiser, listez les tables des modules les plus “bruyants” et vérifiez leur logique de rétention.

Sur l’index de recherche interne, le “nettoyage” consiste plus souvent à rebuild qu’à purger partiellement. Si votre recherche native est secondaire (et que vous utilisez un moteur externe), vous pouvez drastiquement réduire le poids en désactivant/limitant l’indexation, mais ça dépend de l’architecture. Dans tous les cas, gardez en tête que la recherche interne touche l’UX et peut impacter indirectement le SEO (navigation, conversion). Pour relier performance et métriques UX, il y a un pont direct avec : Core Web Vitals : actions concrètes pour améliorer LCP, INP et CLS.

Optimisation InnoDB : statistiques, fragmentation, et risques de verrouillage

Le nettoyage (DELETE) réduit le nombre de lignes, mais ne réduit pas forcément la taille sur disque immédiatement. Avec InnoDB, l’espace libéré est en général réutilisé au sein du tablespace (et n’est rendu à l’OS que dans certains scénarios, typiquement via rebuild de table avec innodb_file_per_table=ON). C’est précisément pourquoi on couple souvent purge + “optimisation”.

Traduction pratique : sur InnoDB, un OPTIMIZE TABLE peut s’apparenter à un ALTER TABLE ... qui reconstruit la table. Résultat : gain potentiel sur data_free, mais coût I/O et possibles locks. Sur des tables de plusieurs Go, ce n’est pas un “cron du dimanche matin”.

La routine “sûre” côté prod consiste d’abord à rafraîchir les statistiques (ANALYZE TABLE) pour améliorer les plans d’exécution, puis à réserver les rebuilds aux tables qui en ont vraiment besoin (mesurer data_free et les ralentissements). Exemple :

ANALYZE TABLE ps_product, ps_product_shop, ps_category_product;

-- À utiliser avec précaution (fenêtre de maintenance)
OPTIMIZE TABLE ps_connections, ps_cart, ps_cart_product;

Pour décider quand optimiser, une règle pragmatique : ciblez les tables volumineuses avec beaucoup de free_mb et un impact ressenti (back-office, recherche, exports). Optimiser “pour optimiser” peut au contraire créer des incidents (I/O saturée, réplication en retard, sauvegardes qui explosent en durée).

Si vous êtes sur MariaDB, le comportement et les algorithmes DDL peuvent différer selon versions (et selon innodb_file_per_table, innodb_flush_log_at_trx_commit, stockage SSD/NVMe…). L’idée reste la même : mesurer, puis agir.

Pour les rebuilds sans interruption, l’écosystème MySQL s’appuie souvent sur Percona Toolkit (table fantôme + triggers + bascule finale). La documentation officielle détaille le fonctionnement et les contraintes : Documentation Percona : pt-online-schema-change

Ce n’est pas magique : vous payez en écritures (triggers + table fantôme) et vous devez dimensionner l’I/O, mais c’est l’option pragmatique quand la table est trop grosse pour un lock.

Enfin, ne confondez pas maintenance DB et “tuning serveur” : l’optimisation durable passe aussi par l’observabilité (slow query log, performance_schema, métriques InnoDB) et la correction des requêtes qui déclenchent des scans. Une base “propre” mais des requêtes non indexées restera lente.

Petit check utile (sans ajouter d’outillage) : activez temporairement le slow log en staging, rejouez des parcours back-office (catalogue, commandes, exports) et notez les requêtes qui dépassent un seuil (ex. 1s). Très souvent, le coupable est un module (liste avec filtres) qui manque un index composite.

Si vous êtes sur PrestaShop 9.1, profitez du contexte de migration/upgrade pour faire le point sur la compatibilité stack (PHP, CLI, tooling), via : PrestaShop 9.1 : compatibilité PHP 8.1–8.5, CLI et nouveautés développeurs.

Automatiser proprement : cron système, verrou applicatif, exécution en batch

L’automatisation “propre” en 2026, sur PrestaShop, se fait rarement via une URL cron exposée. Le pattern robuste : cron système (Linux) → script shell/PHP CLI → exécution SQL paramétrée → logs → alerting. Vous évitez la dépendance au front controller, vous maîtrisez l’environnement, et vous pouvez intégrer un mécanisme de lock.

Un verrou simple côté MySQL : GET_LOCK() permet d’éviter deux exécutions concurrentes (déploiement, cron doublé, etc.). Couplé à des suppressions en batch, vous réduisez le risque de transaction trop longue.

#!/usr/bin/env bash
set -euo pipefail

DB="prestashop"
MYSQL="mysql --defaults-extra-file=/etc/mysql/prestashop.cnf --database=${DB} --silent --skip-column-names"

# Lock global (attendre 0s)
LOCKED=$($MYSQL "SELECT GET_LOCK('ps_db_maintenance', 0);")
if [ "$LOCKED" != "1" ]; then
  echo "Maintenance already running" >&2
  exit 0
fi

# Purge en batch (ex: connections)
$MYSQL "DELETE FROM ps_connections WHERE date_add < (NOW() - INTERVAL 90 DAY) LIMIT 50000;"

# Release lock
$MYSQL "SELECT RELEASE_LOCK('ps_db_maintenance');" >/dev/null

Deux améliorations simples pour rendre cette automatisation “industrial-grade” sans complexifier :

• Boucler tant qu’il reste des lignes (au lieu d’un seul batch), avec une pause courte pour lisser l’I/O :

  while true; do
    AFFECTED=$($MYSQL "DELETE FROM ps_connections WHERE date_add < (NOW() - INTERVAL 90 DAY) LIMIT 50000; SELECT ROW_COUNT();")
    # La commande retourne 2 lignes ; la 2e est ROW_COUNT()
    ROWS=$(echo "$AFFECTED" | tail -n1)
    echo "$(date -Is) ps_connections deleted=$ROWS"
    [ "$ROWS" -lt 50000 ] && break
    sleep 2
  done

• Tracer la durée (au minimum au niveau log). Sur des serveurs mutualisés ou sur des VM sous-dimensionnées, c’est souvent la durée qui révèle une saturation (I/O wait).

Si vous préférez intégrer ça dans l’écosystème PrestaShop 8/9 (Symfony), vous pouvez exposer une commande Symfony dans un module (bin/console) et la déclencher via cron. Avantage : vous réutilisez le container, la config DB, et vous pouvez ajouter des logs structurés. Inconvénient : vous chargez plus de dépendances qu’un simple client MySQL, et vous devez être strict sur les timeouts/mémoire. Dans PrestaShop 9.x, c’est généralement plus cohérent, surtout si vous avez déjà des commandes CLI internes.

Dans tous les cas : rendez la routine idempotente (répéter sans casse), contrôlez les volumes (LIMIT + boucle), et écrivez un état d’exécution (table ps_db_maintenance_log dédiée ou logs fichier) avec au minimum : timestamp, table, lignes supprimées, durée, erreurs. Sans métriques, vous ne saurez pas si ça marche ou si ça dégrade.

Checklist minimale (utile pour éviter les “cron qui font peur”) :

• [ ] le cron tourne en CLI, pas via une URL publique
• [ ] un verrou empêche les doubles exécutions
• [ ] chaque suppression est faite en batch
• [ ] vous avez une sauvegarde restaurée en staging au moins une fois
• [ ] vous loggez lignes supprimées + durée
• [ ] vous avez une procédure de “stop” (désactiver le cron + tuer le process)

Routine type (hebdo/mensuelle) et effets mesurables sur performance, UX et SEO

Une base PrestaShop entretenue n’est pas un luxe : c’est un prérequis pour garder des temps de réponse stables quand le catalogue, le trafic et l’historique grossissent. Une routine réaliste se découpe par fréquence, selon le coût et le risque.

Routine quotidienne (faible risque) : purge des logs applicatifs au-delà d’un seuil (ex. 30 à 90 jours), purge des tables de tracking volumineuses (connexions, sources) en batch, et éventuellement suppression de paniers très anciens non commandés. L’objectif n’est pas de “tout supprimer”, mais de stabiliser le volume d’écriture et d’éviter que certaines tables passent de 1–5 M lignes à 50–100 M lignes, ce qui finit par impacter les temps d’admin (grilles back-office, exports).

Routine hebdomadaire (risque modéré) : ANALYZE TABLE sur les tables critiques et celles fortement modifiées, contrôle des index, vérification des tables les plus volumineuses (information_schema.tables), et surveillance du slow query log. Une dérive typique : une requête back-office liée à un module qui n’a pas d’index adéquat et qui commence à faire des filesort/temp tables. Une autre dérive : la recherche interne indexée sur un catalogue massif avec variations, qui réclame une stratégie dédiée.

Routine mensuelle (risque élevé, à planifier) : opérations de rebuild/optimisation sur les tables réellement fragmentées (gros data_free), rotation et purge des binlogs (si réplication), et éventuellement opérations “online” (Percona) si vous ne pouvez pas prendre de lock.

Pour piloter, définissez 3 indicateurs simples et vérifiables (même sans APM) :

1) Taille DB et top tables (Go / semaine)
2) Temps de réponse back-office sur 2–3 actions représentatives (ex. ouvrir une fiche produit, filtrer une liste de commandes)
3) P95/P99 côté pages (si vous avez des logs Nginx/Apache, vous pouvez déjà extraire des percentiles par URL)

En termes de métriques, ce qu’on cherche à observer : baisse de la taille disque (Go), baisse des temps d’exécution de requêtes (P95/P99), et baisse de la latence côté page. La corrélation avec l’UX et l’acquisition est indirecte mais concrète : une boutique qui répond plus vite, c’est moins de timeouts, moins d’erreurs, et des Web Vitals plus stables. Pour l’angle plan d’action SEO/perf, référence interne utile : SEO PrestaShop : roadmap 3 mois audit, contenu, performance et popularité.

Dernier point souvent ignoré : la maintenance DB ne compense pas une migration ou une montée de version mal préparée. Si vous êtes en cours de passage 1.7 → 8/9 ou 8 → 9, faites d’abord la migration proprement (schéma, modules, overrides, compat PHP), puis stabilisez avec une routine de nettoyage. Le guide interne sur le sujet : PrestaShop 9 : guide de migration depuis 1.7 et 8 avec 1-Click Upgrade.

Au final, une routine de maintenance et nettoyage automatisé pour la base de données PrestaShop doit rester boring : peu de magie, beaucoup de garde-fous, des batchs, des métriques, et une exécution hors web. C’est ce qui permet de garder un socle stable malgré les limites connues du core sur la volumétrie (et malgré les modules qui “loggent tout” sans rétention).