Row limits - The dark side of Glide :)

I ran across this limit when trying to create a user triggered data export that sent CSV data via webhook.

Once the data in the cell reached the 1MB limit, I had to have a conversation with the users. It turned out that there were many, many columns that were only included in the export because we thought there was unlimited space.

We paired down the list to only the essential columns, and now we should be good until Glide creates a native triggerable data export.

1 Like

Hola!

We have talked about this issue earlier and my opinion keeps being the same:
The lack of a classic Client/Server model to handle the data is the source of problem when we have bunch of data (50k+ rows).

Glide needs to use/read its internal databases (base on Firebase) and no matter what external “database” we try to use (GS, Excel, MySQL, etc), any updated or new row (or value) must be replicated to Glide database in order to be loaded and used on our APPs.

On small/mid-size APPs, load all data can be a good idea but on large ones the performance and manipulation problems appear as we know.

For this case (while Glide finds a solution) my workaround is move the historical data out of box (your APP) and access it via API. You can still have/load a small operational data on your APP for regular operations (let’s say 3k-5k rows → 4-6 weeks) but when you need to read or work with historical data, the procedure is to request it via an API and handle it on APP temporally while the user is active.

If my data source is Google Sheet and I have to create an API, I’d use Sheet.Best, I have tested it with a GS with 50k rows (15-17 columns) and its replies last 3-5 secs, an excellent performance. Instead, if you are using Glide Tables, you’ll at least need a Business plan to get it.

I hope it helps.

Saludos a todos!

1 Like

Thanks, indeed a great help, as always.

I actually didn’t know about this service. However, one of our top concerns when building corporate apps is data security. Sheet.best connection process seems to be almost the exact opposite to keeping the data secure :slight_smile:

But your input lit up a lightbulb towards another path to consider: Glide Table API.
My idea was to send the users to another app (hopefully via a web-view inside the “main” app). I’m not sure Glide allows nesting of one app inside a web-view of another app. Another challenge I see is that this API doesn’t allow access to user-specific columns. My initial thought was to have a column that contains the clientID, so that this secondary app can pull all of the client’s historical data.

All in all, I understand the notion of keeping it light and nimble, but I’m not sure why we need it to be SOOOOO light. Glide’s database infrastructure seems to be very restricting, perhaps even by design.
It’s not a direct comparison of course, but if you look as Salesforce, these row limits are non-existent from the very first paid tier (~$25K/month/user).

1 Like

Hi all,
I have a thought:
Can we use arrays here?
For example:
Our table will include client details
One of the columns will be an array of projects related to that client

This is instead of using a seperate table for all projects and linking it back to the clients table with a Relation column. This way we can dramatically downsize the number of rows we use.

What I still cannot figure out is how to “move” array from table to table. More specifically, how to bring the array to our working table for a custom form, append a new row to it and then send it back to the original table with the newly appended values. In fact, array columns don’t appear in any of the actions we have in Glide. Am I missing something?

Another challenge (or maybe it isn’t), which I believe is much easier, is to edit the array values (for example, to change the project name).

BTW - One downside here, assuming we get it to work as detailed above, is that we cannot back up this information to Google Sheets (or any other data source I think). The workaround is to use a webhook to add a row to another Google Sheet, not connected to the Glide App. This seperate Google Sheet should be somewhat of a replica of the Array column we created on Glide.

Be mindful that Joined List columns will join arrays into single value comma delimited text strings.

Split text columns will convert comma delimited text strings into arrays.

Once you understand that, then you can understand how to move “arrays” from one table to another, as well as write that data to a single cell in a google sheet. A single value text string can be used with functions that only work with single values. Once it’s moved, you can still use a Split Text column to convert it to an array.

There are several glide plugins that let you add or remove items from arrays in various ways.

1 Like

Hi Jeff,
Yes, I am just now playing with some Split text and Joined List. I’ve also tried to use the Make Array column with the notion that it’ll create a multiple dimension array. I believe this isn’t the case and the generated array is a single dimension chain of arrays.
So, assuming we have such text, split text and arrays, how do we combine it all into a project array that lives inside a client row?

That’s a pretty high level question and really depends on your particular situation, but generally, if you have an array, you can use a Joined List column to convert it to text. Then using a set column action you can write it wherever you need to. Later, a Split Text column will convert it back to an array when you need to.

1 Like

Are we talking about these columns?
https://www.glideapps.com/docs/reference/basic-columns/array-columns

If so, I’m not sure I understand how to use this.

I’d like to have all data inside a single row the client row. The data we need is:

  1. Project list per client
  2. Activities per project

I’m stuck at understanding how to allow an unlimited number of projects per client (so I don’t have to have columns such as project 1, project 2…which are combined by Glide to a “project” array column). This is even more relevant for the activities per project, which can easily reach thousands of activities per a single project.

But even before that, I’m stuck at how to show (which layout component) the list of projects to a specific client. And then, which layout component to use inside the custom form that logs activities. In this form the user will chose client and, based on the client, chose a project.

You’re probably thinking I’m showing signs of complete lack of knowledge. Sadly, I’m starting to feel the same way and it’s getting hard to attribute this to a simple “writer’s block”. I’m really getting confused in this infinite race to downsize the app to a minimum number of rows.

I’m talking about any array, regardless of how it was created. A single value is a single value. An array is a collection of multiple single values. There are several different ways to end up with an array, but they are pretty easy to recognize in the data editor because each value is contained in separate bubbles, whereas a single value would not be in a bubble.

I don’t know enough about your project to give you step by step advice, but, just speaking about client projects, the goal would be to create a comma delimited list of project ID’s in the client row. A comma delimited list is simply a single text string of multiple values separated by a comma. If you use a Split Text column pointing to that comma delimited list, then you can convert it to an array. Then you can create a relation to link that array to a projects table. That relation can be displayed as an inline list on the client details page. All of that is done with a single Basic Text column, a Split Text column, and a Relation column.

How that basic text column gets populated with a comma delimited list of projects can vary depending on your particular app, and how you plan to add a project to a client. It could be a simple as a multi-select choice component that lists all projects and writes to the basic text column in the client details row. Or it could be much more elaborate depending on how you want projects to be added.

As far as Activities, I assume you mean activities per client per project. That would involve multidimensional arrays, which can add another “dimension” of complexity. It is possible to technically store arrays within arrays, by using different delimiters between projects and between activities. The better way would probably be to build a JSON object using templates and joined lists, and then querying what you need out of that JSON as needed. Inserting or removing data from within an array of JSON object can become quite complicated.

Like I said. We are talking about very high level theories here. It’s all possible with some work, but the deeper we discuss it, we start getting into hundreds of possible different tangents to achieve the result you want. We could discuss it until we turn blue. I’d stay start with the basics I provided above to store a list of projects in a client row. Figure out how you want populate that comma delimited list of projects. Start there before trying to envision a complete rewrite of your project.

Storing projects is easy, because I assume you would only be storing a value to link the client in the client table to the project in the project table. Storing Activities could be a whole other thing since it seems like you want to use this as a table replacement in place of a separate Activities table, and especially when you are talking about 1000’s of activities for a client, then you are talking about quite a large set of data in a single cell. How much data is contained in an “activity”??? Like what’s been discussed before, you start to run to performance issues when you store to much in a cell.

I don’t have a single straight forward answer for you. We’re talking about a very grey area as far as what’s possible, how well glide could handle it, what you want to store in a single cell, and how easy it is to add, edit, delete and unpack that data into a usable form. I think you’re just gonna have to try different things and see what works or doesn’t work. If you get stuck on something specific, I’m sure we can help, but at this point, we are just talking about theories, and talking about something that could have 100 different solutions, and since you are the only one that truly knows your own app, I think some of those solutions can only be determined by you.

1 Like

Wow, thanks. Very detailed and mind opening.

Could you please explain which type of table are you referring to here? Is it a general table for “project types”?

Text strings, not crazy long.

So we come back to the same conclusion: Glide is a small database platform.
If my original “vision”, every single aspect/element is a table on its own. This is the most organized, most easy to manage and even most easy to back up. For example: A client table, linked to a projects table, linked to an activities table, linked to a messages table and so on. It’s also somewhat more futureproof. Each time we wish to add an aspect/dimension, we simply add a table and a relation to it. I am still having trouble to believe that Glide is always assuming the apps are meant to run 100% on mobile device. This is the explanation I got for the row limit. However, this explanation doesn’t explain the limits on the Pages platform, meant for larger screens, with heavier computing power. All discussed here with @david


Yeah. I understand that @Darren_Murphy is working on something like this. Hope he’ll share some more insight soon.

I’m talking about creating those three columns in the Clients table (Basic Text, Split Text, and Relation). I’m assuming you have a Clients table as well as a Projects table that you want to link together. Again, I know nothing about your app, so I’m just making several assumptions here.

Well, 100 characters multiplied by 1000 activities is 100,000 bytes, which is about a tenth of what a cell can theoretically hold. Not to mention if you start wrapping any JSON or HTML into each activity, which can increase the size of data.

It’s all in how you interpret it. I’m sure glide itself is storing billions of rows of data. It’s not a limitation of the database that glide uses. It’s the fact that the phone in your hand is not a very good database server. Keep in mind that the way glide works now, is that an apps database is sent to the user’s device, so they have a local copy which periodically syncs with the glide servers. Also keep in mind that that ALL calculations and computations occur on the user’s device. It’s not a traditional server/client scenario. Glide hands off some responsibility to the end users device as well. That’s how glide was designed from the beginning. While it does limit the amount of data that can be used, conversely, it provides for a much more responsive user experience where everything is instant instead of waiting for data to travel back and forth between client and server before the user see’s results.

Doesn’t matter if it’s an app or page. They both run under the same computation model. If I open a page on my phone, or an app on a desktop, you are still limited by the resources that the end user has on their device. Sure a desktop will most likely be faster and more capable than a phone any day, but you don’t know what kind of device a user is using when they open your page or app. It’s not only the number of rows. Adding columns is exponential. Adding computed columns is exponential computations that need to occur. Until glide can fully develop a hybrid solution that not only provides for very large datasets while still providing a responsive user experience with little to no wait times…we are stuck with these limitations.

I don’t do theoretical discussions very well. I’m a try it, do it, see what sticks, and learn from the experience kind of guy. We can go back and forth all day on this without getting anywhere. If it were me, I would be in there getting my hands dirty and trying different things. I’ve been working for over 3 years to optimize my app for speed and performance. It’s evolved quite a bit, and I’ll never be done. I’ve tried some really crazy out of the box things. Some work and some don’t. It’s a continual process, but you won’t know what you can do until you get in there and try.

But overall, I would at least have a conversation with @Sean_Martin. They are probably capable of doing things with Enterprise customers that I’m not fully aware of. I know they have experimented in the past with the ability to work with larger datasets, and they probably do now with their larger customers.

3 Likes

Indeed!

2 Likes

Thanks for the truly positive and supportive attitude.
Everyone in my organization can tell you that I’m a true advocate for Glide from the moment I started using it. They can also tell you that I’ve lost this passion for Glide over the last few months.

Yes, I’ve touched base with them already and they offered the Big Query option. Not only is it far from being low-code, it’s also rather expensive for an SMB.

I think it’s time to say goodbye to Glide from our side. We seem to fall in between the seams between too big and too small. There’s no middle ground.

One thing is for sure: the community here on Glide is one of a kind. You’re all very helpful, always. Many thanks for that and I’m sure you’ll keep doing so for those that come after me :clown_face:

Goodbye and good luck

Regarding your concerns about IMPORTRANGE, it can be a viable option if implemented carefully. You can have a separate sheet for archived data and use IMPORTRANGE to fetch it when needed. However, be mindful of potential performance impacts.

Using the Glide API could be a more advanced solution but might indeed involve more coding.

I don’t see the 25,000 row limit as the dark side, but I do need to address my row count and I could use some expert advice on which way to proceed. Soon… :fearful:

Notes

  • Business plan
  • Transactional data, always adding rows
  • New data comes into the app via Zapier as well as within Glide. Since the app was in use before the Zapier Glide integration, two of the three main tables are in Google Sheets and I’d like to get away from GS for performance reasons.
  • Would like to be able to access historical data if possible
  • Currently using a lot of computed columns that aren’t compatible with Big Tables, mainly lookups, but also some rollups. Most of these wouldn’t be easy or even possible to change out for basic columns. An example would be lookups into Items from Jobs table.
  • Main tables all have 150-200 columns, so there will be a lot of work with any change. The app grew organically, so there is certainly some pruning that could happen

Three approaches I’m considering are

  1. Completely rethink the logic and see if I can make Big Tables work
  2. Come up with an archiving strategy to keep the working data set below 25K. Also swap GS for regular Glide Tables.
  3. Utilize the JSON trebuchet method to convert one or even two of the three main tables to cells. I would need an anti-fragile code step in Zapier to store line items this way.

Pros and cons with each of these approaches. Any words from the wise? Happy to supply more details as needed, or start a new thread if that’s more appropriate.

I’ve never grown an app to 25k+ rows, so I don’t speak out of experience. But I’m trying to put myself in your shoes. Here would be my thought process.

This leads me to think that you’ll blow through 25k rows no matter what you do, it’s only a question of time. To me it sounds like you’d be better off with a high-scale data source, so Big Tables or SQL. And you’re on a business plan, so no issues there.

High-scale data source, so Big Tables or SQL.

I’ve never used Big Tables, but I have understood that they are limited in some way (though this might change). So I’ve always made a mental note that if I ever had to use them, I’d use them as a data dump only, a sort of running log. I’m uncomfortable having to wiggle my way around limitations, to me it’s like having a car that can only go straight or turn left, I know I can turn right by turning left three times, but that one time I will urgently need to turn right will be show stopper. So the way I see it, if it were me, Big Tables would not be an option.

It sounds like you are going to have to rebuild either way.

  1. Completely rethink the logic and see if I can make Big Tables work: Why not. I would stick to a log, but I know Darren has done magic with them.

  2. Come up with an archiving strategy to keep the working data set below 25K. Also swap GS for regular Glide Tables: Archiving on Big Tables or SQL?

  3. Utilize the JSON trebuchet method to convert one or even two of the three main tables to cells: It sounds hacky.

  4. I wonder if rebuilding on an SQL database would be a viable long-term solution :thinking:

1 Like

Thanks for the thoughts Nathan and I knew someone would come up with a 4th option! SQL would be a whole new adventure.

@Darren_Murphy How would you go about setting up Jobs and Items in Big Tables when it had been a multi-relation with lookups into Items? In my case, the relationship between Jobs and Items is a bit complex with several calculated statuses. Item status depends on Job status, for example: a canceled job overrides any item status. At the same time a calculated job status depends on the combination of item statuses.

1 Like

It’s hard to say without looking at it. But I agree with @nathanaelb, if it’s a transactional table that is expected to grow quite large over time, then you should definitely be considering moving to a high scale data source.

My general rule of thumb is if I expect more than about 10k rows per year, then I’ll use a Big Table. I haven’t yet used any of the other high scale data sources. Not because I haven’t wanted to - I just haven’t had the opportunity yet. I had more than 20 years experience working with PostgreSQL and MySQL before I came to Glide, so I’m certainly looking forward to getting my hands dirty with those at some point in the future.

Getting back to Big Tables, I guess my advice would be to try it. Make a copy of your App, add a Big Table and import some of your data. Play around with it and see what does and doesn’t work. Make sure you enable the “Extra computed columns with Big Tables” preview feature. If you use any Helper Tables to interact with the Big Table, make the Helper Tables Big Tables as well - even if they have only a single row. This is because some of the new computed column support only works from Big Table → Big Table, and not from Regular Table → Big Table.

There are some limitations with Big Tables, but the list is a lot shorter than it was 6 or so months ago, and it’s getting shorter all the time. Don’t let the limitation list scare you off. In my view, the benefits far outweigh any limitations, and I’m yet to find a limitation that I haven’t been able to work around. You won’t know for sure until you give it a try. Get in there and give it a go.

3 Likes

Now I’m looking forward to using them at one point.

1 Like

Also see below for a bit of general advice on migrating from regular tables to Big Tables: