The rate at which those pumps fail is not known to anyone on this forum. I'm sure some people inside Ford and Mazda know but they aren't talking. What we do know is that there are about 3 or 4 dozen threads on this forum talking about it, so it does happen enough that it's a real concern. However, there were about 225,000 first generation CX-9's sold in North America. So, while there are multiple cases of failure, there are also a lot of these on the road.
The Ford Flex, Edge, Explorer, Taurus, and several Lincoln models also use derivatives of this engine, and you can find plenty of examples of pump failures on those forums, too. Once again, though, Ford sold literally millions of these engines.
Would I buy one? Maybe, provided that:
1. I got a screaming deal so I could set aside cash to replace the pump at 100-120k miles to be on the safe side. If you DIY, you can save a ton of money here.
2. It was FWD. The AWD system is not reliable on the first generation and is best avoided. The transfer case design is fatally flawed and prone to failure, and can damage the transmission splines.
The CUV/SUV market is insane right now. At your price point, you are probably looking at 2013-2016 RAV4, CR-V, etc. sized vehicles.