Since a person should believe that once he has repented properly, God has totally erased his sin, as the Prophet states, I have erased your sin like a fog that cleared (Isaiah 44:22), why does the Psalmist assert that his sin always remains before his eyes?
It sometimes happens that a parent wishes to do something for a child's benefit, but in spite of the parent's best intentions, the act causes the child to be harmed. Although there was certainly no hostile intent and no negligence - to the contrary, the parent was trying to help the child - the parent's pain over the incident may never disappear. Even if the child has completely forgiven the parent and knows that the parent's intentions were only for his good, the love of the parent for the child is so intense that the parent cannot make peace with what he or she has done. Furthermore, this distress may not be relieved by any logical argument.
I know of a mother who took her child for a recommended medical treatment which unfortunately resulted in an adverse reaction and very serious consequences. Although the child later recovered, there was no comforting the mother. Though she had done the right thing by any reasonable standard, she could not forgive herself for having brought distress upon her child.
King David's repentance was teshuvah me'ahavah, or repentance out of an intense love for God. David had complete trust that God had erased his sin, but like the mother in the above example, he could never be completely consoled knowing that he had offended the One Whom he so loved.