Useful information above but it's also worth noting that there are many small (or even growing!) college towns with populations wayyyyyy smaller than 400k. A population that size puts you well within the top 50 largest cities in the US. Those can feel small compared to cities like NYC but can feel big compared to towns like Ithaca NY or Providence RI both of which have great schools!
I'm not in grad school yet but I have lived in several cities with population sizes between 300-700k they all provided great career opportunities in psych and I personally never felt bored or without something to do. In a town with a population under 200k, I might start to worry about a lack of career enhancement stuff (and not having much to do for fun) but along the lines of what @eternallyephemeral said, I think it depends a lot on the institution and your personal career goals/interests.
Generally I think the best way to get a sense for what it will be like in another city is to compare it to where you grew up and/or where you live now. If you're going to a city smaller than where you are now, you'll probably feel like there is less to do for fun, people are friendlier but maybe less open to "unusual" lifestyles, and the public transit is no good. If you're moving to a city bigger than where you are now the opposite will probably happen. Everything is relative!