How to Efficiently Save & Retrieve 90 Lac Rows in | Databases

Hey guys, we’re building an app which offers a lot of services like company incorporation, opening company bank account, increase share capital etc. there are 27 such services.

While requesting any service, user has to fill 2-3 forms. You can say there are total of like 50-60 forms on our app. Our approach is to make a table named fields that has these columns:

  • key
  • value
  • form
  • request

This way we store data submitted by all users, through all forms for all services in one table. So, you can say, when one service is requested by a user, he fills 2-3 forms, each form having around 20 fields, ultimately, 60 new rows would be added to fields table. Vertical size of the table i.e. rows would be growing at a very rapid pace.

Each request on average has 3 forms with each form containing average 15 fields which makes it 45 rows each process. Forecasting the rows for 2 years with 10k users & about 20 requests each user we would need about 90 lac rows in 2 years!

Can anyone please give their suggestions to how should we handle this much rows? Is bubble powerful enough to not get slow for 90lac rows if we apply enough constraints & only do it on server side? Also, will using externa DB like AWS will help? (API calls may get slow).

Looking forward!

I don’t see any obvious issues with your setup. Behind the scenes Bubble should be indexing your list of fields anyway by form so the retrieval speed should still be super fast even with hundreds of thousands of records.

Of course yes you should only be filtering the fields server side. If you save them as a list on each form, then you can just pull them out that way when the form is loaded.

What I think would be more important for performance would be the amount of data in each field's: value, rather than the number of records in the table.