libvpx0 has apparently just appeared in the debian repositories, and it was automatically installed when I updated ffmpeg just now (from the debian-multimedia repositories), so I'd say that support for it is either already there, or soon to be added, in the newest builds of the major multimedia frameworks.
Unfortunately, the current build seems to have broken my ffmpeg, so I can't test it right now. All I get is an "error while loading shared libraries: libvpx.so.0:" at this time. But that will no doubt get fixed soon enough, then I can see how it works out.
As for whether you "should" use it, that's up to you. It has great promise, being nearly equivalent to h.264, completely free, and backed by one of the largest tech companies in the world. I'd say it has a great future.
Still, if it were me, I'd wait a few months until everything gets settled out and support for it becomes commonplace. It might be prudent to wait until later this year or early next year before jumping on the bandwagon.
Personally, I don't see why so many people criticize theora though. Sure, it may not be as good at compression as h.264, but it's not a bad codec. I see it as being equivalent to xvid, and a bit better than mpeg2, so it certainly "good enough" for most purposes.
edit: BTW, here's the website of the webm/vpx project.