Visually there's not a whole lot of difference. Ideally, you want something where the shape of each name is reasonably distinct from all the rest — otherwise folks will just ignore that odd blot before the prompt.

And then, once you've started naming things as $ENV-$TYPE, someone will want to cram in the location, and the OS, and the team which maintains it, and the customer, and and and. Then someone will reduce all of those identifiers into single characters … and you'll be in the situation I mentioned. Clearly clpudb1 is CharlesCorp's first London production database system!

I strongly argue against those types of abbreviations in naming, for exactly the points you make.

Here's how I view it:

The distinction between prod and dev is pretty clear cut. The words "dev" and "prod" have significantly different shapes and are immediately unambiguous. There's no need to remember a superhero vs astronomical object distinction.

As the number of database server instances grows, you've got another level of naming issues that arises when needing to distinguish between hosts and db server instances—and perhaps even database/schema independence if necessary. Staying consistent and easy-to-use with an arbitrary naming scheme becomes increasingly unwieldy, in my opinion.

Additionally, I have taken up a habit of not abbreviating "production" and "staging", whenever possible. The fact that len("production") > len("staging") > len("dev") is a feature when you find yourself typing it into a terminal or db shell.