Emergent normal fluid in the superconducting ground state of overdoped cuprates