I really dislike how this principle ends up being used in practice.

A good abstraction that makes actual sense is perfectly good even when it's used only once.

On the other hand, the idea of deduplicating code by creating an indirection is often not worth it for long-term maintenance, and is precisely the kind of thing that will cause maintenance headaches and anti-patterns.

For example: don't mix file system or low-level database access with your business code, just create a proper abstraction. But deduplicating very small fragments of same-abstraction-level can have detrimental effects in the long run.