This is an interesting question. I assume this question could also be applied to a more general setting, but since I mainly concern myself with biblatex I will stick to the realms of bibliographies.
Normally the nature of the redefinitions are so that only \xpatchbibmacro is relevant, \xapptobibmacro and \xpretobibmacro, which have a slightly simpler syntax, are often not applicable. \xpatchbibmacro's syntax is \xpatchbibmacro{<bibmacro>}{<search>}{<replace>}{<success>}{<failure>}. The command replaces the first instance of <search> in <bibmacro> by <replace>.
In general I prefer to redefine bibmacros directly and not via xpatch.
The pros of this approach are
xpatch does not need to be loaded, reducing overhead. xpatch is based on LaTeX3 macros and loads expl3. The overhead is probably negligible, but it is there. I also assume that patching is more expensive than simply redefining a macro, but I can't give you hard numbers here - again the difference is probably negligible. The syntax is slightly simpler: \renewbibmacro{foo}{<new definition>} vs \xpatchbibmacro{foo}{<search>}{<replace>}{<success>}{<failure>}.
- Often a change just involves a few lines, it is then very easy to copy the original
\newbibmacro{foo} definition, drop in a re and modify the macro code. It can be a bit more bothersome to type out \xpatchbibmacro{foo} copy the code to be changed into the <search> argument, add the modified code to <replace> and add the <success> an <failure> code (often just empty groups, don't forget them, though).
- With short macros where a lot needs to change,
\renewbibmacro might actually be shorter.
\renewbibmacro might be more instructive for the reader, since she can see the entire relevant macro
\renewbibmacro*{issue+date}{%
\printtext[parens]{%
\printfield{issue}%
\setunit*{\addcomma\space}%<- change here
\usebibmacro{date}}%
\newunit}
shows more clearly what the new code is doing than
\xpatchbibmacro{issue+date}
{\setunit*{\addspace}}
{\setunit*{\addcomma\space}}
{}{}
The reader does not have to consult the original definition to understand the new behaviour of the macro. She will have to consult the original definition to compare exactly what changed, though.
This also implies that is easier to extend or adopt the changes.
The cons are
Less flexibility. With xpatch you don't have to know the exact definition of the macro you patch. Only the code in <search> is relevant. So xpatch is useful to some extent if the modifications should work independent of the used style.
Code length. It might been seen as excessive to copy tens of lines just to change one bit of punctuation.
This is why for me there is a point at which I will use xpatch. People really like short code.
To a certain extent biblatex's tendency to compartmentalise and use many nested short bibmacros allows for tiny changes without too much code in many situations. But some bibmacros are long, and so are the bibdrivers.
Take for example my answer to How to customize the \textcite command?. The \renewbibmacro would have been around 50 lines, while \xpatchbibmacro only needed 5. All of that was to change one \printnames{labelname} to \printnames[first-last]{labelname}.
Especially when bibdrivers need to be changed directly I prefer xpatch over \DeclareBibliographyDriver. Your average standard bibdriver is 30 to 40 lines long. If several drivers need to be changed, small changes can amass hundreds of lines of code. Examples can be seen in Biblatex/Biber: Put series, volume and number at the end and How to move the field note at the end of the reference.
Both approaches have their problems with changes to bibmacros and drivers. The majority of bibmacros and drivers that the average user would want to change have been quite stable for years. That is not to say that there is not the occasional change to even more frequently changed macros, but these are normally rare.
The obvious advantage for xpatch is that if the relevant <search> code and its context have not been altered, it is quite likely that the patching will continue to do the right thing even after an update to the macro. If, however, the <search> code was modified, the patching will fail. If new code similar to the <search> has been added to the macro, the wrong bit of the macro will be patched. All this holds provided that the commands and macros uses in <replace> have not changed dramatically.
On the other hand, macros redefined with \renewbibmacro will always apply the same change. They are - of course - susceptible to all changes that apply to commands and macros used in their <new definition> argument. With \renewbibmacro you are more likely to miss out on new features or bugfixes since you overwrite the entire definition and not just bits of it. But as long as the macros and commands used in <new definition> are not renamed or changed to do something contrary to their earlier behaviour, your redefinition will continue to do what it was written to do.
Take https://github.com/plk/biblatex/commit/04c939eed70ed2264a2753a811a2364e7f70d4ef as first example. If you redefined the editor bibmacro with \renewcommand based on the code before the change, you will not be able to use editortypedelim properly unless you modify your renewcommand. Your code will continue to work as before though. If you had used \xpatchbibmacro to change bits of the editor macro unrelated to that change, things will continue to work as expected and you can use the new editortypedelim. If you patched something related to the change, i.e. the explicit \setunit{\addcomma\space}, your patch will fail.
A second example is https://github.com/plk/biblatex/commit/189d90db9dbe94dd08f47261e79df09e2a37c66a and https://github.com/plk/biblatex/commit/320f114d493ca7fa310c559c3a48b5912282fa22. If you wanted to change one of the macros that were renamed, say \usebibmacro{date+extrayear}, neither method will give good results. If you changed a macro that calls one of the renamed macros, say the bibmacro bbx:editor from authoryear.bbx, there are again two cases. The xpatch approach is entirely unproblematic if the <search> code does not involve \usebibmacro{date+extrayear}. If <search> does involve \usebibmacro{date+extrayear}, xpatchbibmacro will fail. If, on the other hand, you redefined bbx:editor and the <new definition> references \usebibmacro{date+extrayear} things would go wrong were it not for the backwards compatibility measures taken in the commits.
More styles on CTAN than I expected use xpatch.
Some of these uses are to avoid copying entire definitions from the same package over and over again. In these cases xpatch is a short and elegant method to avoid doubling of code. This is fairly safe since the patched macros are often defined in the same package and are not likely to change without the author of the patches noticing it.
Other uses involve patching bibdrivers. Since drivers are long, it is cumbersome to copy the entire thing just to patch one line.
Some packages use xpatch to work independent of the underlying style.
