FTS DB maintenance functions?

DrChiper · 07-29-2022, 06:00 AM

Hi Kovid, after participating in some discussions about the resulting FTS index DB sizing, it occurred to me that there currently seems to be no FTS index DB maintenance functions like the normal calibre DB maintenance functions. Will there ever be such functions or will this handled automagically (like vacuuming) in the background?

kovidgoyal · 07-29-2022, 06:04 AM

The normal db maintenance also vacuums the fts db.

DrChiper · 07-29-2022, 07:53 AM

Hmmm, what I witnessed: full-text-search.db is not vacuumed when applying the normal DB maintenance.

What did I do: I copied the full-text-search.db to another location, noted its size and applied vacuum via SQLiteSpy (1.9.16) which took some time to compress it slightly. That indicated to me that full-text-search.db would likely to be a vacuum candidate.

Thereafter I fired-up calibre, choose maintenance (=Check Library) and saw the metadata.db being vacuumed after a short time, while file full-text-search.db was never touched. The short time it took was also an indication.

I'm currently using calibre 6.2. Did I applied the maintenance correctly?

kovidgoyal · 07-29-2022, 08:00 AM

https://github.com/kovidgoyal/calibr...db3b705172013a

though it wont make any difference to file size. In my experience vacuuming has very little effect on FTS databases.

ownedbycats · 07-29-2022, 08:01 AM

Out of curiosity, I ran Check Library myself.

The last-modified date of full-text-search.db didn't change (last-accessed did), and the filesize (1,699,258,368 bytes) remained identical.

edit: welp, Kovid responded before I did.

ownedbycats · 07-29-2022, 08:05 AM

Quote:

Originally Posted by kovidgoyal

though it wont make any difference to file size. In my experience vacuuming has very little effect on FTS databases.

From what I recall, vacuuming removes old overwritten/deleted entries (the space is marked for reuse rather than actually deleted), while I'd expect the entries in a FTS database to remain generally static (maybe not fanfics that get frequently updated).

DrChiper · 07-29-2022, 08:10 AM

Quote:

Originally Posted by kovidgoyal

[...]it wont make any difference to file size. In my experience vacuuming has very little effect on FTS databases.

I agree: manually vacuuming did offer only a slight reclaim; however in due time on changing a lot in your database, that may be different. I did checked the FTS community and did found some interesting stories about ever increasing DB sizes.

DrChiper · 07-29-2022, 08:13 AM

Quote:

Originally Posted by ownedbycats

From what I recall, vacuuming removes old overwritten/deleted entries (the space is marked for reuse rather than actually deleted), while I'd expect the entries in a FTS database to remain generally static (maybe not fanfics that get frequently updated).

It had everything to do with the method FTS uses to store its entries (balanced trees). It can take some time to actually delete entries which are marked as "deleted".

kovidgoyal · 07-29-2022, 11:51 AM

FTS databases typically see very little churn because you dont often re-index or delete or update books, so vacuuming is relatively less useful. Especially so soon after it is created, but the vacuum function should be available and I have now made it so.

ownedbycats · 07-29-2022, 12:51 PM

Quote:

Originally Posted by kovidgoyal

FTS databases typically see very little churn because you dont often re-index or delete or update books, so vacuuming is relatively less useful. Especially so soon after it is created, but the vacuum function should be available and I have now made it so.

The main one I can think of is someone splitting up a library into several smaller ones, which would remove a lot of index entries from the original. But that probably wouldn't be very common.

Just curious: Any idea how does re-indexing work when updating a fanfic with new chapters? I'd assume that the marked-as-unused space just immediately gets reclaimed by the new index coming in.

kovidgoyal · 07-29-2022, 01:24 PM

that will depend on page size and size of replacement text vs original text and internal sqlite implementation details.

JSWolf · 07-29-2022, 04:35 PM

Quote:

Originally Posted by kovidgoyal

https://github.com/kovidgoyal/calibr...db3b705172013a

though it wont make any difference to file size. In my experience vacuuming has very little effect on FTS databases.

Is this fix in 6.2.1?

BetterRed · 07-29-2022, 07:34 PM

Quote:

Originally Posted by JSWolf

Is this fix in 6.2.1?

Why don't you look for yourself!

https://github.com/kovidgoyal/calibre/commits/master

BR

07-29-2022, 06:00 AM	#1
DrChiper Bookish Posts: 1,017 Karma: 2003162 Join Date: Jun 2011 Device: PC, t1, t2, t3, Clara BW, Clara HD, Libra 2, Libra Color, Nxtpaper 11	FTS DB maintenance functions? (Solved) Hi Kovid, after participating in some discussions about the resulting FTS index DB sizing, it occurred to me that there currently seems to be no FTS index DB maintenance functions like the normal calibre DB maintenance functions. Will there ever be such functions or will this handled automagically (like vacuuming) in the background? Last edited by DrChiper; 07-29-2022 at 08:27 AM.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Library Maintenance	Cooker	Library Management	8	12-08-2019 11:56 PM
Problem with template functions and library maintenance	Terisa de morgan	Library Management	12	03-11-2017 09:43 PM
Authors maintenance	JCSullivan	Calibre	7	04-14-2012 06:03 AM
Battery Maintenance	rleguillow	Sony Reader	5	03-20-2011 10:53 PM
maintenance and backup	roger64	Sony Reader	1	01-20-2009 11:19 PM

07-29-2022, 06:04 AM	#2
kovidgoyal creator of calibre Posts: 45,337 Karma: 27182818 Join Date: Oct 2006 Location: Mumbai, India Device: Various	The normal db maintenance also vacuums the fts db.

07-29-2022, 07:53 AM	#3
DrChiper Bookish Posts: 1,017 Karma: 2003162 Join Date: Jun 2011 Device: PC, t1, t2, t3, Clara BW, Clara HD, Libra 2, Libra Color, Nxtpaper 11	Hmmm, what I witnessed: full-text-search.db is not vacuumed when applying the normal DB maintenance. What did I do: I copied the full-text-search.db to another location, noted its size and applied vacuum via SQLiteSpy (1.9.16) which took some time to compress it slightly. That indicated to me that full-text-search.db would likely to be a vacuum candidate. Thereafter I fired-up calibre, choose maintenance (=Check Library) and saw the metadata.db being vacuumed after a short time, while file full-text-search.db was never touched. The short time it took was also an indication. I'm currently using calibre 6.2. Did I applied the maintenance correctly?

07-29-2022, 08:00 AM	#4
kovidgoyal creator of calibre Posts: 45,337 Karma: 27182818 Join Date: Oct 2006 Location: Mumbai, India Device: Various	https://github.com/kovidgoyal/calibr...db3b705172013a though it wont make any difference to file size. In my experience vacuuming has very little effect on FTS databases.

07-29-2022, 08:01 AM	#5
ownedbycats Custom User Title Posts: 10,948 Karma: 74999999 Join Date: Oct 2018 Location: Canada Device: Kobo Libra H2O, formerly Aura HD	Out of curiosity, I ran Check Library myself. The last-modified date of full-text-search.db didn't change (last-accessed did), and the filesize (1,699,258,368 bytes) remained identical. edit: welp, Kovid responded before I did.

07-29-2022, 11:51 AM	#9
kovidgoyal creator of calibre Posts: 45,337 Karma: 27182818 Join Date: Oct 2006 Location: Mumbai, India Device: Various	FTS databases typically see very little churn because you dont often re-index or delete or update books, so vacuuming is relatively less useful. Especially so soon after it is created, but the vacuum function should be available and I have now made it so.

07-29-2022, 01:24 PM	#11
kovidgoyal creator of calibre Posts: 45,337 Karma: 27182818 Join Date: Oct 2006 Location: Mumbai, India Device: Various	that will depend on page size and size of replacement text vs original text and internal sqlite implementation details.

Advert

Advert