Database Bloat?
 

Yesterday morning I played around with the new realm copy and import/export features. When I was finished, I deleted several new realms that I had created. After updating this morning I made a new backup and noticed that the file size had increased by around 30 MB. Looking back at the automatic backups that RW generates, the filesize increased around the time I was playing around and never decreased after removing the newly created test realms.

Has anyone else noticed this behavior?

This is normal. Database engines typically behave this way. The data has been deleted within the database, but the file size is not shrunk. Once the database adds to its tables, it's both expensive and inefficient to delete that enlarged storage. Instead, the individual records are deleted, and the database stays the larger size. The next time that new records are written, they will simply reuse the internal space that's been freed up. So the database won't grow again for awhile, even if you add more material to it. The freed space will be reclaimed for use again.

Hope this helps!

Thanks, Rob. I suspected this might be the issue. This could be a problem in the future for users with large realms who might want to excise realms they aren't using to reduce the file size. Since each DB is effectively 11 times its listed size due to automatic backups, this could be an unfortunate side effect unless an option to compact the DB is implemented at some point.

Excellent point. This is something we recently realized (read: a couple weeks ago). We need to figure out how best to resolve it in the VERY near future, but we didn't consider it critical enough to further delay the release. Hopefully, we made the right call on that one. :)

If you have Cloud Service, a workaround would be to delete/rename your Master folder and force Realm Works to download your realms again. Then it'd be as small as the engine would allow, at the cost of losing your local preferences (and any unsynced changes, but hopefully you all know better than that. ;)

There is something more going on than that.

Over the last several days I have made a copy of main realm a couple of times to convert to Pathfinder rules. I deleted the first attempt, assuming a second would not grow my DB, in order to get a log file. However when I checked the DB and the backups it became clear that the DB grew with each copy and the extra space is not being recovered.

I have not used the DB engine you guys are using but it is quite common in DB's for a delete operation to simply mark rows and tables as deleted but to not actually remove them. There may be a setting that needs switching somewhere.

I looked into this a bit further today, and you're right. We're going to need to investigate this in more depth to get a clear handle on how best to handle this. That probably won't happen for a little bit, since our immediate focus is on the few remaining bugs in RW itself and then getting the Content Market officially launched. But I've flagged this as an issue for us to circle back to relatively soon.

Thanks for catching this! :)

NP.

It shouldn't actually be an issue except when people do stuff outside normal use cases. The problem is the forum denizens are exactly the types to do that.

Probably will turn out to be something simple like changing a setting somewhere or adding an additional SQL command on top of the DELETE. I vaguely remember some DB's supporting PURGE for this sort of thing.