While water pumps are generally very reliable in their operation, they can and do leak. This can be due to where the pump joins to the engine block – the gasket can deteriorate and leak, or the actual impeller/bearing can begin to leak over time.
The impellers inside the pump can also fail. On some cars they're made of plastic, or cheap metal, and can suffer from the fins breaking off, or even corroding.
The tell tale signs are a water leak, and ultimately overheating.
Depending on the vehicle, removing a water pump can be an involved process, particularly if you have to remove other components to access it. So a good tool kit is a must.
There are unlikely to be any specialist tools needed, but you ideally need to be a competent DIYer.
As with most components, correct maintenance and servicing can extend the life of the water pump.
Always use the manufacturer's recommended coolant, adhere to the coolant service schedule, and flush the system whenever you change the fluid.
Incorrect coolant, or too higher a percentage of water can cause internal corrosion, which can damage metal components such as the impeller, or even erode pump housing and engine block.
There's no reason why a water pump shouldn't last for the life of the car. If you follow the correct service intervals it should last for many years without issue.
Check to see if your model is known for early pump failure however – as the results can end up being very costly if the engine overheats.
Factoring in the cost of the water pump ($50-$150), coolant ($20-40) and gasket ($5-10) it's not the cheapest of jobs, but would be substantially cheaper than getting the work carried out at a garage.