How to Shrink a Bloated Database Without Losing Data
To shrink a bloated database, you delete or archive the rows that no longer earn their space, then return that freed space to the filesystem with the right maintenance command for your engine. The important part is doing it in an order that keeps the data you still need intact: measure first, remove or compact second, and reclaim disk space last. This guide walks through how to shrink a bloated database on the common relational engines, with the actual commands and settings involved.
What database bloat actually is
Bloat is space reserved by the database but no longer holding useful data. It comes from three places: deleted rows that have not been physically reclaimed, old row versions left behind by updates, and orphaned rows whose parent was removed without a cascade. It also comes from old revisions of content, audit trails, and log tables that grow forever because nobody set a retention rule.
In PostgreSQL, a deleted or updated row stays as a dead tuple until vacuum reclaims it, and the file on disk does not shrink unless you run a full rewrite. In MySQL and MariaDB with InnoDB, deleted rows leave holes inside the tablespace, and the file only shrinks after an OPTIMIZE TABLE or a rebuild. In SQLite, deleted pages stay on the freelist until you run VACUUM. SQL Server has its own story with DBCC SHRINKDATABASE and DBCC SHRINKFILE, and shrinking there has known downsides, so measure before you reach for it. The point is that "delete the rows" and "give the disk space back" are two separate operations on every engine.
Measure before you shrink a bloated database
Start by finding where the space actually went. Do not guess. On PostgreSQL, the pg_total_relation_size function and the pg_stat_user_tables view tell you which tables are large and how many dead tuples they carry.
SELECT relname,
pg_size_pretty(pg_total_relation_size(relid)) AS total,
n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
A table with a large n_dead_tup relative to n_live_tup is a candidate for vacuuming. A table with almost no dead tuples but a huge total size is probably holding data you can archive or delete. On MySQL, information_schema.TABLES gives you DATA_LENGTH and DATA_FREE, and the latter is the space InnoDB has marked free inside the tablespace.
SELECT table_name, data_length, data_free
FROM information_schema.TABLES
WHERE table_schema = 'your_db'
ORDER BY data_free DESC;
Write these numbers down before you change anything. They are your before picture, and you will want them to confirm the shrink worked.
Find the rows worth removing
Orphaned rows are the easiest win. A child table with a foreign key to a parent that no longer exists is wasted space, and it often signals a missing constraint. Find them with an anti join.
SELECT COUNT(*)
FROM child c
LEFT JOIN parent p ON p.id = c.parent_id
WHERE p.id IS NULL;
If the count is large, confirm the rows are truly disposable, then delete them in batches rather than in one statement. A single DELETE of millions of rows holds locks, fills the transaction log, and can stall your application. Loop over chunks of a few thousand rows, commit between batches, and let the engine breathe.
Old revisions are the other common source. If your application writes a new row on every edit and keeps every version forever, decide how many versions you actually need. Archive the rest to a separate table or a cold store, then delete them from the hot table. Log and event tables deserve the same treatment: set a retention window and delete by timestamp in batches.
Reclaim the space
Once the rows are gone, reclaim the space. On PostgreSQL, a plain VACUUM marks dead tuples reusable but does not shrink the file. To return space to the filesystem, use VACUUM FULL, which rewrites the table and takes an exclusive lock, so schedule it during a maintenance window. For large tables where a full lock is unacceptable, the pg_repack extension does the same rewrite online.
VACUUM (VERBOSE, ANALYZE) your_table;
VACUUM FULL your_table;
On MySQL and MariaDB with InnoDB, OPTIMIZE TABLE rebuilds the table and releases the free space inside the tablespace. It also locks the table for writes during the rebuild, so plan it. If you have innodb_file_per_table enabled, the reclaimed space actually leaves the filesystem; if not, the space stays inside the shared system tablespace and you gain nothing visible on disk. Check that setting before you bother.
SET GLOBAL innodb_file_per_table = ON;
OPTIMIZE TABLE your_table;
On SQLite, VACUUM rewrites the database file and drops the freelist. It needs roughly twice the database size in free disk to run, so check df first. Also check PRAGMA auto_vacuum; if it is set to FULL or INCREMENTAL, the engine reclaims space on its own and you may not need a manual vacuum at all.
Protect against corruption while you work
Take a backup and test the restore before you run anything destructive. A dump is not a backup until you have restored it somewhere and queried it. For PostgreSQL, pg_dump with --format=custom gives you a restorable archive. For MySQL, mysqldump --single-transaction gives you a consistent snapshot without locking InnoDB tables. For SQLite, use the .backup command rather than copying the file, because a plain cp of a live database can capture a torn write.
Keep foreign keys enforced during cleanup. If you delete parent rows before children, and the constraint is missing or disabled, you create exactly the orphans you are trying to remove. Run your orphan query again after cleanup to confirm you left none behind. Wrap each batch in a transaction so a failure leaves the table in a known state, and verify row counts against your before picture.
Keep it from coming back
Bloat returns if the conditions that caused it return. Add a retention job that deletes old rows on a schedule, and make it batch. Add the missing foreign keys with ON DELETE CASCADE where appropriate so orphaned rows cannot accumulate. Enable autovacuum on PostgreSQL and tune its thresholds for large, high churn tables, because the defaults often fall behind. On InnoDB, watch DATA_FREE over time and rebuild only when it grows enough to matter. Set a monitoring check that alerts when a table's dead tuple count or free space crosses a threshold you choose.
As a next step, run the measurement queries on your largest tables today and record the numbers. Pick the single table with the most reclaimable space, confirm its rows are safe to remove, and take it through one full cycle of delete, reclaim, and verify in a staging copy first. Once that cycle is clean, repeat it on production during a quiet window, and put the retention rules in place so you never have to do it from scratch again.
