Usually people talk of an aggressive riding position when they are looking at someone who is more aero - stretched out, big saddle to bar drop, as you describe. Classic racing frames from the 80s would naturally put you in a fairly aggressive position, though in general even "racing" bikes were ridden with less drop from saddle to bars than is common now.

As for aggressive being synonymous with less comfy, that depends what you like and are used to. People who are used to riding in a relatively upright position on hybrids or touring bikes find it difficult to believe that one can be comfortable on a racing saddle, riding with a flat back and stretched out in the drops. But one can.