I have said it before and I am sure I will say it again: In D&D 5e specific beats general.
The Rules
General rule on spell effects (PHB p 204):
Unless a spell has a perceptible effect, a creature might not know it was targeted by a spell at all. An effect like crackling lightning is obvious, but a more subtle effect, such as an attempt to read a creature’s thoughts, typically goes unnoticed, unless a spell says otherwise.
So, in general, if the spell has a perceptible effect that effect is perceived; if it isn't then it isn't. However, as always, specific beats general: if the spell says something different then that takes precedence.
General rules on hp & damage (PHB p 196)
Hit points represent a combination of physical and
mental durability, the will to live, and luck.
Whenever a creature takes damage, that damage is
subtracted from its hit points. The loss of hit points has
no effect on a creature’s capabilities until the creature
drops to 0 hit points.
hp do not exclusively represent the creatures physical body - the also represent metal ability, bloody-mindedness and luck.
General rules on psychic damage (PHB p 196)
Damage types have no rules of their own, but other rules, such as
damage resistance, rely on the types.
Psychic. Mental abilities such as a mind flayer’s
psionic blast deal psychic damage.
From these, there are no general rules on how psychic damage works.
Advice on describing damage (PHB p 197)
Describing the Effects of Damage
Dungeon Masters describe hit point loss in different ways.
When your current hit point total is half or more of your hit
point maximum, you typically show no signs of injury. When
you drop below half your hit point maximum, you show signs
of wear, such as cuts and bruises. An attack that reduces you
to 0 hit points strikes you directly, leaving a bleeding injury or
other trauma, or it simply knocks you unconscious.
Now, this is advice so how you want to describe it is up to you but according to the guideline: above half hp - no visible sings, below half hit points - visible signs, 0 hp - direct damage.
Specific rule for Vicious Mockery (PHB p 285)
You unleash a string of insults laced with subtle
enchantments at a creature you can see within range. If
the target can hear you ...
The specific rule is the enchantments are subtle, no one would notice these, possible including the victim (particularly if they save). For describing damage, in the absence of the specific rule you fall back on the general.
The Ruling
Whatever you want is fine so long as you consistently give the players the information they need.
Some people play in meta-game way with hp and treat them purely as numbers with no description applied - "You do 3hp of psychic damage; he now has 8hp left".
Others treat it as pure description with little to no feedback on mechanical effects - "You lash out with your insults, he pauses momentarily and blinks before raising his battle axe and snarling at you."
Or you can take the middle road - ""You lash out with your insults, doing 3 points of damage; he shows no obvious signs of distress."
Whatever works for your group is good.
TL;DR
If you want to follow the advice in the PHB then I would suggest the psychic damage will attack "mental durability, the will to live, and luck" manifesting as glazed eyes, trembling, tears, sweating, unprompted flinching etc. for less than half hp and bleeding from ears, eyes and nose for 0hp.
Yes, this unbalances the spell
Vicious Mockery is not particularly strong in the damage department to begin with. By allowing it to be cast as a bonus action in exchange for the damage, you're essentially giving your player a way to impose disadvantage on one attack every turn, except in the rare circumstances where they have something else to use their bonus action for, such as Healing Word.
Few things you do will actually require a bonus action, so being able to use that bonus action every turn to impose disadvantage is a huge buff.
The only real problem here is that it will stop you from casting any other spells during your turn, except for other cantrips. This might not be nearly as big a deal as it sounds, however, as you don't generally cast a spell every turn anyway.
Another thing this might fight for in a turn is using bardic inspirations, but once again, you don't use these that often.
So for a Bard who uses a lot of cantrips (like most casters) or who uses the attack option a lot, this is overall a big buff.
Best Answer
By the rules, you are correct. The spell affects a creature (a rat qualifies) you can see (which you could) that can hear you (which it could).
However, DMs do have the ability to change the rules for their game. I'd approach him again and get clarification on the ability for future use, noting "it just says a creature." If he's only going to let it work on humanoids, then you could ask if you could switch cantrips since that one's being severely nerfed past what it says in the rulebook.