On my first playthrough and quite enjoying it, question though.
If I keep building up sanctuary will it improve or will it always be a little settlement not worth spending too much time on?
The only thing that will improve by itself will be the population. Without a mod to change it, the limit is 10 plus your charisma. It will be affected by happiness to some extent, so you won't get the max population if the happiness is low.
For everything else, the limit is your time, patience and imagination in the real world and your resources in the gameworld. It will also depend on what mods and/or DLC you have installed and whether or not you have removed the settlement building limit.
Currently I have in Sanctuary:
Complete surrounding defensive wall at least ten feet high with a gated entrance and >1000 defence units of turrets all round. There are stairs on the inside of the wall at regular intervals as emergency escape routes.
An apartment building with 24 bedsits, each with a nice bed, lighting, containers, a comfy chair, a comfy sofa, a radio and some tables.
A house with a kennel in it for Dogmeat.
A large workshop with one of every crafting station except for power armour.
A large power armour warehouse with a couple of dozen suits and the power armour crafting station.
A building containing a shopping centre with one of each shop, a hotel with decent beds in a dorm room style and a pub with chairs and a pool table.
A purified water farm in the river producing ~500 purified water units.
A farm that can feed 36 people. I run a surplus to store grain in case of a bad harvest, purely for roleplay.
A library, full of comfy chairs in the reading section and desks, paper and pens in the writing section, plus half a dozen terminals. It also contains every magazine.
Two power stations and a complete power grid running right around the outer wall and to every building and inside every building (using the mat glitch to get the cables through walls).
When I get around to continuing development, Sanctuary will get a much better shopping centre/hotel/pub (it's basic vanilla wooden and steel shack components at the moment and not very big), a toilet block, a shower block, a laundry room, a print shop, a bowling alley, a basketball court, a museum (to me, of course, since I am the benevolent dictator and founder), a school, a research facility (for both gaining knowledge and finding more/better ways of making cloth, paper, ink, dyes, etc), a garden or two, a pool hall and anything else I think of. There's a
lot of space at Sanctuary.
I do have a whole bunch of settlement building mods installed though, as well as all of the DLC. It gives me more options. It takes a lot of play unless you cheat yourself resources because the costs rocket. My Dalton Farm settlement has cost me ~200,000 caps, for example, and it's a lot smaller than Sanctuary. That's in addition to the resources I've acquired from scavenging on the island and used in building Dalton Farm - it's ~200,000 cash to buy resources from shops. I don't cheat and the only game-altering mods I have installed are ones to stop the endless Preston Garvey quests and the silly attacks on settlements. Why on earth would 20 heavily armed and well armoured settlers inside a walled town with a defence of >1000 need me to go there to defend them against 5 raiders with pipe guns? The mods I have installed are for things like better textures for mattresses (why would you make new mattresses and then soak them in rotting bodily fluids to match the manky old ones that have been decaying for 200 years?) additional building parts that aren't game-breaking (slightly taller walls, for example, to give my settlers a bit more headroom in their homes) and more furniture and decoration. And plumbing. I want to rebuild civilisation and washing is part of that. None of my people have to go without washing, wear dirty clothes, poop in pits and hope they don't die of cholera.
So yeah, the main limit to settlements is how much you want to roleplay.
EDIT: If you want to play the game without doing much in the way of settlement building, I think it's still worthwhile building at least one or two settlements up enough for them to have all the shops (and settlers assigned to work them, of course). That gives you a lot more places to trade and it gives you an additional source of income because all of your shops pay tax to you. You'll find it under "Bottlecaps" in the settlement workshop in each settlement that you have manned shops in. You have to collect it from each settlement - there isn't a common store for it even between settlements linked by provisioning routes and it won't automatically transfer into your spendable caps.