Quickbase Discussions

 View Only

Quick Base Memory Calculations

By Harrison Hersch posted 08-30-2019 11:29


I've consistently been plugging the QuickBase Under the Hood session from Empower 2018 to builders looking to learn more about what is happening behind the scenes with Quick Base. The feedback from attendees is that they enjoyed learning about what makes Quick Base tick. With how successful it was, we have continued to expand on that content by sharing use cases, examples, and learnings in our blog and the Quick Base Community. Continuing to look for new opportunities to share that deeper insight, we thought it might be helpful to go through a thought exercise on a sample application as it relates to resource utilization.

To give some background, Quick Base is an in-memory database. This makes it unbelievably powerful doing complex calculations on operational data in milliseconds. What this means is, rather than Quick Base having to talk to a hard disk to retrieve data like a traditional database, it reads and writes from/to RAM. Let's use an analogy. On your computer, you might have a hard drive, with say 500gb, that has many programs, pictures, videos, etc. Now, if you decide to open a large program or intense game, it might take anywhere from 2 to 10 seconds for your computer to read all this information and process it. Now, once that application/game is in memory, you can generally minimize and restore it freely and quickly in far less than a second. That is why Quick Base is so fast it has all your application's data in memory and can quickly recall it, including calculated data. This isn't a perfect analogy when comparing the power of servers to personal computers but the principle is still the same.

Let's look at an example. These are some fake numbers and data structure for the sake of simplicity and to illustrate the scenario.

So, looking at the above, we can see the text entered into the "Notes" field is consuming about 100mb of data.

Side note: If we were looking to reduce how much data was taken on the Projects table, we could look at some tactics such as programmatically moving the notes data to a file attachment field.

Aside from that ~100mb of data consuming "storage", we also will keep it in memory. Now, this is Quick Base so there is a good chance the customer is going to use this field in some logic, lookups, summaries, formulas and reports down the line to get even more value out of it. With that end goal in mind let's add a formula text field that previews the first 100 characters in the field. This enables reports to have a field that shows a portion of the notes, without having the whole text there which might make the report unwieldy.

Now, this is where things get tricky. If we were to pull a report of all 50,000 records and included the "Notes Preview" field, roughly 7.5mb of memory would be used at face value. But for us to calculate the length and parse the characters, we need to read in the entire Notes data at least temporarily.

What if we then pass in the "Notes" and the "Notes Preview" into a child table, called Tasks.

Side note: This is memory (RAM) usage, not app space. Therefore, it does not count towards any table size limits or your account limits. As well, while there are warnings and thresholds behind the scenes that our great operations group use to ensure application and platform stability, these are far higher than most of our customers will ever hear about, let alone be impacted by.

We are now talking potentially about 1gb of RAM used solely to handle these project notes and this was a relatively simple example. It wouldn't be uncommon in Quick Base for us to see these notes going through multiple relationships, used in many more formulas, summary fields and even permissions. For example, say you only wanted to grant permissions to a certain role when the notes contained a keyword. Quick Base lets you do that, orders of magnitude simpler than other technologies. You can see how much memory QuickBase will need to use to accomplish these requirements.

So why are we sharing all of this? We don't want our app builders counting kilobytes or megabytes. That is overkill. What we think is important is that Quick Base is providing an unparalleled amount of flexibility and value with relatively little effort and without the deep technical understanding that other platforms or tools would require. But, with great power comes great responsibility. Adding these lookups, formulas and other complex calculations is extremely powerful. Understanding, at a very high level, what comes along with that can help builders make informed decisions during application design and development and can help during regular maintenance and clean-up. 

Recommended Reading: https://community.quickbase.com/quickbase/topics/quick-base-makes-complex-logic-simple



10-01-2019 16:46

Quick Base has a "hard stop" at 500MB per table. Trying to store more than that the add attempt fails. Does anyone know an outside storage solution provider that would allow similar table relations as the table to table offering from Quick Base?

02-19-2019 15:24

To piggy-back/expand upon what Harrison is saying, Cross-app relationships is a fantastic and useful feature.   The risk is that doing so pulls both apps into the same CPU/Memory space within the QB cloud.   With large user volumes and/or large data sets... this can pose a problem.    For example, performing a large data import to App-A will impact the performance of App-B during that import as they are sharing resources when a cross-app relationship exists between the two.  Essentially any action on either app will affect that each other due to queuing within the single thread of allotted.

If I remember correctly... the following features/actions within a QB app will pull both apps into the same memory space:
1. Cross-App relationship
2. Report Link with an external App
3. Table-to-Table Imports with an external App
4. ...I think I'm missing one or two others?

NOTE:  Connected Tables does not pull the apps into the same CPU/Memory space as it acts more like a traditional ETL (Extract-Translate-Load) process to transfer data between the apps based on a refresh rate that you choose.

02-19-2019 02:42

We love the engagement here! It is important to remember that the space on disk only refers to scalar/data entry fields (the 2.9gb). The amount of memory is variable, based on all of the things discussed in this article (and more). For example, you could have an app that only has 300mb of data, but 20x that in memory. Conversely, an app could have 4gb of data and not much more memory. While I obviously wouldn't share information on a specific customer app, what I can share is that we have some extremely large environments (larger than the numbers mentioned here) which is due to building out vs up. All that being said, we acknowledge the need to provide greater guidance and clarity up front and during the build process. One example of this is coming soon - we will gently provide a dialogue box encouraging builders to read about data sharing strategies when creating a cross-app. It isn't so much that creating a cross-app is bad, but rather has risk/benefits just like any other tool. You can expect to see these types of things as well as more communications (like this thread) moving forward.

02-19-2019 02:33

Thanks, Kurt. One thing we want to be careful of (from our side) is encouraging builders to go down a path where they are analyzing individual fields down to the byte in a huge app. We feel this would be paralysis by analysis _and we would rather have our builders spending their time adding more value. We have to walk the line of helping people build great apps for the future, and also helping folks like yourselves who could use more advanced analytics currently. There are some more things my team is working on right now as it relates to field usage but it is too preliminary to share specifics.

02-18-2019 19:00

Thx. :)

02-18-2019 18:58

Mark, I want to give you a shout-out for the time you provide supporting this community.  I couldn't begin to count the number of problems your posts have solved for my team... not having to reinvent the wheel so-to-speak.  Thank you!

02-18-2019 18:42

Thx for sharing.  I thought I managed a large ecosystem, as we unfortunately have about 20 apps that are all cross-app connected.  They were built before Connected Sync tables existed.  There are about 250 users.

But not nearly as large as your App.

02-18-2019 18:36

I was reviewing the Feb release notes under the early access which pointed me to this thread.  I definitely feel it's on the right path.  I just wanted to provide some of the next steps to truly unlock the infinite usage fingers that sprout from a field after it is first created.

Don't get me wrong, the flexibility of Quick Base is phenomenal... but as you've pointed out... with that ability, comes great power to cause unexpected harm to end users.  From where I sit... the unexpected can be controlled by providing additional tools to visualize your field/relationship/formula impacts to the application before fully implementing them.

02-18-2019 18:31

Mark, to put it in perspective... I've been managing an Enterprise Project Management app for several years now that is currently ~2.9GB in size with ~1300 registered users.  This app easily swells over the allotted RAM limit.  Upon exceeding the limit, an application will reboot without notice.  We don't have any monitoring abilities to understand what our current RAM footprint is at a given time... so hitting a limit like this in business hours can easily cause a 30+ minute outage for the app to reboot and for queuing to come back under control with reasonable responsiveness.  We've been partnering with QB staff and our QSP for the past 9 months (including Harrison) to combat the issue with mixed results. Though we've optimized reporting/dashboards/limited-personal reports for quicker response times... our RAM consumption issue still persists.

I do believe that if we could create an hour of "quiet" time for the app to go-to-sleep / unload itself at night, the issues would not occur anymore.  The app never puts itself to bed currently.  This a very difficult item to control with the vast user base, nightly processes for our company, and nightly processes configured within Automations themselves.  We need 30-minutes of zero utilization for the app to unload itself.

I'm confident we'll get this figured out, but our users are definitely frustrated.

02-18-2019 18:08

Absolutely. Take a look at the early access available in the February release notes. We have some other things we are working on as well that are not yet written about.

02-18-2019 18:07

Hey Mark. There are some limits as it relates to RAM but they are infrastructure related. The way Quick Base scales is "out", not "up". So environments built over multiple apps that are not connected via cross apps (or similar methods) do really well. Most customers never get anywhere close to RAM limits.

02-18-2019 18:03

I'm not aware of any limitations on RAM.  It seems to me that the platform just provides as much RAM as the App and all its related connected apps through cross app relationships and report links, need.

02-18-2019 18:00

This all makes sense, though it becomes difficult to 'trace' where heavy fields are causing utilization issues.  It would be awesome to have an admin tool that could loop through a source field, all of it's uses, and come back with an impact statement for that field... such as total RAM consumed from this field and all of it's derivatives... possibly listing the RAM consumed for each usage on the field usage tab.  It's very difficult for developers to put a finger on which fields are causing the most consumption in a given app without extensive research and computation outside of the application.  This type of solution would empower developers to design within the limits of an allotted app and explore alternative solutions that are possibly not as RAM intensive but yield the same results for end users.

01-03-2019 20:18

Great article! Very interesting. I'll certainly keep this in mind while designing apps. I'd definitely like to see more "under the hood" articles like this one.

10-15-2018 15:08

Thanks for your feedback, Forrest. We can appreciate you wanting to know as much as possible about Quick Base. In this case, there isn't so much a cut and dry answer to an alternative. In fact, we aren't even saying a builder shouldn't take the above approach. Many factors come into play.

For example, the number of records, the rest of the complexity of the app, etc. We want to help our builders understand best practices as well as some inner workings and potential trade-offs of certain features or implementations. A potential workaround here could be a direct relationship to a table further down the chain (rather than multi-level lookups) or even passing certain data via Automations. When those scenarios arise, we would be happy to help evaluate and suggest best practices.

We are still exploring the best ways to share more technical information and what the appetite is among our builders - so it is good to know there is more interest.

10-15-2018 14:55

This is interesting, but where is the second half of your post?  To meet the same requirements (shortened notes field and full notes field that need to be available in child records), what you recommend that a builder do that doesn't use as much RAM?