Is it actually good, like 2006 good? Can I go to page 300 rabbit hole and find some obscure website nobody knows with interesting content on the topic I am researching?

Unfortunately, those days might be good no matter what - a lot of those sites have disappeared with their hosting platforms (e.g Geocities). That said, I have been able to find them on Kagi with a bit of effort.

Kagi also has a Small Web feature which is meant to surface exactly that sort of thing. It's not amazing, but I appreciate it existing. I think you can get a "random small web page" kind of thing even without a subscription here: https://kagi.com/smallweb/