Use SQLite PRAGMA Optimize Causes Database Issues

[touxiaoling]

Steps to reproduce

komga:
  database:
    # sets the retry timeout when SQLITE_BUSY error happens
    busy-timeout: 30s

    # changes the journal mode
    # accepted values are: DELETE, TRUNCATE, PERSIST, MEMORY, WAL, OFF
    # most likely to be set to wal if needed, check https://sqlite.org/wal.html for more details
    journal-mode: wal

    # pool size will determine the number of connections in the pool
    # this takes precedence over max-pool-size if set
    # defaults to undefined
    # pool-size: 1

    # max-pool-size will determine the maximum number of connections in the pool
    # when set, the number of connections is set to the number of available processors capped at max-pool-size
    # defaults to 1
    max-pool-size: 8

    # pragmas accepts a list of key/value pairs where:
    # - key is the pragma name (see https://www.sqlite.org/pragma.html)
    # - value is the pragma value
    pragmas:
      optimize: 0xfffe
      page_size: 4096 # Increase page size to improve I/O performance
      synchronous: NORMAL # Balance between performance and data safety
      cache_size: -2000 # Increase cache size to improve query speed (in pages)
      temp_store: MEMORY # Store temporary tables in memory to improve access speed
      trusted_schema: false # Disable trusted mode to improve performance

Expected behavior

The SQLite database will be optimized, and everything else should work smoothly.

Actual behavior

I’m unable to find my account in Komga.

Logs

No response

Komga version

1.14.1

Operating system

Truenas

Installation method

Docker

Other details

Hello, after reading the SQLite documentation, I found that it’s recommended to run PRAGMA optimize periodically. However, when I set application.yaml as shown below, I can’t access my existing account after starting Komga.Could anyone help me understand why this might be happening and suggest what I should do?

Acknowledgements

• [X] I have searched the existing issues (open AND closed) and this is a new ticket, NOT a duplicate or related to another open issue.
• [X] I have written a short but informative title.
• [X] I have checked the FAQ.
• [X] I have updated the app to the latest version.
• [X] I will fill out all of the requested information in this form.

[gotson]

SQLite Pragma are only to be used when you know what they do.

[touxiaoling]

Thank you for your response. Before modifying the pragmas parameters, I carefully read the SQLite pragma documentation at https://www.sqlite.org/pragma.html. According to the documentation:

Attempt to optimize the database. All schemas are optimized in the first two forms, and only the specified schema is optimized in the latter two.

In most applications, using PRAGMA optimize as follows will help SQLite to achieve the best possible query performance:

Applications with short-lived database connections should run "PRAGMA optimize;" once, just prior to closing each database connection.

Applications that use long-lived database connections should run "PRAGMA optimize=0x10002;" when the connection is first opened, and then also run "PRAGMA optimize;" periodically, perhaps once per day or once per hour.

All applications should run "PRAGMA optimize;" after a schema change, especially after one or more CREATE INDEX statements.

It can be seen that running optimize once at the end of the program is beneficial. As an alternative, I chose to run this parameter at the start of the program if the configuration takes effect.

Additionally, according to the documentation, the default value of optimize is 0xfffe, so I configured it as 0xfffe.

Furthermore, I tried directly executing the command sqlite3 ./komga/database.sqlite "PRAGMA optimize;" via the command line, and it seemed to work effectively. Therefore, I hope to add this to the configuration file for periodic execution.

Since I have a 800MB SQLite database, I expect this configuration to provide some benefits.

Although I am not sure why this setup did not take effect, thank you for your response..

[gotson]

Closing this.