[1] In modern usage, this term has generally been interpreted to describe the post-orgasmic state of unconsciousness that certain people perceive after having some sexual experiences.
It can also be used when some undesired thing has happened to a person and has affected them so much that "a part of them dies inside."
A literary example of this is found in Thomas Hardy's Tess of the D'Urbervilles when he uses the phrase to describe how Tess feels after she comes across a particularly gruesome omen after meeting with her own rapist: She felt the petite mort at this unexpectedly gruesome information, and left the solitary man behind her.
The term "little death", a direct translation of la petite mort, can also be used in English to essentially the same effect.
As with la petite mort, the earlier attested uses are not related to sex or orgasm.