It sounds like on smaller maps with limited resources, you might only get to see a fraction of what the game is capable of.
This is fairly close. It depends on the players, though. The smallest map is 9 planets, with the rival players only 3 jumps apart. So that one really doesn't have room for much and ends fast. Some "small" maps can have 15ish planets I think (I confess, it's been a while since I looked), and those take longer and let you experience more of the game without ending prematurely.
How big is an average map? (i.e. the ones most played) And how long do most games last? I guess that's my question.
It depends on the number of players, really. Most games I'm used to playing have been medium maps with ~30 or so planets for 4-6 people. Those games also depend on how people play. I've had some that ended in 2-2.5 hours, some that dragged on for 4.
About tech tree scaling, i guess what I meant is in a large map, will i still only get the largest units towards the end of the game? Or will the game end with me spamming these largest units?
Sins is unique in that the largest unit is not necessarily the one you want to build. It relies a lot of having a bit of everything (some things can be passed by, but in general a fleet composed of only 1-2 types of ships, excluding capitals, will not do well). So in that sense, it takes a while to research all the ship hulls, but even after you do you still do find yourself building the ones lower on the tech tree.
As far as the time it takes, if you're very diligent and research focused, you can probably knock out one of the two tech trees in 2.5-3 hours (research is split into civic and military). If you really don't need to build a lot of ships and can dedicate tons of resources to it, you can probably do it sooner. The large games last a lot longer than that, so you'll be playing the majority of the larger maps fully teched up.