This is a big topic! I'm not for unfettered no-regulation zoning, I think we have established that. I'm going to ramble a bit, maybe get closer to where we disagree...
You described enjoying the wildlife in your neighborhood. I recognize that it's a shame when a good thing becomes popular, and the influx of too many people ruin it. I've seen that happen with inner city neighborhoods (gentrifying) and beach communities (tourists). Sometimes this gets complicated. For example, the beach I mention is still there only because of the millions of dollars funded almost entirely by the state, county, and nearby city, properties owners enjoy federal flood insurance subsidized by everyone, and despite all that the people that live their think it is their private beach and use every legal trick in the book to keep others out, which seems wrong to me. The gentrified neighborhoods I've seen are part of a city, there's much interconnectedness and less friction to moving between neighborhoods, so it seems popular neighborhoods come and go over the decades, and that is going to happen no matter what, trying to stop that risks causing market distortions like in San Francisco, which hasn't been good for the character of the city.
I wonder how long is reasonable to try to freeze a place in time. Going back several generations there were a lot less people in this country. People were a lot less mobile. Goods were produced closer to home, and the jobs that go along with that. The culture changes, faster now than ever. Maybe people now don't want to deal with other people? I don't know about trying to legislate good neighbors, that can only go so far without causing other problems.
As an aside, my ideal rural small town would have a center with small apartment buildings in a shopping district, surrounded by a half mile of neighborhoods and greenways, surrounded by nature. This is what small-town Spain is like. Farmers commute from their apartments to their fields. The city streets are like an extension of ones livingroom, people able easily gather together because they are near to each other. Not saying I'd legislate that, but I'd like to see such things be possible. The regulations we have certainly don't incentive that.
I'm in favor of letting a place slowly grow. I think overzealous regulations cause distortions like the popularity of air bnb, and developers who will only build luxury units due to the costs of working through the red-tape. There's not enough housing where people want it, close to the jobs. I blame suburban sprawl more than small towns. Who knows what work from home will do? Maybe everyone will move to small towns. Or maybe suburbs will decrease in value as they are abandoned for more vibrant city communities. You mention wells and infrastructure limitations, and I recognize that. I'm not for growth that would outpace genuine practicalities. I know enough about this to know that many of today's suburbs may not be able to sustain their infrastructure, the density is too low, there are too few people per mile of road, water, electric, and sewer to afford the upkeep, they already need to be subsidized by more dense areas. Cities are already subsiding suburbs.
I believe that a significant factor in all this is that houses were not considered to be appreciating assets in the past. This has now become primarily a UK/US/CA/AU/NZ thing. Japan is not like that - houses there are no more considered an appreciating asset than cars are in the US. They build them to last 20 years, and build new ones frequently. Land is not so much an investment given that the population is declining and will continue to do so. I was glad when Trump raised the standard deduction, as a back-handed way of phasing out the mortgage interest deduction. I'd also like to see a land value tax - where a city taxes the land based on what the market wants to build on a piece of land, so someone expecting to keep their 5 acres near downtown has to pay dearly for that. I'd like to be sure that corporations don't get more involved in housing supply issues than they already are, they will lobby for more regulatory capture.
The variance for a grandma cottage, or building out a duplex as an extension out the back of the house or raised roof, is subject to approval from folks who are primarily motivated by the bulk of their retirement saving being invested in their house. Most places simply won't approve anything like that. You have to convince everyone that it is in their best interests, and they don't care so much about you. The lengths some people go to can be despicable, their opposition being a thin veneer over their concerns over resale value, some of it not well reasoned. I'm not saying that is always the case, but it happens a lot.
You mentioned supporting more density in cities, and I would agree.
You also mentioned tiny houses, which I know a bit about. You might find it surprising that there are very very few populated places in this country where it is legal to live in one, and not a lot of unpopulated places, either. Some places like Austin are allowing them as accessory dwelling units, because Austin is one of those places that has priced out the people who made it an attractive place to live (Austin is about the music, and musicians can't afford to live there anymore), I think that it is Portland or somewhere nearby is experimenting with "pocket communities", where a cluster of tiny houses can be grouped together around a common space on a couple/few acres. There is a dying town in Texas that welcomes tiny houses on single family lots, desperate for citizens. There is a town in Florida that allows them on single family lots. NY has an interesting hunting cabin loophole. Outside of that there are counties in some western rural areas with minimum zoning laws, I've heard of that in Iowa (minimally they want to know that you are treating your sewage properly). And that's about it. But most people who want a tiny house would like to live near other people like themselves who are not so house-focused. It's difficult to check on this, one has to search each municipality individually, and read through their 100 page zoning documentations. The tiny house sales people don't tell their customers that eventually a neighbor will call the town zoning inspector who will promptly give them a notice to vacate. You can permit it as a shed and use it as a writer cabin, but not live in it. More and more localities are making it illegal to camp on your land, even rural land, or live in anything except a house made specifically as detailed in the codes (the inspectors don't know what to pass it otherwise), of a specific minimum square footage, with minimum parking and garage spaces, with limits on how many unrelated people can live there. It's stifling and hard to escape. I think it is good for a society to have room for people who want to do something different.
EDIT: On the subject of democracy, and a majority versus super majority, voting for exceptions, etc. I don't believe that "there are more of us", no matter how many more, is a valid justification to impose on even a single person. Unless a decision is unanimous there should be morally valid reasons to impose on those who disagree, not just force of numbers. I stand by my remark that imposing on a minority with no other justification is tyranny. In the case of zoning there are relevant reasons for limitations to discuss.
Like I said, it's a big topic, and this has been an interesting conversation. I'm still in favor of Japanese style zoning, allowing for measured growth, set at a federal level, eliminating some of the wrong incentives individual neighbors have to impose on others, and giving people more flexibility in other areas. I don't know if you have any other thoughts, or if we have reached an impasse?