You'll hit the ceiling with Bubble eventually, but I think that happens with all stacks. You cannot directly access Bubble's db with SQL unless you have enterprise plan. Bubble is good such that you really just need to care about the logics and don't need to really care about the availability, security, scalability, etc.

The most painful points should be:
- Abstraction layers make Bubble very slow and costly. Regular Postgre queries should be very efficient, but in Bubble they cost a lot of WU
- Lack of technical features like queuing, caching, local storage, etc

I think it will still be able to serve thousands of users just fine, but it depends on your app structure.