Practical code to trim large audit / save backend tables

💬 Self Hosted Retool
1

Hi Retool Community,

I've noticed that two of our backend tables are growing significantly over time as we continue to use them. These tables now occupy approximately 8GB of space and take much longer time to back up (I'm backing up the whole Retool volumn).

image
image1311×363 19.5 KB

Could you provide some queries to trim these tables without breaking the integrity of the backend data? Specifically, I'm looking for:

  • Query to back up the data from these tables by date range.
  • Query to trim the data from these tables.
    • Trim by date range
    • Trim by save type (for exemple, I may want to perserve all versioned releases, but drop all intermediate change history)
  • Query to recover the data backed up in step 1.

Thank you for your assistance!

2

Hi @zelterNN ,
So our recommended storage for self-hosted Retool is 60GB, and you are well under that. (see this document for description of requirements for self-hosted Retool).

Having said that, you can delete audit_trail_events with no adverse effects to your functioning. With respect to the page_saves table, you can join that with our tags table which lists all releases for an app. You can then delete all page saves that took place before the latest release was made to avoid any changes not to be reflected in your latest release. I would advise caution, however, in performing any db surgery on your page_saves tables. This may affect your ability to view prior releases. Sorry, but I do not have any specific queries to share with you.

Another thing is for you to move to Retool Cloud instead of self-hosted, if that is a viable option for you. Then you would not have to worry about storage space in the backend db.

3

Hi @lindakwoo

Thank for the info.

I'm actually concerning the prolonged time in backing up the database in full instead of the utilization of storage space (I actually assign much more place than 60GB for Retool). Besides, the audit log seems having issue fetching the log occasionally as time goes by. I'm thinking if the ever fast growing audit table impacts its indexing and performance.

Unfortunataly, Cloud solution is not viable for us for now. My company is under strict regulation of HIPAA and our database does not expose to the public.