Hi, we have a database with about 150.000 documents, growing every week by 5k docs. We set( via the NODE.js SDK) an expire date to 1 week for every docoument to keep the iOS Client clean and fast.
We are correctly receiving the expired docs via replication but, even thought we correctly purge, compact and close the db, the SQLite database keeps growing. Now it’s over 140mb resulting in a ridicolously bad performance when querying on older devices (like ipad mini 2).
Any ideas?
We also tried manually deleting and purging docs manually, but the SQLIte db doesnt shrink in size.
Thank you.
TL;DR
Documents are set to expire on server ( VIA NOD JS) but the Client (IOS, SWIFT, CBL 2.6) doesnt delete them from disk even tho purge and compact are called. The result is a Sqlite file size that keeps sky rocketing resulting in poor performance.
Here a quick test I did
SQlite file size before compacting using the couchbaselite function -> 149590016
SQlite file size after compacting using the couchbaselite db.compact() function -> 149590016 (DID NOTHING)
SQlite file size after compacting using the couchbaselite db.close() function -> 144949248 === === === === manually purged documents === === === === ===
SQlite file size before compacting using the couchbaselite function -> ** 149126768**
SQlite file size after compacting using the couchbaselite db.compact() function -> ** 149126768** (DID NOTHING)
SQlite file size after compacting using the couchbaselite db.close() function -> ** 144949248**
SQlite file size after compacting using SQLITE Studio -> ** 119234239**
If you delete the document and then purge it, you have removed the “deleted” copy. The deletion will not be propagated because all record of the deletion is gone.
Are you saying that although the size and number of documents is remaining constant, the file size of SQLite is growing? The compact() trims unused blobs (not related to database file size) and then simply calls a few SQLite pragma commands so if that is not causing the size to drop, then I’m curious what commands that SQLite studio is running. Perhaps we could incorporate them into the compact call but it looks like the DB is still 120 MB. What was the original size with 150,000 documents inside?
Expiration is not replicated: An expiration time you set on server is not propagated to clients. So the document will be purged on the server, but clients that already had the document won’t be affected; they still have it.
We also tried manually deleting and purging docs manually, but the SQLIte db doesnt shrink in size.
Hm. Compacting the database runs a SQLite PRAGMA optimize and PRAGMA incremental_vacuum. (The database is put in auto_vacuum=incremental mode when created.) This should shrink the file if there are unused pages.
Check the logs for a message Vacuuming database; if you don’t see that, maybe the actual compact method isn’t being reached for some reason.
Oh also, 140MB isn’t that big, so I wouldn’t expect queries to get too slow. This could be a sign that you don’t have effective indexes, as a linear database scan will definitely get slow as the db grows, especially on devices with slow storage. Have you used the explain method to check your queries?
Well, we set expiration on documents server side and they are indeed replicated to the clients. In fact I cannot query a document anymore if it’s expired, even tho the document count doesnt decrease.
I wrote most of the CBL replicator code, and I can assure you it does not receive expiration times from the server.
And when a doc is purged on the server (through expiration or otherwise) there’s no trace of it remaining, so the server cannot later inform the client that it’s gone.
Just to confirm what you are seeing and to level set on terminology. Are the documents on server set to expire on the server using TTL mechanism?
Under convergence (shared_bucket_access is enabled) , expired documents are tombstoned. In case of tombstoned documents, the metadata corresponding to the document exists but there is no document body. (The tombstoned documents eventually get purged based on the metdata_purge_interval that’s set on the server). This is different from pre-convergence case wherein expired documents are purged and in that case, as Jens indicated, purged docs wouldn’t get replicated .
Based on your observations, I am going to assume that you have shared_bucket_access enabled.
So that’s why the tombstoned documents get replicated to the client. Queries on lite client will not return the deleted documents.
Tombstoned documents are not automatically purged on the client. When you say “correctly purge”, can you clarify how you are purging them on the client?
Yes sharedbucked_access is set on true. And I completely agree and understand the tombstone process.
With “correctly purged” I mean that the sqlite file size does not decrease. In fact I can find the docId (that I expired server side) inside the sqlite database. I cannot fetch it using a n1ql query from the mobile sdk, tho.
So the idea I have is that the purge doesnt work as intended (SQLiteStudio is able to free 30mb out of 140, while the purge using the SDK does almost nothing). Or at least, is not as efficient as any other sqlite software I tried.
Sorry Matteo, I’d forgotten about the newer expiration behavior on the server side, so my answer was misleading.
In fact I can find the docId (that I expired server side) inside the sqlite database.
The “tombstone” from the server is replicated to the client, and by default remains there, as you’ve seen. To get rid of it you’d need to purge the document. You can do this in a replicator progress listener function, if you want to get rid of the tombstone right away, which you probably do.
To get rid of existing tombstones you can run a CBL query on the “deleted” metadata property, and then purge all the documents found by the query.