Opened 3 years ago

Last modified 6 months ago

#24018 assigned New feature

Support journal_mode=WAL for sqlite

Reported by: Curtis Maloney Owned by: Curtis Maloney
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

SQLite, as of 3.7, supports a WAL journal mode https://www.sqlite.org/wal.html

It's been my experience that enabling this can dramatically improve performance, especially under heavy writes. I've seen some write-heavy operations go from a couple of minutes to a few seconds.

It can be enabled by executing the command:

PRAGMA journal_mode=wal;

which will return 'wal' on success.

If it fails to apply for any reason, it will return the mode it is in - 'delete' or 'memory'.

I propose to add JOURNAL_MODE as an option to the sqlite backend.

however, some quick tinkering shows that the sqlite backend doesn't ever explicitly create the DB, so it's not immediately apparent how to detect if/when this command should be issued.

I am still investigating.

Change History (4)

comment:1 Changed 3 years ago by Curtis Maloney

Owner: changed from nobody to Curtis Maloney
Status: newassigned

comment:2 Changed 3 years ago by Tim Graham

Triage Stage: UnreviewedAccepted

comment:3 Changed 6 months ago by tonnzor

Any progress here?

We could add init_command to OPTIONS for sqlite backend like MySQL has.

Then you would be able to use any PRAGMA we need:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
        'OPTIONS': {
            'init_command': 'PRAGMA journal_mode=wal;',
        }
    }
}
Last edited 6 months ago by tonnzor (previous) (diff)

comment:4 Changed 6 months ago by tonnzor

As a workaround, you could use https://stackoverflow.com/a/6843199 (use connection_created signal)

Note: See TracTickets for help on using tickets.
Back to Top