Since you’ve asked for real-world experiences:
I attended a midwestern state flagship that was a safety and was admitted to the honors college with enough merit aid to reduce the price by about 45% (in the days before ubiquitous merit “discounts”). By this calculus, I might have been considered a bigger fish. My core social group, however, was made up of six people. Four of those individuals had won the full ride to the college and the fifth had been a finalist (it remains a competitive full ride scholarship…think Forty Acres or Robertson type scholarships). In comparison with that group, I was not an apex predator in the pond.
But I had no issues with self-confidence (at least with respect to my academic/intellectual abilities), and college was a blast. I was surrounded by intellectually curious people, and not just about their own areas of interest, but about improv comedy or musical history or avant-garde art or international folk dancing. So being in an environment where everyone is open and curious about a variety of things was really just intoxicatingly wonderful.
But, although the quality of the college experience is what I was discussing, I suspect you were more interested in the “outcomes” portion of the question, and @Twoin18’s comments here really ring true:
After graduation, one person went to the state med school (and remained a top student through med school and residency)). One person went to one of the top medical schools (usually will come up as one of the top two that people think of). One person went to a T14 law school. One person went to an elite music conservatory. One person went to an Ivy+ seminary and another went to an Ivy+ PhD program. After divinity school, the one person went back to their state where they are known in the state’s religious circles, but did not pursue anything full-time with a bigger zone of influence. The T14 law grad has spent their career in a public defender’s office in a big coastal city. The person who stayed in-state for med school has remained in-state. The one who went to an Ivy+ doctoral program left after a couple of years to teach at high-poverty public schools. Only two of the six would go on to what might be the outcomes most desired by highly competitive CC-students…the one at a top med school has been practicing in a popular coastal city and the one who went to conservatory is now a conductor in a big coastal city.
Nearly everyone in this group could have gone on to have more “impressive” outcomes, but for nearly everyone in the group, the goal was to come out with happy outcomes. Doing work that we found fulfilling and that would provide a livable wage and live where we wanted to live was what mattered.
So, how does this contrast with someone in a big pool? I have a sibling who attended a SHYMP school followed by a non-Top-X med school and has been practicing medicine in a popular metro. I think my sibling might have been more of a big fish in extracurriculars at the school, but not necessarily an academic big fish there.
In what I’ve observed of my sibling and some of my sibling’s friends from the SHYMP school, there is a much greater emphasis on the appearance of outcomes. There’s much more talk about enviable experiences they’ve had, or the brand of their car, or where they went on vacation, or the names of the schools their kids attend. (One of the sibling’s kids went to a SHYMP and the other went to a SAW school, but my sibling felt the need to make sure the family understood how great a SAW liberal arts college was since it didn’t have the same name recognition as the SHYMP.) I doubt that all people who went to a Top X school are like this (I’m sure they aren’t), but I think this type of attitude is much more prevalent coming out of these institutions than from most “small ponds.”
I have found that people from flyover country tend to be more modest about things that might be “impressive” to others. They mention having gone to school in a certain state rather than naming the school, or having experience in the health field, rather than naming their MD specialty.
There’s nothing inherently right or wrong with one style over the other, but it is something I have noticed.