Hello, I’ve experienced a few issues, but I’m replying here to see if this is a viable strategy to address them. My primary question is: if I run
TRUNCATE TABLE QUERY_CACHE (we also use a Postgres DB), will it cause any issues with Metabase?
I have seen other posts mentioning that deleting directly from tables can cause bugs, but that makes sense if you are deleting something that is referenced in other tables such as users or collections. These cache entries don’t get referenced elsewhere (as far as I know) so I’m hoping that means this would be safe.
If this would cause issues, then is there a safe way to actually clear the cached results? I am looking to minimize the size of our database, and my understanding is that rows in QUERY_CACHE never get removed, but only overwritten by new cached results. If that’s true, then the size of the table will only ever grow.
We have our own fork of the Metabase repo, and we added some functionality to the UI to allow people to manipulate the
cache_ttl value per-question. We were excited to let people have individual control of the cache settings on their questions, but a few hours after we rolled the feature out, our database crashed because it ran out of storage space. Before this incident, we had 20 GB free. Our global settings dictate that cached results can be no larger than 1000 KB, but we allow caching on any question that runs more than 2 seconds. We fixed the issue by increasing the size of our Postgres instance and disabling caching altogether.
Running this query:
SELECT pg_size_pretty( pg_total_relation_size('query_cache') ) tells me that the query_cache table is only 1055 MB. How is that table just over 1 GB, yet caching seemed to suddenly take up 20GB of our storage space? I’m wonder if something was just being written to the disk for our instance, but not being stored in this table.
Anyway, I am looking to see if I can prune away some old results while I’m testing to see how big this table grows. We may even consider a long-term solution that prunes away results only a nightly job or something.