Datasette plugin that copies file into memory on startup

[simonw]

Related:

• Research: demonstrate if parallel SQL queries are worthwhile datasette#1727

Idea from SQLite forum discussion: https://sqlite.org/forum/forumpost/652a9402eb5ea505

Maybe I should investigate the performance benefits of copying the entire database into a :memory: SQLite database on server startup, then serving queries from that.

Based on the observation that SQLite is often I/O bound.

[simonw]

Would use this: https://www.sqlite.org/lang_vacuum.html#vacuuminto

I think like this:

VACUUM INTO 'file:name_of_copy?mode=memory&cache=shared'

[simonw]

datasette-copy-to-memory as the plugin name.

[simonw]

Clue in https://stackoverflow.com/a/67637492/6083

A few tries later... It seems to work if you ping the memory base before the vacuum. I guess the in-memory db is not really initialized without a first contact.

Not sure what "ping" means there - but it wasn't working for me when I tried with an empty database.

[simonw]

This also failed because you can't vacuum into main:

from datasette import hookimpl


@hookimpl
def startup(datasette):
    async def inner():
        for db in datasette.databases.values():
            if db.path:
                memory_name = "{}_memory".format(db.name)
                memory_db = datasette.add_memory_database(memory_name)

                def vacuum_into(conn):
                    conn.execute("ATTACH DATABASE ? AS _copy_from", [db.path])
                    conn.execute("VACUUM _copy_from INTO main")

                await memory_db.execute_write_fn(vacuum_into)

    return inner

[simonw]

This totally works!

from datasette import hookimpl


@hookimpl
def startup(datasette):
    async def inner():
        for db in datasette.databases.values():
            if db.path:
                memory_name = "{}_memory".format(db.name)
                memory_db = datasette.add_memory_database(memory_name)
                # Ensure the in-memory database is initalized
                await memory_db.execute("select 1 + 1")

                def vacuum_into(conn):
                    conn.execute(
                        "VACUUM INTO ?",
                        ["file:{}?mode=memory&cache=shared".format(memory_name)],
                    )

                await db.execute_write_fn(vacuum_into)

    return inner