Wouldn't relaxing all of them lead to inconsistent / useless programs outputs?

^ Never mind. Just saw that memory fences are created to solve these issues.

When would we use this type of implementation? I'm struggling to think of situations that don't need memory consistely operated on

When working with peripherals on Raspberry Pi, there is only weak memory consistency (if any), and can be a particularly tricky source of bugs.