It can mean a lot of things.
In the Hollies’ song, you can take it literally: “a great weight”. In the song, the lyrics are about ‘carrying’ a brother. And while the ‘carrying’ itself may be a metaphor for “support”, in terms of a ‘carry’ you should consider that the brother is considered to be ‘not a great weight’, because of the relationship.
I’m not familiar with the Beatles’ song. Or at least it’s been so long since I heard it that I’ve forgotten the lyrics—and don’t care to look them up right now. (The topic’s not heavy enough for me… ;)
In terms of flight, a “heavy” plane is one that is just taking off… it has a full payload (passengers, freight or bombs) and fuel. (Presumably a freight or passenger plane that is landing will be lightened only to the extent of the spent fuel, but these days one never knows.)
In literature a “heavy” (used as a noun) is often an enforcer type: a policeman or Mob hit man or debt collector who uses corporal punishment to effect his collections.
It’s also a metaphor for “deep” and ‘thought-provoking” ... or “hard” ... or “bad”, depending on context:
heavy weather = bad, very stormy weather
heavy duty = hard usage (or built for that)
This discussion is getting too heavy for me.