I think that there is no single answer to this question, it all depends on the context and what is being tested for.
So, I like that characters that are proficient at something have a high degree of reliability at succeeding at that thing.
So, the answer is, it depends. Context matters, a person that is competent in doing something can do it approaching 100% of the time. We would not call someone a good driver if they crashed the car one of every 100 journeys.
As to how this translated to D&D, the competent lockpicker will pick most locks but some of them might take a bit more time. I am very happy with the way 5e handles this sort of think but I am not sure that there is a single sweet spot. It really depends on who is doing it, what they are trying to do and under what circumstances and the latter is the biggest source of difficulty.