I think people like using small palettes because they don't want to have to think about what color to use when they draw something.
While that's a valid approach to using colors, just as using a random palette is valid, it only barely touches on the use of limited palettes.
There are many popular ways to select and use colors, but it is also easily invented.
I guess for a palette to do something well, it would have be particularly well suited to drawing particular things?
It depends on the purpose of the palette and the goal of the artist at the time.
Some purposes:
GlobalColors used to represent all objects in the image/world.
Can be broken down into sub palettes, using a small group of colors per object.
ObjectColors specific to a single object making it unique from other objects.
GroupColors used on several objects.
This creates a relation between them in the mind and eye.
ThemeColors used to represent a specific event/object/character/idea.
Some examples:
Event -> Christmas: Red + Green
Object -> Egg: White + Yellow/Orange
Character -> Santa Claus: Red + White + Black
Idea -> Magic: Rainbow!
MoodColors that evoke a certain emotional response.
These overlap into other moods and are easily repurposed, but some examples:
Anger: Red
Happy: Yellow
Sad: Blue
NaturalColors specifically chosen to represent an object as it is seen.
Drawing from life.
IconicColors chosen to represent an object as it is thought of/ known as.
Green grass, blue sky, brown dog.
Drawing from the mind.
I'm missing some purposes and each has many more properties, challenges etc.
It's worth investing some time into later.
anything made with that palette will ultimately end up looking like Final Fantasy IV
Only if the groups of colors are used in the same combinations and balances.
The meaning and usage of a palette can be tweaked.
It's a short step to take happy colors and draw something horrific.
They are a part of the fun of the medium. Thing is, people take them too seriously.
It's ok to take fun things seriously.

Closely analyzing a subject allows for ability to manipulate and be creative.
As a mostly graphics programmer I do a lot of color math.
While it is not always palette related I utilize some knowledge of color theory every day.