2.8 KiB
The database
One SQLite file, data/db.sqlite3 (on the server /home/marwin/diora-prod-data, mounted into
diora-prod at /app/data). The books live in it too, encrypted, so it is large (2.5 GB in
October 2026) and some writes are big (a scanned book of 30 MB).
Write-ahead log (since 2026-10-19)
diora/sqlite.py puts every connection in WAL mode (PRAGMA journal_mode=WAL, set once per
file, it stays) with synchronous=NORMAL; settings.DATABASES has transaction_mode: IMMEDIATE, so a transaction takes the write lock when it begins.
Why: in the rollback-journal mode before, a long write kept all readers out. The podcast refresh (cron, every 15 minutes) and big uploads made requests fail with "database is locked", GET /books/ among them — the apps then saw no new books and no changed titles. With WAL, readers read on while one writer writes; writers still take turns (20 s timeout).
Next to db.sqlite3 there are now db.sqlite3-wal and db.sqlite3-shm. They belong to the
database: a copy of db.sqlite3 alone may miss the latest writes. To copy it safely:
docker exec diora-prod python -c "import sqlite3; s=sqlite3.connect('data/db.sqlite3'); d=sqlite3.connect('data/backup.sqlite3'); s.backup(d)"
or stop the container first. Back to the old mode, if ever needed:
PRAGMA journal_mode=DELETE (with no other connection open) and remove diora/sqlite.py's hook.
The book list never reads past the file
A book's row is user, meta_ct, meta_iv, data_ct (the file), data_iv, uploaded_at, is_read, cover_ct, cover_iv. SQLite stores a big value in a chain of overflow pages, and to reach a
column after it, it walks the whole chain: listing 427 books read gigabytes and took 37 s
(the desktop app gives up after 10 s, so it never synced). Since then books.models.book_rows()
reads id, meta_ct, meta_iv from the rows (they stand before the file) and uploaded_at, is_read / whether there is a cover from the covering indexes ebook_list_idx and
ebook_cover_idx — no list query touches a file. books/tests.py checks the query plans.
The files in a table of their own (since 2026-10-19)
books.models.EBookFile (one row per book, book_id its key) holds data_ct/data_iv;
EBook has only the small fields. Before, every change of a title, a folder or "gelesen"
rewrote the whole row with its file (megabytes, for a scanned volume tens of them), and every
EBook.objects.get() loaded the file. EBook.objects.create(data_ct=…, data_iv=…) still
works (the manager makes the EBookFile too); the API is unchanged.
Migration books/0008_ebook_file copied 430 files in one statement and dropped the two
columns: about 2 minutes on the server (rehearsed on a copy), the site down meanwhile. The
pages the old columns used are free now and get reused; VACUUM (exclusive, minutes, at a
quiet time) would give them back to the disk.