What does size of coefficients have to do with multicollinearity or overfitting? In the section on Ridge Regression (source: Elements of Statistical Learning by Hastie, Tibshirani, Friedman) :

When there are many correlated variables in a linear regression model, their coefficients can become poorly determined and exhibit high variance. A wildly large positive coefficient on one variable can be cancelled by a similarly large negative coefficient on its correlated cousin. By imposing a size constraint on the coefficients, this problem is alleviated.

From Hands-on Machine Learning with Scikit-Learn, Keras, and TensorFlow by  Géron:

As we saw in Chapters 1 and 2, a good way to reduce overfitting is to regularize the model: the fewer degrees of freedom it has, the harder it will be for it to overfit the data... For a linear model, regularization is typically achieved by constraining the weights of the model. We will now look at Ridge Regression, Lasso Regression, and Elastic Net, which implement three different ways to constrain the weights.

In the 2 above paragraphs, ridge regression is described as a solution to multicollinearity and overfitting, two very different things. We can have a model that overfits the training data because it includes an extraneous - uncorrelated with all other variables in the model - variable that simply is not part of the data-generating process. And we can have a model that suffers from multicollinearity without overfitting, such as when the data generating process consists of a couple highly correlated variables.
In ridge regression, we first scale the data then add a penalty to the function we're trying to minimize so that instead of minimizing the Sum of Squared Errors we are minimizing that plus a multiple of the sum of the squares of the coefficients. I am struggling to see how imposing this penalty on the sum of the squares of the coefficients would help alleviate either multicollinearity or overfitting.
In the presence of multicollinearity, it is the t-statistics and not the coefficients that are inflated (I misspoke here; I certainly meant to say that standard errors are inflated, and t-statistics deflated). I don't see a relationship at all between multicollinearity and the size of the coefficients.
In the case of overfitting, it manifests itself in the presence of extraneous variables and hence extraneous coefficients. So a penalty along the lines of AIC, BIC (as a function of the number of coefficients in the model) seems to make more sense.
How is the sum of the squares of the coefficients related to multicollinearity and overfitting? Is ridge regression useful only when  overfitting is caused by multicollinearity which happens to be manifested as large coefficients of opposite signs? This seems like a very particular instance of overfitting which might not occur that often.

A couple of hints:

In the presence of multicollinearity, it is the t-statistics and not the coefficients that are inflated. I don't see a relationship at all between multicollinearity and the size of the coefficients.

Multicollinearity inflates standard errors and deflates t-statistics for a reason: the point estimates from different samples may be very different. And that implies they may be very large. (Try being very different without getting large...)

In the case of overfitting, it manifests itself in the presence of extraneous variables and hence coefficients. So a penalty along the lines of AIC, BIC seems to make more sense.

Use of ridge regression instead of OLS may very well improve the expected likelihood on a new observation, something that AIC (and probably BIC) is an estimate of. (To be precise, AIC is an estimate of twice the negative log-likelihood, but that maps one to one to the likelihood itself.)
