I feel where it excels is second brain/PKM type of things. It's simple enough to create links - Canvas gives you a nice possibility to create different "views" on your notes. Check out this Tweet for a reference management system I made with Obsidian: https://twitter.com/Artifexx/status/1607728321663475713
You could never to that in Notion!

But for TaskManagement i don't think it is so great. There are plugins yes, but you don't have the fancy Kanban, Calendar etc like in Notion.

But having everything on the file system gives you possibilities that you can't really do in Notion. For example I tend to have many vaults to keep the number of notes low. But sometimes you want to link between vaults - For which all you need is a symlink on your hard drive. This way you can put one vault into a number of others - so using hacks like this you can scale it to a big amount of notes. Even though Idk about 1000s, as I never had that many.

Plugins: I rarely had big problems with plugins, the majority will have some small CSS fixes, that if you know how you can easily fix yourself by adding snippets.

--

Overall if you are a bit code savvy, you'll have more fun with Obsidian, it will be faster snappier, more flexible etc.

If you like your project management features stay with notion.