I've dealt with postgres DBs larger than that in size though with no stored procedures and have never run into such problems. Except for a single table in a single DB at one stop, and that was a special case of people being extra stupid.
Granted, DB size isn't the best metric to be using here in terms of performance, but it's the one you used.
Everyone tries to plan for a world where they've become one of the hyperscalers. Better to optimize for the much more likely scenarios.