Persistent storage of in memory database data

(1) By anonymous on 2024-08-22 06:47:49 [link] [source]

I need to persistently store some of the cached data in the SQLite3 in memory database on disk. What is the optimal method to use. CPU load and IO must be minimized as much as possible.

(2) By Stephan (stephancb) on 2024-08-22 10:20:41 in reply to 1 [link] [source]

You can attach a database on disk to an initially memory-only database:

ATTACH "atest.db" as a;

and then use ordinary SQL to copy stuff from tables in memory to tables of the disk db. The usual performance considerations within SQL would apply.

Memory-mapped I/O by simply setting PRAGMA mmap_size to a large value boosts performane in my experience (with Linux).

(3) By HashBackup (hashbackup) on 2024-08-24 23:23:54 in reply to 1 [link] [source]

You can use dbpages to copy it to disk.  In Python:

for data, in con.execute('select data from sqlite_dbpage order by pgno'):
  write page to external file

(4) By Bo Lindbergh (_blgl_) on 2024-08-24 23:45:50 in reply to 1 [source]

The backup API is your friend.

(5.1) By Aask (AAsk1902) on 2024-08-26 02:35:55 edited from 5.0 in reply to 1 [link] [source]

What is the optimal method to use. CPU load and IO must be minimized as much as possible.

If your database is in memory AND you want to persist it, one way is:

Step 1:

.once 'e:/temp/inmemScript.sql'
.dump

Step 2:

Open/create an on-disk database

Step 3:

Run the script in 'e:/temp/inmemScript.sql'

Else, simply use VACUUM - see session below.

SQLite version 3.46.1 2024-08-13 09:16:08 (UTF-16 console I/O)
Enter ".help" for usage hints.
sqlite> /* Create a table */
sqlite> CREATE TABLE IF NOT EXISTS tblINMemoryTable as SELECT 'SQLite' as Database,'3.46.1' as Version;
sqlite> .databases
main: "" r/w
sqlite> /* using in-memory database */
sqlite> select * from tblInMemoryTable;/* Check the table */
Database  Version
--------  -------
SQLite    3.46.1
sqlite> /* Persist to an on-disk database */
sqlite> vacuum into 'e:/temp/pinmem.db';
sqlite> .open 'e:/temp/pinmem.db'
sqlite> .databases
main: e:\temp\pinmem.db r/w
sqlite> .tables
tblINMemoryTable
sqlite> select * from tblInMemoryTable;
Database  Version
--------  -------
SQLite    3.46.1
sqlite> /* research what vacuum does */

This exercise will take what it takes CPU- and duration-wise; the alternative is to let the in-memory database simply evaporate.