They have a page about pros and cons: https://h3geo.org/docs/comparisons/s2/

For my use case, the visual distortion of S2 was quite a no-go.

As for DB queries, it really depends on your use case and how you store your data, but you can get some good results. But yeah, if you really need exact parent-child containment, S2 is easier to work with.