The Jewish people tended to look upon David as the perfect king and the ideal government. David was a great person with a good heart. His Psalms are a testimony to his good relationship with God. David was also an effective military leader. He demonstrated his courage in the defeat of Goliath and his battle skills by driving the Jebusites out of Jerusalem.
Despite having a good heart, David was not a good ruler of the people. He did not handle justice well. Absalom was able to make a place for himself, by getting justice for people who did not believe they would get justice from King David (2 Sam 15:2-4).
The sad truth is that David did all the things that Samuel warned a king would do. Samuel said that the king would take the best property for himself. David had such great wealth and property that he needed twelve overseers to organize the people who worked it (1 Chron 27:25-31). Samuel warned that the king would take their daughters. David did worse, he took the wife of one of his soldiers, Uriah the Hitite, and then had him murdered. Samuel warned that the king would take all the young men for his army. David took a census of the people to find out how many soldiers he could have. This sin brought judgement on the nation, as the king was their representative.
So the LORD sent a plague on Israel from that morning until the end of the time designated, and seventy thousand of the people from Dan to Beersheba died (1 Sam 24:15).
King David was just doing what kings do, but his mistake cost 70,000 lives.
He was the best king that Israel had, but he was not good enough.