PostgreSQL autovacuum est à la ramasse : on migre ou on tune ?

Le problème n'est pas PostgreSQL, c'est votre config. Les paramètres par défaut de l'autovacuum sont pensés pour faire tourner une DB sur un Raspberry Pi. Si vous avez du write intensif, il faut être beaucoup plus agressif.

Quelles sont vos valeurs pour `autovacuum_vacuum_scale_factor` et `autovacuum_vacuum_cost_limit` ? Si c'est les valeurs par défaut (0.2), ton vacuum ne se déclenche que quand 20% de la table est sale. Sur une table de 1 To, c'est suicidaire.

Le scale factor c'est une fausse solution sur les grosses tables. Il faut passer sur un `autovacuum_vacuum_threshold` fixe. Et pour les I/O, si vous êtes sur du disque cloud classique, vous êtes probablement étranglés par les IOPS.

Le MVCC de Postgres est certes lourd car il réécrit la ligne entière (Write Amplification), mais c'est ce qui garantit votre cohérence. Passer sur Cassandra pour régler un problème de config, c'est comme changer de voiture parce que le cendrier est plein.

Cassandra pour des logs c'est pas idiot, c'est du LSM-tree, c'est fait pour le write. Mais avant d'en arriver là, est-ce que vous avez partitionné vos tables ?

Le partitionnement par temps permet de drop des partitions entières sans jamais trigger le vacuum. C'est la base pour de l'audit ou du log.

CREATE TABLE audit_logs (id serial, data text, created_at timestamptz) PARTITION BY RANGE (created_at);

Le partitionnement c'est bien, mais ça ne règle pas le bloat des index sur la partition active. Si tu as 50 index sur ta table parce que tes devs veulent pouvoir requêter sur chaque champ JSONB, ton vacuum va ramer de toute façon.

Il faut réduire le `autovacuum_vacuum_cost_delay` à 1ms ou 2ms pour que le worker ne s'endorme pas trop souvent.

Et saturer les disques encore plus vite ? Super conseil. Le vrai levier c'est le `maintenance_work_mem`. Si tu ne donnes pas assez de RAM au vacuum pour stocker les IDs des tuples morts, il va devoir faire plusieurs passes sur les index. C'est ça qui tue tes perfs.

grep maintenance_work_mem /etc/postgresql/15/main/postgresql.conf

On est à 256MB pour `maintenance_work_mem`. C'est peut-être trop bas effectivement. On a des index GIN sur le JSONB, j'imagine que ça n'aide pas pour le bloat.

Le GIN c'est l'enfer à maintenir pour le vacuum. Chaque update sur le JSONB force une mise à jour massive de l'index. Vous devriez regarder du côté de `pg_repack` pour reconstruire les tables online sans lock, ça sauve la vie quand le bloat est déjà là.

pg_repack -t audit_logs

Attention avec `pg_repack`, ça génère un volume de WAL monstrueux. Si vous avez de la réplication, vous allez saturer votre bande passante réseau et créer du lag sur les replicas.

Le problème de fond c'est : pourquoi stocker des logs bruts dans une DB relationnelle ? Postgres n'est pas un puits sans fond.

Parce que c'est pratique pour les joins métier. Arrêtez de vouloir tout sortir de la DB dès que ça gratte un peu. Tuning d'abord, migration ensuite.

Passe ton `autovacuum_max_workers` à 8 ou 10 si tu as les CPUs pour, et surtout augmente `vacuum_cost_limit` à 1000 ou plus. La limite globale est partagée entre tous les workers, donc plus tu as de workers avec une limite basse, moins chacun travaille.

Il y a aussi l'option des colonnes `toast`. Si tes données JSONB sont grosses, elles partent dans le storage TOAST et le vacuum est moins pénible sur la table principale. Mais si tu updates souvent, c'est mort.

On n'a pas parlé du `fillfactor`. Sur les tables avec beaucoup d'updates, descendre le `fillfactor` à 80 ou 90% laisse de la place dans les pages pour les nouvelles versions des lignes (HOT updates). Ça évite de toucher aux index.

ALTER TABLE audit_logs SET (fillfactor = 85);

Le HOT update c'est le Graal, mais ça ne marche que si tu ne touches pas aux colonnes indexées. Si ton `created_at` est indexé et que tu l'updates (ce qui est rare pour un log, certes), le HOT update tombe à l'eau.

On n'update jamais les logs, on fait que du insert. Donc le bloat vient uniquement des suppressions de vieilles données (on garde 30 jours) et des index GIN.

Si tu fais du `DELETE` massif, cherche pas plus loin. C'est l'anti-pattern absolu sur Postgres. Utilise le partitionnement et fais des `DROP TABLE` sur les vieilles partitions. C'est instantané et ça ne génère aucun bloat.

Exact. Le `DELETE` c'est juste du travail en plus pour l'autovacuum. Le partitionnement c'est la seule réponse viable pour des données temporelles. Tu peux même automatiser ça avec `pg_partman`.

Bon, on est d'accord pour une fois. Partitionnement par jour ou par semaine, `maintenance_work_mem` à 1GB minimum, et on ajuste les coûts de l'autovacuum.

Et vire tes index GIN si tu peux utiliser une recherche plus simple, ou limite les aux clés dont tu as vraiment besoin dans ton JSONB.

C'est noté. On va mettre en place `pg_partman` pour arrêter les `DELETE` sauvages et on va booster la config de l'autovacuum selon vos chiffres. Je vais aussi tester le `fillfactor` sur nos autres tables transactionnelles. Merci pour le coup de main, vous m'évitez une migration foireuse sur Cassandra !