mickeyp 2 days ago

These are my PRAGMAs and not your PRAGMAs. Be very careful about blindly copying something that may or may not match your needs.

    PRAGMA foreign_keys=ON
    PRAGMA recursive_triggers=ON
    PRAGMA journal_mode=WAL
    PRAGMA busy_timeout=30000
    PRAGMA synchronous=NORMAL
    PRAGMA cache_size=10000
    PRAGMA temp_store=MEMORY
    PRAGMA wal_autocheckpoint=1000
    PRAGMA optimize <- run on tx start
Note that I do not use auto_vacuum for DELETEs are uncommon in my workflows and I am fine with the trade-off and if I do need it I can always PRAGMA it.

defer_foreign_keys is useful if you understand the pros and cons of enabling it.

Reply View 7 replies
  • mikeocool 2 days ago

    Using strict tables is also a good thing to do, if you value your sanity.

    Reply View 0 replies
  • porridgeraisin 2 days ago

    You should pragna optimize before TX end, not at tx start.

    Except for long lived connections where you do it periodically.

    https://www.sqlite.org/lang_analyze.html#periodically_run_pr...

    Reply View 2 replies
    • masklinn 2 days ago

      Also foreign_keys has to be set per connection but journal_mode is sticky (it changes the database itself).

      Reply View 1 reply
      • porridgeraisin 2 days ago

        Yes, if journal_mode was not sticky, a new process opening the db would not know to look for the wal and shm files and read the unflushed latest data from there. On the other hand, foreign key enforcement has nothing to do with the file itself, it's a transaction level thing.

        In any case, there is no harm in setting sticky pragmas every connection.

        Reply View 0 replies
  • adzm 2 days ago

    Really, no mmap?

    Reply View 2 replies
    • metrix 2 days ago

      I'm curious what your suggest mmap pragma would be.

      Reply View 1 reply
      • adzm 17 hours ago

        PRAGMA mmap_size=268435456;

        for example? I'm surprised by the downvotes. Using mmap significantly reduced my average read query time; durations about 70% the length!

        Reply View 0 replies