Why do you say the most competitive? Just b/c most programs only take a small number of residents doesn't mean they're more competitive than a program that takes more. There's usually not a lot of people applying for those spots, relative to the number of people applying for other spots in, say, derm or plastics. I think someone mentioned in the neurosurg forum that in one of the years an extra 10 people applying (out of 16,000+ 4th yr med students) would change the match rate by ~5%. So it's a very small community.
It can pay off, depends what area you end up specializing in. We just had a DO neurosurg/oncology fellow speak with us the other week and he mentioned that in a lot of cases, that he deals with, they're just allowing people to live longer with their terminal illness. Definitely "life changing", but not a field where you should expect complete turn arounds every day.