There are other psalms that carry the weight of sin: Psalms 38 and 51 are popular for helping the petitioner to find the words and humiliation to express sorrow and regret for sin.
This Psalm does not note the occasion of sin, but we all find sin "ever at the door," and David was a man just as human as all of us are and he must have had other times when he sought forgiveness.
He appeals to God for one reason: his unfailing love. Only because God loves him does he ask for forgiveness. He has worn himself out weeping and his bed is wet with tears; his eyes are weak with sorrow, but he does not appeal to God on those grounds.
He turns away those who might comfort him. It makes me wonder if they were knowledgeable about the deed or were also participants.
In Verse 9 he is convinced that God had heard his prayer and granted forgiveness. He continues to seek acceptance of their guilt on those who are also in need of God's forgiveness.
When you are in need of God's forgiveness, remember David's prayer and seek help from his words when you are convicted of sin.