Small lights look cheap the second you plug in the wrong bulb. A tiny lamp with a cool white LED stuffed into it reads like a hospital corridor, not a cozy corner, and that one mistake will put you off the whole category. Get the bulb warm, 2700 kelvin, soft and a little golden, and the smallest light in the room suddenly does more for the mood than the overhead fixture ever did.
They age fine if you treat them like furniture, not like gadgets. A small lamp or a string of little bulbs will take a few years of regular evening use without complaint, the shade might fade a shade lighter if it sits in direct sun all day, which is the one real risk, so turn it or move it every so often if your window gets strong afternoon light. Dust them weekly with a dry cloth because small shades and little glass globes show dust fast, more than a big floor lamp does, and wipe the base occasionally if it's brass or painted metal. Cords are the part people forget. Check them once a year, especially on anything that gets moved around or sat near a radiator, because that's where the fraying starts.
In an ordinary room, small lights do their best work in the spots a big lamp can't reach, a side table, a bookshelf, the top of a dresser, a windowsill at dusk. I've got a little amber glass lamp on my nightstand next to a deep green wall and it glows like the room's holding its breath in winter, warm and low, nothing competing with it. They pair beautifully with color you've already committed to, a mustard throw, a terracotta rug, anything with some warmth in it, because the light itself usually leans gold and plays nicely with warm tones. Group two or three different small lights at different heights and the room stops looking like it's lit by one flat source, which is honestly the whole trick.
Where they go wrong is anywhere they're asked to do a big light's job. One small lamp trying to light an entire living room at night will just look sad and underpowered, and a cluster of tiny fairy lights taped up as the only light source in a bedroom reads more dorm room than considered. Keep them as the extra, not the main event, and they'll never look like they're struggling.
Before you buy, measure the surface it's sitting on, not just the lamp, because a six inch base on a narrow windowsill looks like an accident waiting to happen.