I know that other desktops support window compositing with Compiz.
Is it possible to enable such functionality on MATE DE?
Yes, it is possible.
mate-tweak(System->Preferences->Look and Feel->MATE Tweak)
in Window manager area switch Select a window manager to Compiz (Advanced GPU accelerated desktop effects) as shown on screenshot below:
Other options are:
Note: you can switch back to Marco (No compositor) if you want simple traditional desktop.