I had a problem with my personal sqlite3 database, perfectly described here. an answer in the article provides a solution, with a detailed explenation here. The first line in that article caught my interest, looking at /etc/.pihole/advanced/Templates/gravity.db.sql, there are 5 AUTOINCREMENT entries, which may (or may not) match the explanation in the article.
Given the fact the gravity db is recreated every time pihole -g is executed, this can be accomplished without disturbing the user.
I don't see how a user would be disturbed with the current solution (but note that dropping AUTOINCREMENT may result in reusing previously deleted rows and newly create rowids might no longer be in strictly ascending order when a table's largest rowid entry is deleted, which cannot be safely ruled out).
That may be because I fail to fully understand your issue yet.
Could you elaborate your specific issue?
The description you linked seems more like a general question than an actual problem: With linked OP's 500k increments per run, database id overflows would happen after about 18.446.744.000.000 runs, or roughly 580,000 years at one second per run.
The first line in the sqlite3 article caught my attention. The user doesn't know this, but the impact described, remains.
In the gravity database, the table domainlist has AUTOINCREMENT, as far as I know there is no option to remove entries from this table, order will thus never change. The domainlist table is by far the largest table in the database (depending on the number of lists). reducing the resouces used, by removing the AUTOINCREMENT keyword, would make a gravity update faster (again, according to the first line of the article).
Not an essential change, but I remember this discussion, some changes can be made without major problems, there is always room for improvement.
Somebody in your team recently suggested to introduce certain improvements for new installations only, could be an option, although, the gravity database is recreated every time pihole -g is executed.
I also checked the pihole-FTL database, there are two tables with AUTOINCREMENT, messages and query_storage. I assume the use of AUTOINCREMENT in these tables is justified, because the user (messages) and FTL (query_storage) do delete entries, the order would be impacted by simply removing the keyword.
So it seems your main concern is the performance penalty imposed by AUTOINCREMENT, where the post you've linked as describing your problem mentions performance only in the context of using INSERT OR IGNORE vs. a conditional INSERT on a SELECT:
MUCH faster than checking the existence with
SELECT ...and do anINSERTif needed.
While AUTOINCREMENT is indeed more costly, note that the potential performance gain would be hidden away from the average user by the fact that gravity swapping only happens after the new db has been fully populated, and only once a week (making it one of saving CPU load rather than actual time).
As gravity rebuild times would vary greatly among different installations for both a user's configuration choices and their choice of h/w hosting Pi-hole, it'd be hard to give any actual numbers as to possible performance gains, but it would seem that you could expect improvements in the high one to low two digit percentage range (say 9% or 10%).
That's not entirely true:
A user may delete the adlist containing the domain at any time.
We recently discussed that it may be necessary to delete orphaned domain entries when the respective adlist was deleted.
That doesn't seem like a point against your suggestion per se at first glance, but when considering it in the light of another one of your (indirect) suggestions of dropping foreign key constraints, then dropping AUTOINCREMENT may result in domains being randomly assigned to an adlist when the conditions of reusing an existing rowid would be met.
Granted, that would be very unlikely indeed, but when weighing database integrity against potential performance gains, I'd personally tend to opt for the former, unless performance benefits would be both substantial and relevant.
That would be a development choice, of course, and as gravity is by default rebuilt once a week, somewhat weakening those potential detrimental effects, this may indeed be something that they'd want to consider.
Thank you for your recommendation.
The performance critical thing in gravity's database operation is the insertion of thousands (possibly millions) of domains into gravity. This table is not using AUTOINCREMENT:
When we're talking about domainlist,
I'm the real impact is easily overestimated here. AUTOINCREMENT is not like scanning the table or something but it is reading a value from an internal table (sqlite_sequence) and subsequently updating this value.
Being 10% of the time will simply mean nothing else than taking an extra nanosecond whereas the ordinary insert would have taken ten nanoseconds without AUTOINCREMENT. This isn't really convincing given what we are loosing in terms of integrity (as @Bucking_Horn detailed above).
It should also be stressed that the AUTOINCREMENT feature only has an impact if no id value is given (so the database has to derive one). This only happens on direct user interaction where a few nanoseconds of extra processing time seem acceptable. Copying the database during pihole -g will not cause the AUTOINCREMENT feature to be triggered in the same way as we are copying rows which already have an ID so the database does not have to derive a new unique ID here.
This is a wrong but I think you've just mixed up gravity (where the millions of domains live) and domainlist (where your manually added exact/regex white-/blacklist entries live). Rest assured Pi-hole has a method to remove entries from this list 
This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.