If some of the Old Testament prophets, patriarchs or kings such as David had the Holy Spirit dwelling inside of them, would they have gone straight to Heaven and not the paradise section of Hades?
Anytime Old Testament people were given the Holy Spirit, he was not dwelling inside of them. The Bible makes a distinction in the Old Testament that the Spirit came upon people, usually through the anointing. But in the New Testament, he dwells in Christians.
I know this is a small distinction to make, but it is made regularly in the Bible. So the difference is that the Holy Spirit came upon people in the anointing, usually the offices of prophets, priests, and kings. But as part of the new covenant, God promises to put his Spirit in those who believe in him.
Prophets, priests, and kings were anointed to serve God in their respective offices in Israel. This means that the Holy Spirit came upon them to perform the works God called them to do. When King Saul turned away from God and was not obedient, the Bible tells us that God’s Spirit left him. And then David was anointed king of Israel. But David did not serve in that office until after King Saul’s death.
David had a thing about not touching the anointed of the Lord. He would not kill King Saul. He waited for him to die before he assumed the office of the king even though he was anointed king earlier than that. He respected the anointing of the Spirit of the office, if not the man in the office.
So God anointed people for works in his service in those offices in the Old Testament. The amazing thing about the new covenant is that even the common person, down to the servants, can receive the indwelling of the Holy Spirit. It went from a few people (comparatively) serving in the offices of Israel to anyone God wished to put his Spirit in.
A second distinction to make according to your question concerns Hades and Sheol. The Old Testament does not give a clear enough distinction for us on exactly how Sheol worked. The translations range from the grave, the pit, resting with forefathers, and just referring to it by its name, Sheol.
Perhaps the best understanding we have is that Sheol was divided into two parts, the part for believing Old Testament saints and a part for the rest who did not make a decision. Some believe that when Jesus was crucified and buried, he went down to Sheol to proclaim himself to everyone there. Those who trusted he is the Messiah were then liberated from Sheol and went with him to heaven. Those who did not stayed in Sheol. This is one understanding of 1 Peter 3:19-20.
Hades is referred to in the New Testament. There is a much more robust understanding of the afterlife in the New Testament. Hades or Hell may be different from Sheol in the Old Testament. It is the final place for those who do not turn to God in this life.
Those distinctions made, people in the Old Testament who operated in the offices of prophets, priests, and kings did not have the Holy Spirit dwelling in them, but he came upon some people for these tasks. As we saw with King Saul, the Holy Spirit can leave a person who is not fulfilling the office the way God wants them to.
For this purpose, we can gather that the Holy Spirit coming upon a person for service in an office is not the same as being sealed with the Holy Spirit in salvation (Ephesians 1:13-14). It appears that one is for utility while the other is a confirmation of salvation.
This is not to say the Holy Spirit cannot leave a person who has turned away from God after knowing him, falling away from grace, or apostasy. But that is a completely different matter for another question. The point here is that the Holy Spirit coming upon or dwelling in a person does not guarantee that they will go right to heaven.
Another point to make is that because Sheol was more like a holding place before Christ came and completed his mission to bring salvation, we cannot make the assumption that people immediately went to heaven in the Old Testament. The only exemptions to this are Enoch and Elijah, who were taken up to be with God and never died, going to Sheol.
While the Old Testament sacrificial system was in place to forgive sins and foreshadow the coming of Christ, the ultimate sacrifice on the cross, this system was only in place for a time. It was not a system of salvation but a system to keep a person from feeling the full brunt of the Law’s prescription, in most cases death, for sins committed in Israel.
Sacrifices were done every day and had to be done for as many times as a person sinned against God. Jesus came as the sinless sacrifice, once and for all. This was a game changer. But as I said before, we do not have a full understanding of Old Testament beliefs regarding the afterlife.
It is still up to God to decide the final place for every soul. If you know Jesus, you will be with God for eternity. If you don’t know Jesus, the Bible teaches your eternal destiny is much less secure. Hell is reserved for Satan and his demons. But there are also indications that those who did not believe in Christ will end up there.