Historically, we know a little bit.
We know that he was removed from his post around 36 AD after brutally putting down a Samaritan uprising, then ordered back to Rome to answer to the emperor. Tiberius died before he got there, and after that the record basically goes… dark.
Eusebius, the early church historian, passed along a tradition that he eventually killed himself, though there’s no way to verify it. The Ethiopian church has a different tradition, that he and his wife later came to faith, and they regard him as a saint which I think would surprise a lot of Christians that haven’t really studied the Bible.
As for whether his sin was too big… I don’t think so, and it worries me when we start measuring which sins are beyond reach (and we were kind of just talking about this here). Jesus prayed ‘Father, forgive them, for they know not what they do’ over the very people carrying out his execution. Paul approved the killing of Christians before his conversion and still got grace. If forgiveness had a size limit, most of the New Testament’s heroes wouldn’t have made the cut.
Whatever happened to Pilate, I don’t think he was ever beyond the reach of the mercy he unknowingly helped purchase.