# Guide d'application des optimisations BD — PRODUCTION (GPHARMA)

> **But** : reproduire, sur la base **de production**, les optimisations validées en pré-prod.
> **Public** : personne disposant d'un accès `psql` superuser à la base de prod + capacité à déployer le code et lancer `php artisan`.
> **Durée** : 30 min de préparation + fenêtre de faible trafic pour la création des index (peut durer de quelques minutes à 1–2 h selon les volumes réels).

---

## 0. Ce que couvre (et ne couvre pas) ce guide

> 📌 Ce fichier recense les optimisations BD **effectivement réalisées** (validées en pré-prod), afin de les reproduire à l'identique en production. Toute nouvelle optimisation BD **réellement appliquée** doit y être ajoutée.

**Opérations réalisées (3 lots BD) :**
1. **Tuning PostgreSQL** (`ALTER SYSTEM`) — réglages planner + mémoire. *À adapter au matériel prod.*
2. **Index** — création de 13 index + suppression de 5 doublons stricts + suppression des index mono-colonne sur 4 colonnes techniques + `ANALYZE`.
3. **Triggers de réplication** — retrait sur les tables techniques Laravel uniquement.

**NON couvert ici** (déploiement de code applicatif classique, via ta procédure Git/déploiement habituelle) :
- Les optimisations du contrôleur `produitController::listeProduit` (N+1 `ModeGpharma`, `distinct`, etc.).
  → Ce sont des **fichiers PHP**, ils partent avec le déploiement du code, pas avec ce runbook BD.

**Artefacts présents dans le dépôt** (à déployer avec le code) :
| Lot | Fichier |
|---|---|
| 1. Tuning | `database/sql/optimisation_postgresql.sql` |
| 2. Index | `database/migrations/2026_07_07_000000_add_optimisation_indexes.php` |
| 3. Triggers | `database/migrations/2026_07_07_000001_drop_replication_triggers_laravel.php` |

---

## 1. Pré-requis & règles d'or

- [ ] **SAUVEGARDE FRAÎCHE ET VÉRIFIÉE** de la base de prod (voir Phase 0). Non négociable.
- [ ] Accès `psql` avec un rôle **superuser** (nécessaire pour `ALTER SYSTEM`). Vérifier :
  ```sql
  SELECT current_user, rolsuper FROM pg_roles WHERE rolname = current_user;
  ```
- [ ] Fenêtre de **faible trafic** (les `CREATE INDEX CONCURRENTLY` consomment CPU/I-O ; ils sont *en ligne* — pas de blocage des écritures — mais lourds sur gros volumes).
- [ ] Le **code déployé en prod est la même version** que celle testée (mêmes migrations, même `produitController`).
- [ ] Toutes les opérations sont **idempotentes** (`IF NOT EXISTS` / `IF EXISTS` / détection dynamique) → ré-exécutables sans danger.
- [ ] **Ordre : Phase 1 → Phase 2 → Phase 3.** Le tuning (Phase 1) augmente `maintenance_work_mem`, ce qui **accélère la création des index** de la Phase 2.

> ⚠️ **Différence clé avec la pré-prod** : en prod la **réplication est vivante** et les **volumes sont réels**. On ne touche donc JAMAIS aux triggers des tables métier, et on adapte le tuning au matériel du serveur de prod.

---

## 2. Phase 0 — Sauvegarde & état des lieux (AVANT)

### 2.1 Sauvegarde
```bash
# Adapter host/port/base. Sauvegarde compressée.
pg_dump -h <HOST_PROD> -p <PORT_PROD> -U <USER> -Fc -f gpharma_avant_optim_$(date +%F).dump <BASE_PROD>
# Vérifier que le fichier est non vide et lisible :
pg_restore --list gpharma_avant_optim_*.dump | head
```

### 2.2 Photographier l'état initial (pour comparer après)
```sql
-- Nombre d'index
SELECT count(*) AS index_avant FROM pg_indexes WHERE schemaname = 'public';

-- Réglages serveur actuels
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('shared_buffers','effective_cache_size','work_mem',
               'maintenance_work_mem','max_wal_size','wal_compression','random_page_cost');

-- Taille de la base et taux de cache
SELECT pg_size_pretty(pg_database_size(current_database())) AS taille_bd;
SELECT round(100.0*sum(heap_blks_hit)/nullif(sum(heap_blks_hit)+sum(heap_blks_read),0),2) AS cache_hit_pct
FROM pg_statio_user_tables;

-- Nombre de triggers de réplication
SELECT count(*) AS triggers_replica FROM pg_trigger
WHERE tgname LIKE 'tr_replica_%' AND NOT tgisinternal;
```
> 📝 **Noter ces valeurs** quelque part : elles servent de point de comparaison en fin de déploiement.

---

## 3. Phase 1 — Tuning PostgreSQL (adapté au matériel PROD)

> 🚨 **Ne PAS copier tel quel les valeurs de la pré-prod.** Elles dépendent de la RAM et du type de disque du serveur de prod.

Réglages appliqués en pré-prod (via `ALTER SYSTEM`, rechargés à chaud) :

| Paramètre | Valeur pré-prod | Règle d'adaptation prod |
|---|---|---|
| `random_page_cost` | 1.1 | **1.1** si SSD/NVMe · 1.5–2 si SAN · **garder 4 si disque mécanique** |
| `effective_cache_size` | 12GB | ≈ **50–75 % de la RAM totale** (simple indice au planner, pas une allocation) |
| `work_mem` | 24MB | ≈ (RAM × 0,25) ÷ `max_connections`, plafonné (prudent : 16–32MB) |
| `maintenance_work_mem` | 512MB | 512MB–1GB |
| `max_wal_size` | 4GB | 4GB (charge d'écriture forte à cause des triggers de réplication) |
| `wal_compression` | on | on |

### 3.1 Diagnostiquer le matériel prod
```sql
SHOW shared_buffers;      -- souvent ~25% de la RAM
SHOW max_connections;     -- borne le calcul de work_mem
SELECT pg_size_pretty(pg_database_size(current_database()));  -- si la base tient en RAM, random access ~ gratuit
```
Déterminer aussi (auprès de l'hébergeur/sysadmin) : **RAM totale** du serveur et **type de disque (SSD/NVMe ou HDD)**.

### 3.2 Appliquer
Le fichier `database/sql/optimisation_postgresql.sql` contient déjà ces `ALTER SYSTEM`.
**Éditer d'abord les valeurs** selon le tableau ci-dessus (surtout `random_page_cost` et `effective_cache_size`), puis :
```bash
psql -h <HOST_PROD> -p <PORT_PROD> -U <USER> -d <BASE_PROD> -f database/sql/optimisation_postgresql.sql
```
Tous ces paramètres sont **rechargeables à chaud** (`SELECT pg_reload_conf();` est inclus) → **aucun redémarrage** requis.

### 3.3 Vérifier (dans une NOUVELLE connexion)
```sql
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('random_page_cost','effective_cache_size','work_mem',
               'maintenance_work_mem','max_wal_size','wal_compression');
-- Vérifier qu'aucun réglage par rôle/base ne masque ALTER SYSTEM :
SELECT setrole::regrole, unnest(setconfig) FROM pg_db_role_setting;
```
> ℹ️ `pg_reload_conf()` est asynchrone : ouvrir une **nouvelle** session pour voir les valeurs effectives.

**Rollback Phase 1 :** `ALTER SYSTEM RESET <param>;` (ou `ALTER SYSTEM RESET ALL;`) puis `SELECT pg_reload_conf();`

---

## 4. Phase 2 — Index (création + nettoyage)

Cette migration fait, dans l'ordre : (A) `CREATE EXTENSION pg_trgm` + 13 index en `CONCURRENTLY`, (B) suppression de 5 doublons stricts, (C) suppression des index mono-colonne sur 4 colonnes techniques **jamais filtrées dans le code** (`IDSITE_MAITRE`, `ReceptionWS`, `TamponNumerique1`, `TamponChaine1`), (D) `ANALYZE` des grosses tables.

### 4.1 Contrôle de sûreté préalable (colonnes échafaudage)
La suppression des index de l'étape (C) repose sur le fait que ces 4 colonnes ne sont **jamais** utilisées en filtre/jointure/tri dans le code. Comme la prod tourne le même code, revérifier rapidement :
```bash
# Doit ne renvoyer AUCUNE utilisation en where/join/orderBy applicatif :
grep -rniE "IDSITE_MAITRE|ReceptionWS|TamponNumerique1|TamponChaine1" app/ resources/helpers/ | \
  grep -viE "//|/\*|\*" | head
```
Si une de ces colonnes est désormais utilisée, **retirer la colonne concernée** de `$colonnesEchafaudage` dans la migration avant de lancer.

### 4.2 Lancer (⚠️ `CONCURRENTLY` = potentiellement long sur gros volumes)
> Le `CONCURRENTLY` interdit la transaction : la migration a `public $withinTransaction = false`. Chaque instruction est isolée (try/catch), donc une erreur ponctuelle est journalisée sans tout arrêter.

```bash
php artisan migrate --path=database/migrations/2026_07_07_000000_add_optimisation_indexes.php --force
```
> `--path` = n'exécute QUE cette migration. `--force` = pas d'invite interactive en environnement de prod.

### 4.3 Vérifier
```sql
-- Les 13 index doivent exister et être VALIDES (aucun invalide) :
SELECT count(*) FILTER (WHERE indexname LIKE 'idx_opt_%') AS opt_crees FROM pg_indexes WHERE schemaname='public';
SELECT c.relname AS index_invalide
FROM pg_index x JOIN pg_class c ON c.oid=x.indexrelid
JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname='public' AND NOT x.indisvalid;   -- doit être VIDE

-- Comparaison du nombre total d'index avec la valeur "AVANT" (Phase 0) :
SELECT count(*) AS index_apres FROM pg_indexes WHERE schemaname='public';
```
Puis contrôler que les nouveaux index sont **réellement utilisés** (voir `database/sql/test_performance.sql`, section TEST A) : les `EXPLAIN (ANALYZE)` doivent montrer `Index Scan`/`Bitmap Index Scan on idx_opt_...`, pas `Seq Scan`.

### 4.4 En cas d'échec d'un `CONCURRENTLY`
Un `CREATE INDEX CONCURRENTLY` interrompu laisse un index **INVALIDE** (que `IF NOT EXISTS` ne recréera pas). Le détecter (requête ci-dessus), puis :
```sql
DROP INDEX CONCURRENTLY IF EXISTS public."<nom_index_invalide>";
```
et relancer la migration (idempotente).

**Rollback Phase 2 :**
```bash
php artisan migrate:rollback --path=database/migrations/2026_07_07_000000_add_optimisation_indexes.php --force
```
(`down()` supprime les 13 index créés et recrée un équivalent des index supprimés.)

---

## 5. Phase 3 — Triggers de réplication (tables techniques Laravel)

Retire les triggers `tr_replica_*` **uniquement** sur : `cache`, `cache_locks`, `sessions`, `jobs`, `job_batches`, `failed_jobs`, `migrations`, `password_reset_tokens`, `personal_access_tokens`, `replication_journal`.
La table `users` est **volontairement préservée** (réplication métier légitime). La détection est dynamique : seuls les triggers réellement présents sur ces tables sont supprimés.
*(En pré-prod, 6 triggers ont été supprimés : `failed_jobs`, `jobs`, `migrations`, `password_reset_tokens`, `personal_access_tokens`, `replication_journal` — les autres tables n'existaient pas.)*

### 5.1 Contrôle de sûreté préalable (spécifique prod)
```sql
-- Voir EXACTEMENT ce qui sera supprimé AVANT d'agir :
SELECT c.relname AS table, tg.tgname AS trigger
FROM pg_trigger tg
JOIN pg_class c ON c.oid = tg.tgrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_proc p ON p.oid = tg.tgfoid
WHERE n.nspname='public' AND NOT tg.tgisinternal AND p.proname='tr_replica_generic'
  AND c.relname IN ('cache','cache_locks','sessions','jobs','job_batches','failed_jobs',
                    'migrations','password_reset_tokens','personal_access_tokens','replication_journal');
```
- [ ] Confirmer que **seules des tables techniques** apparaissent (aucune table métier).
- [ ] Vérifier le driver de file d'attente en prod : si `QUEUE_CONNECTION=database`, la table `jobs` reçoit des écritures fréquentes → le gain est réel. Si cache/session sont en base (`database`), idem.

### 5.2 Lancer
```bash
php artisan migrate --path=database/migrations/2026_07_07_000001_drop_replication_triggers_laravel.php --force
```

### 5.3 Vérifier
```sql
-- Plus aucun trigger de réplication sur les tables techniques (doit = 0) :
SELECT count(*) FROM pg_trigger tg
JOIN pg_class c ON c.oid=tg.tgrelid JOIN pg_namespace n ON n.oid=c.relnamespace
JOIN pg_proc p ON p.oid=tg.tgfoid
WHERE n.nspname='public' AND NOT tg.tgisinternal AND p.proname='tr_replica_generic'
  AND c.relname IN ('cache','cache_locks','sessions','jobs','job_batches','failed_jobs',
                    'migrations','password_reset_tokens','personal_access_tokens','replication_journal');

-- Le trigger sur users doit TOUJOURS être présent :
SELECT tgname FROM pg_trigger WHERE tgname = 'tr_replica_users' AND NOT tgisinternal;
```

**Rollback Phase 3 :**
```bash
php artisan migrate:rollback --path=database/migrations/2026_07_07_000001_drop_replication_triggers_laravel.php --force
```
(`down()` recrée le trigger `tr_replica_generic()` sur les tables techniques présentes.)

---

## 6. Vérifications finales (checklist post-déploiement)

- [ ] `SELECT` des `pg_settings` = valeurs cibles de la Phase 1 (nouvelle connexion).
- [ ] 13 index `idx_opt_*` présents, **0 index invalide**.
- [ ] Nombre total d'index cohérent (baisse nette vs Phase 0 après nettoyage).
- [ ] `EXPLAIN (ANALYZE)` des requêtes clés → `Index Scan`, pas `Seq Scan` (`database/sql/test_performance.sql`).
- [ ] 0 trigger de réplication sur tables techniques ; `tr_replica_users` présent.
- [ ] Taux de cache toujours élevé (`> 99 %`).
- [ ] **Test fonctionnel applicatif** : ouvrir les écrans clés (liste Produits, recherche produit, stock/FEFO, factures) → aucune régression, aucun doublon de ligne.
- [ ] Surveiller les logs applicatifs et `pg_stat_activity` pendant les heures qui suivent.

### Outil de contrôle intégré
```bash
php artisan db:index-audit          # audit lecture seule (invalides / inutilisés / doublons)
```

---

## 7. Ordre de bataille résumé

```
0. pg_dump + photo de l'état  ───────────────►  (sauvegarde vérifiée)
1. Éditer valeurs tuning → psql -f optimisation_postgresql.sql → vérifier (nouvelle session)
2. Vérif colonnes échafaudage → migrate 000000 (index) → vérifier (0 invalide, EXPLAIN)
3. Prévisualiser triggers → migrate 000001 (triggers) → vérifier (0 technique, users OK)
4. Checklist finale + test fonctionnel
```

## 8. Rollback global (ordre inverse)
```bash
php artisan migrate:rollback --path=database/migrations/2026_07_07_000001_drop_replication_triggers_laravel.php --force
php artisan migrate:rollback --path=database/migrations/2026_07_07_000000_add_optimisation_indexes.php --force
# Tuning :
psql -d <BASE_PROD> -c "ALTER SYSTEM RESET ALL; SELECT pg_reload_conf();"
```
En dernier recours (peu probable) : restauration du dump de la Phase 0.

---

## Annexe A — Les 13 index créés
Recherche produit (GIN trigram, pour `LIKE '%…%'`) : `RubriqueRechercheMulticritere`, `DesignationProduit_IgnoreEPS`, `CodeCipProduit1`, `CodeCipProduit2`, `CodeEanProduit`.
Composites : `FACTURECLIENT(IDAGENCE,TypeFactureClient,DateFactureClient)`, `FACTURECLIENT(IDCLIENT,DateFactureClient)`, `COMMANDECLIENT(IDAGENCE,DateCommandeClient,EtapeCommande)`, `REGLEMENTCLIENT(IDCLIENT,DateEcheanceReglement)`, `REGLEMENTCLIENT(IDPROTOCOLECLIENT,DateEcheanceReglement)`, `STOCKAGENCE_LOTS(IDAGENCE,IDPRODUIT)`.
Partiels : `STOCKAGENCE_LOTS(IDAGENCE,IDPRODUIT,DatePeremption) WHERE StockTotal_Lot>0` (FEFO), `FACTURECLIENT(IDAGENCE,IDCLIENT,DateFactureClient) WHERE …` (factures à expédier).

## Annexe B — Doublons stricts supprimés
`WDIDX_DROIT_IDDROIT`, `WDIDX_DROIT_PROFIL_IDDROIT_PROFIL`, `WDIDX_DROIT_PROFIL_LIGNE_IDDROIT_PROFIL_LIGNE`, `WDIDX_RACK_IDRACK`, `idx_rubrique_recherche_multi`.

## Annexe C — Contacts / décisions à valider
- Type de disque + RAM du serveur de prod (pour la Phase 1).
- Responsable de la réplication (pour valider la Phase 3).
- Fenêtre de maintenance retenue.