Sequences close to overflow (int4)
Measures how much of each sequence is consumed relative to its max_value: an int4 sequence that overflows blocks every insert overnight.
Prerequisites
PostgreSQL 10+ (vue pg_sequences)
SQL
SELECT
schemaname || '.' || sequencename AS sequence,
data_type,
last_value,
max_value,
ROUND(100.0 * last_value / max_value, 2) AS pct_consomme,
CASE
WHEN 100.0 * last_value / max_value > 80 THEN 'CRITIQUE — migrer en bigint'
WHEN 100.0 * last_value / max_value > 50 THEN 'A SURVEILLER'
ELSE 'OK'
END AS statut
FROM pg_sequences
WHERE last_value IS NOT NULL
ORDER BY pct_consomme DESC
LIMIT 5;Result
sequence | data_type | last_value | max_value | pct_consomme | statut -------------------------+-----------+-------------+------------+--------------+---------------------------- public.events_id_seq | integer | 1985225472 | 2147483647 | 92.44 | CRITIQUE — migrer en bigint public.orders_id_seq | integer | 1240881934 | 2147483647 | 57.78 | A SURVEILLER public.audit_id_seq | integer | 310240112 | 2147483647 | 14.45 | OK public.customers_id_seq | integer | 8122044 | 2147483647 | 0.38 | OK public.invoices_id_seq | bigint | 44120023 | 9.22e+18 | 0.00 | OK (5 rows)
SQLAuditSéquencesCapacité