I had a 2 gallon fish bowl that I did for a contest, and currently a 5 gallon tank and a 10 gallon tank for 5 and 4 years, respectively, in the nano range and 50 and 75 gallon in the medium range. In the past I have had a 150 gallon tank and a 350 gallon tank. The 2, 5 and 10 gallon tanks are a joy to take care of in that I can wipe the walls, baster the medium to stir it up and do 80-90% water changes in the 2 and 5 gallon tanks in 5 minutes (once a week) and they look brand new, but chock full of corals. The 10 gallon I do about an 80% change every week in 10 minutes and again, it looks brand new. For these no dosing, no skimmers, no anything else other than lights (AI Primes) heaters and powerheads. The 50 and 75 gallon tanks are very manageable but take an hour each week and a couple of hours each once every month to maintain in top shape. There are many more choices for livestock. The 150 and 350 were both far too much work for my tastes and time. The water changes were less extensive, about 10% per week but the equipment far more elaborate and all of the equipment required regular care and maintenance. They were both much more work per gallon than the nano's and a good bit more than the 50/75s. Of course livestock choices are even wider in the larger tanks.
If you are in for a lot of thought and maintenance the larger the tank the more spectacular the possiblities (I used to enjoy the mechanical side of things so they were even fun for several years). But if time or interest in maintence is lacking the small tanks spend an enormously larger fraction of their time being enjoyed than maintained. And there are lots of corals and fish that look great in them that would be lost in a large tank. In my 5 gallon I have a small colony of green banded gobies (they started out in the 2 gallon bowl and were transferred to the 5 when I tore down the bowl. ) They are front and center in this small tank but in even a 50 gallon tank they would be lost. But then, if you want some tangs or angels or triggers, then you are definitely talking 150 and above for the more docile species and larger yet for many species.
Many years ago (more than 40) I cared for an 800 gallon tank that included an imperator angel and a clown trigger, as well as several large tangs. If you are into fish, the imperator and clown are truly the most spectacular two aquarium fishes I have ever cared for and to this day the most beautiful I have ever seen. I feel nothing but great enjoyment looking at those two species (and all other larger angels and triggers). I constantly think I should set up a 10 foot tank before I die to have one of each of these two. Then I think of the work and decide, nah, I'll ogle the imperator at the LFS and call it a day.
I have 3 neighbors with large tanks, one has a 250 gallon, one has a 400+ gallon and one has twin 800 gallon tanks. The guy with the 250 cared for it himself for two years and decided to leave it empty for a while to deal with burnout - it is still empty in his living room 10 years later (a popular topic with his wife). The guy with the 400 started out caring for it but now the LFS (which specializes in caring for larger tanks) spends an hour or two on it each week. The husband/wife with the twin 800 gallon tanks have never touched them. They were set up by the LFS and maintained by the LFS - they are the most content large tank owners I have ever met, they have had the tanks up around 15 years now.
All sizes of tanks can be, and I have found are, very enjoyable. For me, my commitment to time for maintaining the tanks has dictated 75 gallon as the absolute largest I will do now and going forward - but I do miss the imperator and clown.....