SQLite – Should I add some indexes even if select queries are fast

The problem with MySQL's InnoDB engine is that the storage architecture is essentially using index-organized tables, which will unfortunately experience performance issues for large numbers of inserts when the target table is large. The only case in which this can be mitigated is if you have sequential inserts, that is, the primary key of the set of inserts follows a sequence.

For high ingestion rates of data, different technologies are recommended, such as Cassandra which uses a log-structured merge tree, or in your case, I might recommend TokuDB, which has been acquired by Percona, and which uses a technology called a fractal tree index, which is essentially creative way to cache, pre-sort, and batch insert into your index-organized table (of course, there's much more to it than that, but no need to go into the details here.)

Long story short, if you have moderately high ingestion rates, you need a heap organized table structure implemented by databases like Oracle and PostgreSQL, and if you have very high ingestion rates, you need to use a database like Cassandra or TokuDB.

In WAL mode, the database must do regular checkpoints to prevent the log from growing infinitely large.

So after a large amount of changes, it decides to do an automatic checkpoint (especially since you have set wal_autocheckpoint to a rather small value).

You can move the checkpoint delay to another time (but not avoid it altogether) by doing checkpoints manually.