Hi Fam
One of the reasons I like PocketBase is the ease of deployment. Its a webserver that you can host your apps in and its also a powerful back-end, authentication built in, SSE too.
Also built in email sending functionality where one just sets up the SMTP credentials make it my first option eversince I discovered it. One user on the Github Discussions indicated 1M requests per month on his app. The other thing is how easy is it to extend with javascript. You write hooks in javascript and use routeAdd, has crob job hooks, can create command line app and other things I still dont know. Ohh yes, files sent with FormData are saved on the hard disk, you get the URL link and can reference them using the record ID. Aha, we are using SSDs now. Yeah!
My SithasoDaisy Desktop app is rather large (built with SD2*), with 2 other mobile web apps, one for staff members (SD2*) and one for participants (SD5*). Its an online attendance app with lots and lots of other features like reporting using Excel Predefined Templates, editing PDF documents, Importing CSV and Excel files, Uploading Documents etc etc.
Everything was fine with 300 participants checking-in (9AM) and checking-out (5PM), then the number increased to 800.
The online checking process is using SignaturePad where each participant should sign during the morning when they attend and then sign out when they leave. The signature from a base64 string is then converted to a file object and sent to the server using FormData (multi-part). NB: NEVER EVER SAVE BASE64 as strings in a DB.
I had one table to save these signatures and it had indexes as it also references other tables for relationships, for example, the province, the programme, time, id number and other things. So on any other day, there is close to 40K records on this file per month. 800 participants * 2 times a day * 23 working days for example.
Writes in PocketBase are queued as its a single write mechanism. So imagine 800 people checking-in at the same time. A que is built, a large one. This worked for 300 people without issues. So I went back to my design. What can I do differently?
Enter Sharding...
The whole deployment had grown to 6GB including files and has been running for a year without breakdowns. The increase however proved challenging. The maximum signature I had found was about 24KB and the lowest stood at 1KB.
I had thought of porting over to MySQL but I was still convinced I could do something different. I understand my traffic gets congested so I needed to also get someone technical to look at how windows TCP/IP handled traffic and how that could be optimized. RAM usage for pocketbase was rising from 100MB to 2GB during these peak times. Shared hosting too.
Redeployment
This was easy. Stop the web-server. Zip the 6GB pocketbase deployment, move it to the new server, start the webserver, open ports on firewall, login to the admin panel and check if all is well. Check. 96GB of RAM on this new server. This should do it.
Day 1, Day 2, Day 3 - Crash. And then?
ChatGPT - how much RAM do I need for 800 multi-user pocketbase application who upload files at the same time? Answer - Approximately 6GB and other factors and other factors and what the garbage collector will do. This RAM will be used for handling everything that will happen on the deployment, traffic etc. Its not just the DB afterall, its a whole complete package. If you dont set it properly, PocketBase can and will eat all of your RAM.
So I had to tweak it..
This way no matter what happens, limit total RAM used by it to 6BG and then have a mid aggressive garbase collector.
Start Sharding
Whilst I thought deeply about this to become an admin nightware, I decided to go for it anyway. 800 tables later (per participant), and moved their respective signatures to each persons files. It became the solution I needed. The tables are small, quick to read and quick to write and indexing quicker.
We moved from Day Zero, Day 1, Day 2 and so forth and so forth. Issue free.
I'm still maintaining the large table as all my reports are based on it. So I re-consolidate the data back again to it, but this time only for reporting and I clear it when I'm done, after month end. Each participant has their own table to store signatures for reading and displaying timesheets and then saving their signatures in the morning and afternoon.
Then Some Code Refactoring
1. I spoke about this RAM consuming beast here. Long story short, if you fetch everything, it gets stored in RAM, if you page it, it gets managable and the garbage collector works well.
www.b4x.com
2. Using View...
One other thing I discovered was that if I used "Views", my application performed so much better than running requests. So code to create views at runtime came handy, but only for those that were dynamic and I could not do otherwise to avoid.
Learning..
When one starts programming as a hobby and self teaches themselves, some things I guess fall through the cracks. I love that I learned a lot through this process and now I know what to do and what not to do when it comes to PocketBase back-ends. I started using PocketBase as a hobby back-end, enjoying that it came as a webserver. BTW, the 3 WebApps are running inside PocketBase. The both back-end and one can publish their front-end to it make it a breeze to work with. This helps the non-technical me to focus on what I know how to do best, UI development with CRUD functionality.
Other things I tried...
1. Using PHP CRUD API to access pocketbase back-end. This was due to Apache running on the same server with PHP. I was sstuck on a 401 Error with this on my dev machine. I didnt deploy this to test further as there were cors errors etc. Funny thing, PostMan worked perfectly without errors, my code didnt. Strange.
2. Migrating the DB to the newer version of Pocketbase that has reverse proxy, rate limiting and other nice things. I wasnt ready. It was not as easy as I thought. DB Size 2GB and the automated migration will have to cover everything from v22.35 Im running to V29.2, thats a huge difference and its not only about data, its the schema and that and that and that. Phew. One day.
Glossary
* SD2 - SithasoDaisy2
* SD5 - SithasoDaisy5
Related Content
A beautiful b4a library to use Pocketbase by Alexander
www.b4x.com
One of the reasons I like PocketBase is the ease of deployment. Its a webserver that you can host your apps in and its also a powerful back-end, authentication built in, SSE too.
Also built in email sending functionality where one just sets up the SMTP credentials make it my first option eversince I discovered it. One user on the Github Discussions indicated 1M requests per month on his app. The other thing is how easy is it to extend with javascript. You write hooks in javascript and use routeAdd, has crob job hooks, can create command line app and other things I still dont know. Ohh yes, files sent with FormData are saved on the hard disk, you get the URL link and can reference them using the record ID. Aha, we are using SSDs now. Yeah!
My SithasoDaisy Desktop app is rather large (built with SD2*), with 2 other mobile web apps, one for staff members (SD2*) and one for participants (SD5*). Its an online attendance app with lots and lots of other features like reporting using Excel Predefined Templates, editing PDF documents, Importing CSV and Excel files, Uploading Documents etc etc.
Everything was fine with 300 participants checking-in (9AM) and checking-out (5PM), then the number increased to 800.
The online checking process is using SignaturePad where each participant should sign during the morning when they attend and then sign out when they leave. The signature from a base64 string is then converted to a file object and sent to the server using FormData (multi-part). NB: NEVER EVER SAVE BASE64 as strings in a DB.
I had one table to save these signatures and it had indexes as it also references other tables for relationships, for example, the province, the programme, time, id number and other things. So on any other day, there is close to 40K records on this file per month. 800 participants * 2 times a day * 23 working days for example.
Writes in PocketBase are queued as its a single write mechanism. So imagine 800 people checking-in at the same time. A que is built, a large one. This worked for 300 people without issues. So I went back to my design. What can I do differently?
Enter Sharding...
The whole deployment had grown to 6GB including files and has been running for a year without breakdowns. The increase however proved challenging. The maximum signature I had found was about 24KB and the lowest stood at 1KB.
I had thought of porting over to MySQL but I was still convinced I could do something different. I understand my traffic gets congested so I needed to also get someone technical to look at how windows TCP/IP handled traffic and how that could be optimized. RAM usage for pocketbase was rising from 100MB to 2GB during these peak times. Shared hosting too.
Redeployment
This was easy. Stop the web-server. Zip the 6GB pocketbase deployment, move it to the new server, start the webserver, open ports on firewall, login to the admin panel and check if all is well. Check. 96GB of RAM on this new server. This should do it.
Day 1, Day 2, Day 3 - Crash. And then?
ChatGPT - how much RAM do I need for 800 multi-user pocketbase application who upload files at the same time? Answer - Approximately 6GB and other factors and other factors and what the garbage collector will do. This RAM will be used for handling everything that will happen on the deployment, traffic etc. Its not just the DB afterall, its a whole complete package. If you dont set it properly, PocketBase can and will eat all of your RAM.
So I had to tweak it..
B4X:
@echo off
REM === Go runtime memory tuning for many small uploads ===
set GOMEMLIMIT=6GiB
set GOGC=100
set GODEBUG=madvdontneed=1
pocketbase.exe serveThis way no matter what happens, limit total RAM used by it to 6BG and then have a mid aggressive garbase collector.
Start Sharding
Whilst I thought deeply about this to become an admin nightware, I decided to go for it anyway. 800 tables later (per participant), and moved their respective signatures to each persons files. It became the solution I needed. The tables are small, quick to read and quick to write and indexing quicker.
We moved from Day Zero, Day 1, Day 2 and so forth and so forth. Issue free.
I'm still maintaining the large table as all my reports are based on it. So I re-consolidate the data back again to it, but this time only for reporting and I clear it when I'm done, after month end. Each participant has their own table to store signatures for reading and displaying timesheets and then saving their signatures in the morning and afternoon.
Then Some Code Refactoring
1. I spoke about this RAM consuming beast here. Long story short, if you fetch everything, it gets stored in RAM, if you page it, it gets managable and the garbage collector works well.
PocketBase: Why you should ABSOLUTELY AVOID using getFullList when Querying large tables
Hi Fam There are a variety of ways to get data from Pocketbase, one of them being getFullList. Remember, you can specify the fields you want to show in your end result and even filter your resultset. This is like a where clause where you use a filter property in your options THE PROBLEM WITH...
www.b4x.com
2. Using View...
One other thing I discovered was that if I used "Views", my application performed so much better than running requests. So code to create views at runtime came handy, but only for those that were dynamic and I could not do otherwise to avoid.
Learning..
When one starts programming as a hobby and self teaches themselves, some things I guess fall through the cracks. I love that I learned a lot through this process and now I know what to do and what not to do when it comes to PocketBase back-ends. I started using PocketBase as a hobby back-end, enjoying that it came as a webserver. BTW, the 3 WebApps are running inside PocketBase. The both back-end and one can publish their front-end to it make it a breeze to work with. This helps the non-technical me to focus on what I know how to do best, UI development with CRUD functionality.
Other things I tried...
1. Using PHP CRUD API to access pocketbase back-end. This was due to Apache running on the same server with PHP. I was sstuck on a 401 Error with this on my dev machine. I didnt deploy this to test further as there were cors errors etc. Funny thing, PostMan worked perfectly without errors, my code didnt. Strange.
2. Migrating the DB to the newer version of Pocketbase that has reverse proxy, rate limiting and other nice things. I wasnt ready. It was not as easy as I thought. DB Size 2GB and the automated migration will have to cover everything from v22.35 Im running to V29.2, thats a huge difference and its not only about data, its the schema and that and that and that. Phew. One day.
Glossary
* SD2 - SithasoDaisy2
* SD5 - SithasoDaisy5
Related Content
A beautiful b4a library to use Pocketbase by Alexander
[B4X] PocketBase - Open Source backend in 1 file
PocketBase was created to assist building self-contained applications that can run on a single server without requiring to install anything in addition. The basic idea is that the common functionality like crud, auth, files upload, auto TLS, etc. are handled out of the box, allowing you to focus...
www.b4x.com
Last edited: