For sure there are tradeoffs for big projects that don't make sense for small ones. But there are also times where big projects need a tool that's "just better" than what small projects need, and once that tool has been built it can make sense for everyone to use it. I think good, strong, convenient version pinning is an example of the latter, when the tools are available. That was the inspiration for the peru tool (https://github.com/buildinspace/peru).
Any tiny open source project benefits from a reproducible build (when you come back to it months later) and also new versions (with fixed vulnerabilities, and compatibility with the new thing you're trying to do).
A certain amount of reproducibility - a container, pinned dependencies - gives such large reward for how easy it is to achieve that it absolutely is worth it for a tiny open source project.
Worrying about the possibility of unavailable package registries and revoked signing keys, on the other hand, probably isn't.
It's a trade-off. But you certainly don't need to be Google-scale for some of it to be very worth your while.