Use idempotency wherever a retry could cause damage: payment APIs, order placement, sending email or SMS, message consumers reading from a queue, and any write endpoint behind a load balancer or proxy that might re-send on timeout. Message systems like Kafka and SQS deliver at least once, meaning duplicates are expected, so consumers must be idempotent to be correct.
The cost is real but small. You need somewhere to store keys and their results, you pay an extra lookup on every write, and you have to decide on a TTL and a key-generation scheme. Storing the full response so retries get an identical answer adds more storage than just storing a boolean.
The common mistake is assuming a database unique constraint alone gives you idempotency. It prevents duplicate rows, but the client still gets an error on the retry instead of the original success response, which often triggers more retries. True idempotency returns the same successful answer to the duplicate request, not an error.