All depends on your needs really, also pricing. Not sure where you are located but here in the Suburbs of Toronto, a 3000 sqft detached house goes for about $700,000 canadian dollars. I prefer the detached, more space, bigger backyard and your not sharing a huge house with people (i know houses are sepearated but still).
get 1 acre of land with a 2 story house, 3 bedroom and 2 baths....good enough for small to large family.
2500 - 3200 square feet is a decent size house for someone on a tight budget. I also recommend sourcing at least 10,000 sqare feet for the land. This is sufficient for a 3 bedroom, even 4 bedroom house with at least 2 bathrooms.
put it this way, if you have rooms that you forgot existed, you have too many rooms. if no one is complaining of lack of space, then you are good. no fights for washroom, you are good. point is, if you need more space or have to much space, you will know.
It all depends on the size of your family I think A big house is nice to imagine, but may be impractical for some Personally, I think 2000 sqft would be comfortable for a small family