Database size not shrinking after deleting data

What you see

You deleted a large share of a table, but the database size on the dashboard's Usage & billing page and in Studio stays the same, or keeps growing.

Why it happens

DELETE doesn't remove rows from disk right away. Postgres marks them as dead, because other transactions might still need to see them. Later, autovacuum clears dead rows and marks their space as free inside the table's files, so future inserts and updates can reuse it. The files themselves don't get smaller, except for empty pages at the very end of a table.

That's normal and usually what you want: a table that fills up again reuses the space without growing. To actually hand the space back, the table has to be rewritten into new files.

How to fix it

1. See the size and the biggest tables in this project's database:

   select pg_size_pretty(pg_database_size(current_database())) as database_size;
   
   select c.oid::regclass as table_name,
          pg_size_pretty(pg_total_relation_size(c.oid)) as total_size,
          s.n_live_tup as live_rows,
          s.n_dead_tup as dead_rows,
          s.last_autovacuum
   from pg_class c
   left join pg_stat_all_tables s on s.relid = c.oid
   where c.relkind in ('r', 'm')
   order by pg_total_relation_size(c.oid) desc
   limit 15;
   

   total_size includes indexes and TOAST (large values stored out of line).

2. If the table is now empty, or you can empty it, truncate frees the space immediately:

   truncate table public.chat_events;
   

3. To shrink a table that keeps some rows, rewrite it with vacuum full:

   vacuum (full, analyze, verbose) public.chat_events;
   

   Two things to know first. It takes an exclusive lock, so every read and write on that table waits until it finishes. And it needs free disk space for a full copy of the remaining rows and indexes while it runs. Do it in a quiet period, one table at a time. vacuum full can't run inside a transaction block, so run it as a statement on its own.

4. If only indexes are bloated, rebuild them without blocking writes:

   reindex table concurrently public.chat_events;
   

5. For tables where you regularly delete old rows, consider partitioning by time and dropping whole partitions. Dropping a partition frees space at once and needs no vacuum. See Partitioning an existing table.

You can only vacuum full tables your role owns. Tables in auth, storage, net and other Supabase schemas belong to Supabase's own roles.

On SupaHost

• Your database quota is measured with pg_database_size for each project's database, added up across the instance's projects. The query in step 1 shows exactly the number that counts for this project. The usage figure updates on the next measurement after you shrink a table.
• Storage files aren't in the database size; they count toward file storage. Deleting rows from storage.objects by hand doesn't delete files. See Unexpectedly high storage usage.
• Cron run history lives in the instance's own postgres database, not in your project's database, so it doesn't count toward your project's size. It's pruned after 14 days.
• The VM's disk grows automatically and never shrinks. That's separate from your database quota, which is what you're billed on.
• vacuum full locks the table for its project's Data API too. With a pool of 5 connections per project, a long lock can make that project's API requests queue up.

Related

• Usage, quotas and add-ons
• Quota alert emails and going over
• Unexpectedly high storage usage
• Partitioning an existing table