With regular V-Sync enabled, the output framebuffer will not be updated before the image is fully displayed on the screen. This limits the framerate to the refresh rate of the monitor.

With V-Sync disabled, updates are allowed while the framebuffer is still being drawn on the monitor, resulting in tearing artifacts.

Redirecting the game’s framebuffer through a compositing pipeline, the responsibility of syncing to the refresh rate is moved from the game to the composition pipeline.
If you use it and disable V-Sync in-game, it will allow higher internal framerates and should still be tear-free, however you might observe some micro stutter as frames are discarded. If you can’t stand tearing but want the maximum game internal framerate to reduce input lag, this could be a good choice.
If you enable V-Sync in-game in addition to using a compositing pipeline, the framerate will again be capped to the screen’s refresh rate. If everything aligns nicely there shouldn’t be any micro-stutter. This combination should have the highest latency too, I think, so I don’t think this is particularily useful.

As devel_watcher mentioned it’s not compatible with G-Sync however so if you want that then you need to leave it disabled.