I strongly encouraged MIL to buy the house next door to us when it came up for sale. It turned out to be a brilliant decision.
We saw her daily. So much easier to drop in for a coffee once a day than packing up to visit for a weekend every 6 weeks. The boys (7&5) adored having grandma next door and spent a lot of time with her, they had an amazing relationship. We were all on hand for each other if and when needed. She was more independent and less needy as she got older because we were just there. Aside from online stuff which I did, she paid for a cleaner and had meals delivered.
However, we had a brilliant relationship for nearly 30yrs prior to her moving next door. She was the kindest, loving, non interfering person. She never expected or demanded our time and was always happy to let the boys stay at hers. She never just came round to ours or assumed we were available 24/7.
We had honest conversations about what life would look like if she needed caring for. I was totally willing to provide any practical help, for her to be included in our family life but any personal care would not come from us - that would have been mortifying for her.
When she died aged 96 we had had 13yrs of happily living next door to each other and I can only say it was a great solution for all of us. It really can work and can make life less stressful than living miles away, especially when you have a young family. But it obviously depends on the type of relationship that you have in the first place. I loved my MIL, would I live next door to my own mother? Absolutely never in a million years.