It doesn't say anywhere in the Bible that all good people go to heaven, yet this is what most people believe since they they are never taught about God's purpose for the earth.
David did not go to heaven for two reasons,1) no one went to heaven before Jesus was resurrected and went to heaven:
(Colossians 1:18) He is the beginning, the firstborn from the dead, that he might become the one who is first in all things;
2) David had the same hope that I do; of living forever on the paradise earth. One of the Psalms that David wrote under inspiration from God was Psalms 37. Consider some verses there about his hope:
(Psalm 37:10-11) 10 And just a little while longer, and the wicked one will be no more; And you will certainly give attention to his place, and he will not be. 11 But the meek ones themselves will possess the earth, And they will indeed find their exquisite delight in the abundance of peace.
(Psalm 37:29) 29 The righteous themselves will possess the earth, And they will reside forever upon it.
Jesus quoted from this Psalm in his sermon on the mount:
(Matthew 5:5) “Happy are the mild-tempered ones, since they will inherit the earth.
Theses ones will live under the government of God's son as it accomplishes God's will for the earth:
(Matthew 6:10) 10 Let your kingdom come. Let your will take place, as in heaven, also upon earth.
Some 33 times in the book of Matthew we see the expression "the Kingdom of the heavens" because that is where the capital of God's Kingdom is located, however not all citizens of God's kingdom have to go to heaven anymore than all citizens of the United States government have to go to Washington D.C.Jesus stated what the requirement is for those to go to heaven:
(John 3:5-6) 5 Jesus answered: “Most truly I say to you, Unless anyone is born from water and spirit, he cannot enter into the kingdom of God. 6 What has been born from the flesh is flesh, and what has been born from the spirit is spirit.
Those who go to heaven are resurrected with spirit bodies, and those who will live on this earth will be resurrected with physical bodies. Those who are going to have spirit bodies are given a token of that fact by God: it is a special witness given to them by his Holy Spirit. Jesus was the first one who was "born again" since, if you recall, at the time of his baptism the Holy Spirit descended upon him in the form of a dove. After this the first humans who were born again were those who were present in the upper room in Jerusalem at Pentecost of 33 C.E.. Being "born again" is not the same as having God's Holy Spirit. David had the Holy Spirit, but he was not "born again":
(2 Peter 1:21) For prophecy was at no time brought by man’s will, but men spoke from God as they were borne along by holy spirit.
(2 Samuel 23:2) The spirit of Jehovah it was that spoke by me, And his word was upon my tongue.
So before the time of Christ the resurrection hope was for life on the earth. Then after Jesus came to earth the way was opened for those few who will rule with him to go to heaven. That is another thing that most people do not realize: that those who go to heaven have a job to do there:
(Revelation 5:9-10) 9 And they sing a new song, saying: “You are worthy to take the scroll and open its seals, because you were slaughtered and with your blood you bought persons for God out of every tribe and tongue and people and nation, 10 and you made them to be a kingdom and priests to our God, and they are to rule as kings over the earth.”
However David will live right here on the earth and enjoy the blessings of God's Kingdom:
(Revelation 22:1-2) 22 And he showed me a river of water of life, clear as crystal, flowing out from the throne of God and of the Lamb 2 down the middle of its broad way. And on this side of the river and on that side [there were] trees of life producing twelve crops of fruit, yielding their fruits each month. And the leaves of the trees [were] for the curing of the nations.