Why does the database size not decrease after deleting data?

Overview

Customers frequently report that after deleting large amounts of data from their EventSentry database, the database file size remains unchanged or the free disk space doesn't increase as expected. This behavior is normal and by design for both PostgreSQL and Microsoft SQL Server databases.

Why This Happens
Databases are optimized for performance, not for immediate disk space reclamation. When you delete records, the database engine marks that space as "available for reuse" rather than immediately returning it to the operating system. This design decision provides several benefits:

• Performance: Avoids the overhead of constantly rebuilding indexes and reorganizing data structures
• Efficiency: Prevents file system fragmentation from repeatedly growing and shrinking the database
• Speed: New data can be written immediately into the freed space without file expansion operations

PostgreSQL

In PostgreSQL, deleted rows are marked as "dead tuples" but remain in the database files. The space is tracked as reusable, but the physical file size stays the same.

What happens:

• DELETE operations mark rows as deleted but don't remove them physically
• The space becomes available for new INSERT or UPDATE operations
• Routine AUTOVACUUM processes clean up dead tuples but don't shrink files
• The database files (stored in the DATA directory) maintain their current size
• WAL (Write-Ahead Log) files in pg_wal directory also don't automatically shrink

To reclaim space: You must run a VACUUM FULL operation, which rewrites the entire table and returns unused space to the operating system. Note that this is a resource-intensive operation that locks tables.

Note: When purging old data with a maintenance job in the EventSentry Web Reports, then a VACUUM TRUNCATE will automatically be performed after each DELETE statement. This can truncate empty pages at the end of the file and can thus regularly free up disk space.

Command:

VACUUM FULL;

See also: KB241 - How do I reclaim disk space after purging data by shrinking the built-in PostgreSQL database

Microsoft SQL Server

SQL Server uses a similar approach with its data files (.mdf) and log files (.ldf).

What happens:

• Deleted rows leave empty pages in the data file
• These pages are marked as available for new data
• The database file size remains at its maximum size
• Transaction log files continue to grow unless managed

*To reclaim space: **You must explicitly shrink the database using *DBCC SHRINKDATABASE or DBCC SHRINKFILE. However, this can cause index fragmentation and impact performance.

sql-- Shrink the entire database
DBCC SHRINKDATABASE (EventSentry, 10);
-- Or shrink a specific file
DBCC SHRINKFILE (EventSentry_Data, 10);

After shrinking, you should rebuild indexes:

ALTER INDEX ALL ON [TableName] REBUILD;

Important Considerations

Performance Impact

• PostgreSQL VACUUM FULL: Locks tables during the operation, blocks all access
• SQL Server SHRINKDATABASE: Causes significant index fragmentation, degrades query performance
• Both operations are CPU and I/O intensive

When to Reclaim Space

Only reclaim space when:

• You've deleted a substantial portion of the database (>30-40%)
• Disk space is critically low
• The database won't grow back to its current size soon
• You can schedule the operation during a maintenance window

Best Practices

• Monitor, don't react: Track database growth trends rather than reacting to size (from WebReports: Tools / Database Usage)
• Automate Retention: Schedule regular data retention policies to prevent explosive growth (from WebReports: Tools / Maintenance Wizard)
• Maintain indexes: Regular index maintenance is more important than shrinking
• Allow headroom: Databases perform better with pre-allocated space for growth
• PostgreSQL: Rely on regular AUTOVACUUM; use VACUUM FULL sparingly
• SQL Server: Avoid shrinking as routine maintenance; only when necessary

Summary

The database file size not decreasing after deleting data is expected behavior. The space is reusable and will be automatically filled by new data. Only reclaim disk space when absolutely necessary, and understand that doing so may temporarily impact performance.

For EventSentry-specific instructions on reclaiming space, refer to: KB241 - PostgreSQL database shrinking