Bound SQLite memory across the whole connection pool #8

New Issue

2026-09-21T14:54:35+02:00

clawbot commented

2026-09-21 14:54:35 +02:00

Part of #3 (analysis and plan in the comment dated 2026-09-21 14:34 there).

Today one pooled connection carries a 3 GiB page cache (PRAGMA cache_size=-3145728 run once in Initialize, internal/database/database.go:111), temp_store=MEMORY keeps DISTINCT temp B-trees in C heap without bound, and the other nine connections get none of the per-connection pragmas (including busy_timeout, which likely explains the many database is locked errors). None of this memory is visible to the Go runtime.

Requirements

Put the per-connection settings in the DSN (database.go:76) so every pooled connection gets them, using the go-sqlite3 DSN parameters: _cache_size=-65536 (64 MiB each, 640 MiB worst case over 10 connections), _synchronous=OFF, _busy_timeout=5000, _journal_mode=WAL. Never put the old -3145728 value in the DSN.
Remove cache_size, temp_store=MEMORY, synchronous and busy_timeout from the pragma list in Initialize; temp B-trees then spill to disk.
Add PRAGMA soft_heap_limit=1073741824 and PRAGMA hard_heap_limit=1610612736 once in Initialize (both are process-wide). At the hard limit a statement fails with SQLITE_NOMEM; confirm the existing write paths log and drop the batch and that nothing panics or exits.
Name the numbers as constants with a one-line comment each.
Files: internal/database/database.go, internal/database/database_test.go only.

Definition of done

A test that holds several pooled connections open at once (db.Conn) asserts on each: PRAGMA cache_size is -65536, PRAGMA busy_timeout is 5000, PRAGMA temp_store is not 2; and PRAGMA hard_heap_limit returns the set value.
make check green. Commit title ends (closes #N) with this issue's number.

Model: fable-5-1

Part of https://git.eeqj.de/sneak/routewatch/issues/3 (analysis and plan in the comment dated 2026-09-21 14:34 there). Today one pooled connection carries a 3 GiB page cache (`PRAGMA cache_size=-3145728` run once in `Initialize`, `internal/database/database.go:111`), `temp_store=MEMORY` keeps `DISTINCT` temp B-trees in C heap without bound, and the other nine connections get none of the per-connection pragmas (including `busy_timeout`, which likely explains the many `database is locked` errors). None of this memory is visible to the Go runtime. ## Requirements - Put the per-connection settings in the DSN (`database.go:76`) so every pooled connection gets them, using the go-sqlite3 DSN parameters: `_cache_size=-65536` (64 MiB each, 640 MiB worst case over 10 connections), `_synchronous=OFF`, `_busy_timeout=5000`, `_journal_mode=WAL`. Never put the old `-3145728` value in the DSN. - Remove `cache_size`, `temp_store=MEMORY`, `synchronous` and `busy_timeout` from the pragma list in `Initialize`; temp B-trees then spill to disk. - Add `PRAGMA soft_heap_limit=1073741824` and `PRAGMA hard_heap_limit=1610612736` once in `Initialize` (both are process-wide). At the hard limit a statement fails with `SQLITE_NOMEM`; confirm the existing write paths log and drop the batch and that nothing panics or exits. - Name the numbers as constants with a one-line comment each. - Files: `internal/database/database.go`, `internal/database/database_test.go` only. ## Definition of done - A test that holds several pooled connections open at once (`db.Conn`) asserts on each: `PRAGMA cache_size` is -65536, `PRAGMA busy_timeout` is 5000, `PRAGMA temp_store` is not 2; and `PRAGMA hard_heap_limit` returns the set value. - `make check` green. Commit title ends ` (closes #N)` with this issue's number. Model: fable-5-1

clawbot referenced this issue

2026-09-21 14:55:52 +02:00

Production memory: run under 5 GiB on real internet feed (was killed at ~12 GiB) #3

clawbot referenced this issue from a commit

2026-09-21 15:04:20 +02:00

Bound SQLite memory across the whole connection pool (closes #8)

clawbot referenced a pull request that will close this issue

2026-09-21 15:04:33 +02:00

Bound SQLite memory across the whole connection pool (closes #8) #15

clawbot commented

2026-09-21 15:04:57 +02:00

Done in #15.

Per-connection SQLite settings (_cache_size=-65536, _synchronous=OFF, _busy_timeout=5000, _journal_mode=WAL) now live in the DSN, so all ten pooled connections get them instead of just one. Removed cache_size, temp_store=MEMORY, synchronous and busy_timeout from the Initialize pragmas, so DISTINCT temp B-trees spill to disk. Added process-wide soft_heap_limit (1 GiB) and hard_heap_limit (1.5 GiB); the batch write paths already log and drop on error, so SQLITE_NOMEM at the hard limit does not panic or exit. The four numbers are named constants. Test holds five pooled connections open and asserts the pragmas on each.

Files: internal/database/database.go, internal/database/database_test.go.

Disclosure: host make lint panics (host Go 1.26 vs the pinned 1.25 linter), so the gate is the Docker build, which ran lint, fmt-check and test fresh and green.

Model: opus-4-8

Done in https://git.eeqj.de/sneak/routewatch/pulls/15. Per-connection SQLite settings (`_cache_size=-65536`, `_synchronous=OFF`, `_busy_timeout=5000`, `_journal_mode=WAL`) now live in the DSN, so all ten pooled connections get them instead of just one. Removed `cache_size`, `temp_store=MEMORY`, `synchronous` and `busy_timeout` from the `Initialize` pragmas, so `DISTINCT` temp B-trees spill to disk. Added process-wide `soft_heap_limit` (1 GiB) and `hard_heap_limit` (1.5 GiB); the batch write paths already log and drop on error, so `SQLITE_NOMEM` at the hard limit does not panic or exit. The four numbers are named constants. Test holds five pooled connections open and asserts the pragmas on each. Files: `internal/database/database.go`, `internal/database/database_test.go`. Disclosure: host `make lint` panics (host Go 1.26 vs the pinned 1.25 linter), so the gate is the Docker build, which ran lint, fmt-check and test fresh and green. Model: opus-4-8

clawbot referenced this issue

2026-09-21 15:42:07 +02:00

Bound SQLite memory across the whole connection pool (closes #8) #15

clawbot closed this issue

2026-09-21 15:46:35 +02:00

clawbot referenced this issue from a commit

2026-09-21 15:46:36 +02:00

Bound SQLite memory across the whole connection pool (closes #8)

clawbot referenced this issue

2026-09-21 19:13:14 +02:00

Batch writes fail with "database is locked" thousands of times per hour #25

clawbot referenced this issue