Reducing memory consumption when executing large transactions

I increased the speed of INSERT queries following an example from the Internet, using the following parameters for SQLite:

PRAGMA journal_mode = OFF;
PRAGMA synchronous = 0;
PRAGMA cache_size = 1000000;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA temp_store = MEMORY;

Here is my code:

tx, err := db.Begin()
if err != nil {
    log.Fatal(err)
}
pr, err := tx.Prepare("INSERT INTO Table (p1, p2, p3, p4, p5) VALUES (?, ?, ?, ?, ?)")
if err != nil {
    log.Fatal(err)
}
defer pr.Close()
for i := 0; i < maxI; i++ {
    for j := 0; j < maxJ; j++ {
        ...
        _, err = pr.Exec(param1, param2, param3, param4, param5)
        if err != nil {
            log.Fatal(err)
        }
    }
}
err = tx.Commit()
if err != nil {
    log.Fatal(err)
}

Now the query is running fast, but too much RAM is being consumed. Because the data is stored in RAM and saved to the database file only at the very end of the execution.

I thought that it is possible to periodically save the data to the database file, this will slightly increase the execution time, but reduce the memory consumption. With each change of "i", the transaction starts and when all "j" are completed, the transaction ends:

for i := 0; i < maxI; i++ {
    tx, err := db.Begin()
    if err != nil {
        log.Fatal(err)
    }
    pr, err := tx.Prepare("INSERT INTO Table (p1, p2, p3, p4, p5) VALUES (?, ?, ?, ?, ?)")
    if err != nil {
        log.Fatal(err)
    }
    defer pr.Close()
    for j := 0; j < maxJ; j++ {
        ...
        _, err = pr.Exec(param1, param2, param3, param4, param5)
        if err != nil {
            log.Fatal(err)
        }
    }
    err = tx.Commit()
    if err != nil {
        log.Fatal(err)
    }
}

I thought that now data should be written to the file in blocks and only one data block should be in RAM.

But in the process of execution, the data is not saved to the file and the RAM continues to fill up. That is, there is no difference in execution between the first and second code options.

I thought that when call "Commit" for a transaction, the data should be saved to a file and the RAM should be cleared. Please tell me what am I doing wrong.

The parameter of PRAGMA cache_size is the number of pages (normally 4k bytes per page).

PRAGMA cache_size = 1000000; will allocate a maximum of 4GB of RAM to the page cache.
Page cache is allocated when needed, up to that maximum, but is not freed until the connection is closed.

Since you are inserting a large number of rows, they will end up on different pages, so you will end up having in cache all the pages you have written to disk, until you fill the cache.

If you want to reduce memory consumption, just reduce the value to something like 1000, which corresponds to 4 MB, or remove it at all. Default cache is 2 MB and is enough if you are just inserting rows.

Also note that data is indeed written to disk when you call COMMIT (or even before commit if there is not enough cache). But Sqlite will keep a copy in cache in the event that it's needed afterwards, to avoid having to reread it from the disk.