Opened 9 years ago

Closed 4 months ago

#24018 closed New feature (fixed)

Support setting pragma options for SQLite.

Reported by: Curtis Maloney Owned by: Aaron Linville
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: bcail Triage Stage: Ready for checkin
Has patch: yes 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 (17)

comment:1 by Curtis Maloney, 9 years ago

Owner: changed from nobody to Curtis Maloney
Status: newassigned

comment:2 by Tim Graham, 9 years ago

Triage Stage: UnreviewedAccepted

comment:3 by Artem Skoretskiy, 7 years ago

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 7 years ago by Artem Skoretskiy (previous) (diff)

comment:4 by Artem Skoretskiy, 7 years ago

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

comment:5 by Aaron Linville, 3 years ago

Owner: changed from Curtis Maloney to Aaron Linville

comment:6 by Aaron Linville, 3 years ago

Has patch: set

https://github.com/django/django/pull/14824

I needed the ability to adjust some pragma settings associated with caching but as Artem noted, this will work for any pragma setting.

comment:7 by Mariusz Felisiak, 3 years ago

Needs documentation: set
Patch needs improvement: set

comment:8 by Carlton Gibson, 3 years ago

Maybe allowing PRAGMAS to be set is a good idea, in general (e.g. #31765) but the WAL setting is persistent:

The persistence of WAL mode means that applications can be converted to using SQLite in WAL mode without making any changes to the application itself. One has merely to run "PRAGMA journal_mode=WAL;" on the database file(s) using the command-line shell or other utility, then restart the application. SQLite Docs.

So narrowly, running that a single time is sufficient, and an entry in the SQLite notes may be the quickest route forward (for this narrower issue).

comment:9 by Afonso Silva, 10 months ago

Based on the previous answers, a workaround for allowing init_command similar to MySQL is to use a signal receiver for connection_created, e.g.:

from django.conf import settings                                                
from django.db.backends.signals import connection_created
from django.dispatcher import receiver
                         
@receiver(connection_created)    
def init_command(sender, connection, **kwargs) -> None:
    command = connection.settings_dict["OPTIONS"].get("init_command")
    if connection.vendor == "sqlite" and command:
        cursor = connection.cursor()
        cursor.execute(command)

This way you could add init_command to your settings.py:

DATABASES = {
     'default': {
         'ENGINE': 'django.db.backends.sqlite3',
         'NAME': BASE_DIR / 'db.sqlite3',
         'OPTIONS': {
             'init_command': 'PRAGMA journal_mode=wal;'
         }
    }
}

I think having this natively in Django would be useful, is there any help needed for the previous linked PR?

in reply to:  9 comment:10 by Aaron Linville, 10 months ago

Replying to Afonso Silva:

I think having this natively in Django would be useful, is there any help needed for the previous linked PR?

I updated the PR and modified it with a more compelling example; synchronous=0 will not persist.

comment:11 by bcail, 4 months ago

@Aaron, are you still wanting to work on this? If so, if you fix the tests you could update the flags on this issue so it goes back in the list of tickets to be reviewed.

in reply to:  11 comment:12 by Aaron Linville, 4 months ago

Replying to bcail:

@Aaron, are you still wanting to work on this? If so, if you fix the tests you could update the flags on this issue so it goes back in the list of tickets to be reviewed.

Yes, I've rebased it on latest and fixed the tests I think (a couple are still pending). Assume you mean uncheck the Needs doc and Patch needs improvement?

comment:13 by bcail, 4 months ago

Cc: bcail added

Yup, exactly - so it shows up on the Patch review page.

comment:14 by Aaron Linville, 4 months ago

Needs documentation: unset
Patch needs improvement: unset

comment:15 by Mariusz Felisiak, 4 months ago

Summary: Support journal_mode=WAL for sqliteSupport setting pragma options for SQLite.

comment:16 by Mariusz Felisiak, 4 months ago

Triage Stage: AcceptedReady for checkin

comment:17 by Mariusz Felisiak <felisiak.mariusz@…>, 4 months ago

Resolution: fixed
Status: assignedclosed

In 7a05b8a:

Fixed #24018 -- Allowed setting pragma options on SQLite.

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