How to reduce your Retool main storage DB size (self-hosted)

🧑‍💻 Retool Tips & Tricks
, ,
1

You can query your main storage DB to find out which tables use up how much space in your DB with:

SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size,
    n_live_tup,
    n_dead_tup
  FROM pg_stat_user_tables
  WHERE schemaname = 'public'
  ORDER BY pg_total_relation_size(relid) DESC
  LIMIT 20;

Retool's main storage database usually grows unexpectedly due to two tables:

  • page_saves: stores every autosave of every app. Independent of Git version control. Deleting from Git does not shrink this.
  • audit_trail_events: stores audit log events. On self-hosted, retention is manual only.

Before you start

  • Take a backup of the main storage DB. Every option below is destructive.
  • Test on staging first. Not just backup, actually run the same operation on a non-prod copy.
  • For manual DELETEs, run them inside a transaction (BEGIN; ... COMMIT;) with a COUNT first - that lets you verify the affected row count and ROLLBACK before committing if anything looks off.
  • Main storage tables have inter-table dependencies. Only touch what's specifically covered below.
  • Ensure you have auto-delete branches enabled. Stale branches keep page_saves alive which matters as the compaction command only deletes records for unprotected records.
  • Please configure queries to something you are fully comfortable with.

Cleaning up page_saves

Preferred: run the built-in compaction script (self-hosted 3.178+):

./retool_backend --run-pagesave-compaction

Shell into a backend pod and run this. Cloud handles it automatically; self-hosted does not. This command does two things: it removes entries for apps that have been deleted, and it compacts app edit data older than a month. It respects references in app_metadata, branches, commits, tags, source_control_relationships, and related tables. As a safety measure, even if a save has no references pointing to it, the most recent one is always kept so the current state of the app is never lost.
Note that compaction skips protected apps - as a workaround you may need to unprotect -> run compaction -> reprotect.

Optional flag: --concurrency N , ie. --concurrency 4 (controls concurrent DB connections per org, defaults to 1).
Can be run as often as needed. Docs on this here.

Manual cleanup (< self-hosted 3.178 or if you need custom control)

If you use Releases, this is the safe way to proceed:

  1. Create a new release of your app (this is what you'll benchmark against).
  2. Join page_saves with the tags table (tags lists all releases per app).
  3. Decide how far back you want to keep - for example, "everything from the last 2 releases."
  4. Delete page_saves rows older than the cutoff release for each app.

Deleting a page_save means you cannot restore that specific version/release. Every tagged release preserves the version it points to, which is why release-based cutoffs are safe.
Also note that running the manual clean up will delete records on existing and deleted apps.

Cleaning up audit_trail_events

The audit_trail_events table is safe to trim as needed. You can delete old rows via a Retool workflow:
Connect your main storage DB as a Retool Postgres resource, then build a Retool workflow that deletes rows older than your chosen retention window on a schedule. This is the most common self-service approach.
Deleted rows are permanent audit history loss.

You can also disable audit logs entirely by setting DISABLE_AUDIT_TRAILS_LOGGING=true which stops new audit events from being written to the storage DB.
Existing rows are untouched - combine with option 1 or manual DELETE to shrink what's already there.
Note that this stops all audit events including sign-ins, permission changes, and query executions and therefore is not appropriate if you rely on audit logs for security/compliance.

Reclaiming physical disk space

Postgres does not immediately return disk to the OS after DELETE. If you specifically need disk reclaimed:

  • Standard VACUUM reuses space within Postgres but doesn't shrink the file
    • VACUUM (VERBOSE, ANALYZE) page_saves
  • VACUUM (FULL) reclaims disk but takes an exclusive lock (requires downtime)
    • VACUUM (FULL, VERBOSE) page_saves
  • pg_repack extension does the same without locking, if installed

Please know that this is Database Administrator territory, not Retool support territory and your infrastructure team should handle this.

8 Likes
Onpremise Retool PostgreSQL size