I live in a 2-bed flat at the moment and it's perfect.
But if money was not an issue, I'd love a huge, ancient, opulent manor on the verge of collapse. 12 bedrooms and a ballroom, and grounds for me to be a batshxt crazy lady in.
I guess it depends on the type of house. If it's a modern house, I'd want it to be small-ish. But a castle needs space to be a castle.
Edit: realised I didn't answer the question at all. I've lived in a big house and a flat, and the flat wins hands down. Safe, easier to clean, cosier. You can't quite mark your territory the same way in a big house.
Leonard Cohen (Bird on a Wire): I have tried in my own way to be free
Mrs Hudson (BBC Sherlock): Sherlock! The mess you've made!