Back in the mid 80s, our family did OK with a Chevy Celebrity wagon with a 4 cyl engine. It wasn’t very sporty but carried the load and got good fuel economy.
For a vacation trip we had to load a carrier on the roof, but that wasn’t a big deal.
My daughter (now with her own family) pointed out that a station wagon based on the new Chevy Malibu would be a pretty nice ride.
Apparently hardly anybody wants decent-sized station wagons. Mazda had a wagon version of the Mazda 6 - the new model has no wagon version, probably because I don’t think they really sold very many of them. A few companies (mostly European ones, where wagons are still fairly popular) do a pretty good business in wagons, but the Japanese have largely abandoned them. I would have loved a new Honda Accord wagon, but they don’t have one in north America - the european Accord is a little smaller than ours, but they do have a wagon version.