Database size increasing when deleting fields

[Laurent]

Hello Brendan,

I have performed some cleaning on my TF database by deleting some fields (photo fields mainly) from different forms in oder to reduce my database size. To my surprise, my DB size increased from 380 to 450 MB after the cleaning operations. Opening/closing TF and running the compacting maintenance operation did to help, my DB is now 450 MB.

This is unexpected behavior contrary to what I wanted to achieve.

Would you please let me know what happened. I am a little bit worried about this inflating file size…

Thanks a lot,
Kind regards,
Laurent

[Brendan]

When you compact the database, the document size does increase until you close and re-open it again. Then it may go back down to what it was before (roughly). Compacting should purge any deleted records and any attachment blob files that are no longer referenced in the database.

[Laurent]

Brendan,

Even after closing and re-opening the program my DB size did not go back to the size it was before. I deleted fields shouldn’t it have decreased the size of the DB?

Thanks for feedback.

[Brendan]

I think I need to look into my code that compacts the database. It runs a routine which removes the data for fields which have been deleted. However, I just notice that it’s not deleting the attachments during that process. I’ll have to enhance it to do that. It works fine if you’re deleting the records, but not when just deleting a field because the record is just being updated and not being deleted.

[Laurent]

Good to know. I have made some adjustments to my forms recently, linking some of them together but not adding some much new data, but still I have noticed that my DB file size increased dramatically at the same time. I am worried because it is becoming slower to open now.

[Brendan]

I’ve modified the Compact Database code so that it removes attachments from the records from those fields too. I just have to test it now to make sure it’s working.

[Laurent]

Hi Brendan,

It seems the last version 5.2.10 has not solved the DB size issue I have after having deleting some fields from my different forms. I ran the “compact database maintenance script” but the DB file size has not been reduced, still stuck at about 450 MB.

Would you please help ?

Thanks a lot,
Laurent

[Brendan]

Hi Laurent,

It just dawned on me that this makes sense given the way CouchbaseLite handles updating the database. Whenever you make a change to a record, a new revision of that record is created in the database. So there’s some extra space used up for that.

When you delete a field, Tap Forms simply deletes the field object and nils out references to that field in other objects. For example, if you were sorting based on the field you deleted, Tap Forms would nil out the reference to that field on the form.

The data in the records is not deleted until you compact the database. However, all this deleting function does is nils out the values stored in the record, thus creating a new revision of that record. For each record in the form that referenced that field. So the original data is still there in the database as an older revision. Eventually this will be cleared away as you make new revisions to the records.

It’s different than when you delete a record because when you delete a record, a new small, without data revision is created that just flags the whole record as being deleted. Then when you compact the database, those old revisions are cleared out.

If your field you’re deleting contains file references in its values, then those attachments will be deleted from the database, thus causing the file size to decrease significantly.

So I think that’s why your file size isn’t going down as much as you had hoped by deleting a field.

You could do an experiment and export your forms to Tap Forms Archive files, then create a new document, then import the Tap Forms Archive files. I’m pretty sure the new document will be smaller than the old one.

Thanks,

Brendan

[Laurent]

Thanks a lot Brendan for your detailed explanation.

Nice WE.

[Laurent]

Hello Brendan,

Back to this topic, I notice that the size of my DB synced on my mobile devices through iCloud (iPhones & iPad) is much lower than on my Mac (about 200 MB vs 470 MB). Do you know why ? Is there a way to purge the DB on my Mac so that the file size can be decreased?

Thanks a lot for your feedback,
Laurent

[Brendan]

Hi Laurent,

It could just be that the Mac version’s WAL files are larger than on your iOS devices.

If you right-click on your .tapforms document in the Finder and choose the “Show Package Contents” function, you can then navigate into the “db-” folder to see the db.sqlite file. There will probably also be 2 other files there. A db.sqlite-wal and db.sqlite-shm file. Those are just working files that grow and shrink as you update the database. They’re important files, but nothing really to worry about. Generally when you close a document those files should automatically get deleted once the transactions within them are applied to the main db.sqlite database file.

Thanks,

Brendan

[Laurent]

Hi Brendan,

Thank you for your answer.

In the package content of my .tapforms doc I have only db.sqlite3 file (no wal, nor shm file). This file is 456 Mo !

Do you have any plan to introduce a purge function to delete old data (the “original data is still there in the database as an older revision” you talk about in your post of April 13th)?

Thank you and nice WE to you,
Laurent

[Brendan]

I already have the purge function that I wrote. I just haven’t enabled it yet for public use.

I’ll think about adding it to a future update.

[Author]

Posts