VACUUM, ANALYZE and Table Bloat on Amazon Redshift: What Automatic Maintenance Misses

Amazon Redshift has quietly taken over most of the maintenance work that used to fill a DBA's morning. Auto vacuum delete reclaims space, auto vacuum sort re-sorts hot regions of a table, auto analyze refreshes statistics, and Automatic Table Optimization changes sort and distribution keys underneath you. The honest version of the story, though, is that "automatic" means best effort during idle capacity. On a warehouse that loads continuously, or one that deletes and rewrites large slices of a table every night, the background workers fall behind and nobody notices until a dashboard doubles in latency or disk usage climbs toward the red.

This tutorial covers what the automatic maintenance actually does, how to measure whether it is keeping up, and the specific situations that still justify a manual VACUUM or ANALYZE.

What runs on its own

• Auto vacuum delete reclaims blocks left behind by DELETE and UPDATE (Redshift updates are delete-plus-insert, so every update leaves a tombstoned row). This runs fairly aggressively and is the piece you can mostly trust.
• Auto vacuum sort re-sorts rows that landed outside the sorted region. It works incrementally and prioritises tables that queries are actually scanning. It does not guarantee a fully sorted table, and it will not rebuild a badly interleaved one.
• Auto analyze updates statistics after enough of a table has changed. It uses the same "changed rows since last analyze" signal you can read yourself.
• Automatic Table Optimization (ATO) converts SORTKEY AUTO / DISTSTYLE AUTO tables based on observed query patterns, applying changes in the background.

None of this is free-standing magic: it all competes with your workload for capacity and is deliberately throttled. Serverless namespaces and provisioned clusters both behave this way.

Measuring where you stand

Three numbers matter per table: percentage of rows unsorted, tombstoned/deleted rows waiting to be reclaimed, and staleness of statistics.

SELECT "schema", "table", size AS mb, tbl_rows,
       unsorted, stats_off, vacuum_sort_benefit, sortkey1, diststyle
FROM SVV_TABLE_INFO
WHERE tbl_rows > 1000000
ORDER BY vacuum_sort_benefit DESC NULLS LAST
LIMIT 25;

Read it like this:

• unsorted — percent of rows outside the sorted region. A 30% unsorted table is not automatically a problem; a 30% unsorted table that every dashboard filters on its sort key is.
• vacuum_sort_benefit — Redshift's own estimate, in percent, of the scan improvement a sort vacuum would deliver. This is the single most useful column in the view and the right thing to sort a maintenance queue by.
• stats_off — how stale statistics are, 0 to 100. Above roughly 10 on a large fact table means the planner is guessing with old row counts, which is how you get a nested loop where a hash join belonged.
• size vs tbl_rows — a table whose size grows while row count stays flat is accumulating tombstones or has terrible compression after a schema change.

To see whether the automatic workers are actually running against your tables:

-- Auto and manual vacuum history
SELECT table_id, table_name, status, start_time, end_time,
       rows AS rows_before, sortedrows, is_recluster
FROM SYS_VACUUM_HISTORY
WHERE start_time > DATEADD(day, -7, GETDATE())
ORDER BY start_time DESC;

-- Auto analyze activity
SELECT * FROM SYS_ANALYZE_HISTORY
WHERE start_time > DATEADD(day, -7, GETDATE())
ORDER BY start_time DESC;

On older clusters the equivalents are SVV_VACUUM_SUMMARY, STL_VACUUM and STL_ANALYZE. If a large, high-benefit table has no vacuum entries for a week, auto vacuum is being starved — that is your signal to schedule work yourself, not to widen the window and hope.

Also watch tombstones that cannot be reclaimed:

SELECT SUM(rows) AS tombstoned_rows
FROM STV_TBL_PERM
WHERE name = 'fact_orders';

Blocks stay tombstoned while any long-running transaction could still need them. One analyst session left open in a BI tool for three days will pin space on every table it touched, and no amount of vacuuming clears it. Find them before you blame the vacuum:

SELECT pid, user_name, starttime, DATEDIFF(minute, starttime, GETDATE()) AS minutes, query
FROM STV_RECENTS
WHERE status = 'Running' AND starttime < DATEADD(hour, -2, GETDATE());

When to run VACUUM by hand

Four cases genuinely warrant it.

1. After a bulk delete or a large backfill. If you just deleted a year of history or rewrote 40% of a fact table, do not wait for the background worker:

VACUUM DELETE ONLY fact_orders;          -- reclaim space, do not re-sort
VACUUM SORT ONLY fact_orders TO 100 PERCENT;  -- re-sort, do not reclaim
VACUUM FULL fact_orders;                 -- both

VACUUM DELETE ONLY is cheap and the right first move when the complaint is disk usage. VACUUM SORT ONLY is the right move when the complaint is scan time on sort-key-filtered queries.

2. Before a known heavy read window. A monthly close, a regulator extract, a model training run. Sorting the table the night before turns a 40-minute scan into a few minutes because zone maps can skip blocks again.

3. After changing a sort key. ALTER TABLE ... ALTER SORTKEY leaves existing rows where they are. The table is only sorted on the new key once it is vacuumed (or rebuilt).

4. Interleaved sort keys. These degrade in a way incremental sorting cannot repair and need a reindex:

VACUUM REINDEX fact_events;

If you are running VACUUM REINDEX regularly, the real fix is usually to move to a compound sort key — interleaved keys are rarely the right answer on modern RA3 and Serverless.

Make manual vacuums safe to run

-- Bound the work: stop when the table is 95% sorted
VACUUM SORT ONLY fact_orders TO 95 PERCENT;

-- Give maintenance a low-priority lane so it yields to user queries
ALTER USER maintenance_svc SET query_group TO 'maintenance';

Practical rules: only one vacuum runs at a time per warehouse, so serialise your queue rather than firing ten in parallel; a vacuum can be cancelled safely and resumes progress on the next run; and VACUUM FULL on a very large, very unsorted table can be slower and riskier than a deep copy.

The deep copy alternative

When a table is so unsorted that vacuuming would take hours, rebuilding it is often faster and completely predictable, because loading into an empty table with a sort key writes rows in sorted order:

BEGIN;
CREATE TABLE fact_orders_new (LIKE fact_orders);
INSERT INTO fact_orders_new SELECT * FROM fact_orders;
ALTER TABLE fact_orders RENAME TO fact_orders_old;
ALTER TABLE fact_orders_new RENAME TO fact_orders;
COMMIT;

ANALYZE fact_orders;
DROP TABLE fact_orders_old;

CREATE TABLE ... (LIKE ...) carries over the sort key, distribution style and encodings but not the grants or foreign-key constraints — re-apply those, and prefer granting to roles so the re-apply is one statement. Keep the old table for a day before dropping it.

ANALYZE: less often than you think, more often than never

Auto analyze handles the common cases. Run it explicitly when:

• The load just finished and the next query in the pipeline is a large join. Put ANALYZE at the end of the ELT job, not on a cron an hour later.

• You only need the columns that matter. Full-table analyze on a 400-column table is wasted work:

  ANALYZE fact_orders(order_date, customer_id, status);
  

• You want to force a refresh regardless of the change threshold:

  SET analyze_threshold_percent TO 0;
  ANALYZE fact_orders;
  RESET analyze_threshold_percent;
  

COPY computes statistics automatically on an initial load into an empty table, so a fresh staging table generally does not need an explicit analyze — but the target of a MERGE or INSERT ... SELECT usually does.

A maintenance job that is actually worth running

Rather than a blanket nightly VACUUM FULL across the schema — which burns capacity on tables that do not need it — drive the work from Redshift's own estimates. Schedule this with the Data API and EventBridge, or as a final step in your orchestration DAG:

-- Candidate list: what is worth vacuuming tonight
SELECT "schema" || '.' || "table" AS relname,
       size AS mb, unsorted, vacuum_sort_benefit, stats_off
FROM SVV_TABLE_INFO
WHERE (vacuum_sort_benefit > 20 AND unsorted > 10)
   OR stats_off > 10
ORDER BY vacuum_sort_benefit DESC NULLS LAST;

Then, for each row, emit VACUUM SORT ONLY <relname> TO 99 PERCENT; and/or ANALYZE <relname>;, run them one at a time under a low-priority query group, cap the whole job with a wall-clock budget, and log the before/after unsorted and vacuum_sort_benefit so you can prove the window is sized correctly. A job like this typically touches three or four tables a night instead of three hundred.

Alerts that catch the real failures

• Any table with vacuum_sort_benefit > 40 for more than three consecutive days.
• stats_off > 20 on any table above 10 GB.
• Percentage of disk used trending up while row counts are flat (tombstones or a pinned transaction).
• A transaction open longer than two hours — the most common hidden cause of "vacuum did nothing."
• Zero rows in SYS_VACUUM_HISTORY in the last 48 hours on a busy warehouse.

Wire these into the CloudWatch alarms and runbooks described in our monitoring and alerting tutorial.

The short version

Trust auto vacuum delete. Verify auto vacuum sort. Treat vacuum_sort_benefit as your work queue. Run ANALYZE at the end of the pipeline that changed the data. Rebuild instead of vacuuming when a table has gone too far, and check for long-open transactions before concluding that maintenance is broken.

If your Redshift warehouse is growing faster than its maintenance window, our performance optimization and administration teams do exactly this work — measuring what is actually costing you scan time, then sizing the maintenance job to match.