Can I delete data on query table

Currently, we have 3M rows (with ~30GB stored) on query table. Can I delete data on this table?
SQL get data storage:
select pgs.schemaname, pgs.tablename, pgc.reltuples as num_rows, count(pgs.attname) as num_columns, sum(pgs.avg_width)pgc.reltuples/(10241024) as table_size_mb
from pg_stats pgs left join (SELECT nspname AS schemaname, relname, reltuples
from pg_class C
left join pg_namespace N ON (N.oid = C.relnamespace)
where relkind=‘r’
) pgc
on pgs.schemaname = pgc.schemaname and pgs.tablename = pgc.relname
where tablename = ‘query’
group by 1, 2, 3

Hi @dungngo2912
I’m not sure I understand. If you want to use Metabase to delete data, then no, Metabase only has read access to your database, so you would need to delete data with another tool.

@flamber Yep, I do understand. In my case, query table created by Metabase and it on PostgreSQL Database I used when setup Metabase. I think this table seem to table query_execution so I want to delete its data.

@dungngo2912
Ahh, okay, yes you can trunacte query_execution or make a script that cleanup daily.
There’s an issue open about controlling size of the table:
https://github.com/metabase/metabase/issues/4155

@flamber Yep. I truncated table query_execution but can I do seem with table query???

Screenshot%20from%202019-06-26%2016-02-35

@dungngo2912
I’ve never tried with query. I’m not sure if it is used by other tables.
You can try renaming the table, and create a new table with same structure in it’s place.

@flamber Ok thanks U, i will try with your suggest solution. Waiting for my good news.

Sadly, I can’t rename the query table. I would try:
alter rename....
That makes Metabase service crash immediately.

Not sure about MySQL, but renaming tables isn’t supported elsewhere (though that’s often hidden by a gui).
Try creating a new table, inserting all the rows from the existing table, then truncating.

@dungngo2912
May I recommend that you don’t try to rename tables or do other major changes to the metadata with Metabase running.
If Andrews suggestion doesn’t work, then simply backup metadata and truncate the table.

Hey team !
Since this discussion's last message, it seems Metabase introduced a feature (for Paid plans) to clean up the query_execution table after a defined number of days through the MB_AUDIT_MAX_RETENTION_DAYS variable.

Is there a rationale not to include the query table in this cleaning up logic? The query table is taking considerably more disk space in our case, because of the very long query strings.

Is it safe to delete records in this table (e.g. if they do not map to any recent query executions) ?

Thanks in advance :pray: :pray:

We should clean that table as well, there’s no reason to exclude it. Always do a backup first

1 Like

Thank you for your reply Luiggi !

How about the view_log table, is this table also safe to clean up ?