My children are too young for school right now, but my wife and I constantly go back-and-forth on this exact issue. To a large extent, I think you have to consider each child's individual personality and how they are likely to respond to a high-pressure environment. I suspect there is (unfortunately) a strong genetic component to this—some people are able to shrug off others' expectations of them with ease, whereas for many "failure" in the eyes of their peers or family is mentally crippling.

There's also an environmental component on how a "high achieving personality" manifests psychologically (i.e., whether in a healthy or unhealthy way) that is due to upbringing and expectations. Personally, I've always felt a significant amount of pressure to achieve certain goals, but this pressure has always been self-induced / intrinsically motivated. I've never felt pressure from others to "succeed", and my parents always placed a strong emphasis on the actual learning rather than playing the grades game. As a result, my personal vision of "material success" (as opposed to the much more important "moral success") is quite different than society's: I want to achieve financial independence as quickly as possible so I can spend 8-10 hours a day working on moonshot research projects that would never be funded by a company or lab due to the high likelihood that the research goes absolutely nowhere. The fact that I ended up working at a FANG company is merely an incidental byproduct of what I consider an efficient path to achieving my primary objective—it was never the objective itself. I would gladly take a job as a window cleaner if I thought it helped me reach my goal quicker (perhaps a horrifying notion to many students at high achieving schools...)

On the other hand, my wife has told me that her motivation is primarily extrinsic. When she was in high school, her parents would take her siblings out to restaurants to celebrate their academic achievements (both were valedictorians) and leave her at home. Despite being very intelligent, she remains extremely insecure about the subject—her mom used to call her "the pretty one" and her sister "the smart one". This seems to have permanently damaged her self-esteem, and she told me she is often scared to try new things in life due to an intense fear of failure. It's hard to fail at something you never attempt, right?

We currently live in an area where the schools aren't considered that good by conventional standards, but we do have the ability to move when our children are old enough to attend school. I think we're going to take a wait-and-see approach. It's also worth considering that schools have a strong influence on what children consider culturally normal or socially acceptable while growing up, so there are non-academic considerations that we have to weigh as well. I know of many students who committed suicide while attending high achieving schools in Palo Alto or Cupertino. But I also know of students who ended up in low paying and dead-end careers due to a desire to stay near their family in areas of the country where merely graduating is an achievement. It's a delicate balance that's hard to get right, but the decision of where to send children to school is probably one of the most important ones that parents can make.