We "stop" where it is natural to, like any other place in human history: lack of resources, lack of space*, there's better places to move to, etc. And if the only thing holding back the Bay Area's development is artificial zoning laws -- we are certainly not at its "natural" equilibrium.
While CA has had its generational water supply issues, only 10% of it is used for residential purposes; 90% of it is used for agriculture. Adding more people in SF, or Palo Alto or Berkeley, does not put special strain on CA versus anywhere else. Not to mention, single family homes often consume _much_ more water per family than those in apartments or condos (yards, many bathrooms, etc).
*Lack of space, for what is a _city_, is not "it's too populated here for me to have a backyard without paying a ton of money"