I have a job board where jobs come up & need to go back down daily. I need a workflow to basically delete sometimes 20k jobs from my database daily. When I use a normal workflow to delete it sometimes takes me 90 minutes to remove 20k Entries from the database. Does anyone know of a smarter way to remove data from the Database?
Don’t know if this will work - never tired it… (I have run into the same issue but not so big)
What if you called a recursive server side API, I think bubble limits delete records to #100, so you might be able to stack up API calls if they run in parallel. Not sure if bubble limits APIs to a serial stream on a “per user” or “per account” basis? If they do, it will not work, but if the APIs can run in parallel.
Well, it must run the APIs in parallel, so that is in intresting finding.
If you are using distinct fields as your search criteria you may try Algolia and use it as the index. Searches such as X contains on a data field list do not index.
One issue with Bubble is the fact we the builders can not control the DB indexing, and rely on Bubble"s algorithm to decide how to optimize searches. Hence, if your repeating group is accessing a DB that is not correctly indexed the query is performing a sequencial search and hence will be very, very slow. Typically you would create an index so that the DB engine can perform a binary search (very fast). This is what Algolia will do for you since it, by design, creates sorted indexes that enable this. No disrepect if you already understand this.
Of course this will not speed up deletes but may mitigate the need for it.
The sad news is that bubble actually uses one of the best OpenSource engines which can handle such “deletes” in milliseconds with the correct indexing and a single simple:
SQL DELETE FROM table LIMIT 20000;
The PostgreSQL DB is not the limitation, rather I suspect Bubble server-side throttling is likely the real culprit. Alas CPU cycles even on server farms is not unlimited. That said, as mentioned offloading the indexing can go a long way to solving searching issues. FYI: you can set up a free Algolia account and test out your repeating group responsiveness. It will not solve the slow delete. Bubble has built in integration with Algolia. Another solution is Typesence Cloud which is less expensive, and a new plugin is on the horizon that offers connectivity.
BTW: I can’t take credit for using an external indexing solution, rather credit goes to Gregory John who mentioned Algolia in his Fuzzy Search video. @gregjohnkeegan