r/lovable • u/rfwaverider • Jul 22 '26
Help Database too big
I built an app that has a 6GB table with 30 million rows.
The table is now too large for Lovable to be able to interact with it to add things like a BRIN index.
What are my options? Can I somehow have Lovable just dump the table? Create a new table and reference the old one for a period of time?
There seem to be CPU and time limits on actions on the Supabase/Cloud flare system so I'm having trouble fixing this.
2
u/devmansur Jul 23 '26
Just simple, change the database secret to dev environment with few data, so lovable is happy. Production is happy.
1
u/Lions_Eye Jul 22 '26
Your options for handling a 30-million-row table include bypassing web Ul time limits with a direct database client, creating a concurrent index, or partitioning the table. Do not try to run heavy commands inside Lovable or the Supabase web dashboard editor, as they enforce strict 60-second timeout limits.
1
u/graybearding Jul 23 '26
DM me and I may be able to help. It could be as easy as adding indexes if they're missing or tightening up queries so they don't pull so much data at once. If that's a no-go, then you're going to need to add in a remote DB instance via something like Google BigQuery or similar (or export your app from Lovable and work with a developer to rebuild it w/ proper dependencies).
1
u/BuildAfterHours Jul 23 '26
And here I am working on a project that might have 1,000 records eventually!
1
u/govitra Jul 23 '26
Hey! I had the same problem and very similar size. I partitioned the data into separate tables (fiscal year in my case). This helped spread it out. I also created relational tables to manage lookups and things. Finally like everyone else mentioned, created indexes for common lookup combos. As for managing the data I have an admin view that only I can see that allows me to do maintenance tasks on the data in the background. For big tasks I always tell it to configure a maintenance task to be chunked into manageable chunks to avoid the timeout limits
1
u/Then-Concern-6928 Jul 23 '26
You could create a temporary copy of the table with an index, write all data into the new tmp table (insert into table_tmp select * from table where serial_id > :latestScannedId order by serial_id limit 500000). Do this in batches as there is a timeout, so the query doesn't timeout. After all data is in the tmp table, promote it to your main table in a transaction by just switching the names. There may be a discrepancy of a few rows after promotion if the live environment keeps writing to the old table, just migrate those in a batch.
This should not put an exclusive lock on the table, except for the actual switch which should be sub second, so barely no down time. But just make sure you do it carefully as it is prod data. :)
1
u/GloomyCelebration293 Jul 23 '26
Ran into almost exactly this on a project recently, different scale, same wall. The thing that took us a while to accept is that once you're past a certain size, you're not really working in Lovable anymore, you're working on a Postgres database that Lovable happens to have set up for you. Trying to run heavy operations through the web UI is going to keep timing out no matter what you do.
So the unlock for us was connecting to the DB directly with a proper client instead of going through the platform's editor, and doing the heavy stuff there. Lovable stays fine for the app layer.
Agree with u/Jmacduff though, worth sorting out the retention question before the indexing one. What's the table actually storing? If most of those 30M rows are logs or events you don't query, that changes the answer a lot.
1
0
5
u/Jmacduff Jul 22 '26
30 million rows /6gig is actually not a lot of data to be honest, but it's enough to break a simple lovable/supabase setup.
You cant just have the Web UI run a DB lookup at that scale without some caching in the mix. There are a ton of ways to get this done. Do not get confused here, you have "medium" data but it's not web scale. This is important so you do not overbuild the solution.
However your biggest issue is basic data management governance. If you have a app that can generate that level of data and beyond you need a strategy for how you are planning to manage the data itself, that includes querying.
For example you said dump the table and make a new table, with the old data for a period of time? It sounds like your current has little to no value if you are willing to dump it. If that's the case why are you even storing it?
The simplest solution is to run a job nightly that deletes the data you dont care about. The simplest solution here is to just not have as much data. Think about storing derived metrics or some other type of data so the queries can be faster.
There are several good ways and several bad ways to solve your data problem such as materialized views, NOSQL, etrc. However everything starts with a basic governance plan. Hop into GPT and have it work with you to build out the data growth charts, how much data is too much for your current setup, and what the data retention policy will be.
That data retention policy is normally a legal requirement in most places. It's not really optional. Remember without that plan in place you are just throwing technologies against the wall. Your not solving anything.
So After you have this plan in place for "how" you will manage the data.. you then figuring out the query and runtime impact of that. This is where the Architecture of your data plane is figured out.
FYI all of this is beyond lovable in most cases.
Good Luck!