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

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.

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.