PostgreSQL – 1) Collecte & cadrage

1.1 Inventaire matériel & OS

Matériel

Élément	Détails à collecter
RAM	Totale (GiB) • Bande passante • ECC • Ratio data/RAM visé
CPU	cœurs/threads • freq • NUMA on/off • caches L2/L3
Stockage	NVMe/SAS/SATA/EBS • RAID/nombre de disques • write-cache • TRIM
Réseau	MTU, latence inter-tiers, débit, CPU pinning IRQ
Type	bare-metal / VM (vCPU, réservations) / cloud (noisy neighbors, crédits)

OS & kernel

Distribution & version (Ubuntu/Debian/RHEL/Alma), uname -r
THP/HugePages : Transparent Huge Pages = disabled recommandé pour PG
tuned-adm / sysctl (vm.swappiness, dirty ratios, readahead)

FS & I/O

FS: EXT4 / XFS, options : noatime, discard (SSD), barrières.
Scheduler I/O : mq-deadline (NVMe) ou none (si contrôlé par device).
Alignement partitions, write-back cache contrôlé, file d’I/O (nr_requests).

Commandes utiles

                        # CPU/NUMA
                        lscpu && numactl --hardware
                        # RAM / hugepages / THP
                        free -h; cat /sys/kernel/mm/transparent_hugepage/enabled
                        grep -i huge /proc/meminfo
                        # Disques & FS
                        lsblk -o NAME,ROTA,SIZE,MODEL,MOUNTPOINT,FSTYPE
                        blkid; mount | grep -E 'xfs|ext4'
                        cat /sys/block/nvme0n1/queue/scheduler
                        # Kernel & tuned
                        uname -r; sysctl vm.swappiness vm.dirty_ratio vm.dirty_background_ratio
                        tuned-adm active
                        # Réseau
                        ip -br a; ethtool -S <iface> | head

1.2 Profil applicatif & charge

Catégories

OLTP : petites transactions, forte concurrence, latence p95 stricte.
OLAP : scans volumineux, agrégations, débit prioritaire.
Mixte : isoler via schémas/DBs, queues, planification.

Taille & ratios

                        -- Taille DB & "actif" approximatif
                        SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;

                        -- Tables "chaudes" (accès lecture/écriture)
                        SELECT relname,
                        n_tup_ins + n_tup_upd + n_tup_del AS writes,
                        idx_scan + seq_scan AS reads
                        FROM pg_stat_user_tables
                        ORDER BY writes+reads DESC
                        LIMIT 20;

                        -- Ratio data/RAM cible (ordre de grandeur) :
                        --   actif ≤ 0.5 × RAM -> latence plus stable

I/O & pics

Profil I/O : aléatoire vs séquentiel, read-heavy vs write-intensive.
Pics : connexions simultanées, req/s, batchs & ETL, fenêtres CRON.
Durabilité : synchronous_commit (on | remote_apply | off) selon RPO/RTO.

                        -- Concurrence & connexions
                        SELECT now(), numbackends FROM pg_stat_database WHERE datname=current_database();

                        -- Requêtes lentes (si pg_stat_statements activé)
                        SELECT query, calls, total_time/calls AS avg_ms
                        FROM pg_stat_statements
                        ORDER BY avg_ms DESC LIMIT 20;

                        -- WAL & checkpoints
                        SELECT * FROM pg_stat_bgwriter;

Exigences

RPO/RTO & politique de sauvegarde (base + WAL) • éventuelle HA (sync/async).
Fenêtres de maintenance (VACUUM/REINDEX/ANALYZE), budgets temps CPU/IO.

1.3 Synthèse & décisions

Volet	Décisions / critères
SLO	p95 latence, débit req/s, budget (CPU/IO/mémoire), coût cloud
Durabilité	`synchronous_commit`, taille & rotation WAL, politique checkpoints
Architecture	Réplication (sync/async), pgbouncer, partitionnement, stockage
OS/FS	Scheduler I/O, noatime, THP off, tuned-adm & sysctl
Risk log	Noisy neighbors (VM/cloud), CPU steal, file d’attente I/O, bloat

Pack de commandes d’inventaire (export Markdown/JSON)

                #!/usr/bin/env bash
                # save as: pg_inventory.sh  — usage: ./pg_inventory.sh > inventory_$(hostname).md
                echo "# Inventaire $(hostname) — $(date -Is)"
                echo "## OS / Kernel"
                lsb_release -a 2>/dev/null || cat /etc/os-release
                uname -r
                echo "## CPU"
                lscpu; numactl --hardware
                echo "## RAM / THP / HugePages"
                free -h
                cat /sys/kernel/mm/transparent_hugepage/enabled
                grep -i huge /proc/meminfo
                echo "## Disques / FS / Scheduler"
                lsblk -o NAME,ROTA,SIZE,MODEL,MOUNTPOINT,FSTYPE
                blkid
                mount | egrep 'xfs|ext4'
                for d in /sys/block/*/queue/scheduler; do echo "$d : $(cat $d)"; done
                echo "## Réseau"
                ip -br a
                echo "## tuned-adm / sysctl"
                tuned-adm active 2>/dev/null || true
                sysctl vm.swappiness vm.dirty_ratio vm.dirty_background_ratio 2>/dev/null || true
                echo "## PostgreSQL"
                psql -Atc "SELECT version();" 2>/dev/null || echo "psql indisponible"
                psql -Atc "SHOW data_directory; SHOW synchronous_commit;" 2>/dev/null || true

Schéma JSON – Entrées standardisées

{
                "host": {"name": "", "env": "prod|staging|dev"},
                "hardware": {
                "ram_gib": 64, "cpu_cores": 16, "cpu_threads": 32, "numa": true,
                "storage": [{"type":"nvme","count":2,"raid":"1","model":""}],
                "network": {"mtu":1500,"latency_ms":1.2}
                },
                "platform": {"type":"bare-metal|vm|cloud","provider":"", "credit_cpu": false},
                "os": {"distro":"Ubuntu","version":"24.04","kernel":"6.8","thp":"disabled","hugepages":{"enabled":false}},
                "filesystem": {"fs":"xfs","mount_opts":["noatime"],"scheduler":"mq-deadline"},
                "workload": {
                "profile":"oltp|olap|mixte",
                "active_dataset_gib": 120, "total_dataset_gib": 400,
                "io_pattern":"random-read|random-write|sequential|mixed",
                "read_heavy": true, "write_intensive": false,
                "peaks": {"max_conn": 600, "req_per_s": 2000, "batch_windows":["02:00-04:00"] }
                },
                "slo": {"p95_ms": 120, "throughput_rps": 1500, "budget_note":"", "cost_ceiling": null},
                "ha": {"rpo_seconds": 0, "rto_minutes": 5, "sync_commit": "on|remote_apply|off"},
                "notes": ""
                }

Ce schéma sera consommé par la brique 12 pour générer un postgresql.conf suggéré + un runbook.

Checklist & pièges (pré-tuning)

THP désactivé, hugepages non forcées pour PG (sauf cas expert).
NUMA : interleave ou affinité cohérente (éviter migrations mémoire aléatoires).
FS en noatime, scheduler adapté (NVMe: mq-deadline/none).
Write-cache contrôlé (batterie/BBU/RAID) ; TRIM/Discard vérifié sur SSD.
Sur VM/Cloud : CPU steal time, crédits CPU, EBS burst balance, noisy neighbors.
Mesures de base collectées 24–48 h (pg_stat_statements + pgbadger) avant tout tuning.
Définir SLO p95 et objectifs de débit avant de toucher aux paramètres.
Valider RPO/RTO et impact de synchronous_commit sur la latence.

🐘 1) Collectes & cadrage

Inventaire matériel & OS

Profil applicatif & charge

Synthèse & décisions

Scripts d’inventaire (Bash)

Schéma JSON – Entrées standardisées

Checklist & pièges