While working on my recent post about why crash-consistent VM backups aren’t always enough, I ran into an unexpected but very useful side effect of adding application-aware database backups.
Once I started creating regular database dumps for my phpIPAM instances, I noticed something that had been completely invisible when relying solely on full VM backups: the database backups themselves were wildly different sizes.
That observation kicked off a short investigation that ultimately led to cleaning up unnecessary data, shrinking backups, and better understanding what was actually stored in the application.
The Initial Observation: Backup Size Discrepancies
I run multiple phpIPAM instances in my lab. Functionally, they’re similar and store roughly comparable types of data. When I began dumping their databases as part of a snapshot freeze workflow, I expected the backups to be in the same general size range. They weren’t.
- One instance produced a database dump of roughly 489 MB uncompressed (about 23 MB compressed)
- Another instance produced a dump of only 5 MB uncompressed (under 1 MB compressed)
At the VM level, this difference was completely masked. A full-VM backup doesn’t make it obvious whether one application’s data is growing abnormally or not—it all just looks like blocks on disk.
The database-level backups, however, made the discrepancy impossible to ignore.
Why VM-Level Backups Hid the Problem
This is one of those cases where VM backups were doing their job perfectly—and still hiding a problem.
From the perspective of the hypervisor:
- The VM was healthy
- Snapshots completed successfully
- Backups restored without issue
But VM backups don’t provide visibility. They protect everything equally, whether the data is critical, redundant, or no longer useful.
Application-aware backups, by contrast, force you to look directly at what’s being protected. In this case, the size difference alone was enough to raise questions.
Digging into the phpIPAM Database
With the size discrepancy in hand, the next step was to look at the database itself.
By inspecting table sizes and row counts, it quickly became clear that one instance was retaining a significant amount of historical or log-related data that the other was not.
To connect to the database, which was running in a container, I ran:
docker compose exec devipam-mariadb /bin/bashOnce I was inside the container, I connected to the database with
mariadb -u root -pFrom here, ChatGPT helped me with some SQL queries. The one to find the largest table was:
SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC
LIMIT 5;This was pointing me at the phpipam.logs table, and to get a feel for some of the events it contained I ran:
SELECT *
FROM phpipam.logs
LIMIT 5;A few more investigative queries, grouping my username and command, led me to an existing phpIPAM issue:
phpIPAM GitHub Issue #3545 – Excessive database growth due to retained data
The issue documents how certain tables can grow unbounded over time, particularly with historical scan and discovery data enabled. This issue (https://github.com/phpipam/phpipam/issues/3545) even provided a sample query to aid with cleanup. The issue showed creating this as a recurring job, but based on my data this issue was no longer occurring on a regular basis, it was an issue that happened in the past.
Cleaning Up the Data
Armed with that context, I ran a small number of targeted queries to understand and then remove old, unnecessary entries. The goal wasn’t to blindly delete data, but to:
- Identify logs events responsible for the majority of the growth
- Confirm the data was no longer operationally useful
- Reduce backup size without impacting functionality
The following query tested the logic I was going to use for removals:
SELECT
COUNT(*) AS rows_to_delete,
MIN(date) AS oldest,
MAX(date) AS newest
FROM phpipam.logs
WHERE (command = 'user login' or command like 'users object % edit' or details like '% in ipaddresses edited. hostname: %')
AND date < NOW() - INTERVAL 60 DAY;This showed about 2.8m rows, dating back nearly 3 years, that I thought would be safe to delete. Changing the statement (replacing the SELECT with a DELETE) resulted in the final cleanup query:
DELETE FROM phpipam.logs
WHERE (command = 'user login' or command like 'users object % edit' or details like '% in ipaddresses edited. hostname: %')
AND date < NOW() - INTERVAL 60 DAY;This query took about 20 seconds to execute and deleted the expected 2.8m rows. The functionality of phpIPAM is unchanged, but the backup related results were immediate.
- Database sizes across instances were now much closer
- Compressed backup sizes dropped significantly
- Backup and restore operations became faster
The Secondary Win: Smaller, Faster Backups
Reducing database size isn’t just about saving disk space. Smaller application backups mean:
- Faster freeze-script execution
- Shorter snapshot windows
- Less data to validate during restores
- Lower risk during recovery
In other words, improving the quality of the data improved the reliability of the backup process itself.
Lessons Learned
This entire chain of events started with a simple goal: making sure I had a known good copy of application data. What I didn’t expect was that application-aware backups would act as a diagnostic tool:
- They exposed abnormal data growth
- They encouraged closer inspection of the database
- They led to tangible improvements in backup efficiency
It’s a good reminder that backups aren’t just about recovery… they’re also a feedback mechanism. When you actually look at what you’re backing up, problems that were previously hidden at the VM layer become much easier to spot.
Conclusion
Crash-consistent VM backups remain a solid foundation, especially in lab environments. But once you add application-aware backups, you may gain another layer of visibility.
In this case, that visibility surfaced unnecessary data growth in phpIPAM, reduced backup sizes, and improved overall reliability. That’s a win well beyond the original goal of “just” having a safer backup.
If nothing else, this experience reinforced one idea: when you back up data at the application level, you’re forced to understand the application better.
