I just took over a medium-sized postgres database and the team has been surprised with many tables hitting their auto-incrementing id int max limits which triggered unscheduled downtime to fix.
Does anyone know of a simple tool to help check all tables/columns and easily find which are at risk of hitting the int max limits? I'm sure we can come up with a script to check, but I remember reading somewhere of a tool to help me identify potential issues exhaustively.
As mentioned above, if you're using pg 10+ you can run something similar to the code bellow to find sequences and how much (in %) they're from their limit.
SELECT LAST_VALUE * 100 / max_value AS percent_from_limit, *
FROM pg_sequences
WHERE LAST_VALUE IS NOT null
ORDER BY percent_limit desc
As you mention auto-incrementing, it sounds like you want to check for sequence exhaustion - check_postgres has a sequence check which will warn about this.
Sequence metadata is stored separately from the associated tables, so is relatively easy to access without having to check each table individually. If you're using PostgreSQL 10 or later, the pg_sequences view provides a useful overview of the current state of all sequences. The default sequence naming makes it obvious which table/column the sequence is associated with (though it's also possible to create a query which returns the actual table/column name as well).
