The pros and cons of SQLite using AutoIncrement

In project use we often need an auto-growing primary key, such as adding a autoincrement constraint to make a primary key grow each time the data is inserted. Since you typically create a table with a field that becomes ROWID by default (unless declared as without rowid), the integer primary key you add is actually a rowid alias. But if you add the AutoIncrement constraint, the database engine generates ROWID's algorithm that changes accordingly.

Their generation algorithm is as follows

Integer PRIMARY key:

Gets the maximum value of the current column, if the maximum value of the 64-bit signed integer is not reached, then on the basis of the maximum value of +1 as the new value, if the maximum value has been reached, then re-randomly looking for an unused, that is, you delete a row of the unused value. If it is no longer possible to insert, the Sqlite_full error is returned.

Integer PRIMARY key AutoIncrement:

Gets the current column maximum value, or 1 if the maximum value is not reached, or Sqlite_full is returned.

The integer primary key is automatically incremented by default, and using AutoIncrement guarantees that the newly inserted data will be larger than before, but it also brings a lot of extra consumption, consumes extra CPU, disk space, and the overhead of I/O disk operations. The recommendation from the official website is that it should be avoided if there is no strict need, usually it is not necessary.

Here are some of the data I tested:

insert operation
Turn off transaction processing
100W data not autoincrement 74.7088160515 s
100W data autoincrement 91.2676999569 s
Turn things on
100W data not autoincrement 69.6123759747 s
100W data autoincrement 86.1955218315 s 

The pros and cons of SQLite using AutoIncrement