SQLite Learning-Temp file

First, Introduction:

Although SQLite database is composed of a single file, but in fact there are some hidden temporary files in SQLite runtime, these temporary files for different purposes exist, for developers, they are transparent, so in the process of development we do not need to pay attention to their existence. However, if you can have a good understanding of the mechanism and application scenarios of these temporary files, it is very helpful for us to optimize and maintain the application in the future. The following seven types of temporary files are mainly produced in SQLite, such as:
1). Roll back the log.
2). Primary database log.
3). SQL statement log.
4). Temporary database file.
5). Temporary persistence file for views and subqueries.
6). Temporary index file.
7). Temporary database file used by the vacuum command.

Second, the specific description:

1. Roll back the log:
SQLite creates the temporary file at the beginning of the thing in order to guarantee the atomic submission and rollback of things. This file is always located in the same directory as the database file and has the following file name format:Database file name + "-journal"。 In other words, if there is no such temporary file, SQLite will not be able to guarantee the integrity of the thing and the consistency of the data state when there is any failure of the system running on the program. The file is deleted immediately after the thing is committed or rolled back.
During a thing run, if the current host is down due to a power failure, and because the rollback log file has been saved on disk, the next time the program starts, SQLite will find the temporary file in the process of opening the database file, we call this log file "Hot Journal". SQLite completes the recovery of the database based on the file before successfully opening the database to ensure that the database's data reverts to the state before the previous thing started.
In SQLite we can modify thejournal_mode pragma, and SQLite uses a different strategy for maintaining the file. By default, this value isDelete, which deletes the log file after the end of the transaction. The PERSIST option value will not delete the log file, but instead will roll back the header of the log file, eliminating the disk overhead associated with file deletion. Then there is the off option value, which indicates that SQLite does not produce a rollback log file at the start of a thing, so that SQLite can no longer guarantee database data consistency in the event of a system failure.

2. Primary database log:
In SQLite, if the operation of a thing works on multiple databases, that is, by attaching to a database in the current connection via the Attach command, SQLite generates the primary database log file to ensure that the change in things is atomic across multiple databases. As with the rollback log file, the primary database log file is also located in the directory where the primary database file resides in the current connection, in the file name format: Primary database file name + random suffix. In the file, it will contain the names of all the attached databases that the current thing will change. After the thing is submitted, this file is also deleted by SQLite.
The primary database log file can be created only when a thing is operating multiple databases simultaneously (the primary and attached databases). With this file, SQLite can achieve the atomicity of things across multiple databases, otherwise, it can simply guarantee the consistency of the state within each single database. In other words, if the event occurs during the execution of a system crash or a host outage, in the case of data recovery, without the existence of the file, it will cause some SQLite database to be in the commit state, while the other part is in the rollback state, so that the consistency of the thing will be broken.

3. SQL statement log:
In a larger thing, SQLite creates a log file of SQL statements at the beginning of a thing in order to ensure that some of the data can be rolled back normally in the event of an error. For example, the UPDATE statement modifies the first 50 data, but when you modify the 51st data, it is found that the operation will break the uniqueness constraints of a field, and SQLite will eventually have to roll back the first 50 data that has been modified through the log file.
SQL statement log files can only be created when an INSERT or UPDATE statement modifies multiple rows of records, and at the same time, these operations are most likely to break some constraints and throw exceptions. However, if the INSERT or UPDATE statement is not included in Begin...commit, and no other SQL statement is running on the current connection, SQLite will not create the SQL statement log file, but simply rollback the log to complete the undo operation of some of the data.
Unlike the above two temporary files, the SQL statement log file is not necessarily stored in the same directory as the database file, and its file name is randomly generated. The amount of disk space that the file occupies depends on the number of records that the update or INSERT statement will modify. After the end of the thing, the file will be automatically deleted.

4. Temporary Database files:
When a temporary data table is created using the Create temp table syntax, the data table is only visible within the current connection, and the temporary table disappears after the current connection is closed. However, during the lifetime, the temporary table will be stored in a temporary database file along with its associated indexes and views. The temporary file is created the first time the Create TEMP TABLE is executed, and the file is automatically deleted after the current connection is closed. Finally, it is necessary to note that the staging database cannot be executed with the detach command, and the attach command cannot be executed by another process.

5. Temporary persistence file for views and subqueries:
In many queries that include subqueries, SQLite's executor splits the query statement into separate SQL statements, persisting the results of the subquery into a temporary file, and then associating the data in the temporary file with the external query, so we can call it a materialized subquery. In general, SQLite's optimizer tries to avoid the materialization of subqueries, but sometimes the operation is unavoidable. The disk space occupied by the temporary file needs to depend on the number of data retrieved by the subquery, and the file will be automatically deleted after the query is completed. See the example below:
SELECT * from Ex1 WHERE ex1.a in (SELECT b from EX2);
In the query statement above, the result of the subquery Select B from EX2 will be persisted to the temporary file, and the external query will check the temporary file for each record at run time to determine if the current record is present in the temporary file, and if so, output the current record. Obviously, the above behavior will produce a large number of IO operations, which significantly reduce the execution efficiency of the query, in order to avoid the generation of temporary files, we can change the above query statement to:
SELECT * from Ex1 where EXISTS (select 1 from ex2 where ex2.b=ex1.a);
For the following query statement, if SQLite does not do any intelligent rewrite operation, the subquery in the query will also be persisted to the temporary file, such as:
SELECT * FROM Ex1 JOIN (select-B from Ex2) as-T on t.b=ex1.a;
After SQLite automatically modifies it to the following wording, it will no longer generate temporary files, such as:
SELECT ex1.*, ex2.b from Ex1 joins EX2 on ex2.b=ex1.a;

6. Temporary index file:
When a query statement contains the following SQL clause, SQLite creates a temporary index file for storing intermediate results, such as:
1). ORDER by or GROUP BY clause.
2). The DISTINCT keyword in the clustered query.
3). Multiple Select query statements that are connected by union, except, and intersect.
It is important to note that if an index already exists on the specified field, SQLite will no longer create the temporary index file, but instead accesses the data and extracts useful information by traversing the index directly. If you do not have an index, you need to store the results of the sort in a temporary index file for later use. The amount of disk space that the temporary file occupies depends on the number of sorted data that is automatically deleted after the query is finished.

7. Temporary database files used by the vacuum command:
The vacuum command creates a temporary file at work and then writes the entire rebuilt database to the temporary file. Then copy the contents of the temporary file back into the original database file, and finally delete the temporary file.
The temporary file occupies no more disk space than the size of the original file.

Iii. Related compile-time parameters and directives:

For SQLite, the rollback log, the primary database log, and the SQL statement log files are written to the disk file by SQLite when needed, but for other types of temporary files, SQLite can store them in memory to replace the disk files. This reduces the amount of IO operations that can be made during execution. The following three factors are relied upon to complete the optimization:
1. Compile-time parameter sqlite_temp_store:
This parameter is a macro definition (#define) in the source code with a value range of 0 to 3 (the default is 1), as described below:
1). Equal to 0 o'clock, temporary files are always stored on disk without regard to the settings of the temp_store pragma directive.
2). Equals 1 o'clock, the temporary file is stored on disk by default, but the value can be overwritten by the temp_store pragma directive.
3). Equals 2 o'clock, the temporary file is stored in memory by default, but the value can be overwritten by the temp_store pragma directive.
4). Equal to 3 o'clock, temporary files are always stored in memory without regard to the settings of the temp_store pragma directive.

2. Runtime directives temp_store Pragma:
The range of values for this directive is 0 to 2 (the default is 0), which can be dynamically set when the program is run, as described below:
1). equals 0 o'clock, the storage behavior of temporary files is determined entirely by the Sqlite_temp_store compile-time parameters.
2). Equals 1 o'clock, if the compile-time parameter sqlite_temp_store specifies to use memory to store temporary files, then the directive overrides this behavior and uses disk storage.
2). Equals 2 o'clock, if the compile-time parameter sqlite_temp_store specifies that the disk is used to store temporary files, then the directive overrides this behavior and uses memory storage.

3. The size of the temp file:
For the above two parameters, there is a parameter value indicating that the default is stored in memory, only when the size of the temporary file exceeds a certain threshold, the data will be written to disk according to certain algorithm, so as to avoid the temporary file occupying too much memory and affect the execution efficiency of other programs.

Finally, again, Sqlite_temp_store compile-time parameters and Temp_store pragma runtime directives only affect the storage policy for temporary files other than the rollback log and the primary database log. In other words, the rollback log and the primary database log will always write data to disk, without paying attention to the values of the above two parameters.

Iv. Other optimization Strategies:

In SQLite due to the use of the buffer optimization mechanism of page cache, so even if the temporary files are designated to be stored on disk, it is only when the size of the file grows to a certain size, it is possible that SQLite flushed to the disk file, before they will still reside in memory. This means that for most scenarios, if the amount of data in temporary and temporary indexes is relatively small, they will not be written to disk, and there will be no IO events. They are flushed to the disk file only when they grow to the point where the memory cannot be accommodated. Where the sqlite_default_temp_cache_size compile-time parameter can be used to specify how many CACHE pages the temporary tables and indexes need to be flushed to the disk file, the default value for this parameter is 500 pages.

SQLite Learning-Temp file