Hello,
I am trying to understand the data model employed (possibly by Bubble) and how this may be different from data models employed by conventional relational databases
The apps I have built so far are quite slow working with small quantities of data so I am trying to understand how the database works?
Although Bubble uses Postgres under-the-hood, it doesn’t expose it to you as a native SQL API, so there’s an abstraction layer above it which forces you to access the data the Bubble way. In fact it could be any DB underneath it & I believe previously it was sat on Firebase (the original Realtime Database)
The way to think of it is it works more like a No-SQL database, like Firebase, but with aggregation features, sum, count etc. There are no joins or SQL style query building and you don’t have control over indexing. If you’re from a SQL background like me, this is quite alien at first. You can do pseudo joins by having nested searches in your searches but I doubt that’s very optimised for large datasets… Essentially the only way to really make Bubble’s DB sing is to have data duplicated where it’s needed for performance just as do in no SQL dbs like Firebase or Mongo & just like with Firebase you optimise for reads over writes, meaning you design your data structures for performant searches/queries which may involve duplicated data but worth it for searches that may need to be run for large datasets & you know will run thousands of times iin the lifetime of the app. rather than designing for a single-source-of-truth structure where you optimise for writes as you would in SQL databases, ( normalisation as SQL DB peeps call it) but which are written just once and maybe updated a few dozen times over the lifetime of the app. Use backend DB triggers to keep duplicated data in-sync.
I would highly recommend @petter 's book on Bubble performance to help understand how Bubble works under the hood & how you can design your data structures for optimal performance.
FYI check out this very detailed reply I recently got from Matthew on the Bubble team about how Bubble’s DB works under the hood esp in regard to aggregation.
