Hi all, One pic to explain the problem: [image] Surprisingly (or not), the app is still semi-functional. The majority of these ~73K rows are activity rows. These are rows that refer to a specific interaction with a client. On the upside, our team is rather meticulous and have excellent logging ethics. On the down side. this isn’t Glide’s finest hour. Although these are rather simple rows, without any complex computations. It is not a good situation. Any ideas about how to archive, yet allow users to access data upon request? Something like the 2nd layer of a database, which allows high volume storage, but low loading times. In other words, it will allow users access to VERY old data, but they’ll know it’ll take the system some time to retrieve this data. I’ve been reading Jeff’s detailed post about his idea. [image]Auto Archiving, Sanity Check, Reusable Forms, etc A little background…I’m building a process in my app that will replace the email address with a template value. What it does is replace user@email.com with ARCHIVE-user@email.com. The email column is a row owner column. Once ARCHIVE is appended to the email, it becomes an unowned row and is permanently hidden from the app. I’m doing this in an effort to speed up my app by hiding data that’s more than 2 years old. Unowned rows will not be downloaded, and thus, unnecessary computations won’t be performed on those old rows. That should hopefully speed up the app by reducing the amount of data that has to be processed. @Jeff_Hager - has this indeed allowed you to lower the row count and, more importantly, speed up the app? How will you approach the notion of still allowing users access to your “ARCHIVE-” rows? I was thinking about IMPORTRANGE, but I’m afraid it’ll mess up the app somewhere downstream, or even upstream. It’s also a bit challenging as this IMPORTRANGE will need to be user-specific but Google Sheets cannot see user-specific columns. Or can it? Maybe this is something we need to do with the Glide API, but that’s hardly “low code”. Any other ideas? I know BQ is already in the works, but that for Enterprise plan that are expensive, to put it mildly. Thanks

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.

[image] Jeff_Hager: if you have an array, 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. [image] Jeff_Hager: depends on your particular situation I’d like to have all data inside a single row the client row. The data we need is: Project list per client 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.

[image] Test_Test: Are we talking about these columns? 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. [image] Test_Test: I’d like to have all data inside a single row the client row. The data we need is: 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.

Wow, thanks. Very detailed and mind opening. [image] Jeff_Hager: Then you can create a relation to link that array to a projects table. Could you please explain which type of table are you referring to here? Is it a general table for “project types”? [image] Jeff_Hager: How much data is contained in an “activity”??? Text strings, not crazy long. [image] Jeff_Hager: you start to run to performance issues when you store to much in a cell. 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 [image] Excel / Airtable as data sources. Pros/Cons? Ask for Help The 25k row limit is a technical limit, constrained by the RAM on your phone. We’re working on it–it’s not an arbitrary business limit, for example. Glide Tables will always be the best performing, most scalable, most powerful choice. No external data source when added to Glide will give you extra scale or performance. [image] Jeff_Hager: The better way would probably be to build a JSON object using templates and joined lists Yeah. I understand that @Darren_Murphy is working on something like this. Hope he’ll share some more insight soon.

[image] Test_Test: Could you please explain which type of table are you referring to here? Is it a general table for “project types”? 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. [image] Test_Test: Text strings, not crazy long. 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. [image] Test_Test: So we come back to the same conclusion: Glide is a small database platform. 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. [image] Test_Test: 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 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.

[image] Test_Test: Thanks Jeff. As always, a delight to read your detailed reposes. Indeed!

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. [image] Jeff_Hager: They are probably capable of doing things with Enterprise customers 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 Completely rethink the logic and see if I can make Big Tables work Come up with an archiving strategy to keep the working data set below 25K. Also swap GS for regular Glide Tables. 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. [image] HeartwoodTreeCo: Transactional data, always adding rows 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. [image] HeartwoodTreeCo: Would like to be able to access historical data if possible High-scale data source, so Big Tables or SQL. [image] HeartwoodTreeCo: Currently using a lot of computed columns that aren’t compatible with Big Tables, mainly lookups, but also some rollups. 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. [image] HeartwoodTreeCo: two of the three main tables are in Google Sheets and I’d like to get away from GS for performance reasons It sounds like you are going to have to rebuild either way. 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. 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? Utilize the JSON trebuchet method to convert one or even two of the three main tables to cells: It sounds hacky. I wonder if rebuilding on an SQL database would be a viable long-term solution :thinking:

Row limits - The dark side of Glide :)

Ask for Help

Darren_Murphy April 25, 2024, 7:09am 40

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

Topic		Replies	Views
Glide Big Tables - pricing Ask for Help big-tables	56	2232	January 18, 2024
Are there any other sites like Glideapp? Ask for Help	49	1966	February 5, 2021
25k row limit workaround Ask for Help	16	319	May 18, 2026
Any suggestions to have more rows beyond 25000? Ask for Help	11	1970	March 8, 2022
How scalable is Glide Ask for Help	41	12670	January 10, 2022

Row limits - The dark side of Glide :)

Related topics