Supascale
Sign Up

Database Maintenance for Self-Hosted Supabase: VACUUM, WAL, and Disk Management

Learn essential PostgreSQL maintenance tasks for self-hosted Supabase: VACUUM, ANALYZE, WAL cleanup, and preventing disk space disasters.

Cover Image for Database Maintenance for Self-Hosted Supabase: VACUUM, WAL, and Disk Management

When you self-host Supabase, you inherit full responsibility for PostgreSQL maintenance. Unlike Supabase Cloud where these operations happen automatically behind the scenes, your self-hosted instance won't vacuum itself, won't manage WAL files, and definitely won't warn you before running out of disk space at 3 AM.

This guide covers the essential maintenance tasks every self-hosted Supabase operator needs to understand: VACUUM operations, Write-Ahead Log management, disk space monitoring, and establishing a maintenance routine that prevents those dreaded "disk full" emergencies.

Why Database Maintenance Matters for Self-Hosted Supabase

PostgreSQL doesn't immediately reclaim space when you delete data. Instead, it marks rows as "dead tuples" that remain on disk until a VACUUM operation cleans them up. In a busy application with frequent updates and deletes, these dead tuples accumulate rapidly.

The consequences of neglected maintenance include:

  • Bloated tables that consume far more disk space than your actual data requires
  • Degraded query performance as PostgreSQL scans through dead tuples
  • WAL file accumulation that can fill your disk in hours during high-write workloads
  • Failed replication when Realtime or read replicas can't keep up with changes

One of the most common issues reported by self-hosters is discovering their Supabase instance has ballooned to tens of gigabytes despite having only a few hundred megabytes of actual data. The culprit is almost always unmanaged WAL files or disabled autovacuum.

Understanding PostgreSQL VACUUM

VACUUM is PostgreSQL's garbage collection mechanism. It reclaims storage occupied by dead tuples and makes that space available for reuse.

Types of VACUUM Operations

Plain VACUUM runs without blocking normal database operations. It marks dead tuple space as reusable but doesn't return it to the operating system:

-- Vacuum a specific table
VACUUM your_table_name;

-- Vacuum with statistics update
VACUUM ANALYZE your_table_name;

VACUUM FULL rewrites the entire table to a new file, actually shrinking disk usage. However, it requires an exclusive lock, blocking all reads and writes:

-- Warning: This locks the table completely
VACUUM FULL your_table_name;

For production self-hosted Supabase, avoid VACUUM FULL during business hours. Use plain VACUUM for routine maintenance and reserve VACUUM FULL for scheduled maintenance windows when you can afford downtime.

Checking Table Bloat

Before vacuuming, identify which tables need attention. The Supabase CLI provides a convenient command:

supabase inspect db bloat

Or query directly:

SELECT
  schemaname,
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Tables with a dead tuple ratio above 20% are good candidates for manual vacuuming.

Configuring Autovacuum for Self-Hosted Supabase

PostgreSQL's autovacuum daemon handles routine vacuuming automatically, but the default settings may not match your workload. For self-hosted Supabase, you should tune these parameters based on your usage patterns.

Key Autovacuum Settings

Access your PostgreSQL configuration through the Supabase Studio SQL editor or by modifying postgresql.conf:

-- View current autovacuum settings
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_threshold;

Common adjustments for production workloads:

-- Lower thresholds for more aggressive vacuuming
ALTER SYSTEM SET autovacuum_vacuum_threshold = 25;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_analyze_threshold = 25;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.025;

-- Reload configuration
SELECT pg_reload_conf();

These settings trigger autovacuum earlier, preventing bloat from accumulating. The trade-off is slightly higher background I/O, which is usually preferable to emergency maintenance.

Per-Table Autovacuum Settings

For high-churn tables like session stores or event logs, configure more aggressive per-table settings:

ALTER TABLE sessions SET (
  autovacuum_vacuum_threshold = 50,
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_threshold = 50,
  autovacuum_analyze_scale_factor = 0.01
);

Managing Write-Ahead Log (WAL) Files

The Write-Ahead Log is where PostgreSQL records all changes before applying them to the database. WAL files are critical for crash recovery and replication, but they're also the most common cause of disk space emergencies in self-hosted Supabase.

Why WAL Files Accumulate

WAL files stick around until:

  1. A checkpoint occurs and changes are synced to the main database
  2. Any replication slots have consumed the changes
  3. Backup systems (like WAL-G) have archived them

The problem? Supabase uses replication slots for Realtime functionality. If a slot becomes inactive or falls behind, PostgreSQL keeps WAL files indefinitely, waiting for that consumer to catch up.

Checking WAL Size and Replication Slots

Monitor your WAL directory size:

SELECT pg_size_pretty(SUM(size)) as wal_size
FROM pg_ls_waldir();

Check replication slot status:

SELECT
  slot_name,
  active,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as lag
FROM pg_replication_slots;

Inactive slots with large lag values are your problem. They're holding onto WAL files that can never be reclaimed.

Safely Removing Stale Replication Slots

Warning: Never delete files directly from /pg_wal/ - this will corrupt your database.

Instead, drop inactive replication slots through SQL:

-- List all slots first
SELECT * FROM pg_replication_slots;

-- Drop a specific inactive slot
SELECT pg_drop_replication_slot('slot_name');

For Supabase Realtime slots, dropping them means Realtime will create a new slot on the next connection. Any clients depending on that slot will need to reconnect and may miss changes that occurred during the gap.

Preventing WAL Disasters

Configure WAL retention limits to prevent runaway growth:

-- Limit WAL retention (adjust based on your recovery requirements)
ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
SELECT pg_reload_conf();

This setting allows PostgreSQL to remove WAL files once they exceed the limit, even if a replication slot still needs them. The slot will be invalidated, forcing consumers to resync, but your database won't run out of disk space.

For backup configurations using WAL-G, ensure backups are running successfully. WAL files won't be cleaned up until they've been archived.

Index Maintenance with REINDEX

Indexes can become bloated over time, especially on tables with heavy update/delete patterns. Bloated indexes slow down queries and waste disk space.

Checking Index Bloat

SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
  idx_scan as index_scans
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Indexes that are large relative to their table size and have low scan counts may be candidates for reindexing or removal.

Performing REINDEX Operations

-- Reindex a specific index
REINDEX INDEX index_name;

-- Reindex all indexes on a table
REINDEX TABLE your_table_name;

-- Reindex entire database (use during maintenance window)
REINDEX DATABASE postgres;

PostgreSQL 12+ supports concurrent reindexing, which doesn't block queries:

REINDEX INDEX CONCURRENTLY index_name;

This is the preferred approach for production self-hosted Supabase instances.

Keeping Statistics Fresh with ANALYZE

PostgreSQL's query planner relies on statistics about your data to choose efficient query plans. Stale statistics lead to poor query performance - sometimes dramatically so.

-- Analyze a specific table
ANALYZE your_table_name;

-- Analyze entire database
ANALYZE;

While autovacuum runs ANALYZE automatically, you should manually analyze tables after:

  • Bulk data imports
  • Large DELETE operations
  • Schema changes that affect indexes
  • Any operation that significantly changes data distribution

Disk Space Monitoring

Proactive monitoring prevents emergencies. Set up alerts for disk usage before you hit critical thresholds.

Checking Current Disk Usage

-- Database size
SELECT pg_size_pretty(pg_database_size('postgres')) as database_size;

-- Size by schema
SELECT
  schemaname,
  pg_size_pretty(SUM(pg_total_relation_size(schemaname || '.' || tablename))) as total_size
FROM pg_tables
GROUP BY schemaname
ORDER BY SUM(pg_total_relation_size(schemaname || '.' || tablename)) DESC;

-- Largest tables
SELECT
  schemaname || '.' || tablename as table_name,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) as table_size,
  pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) as index_size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Setting Up Monitoring

For production self-hosted Supabase, integrate disk monitoring with your existing observability stack. Key metrics to track:

  • Total disk usage percentage
  • WAL directory size
  • Dead tuple counts across tables
  • Replication slot lag

If you're using Supascale to manage your self-hosted instance, these metrics can be configured through the dashboard with built-in alerting.

Establishing a Maintenance Routine

Rather than waiting for problems, establish a regular maintenance schedule:

Daily (Automated)

  • Monitor disk usage and WAL size
  • Check autovacuum is running (look for recent last_autovacuum timestamps)
  • Verify replication slots are active

Weekly

  • Review table and index bloat
  • Manually VACUUM ANALYZE high-churn tables
  • Check for orphaned or unused indexes

Monthly

  • Full database statistics update: ANALYZE;
  • Review and adjust autovacuum settings based on observed patterns
  • REINDEX CONCURRENTLY on heavily-updated indexes
  • Clean up any abandoned replication slots

Quarterly

  • Consider VACUUM FULL on significantly bloated tables (scheduled maintenance window)
  • Review overall database size trends
  • Evaluate if schema changes could reduce maintenance burden

Automating Maintenance Tasks

Create a maintenance script to run during off-peak hours:

-- Weekly maintenance script
DO $$
DECLARE
  r RECORD;
BEGIN
  -- Vacuum analyze all user tables
  FOR r IN SELECT schemaname, tablename
           FROM pg_tables
           WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  LOOP
    EXECUTE format('VACUUM ANALYZE %I.%I', r.schemaname, r.tablename);
  END LOOP;
END $$;

Schedule this with pg_cron for hands-off automation.

Common Maintenance Pitfalls

Running VACUUM FULL during peak hours: VACUUM FULL locks tables exclusively. Schedule it for maintenance windows only.

Ignoring WAL growth: WAL files can fill a disk in hours during high-write periods. Monitor proactively.

Dropping replication slots without understanding the consequences: Realtime clients will disconnect and may miss data.

Over-aggressive autovacuum settings: While better than too passive, overly aggressive vacuuming creates unnecessary I/O load. Find the balance for your workload.

Forgetting about storage backups: Supabase Storage backups are separate from database maintenance but equally important.

Conclusion

Database maintenance is one of the hidden costs of self-hosting that catches many teams off guard. Unlike Supabase Cloud where these operations are managed transparently, your self-hosted instance depends on you to keep PostgreSQL healthy.

The good news: once you understand VACUUM, WAL management, and disk monitoring, maintenance becomes routine rather than reactive. Establish automated checks, schedule regular maintenance windows, and your self-hosted Supabase will run smoothly for years.

Need help managing your self-hosted Supabase deployment? Supascale simplifies backup management, monitoring, and routine operations with a one-time purchase - no recurring fees, unlimited projects.

Further Reading