[PR #3267] fix(sqlite): revert no-op finalize wrapper and aggressive PRAGMAs (#2120) #36921

New Issue

GiteaMirror · 2026-06-18T22:11:01-05:00

GiteaMirror commented

2026-06-18 22:11:01 -05:00

📋 Pull Request Information

Original PR: https://github.com/fosrl/pangolin/pull/3267
Author: @Josh-Voyles
Created: 6/13/2026
Status: 🔄 Open

Base: dev ← Head: memfix1-1.18.4

📝 Commits (2)

d94af2b fix(sqlite): remove cache_size and mmap_size PRAGMAs (#2120)
522ca67 fix: remove no-op autoFinalizeStatement wrapper and redundant busy_timeout (#2120)

📊 Changes

1 file changed (+12 additions, -50 deletions)

View changed files

📝 server/db/sqlite/driver.ts (+12 -50)

📄 Description

Community Contribution License Agreement

By creating this pull request, I grant the project maintainers an unlimited,
perpetual license to use, modify, and redistribute these contributions under any terms they
choose, including both the AGPLv3 and the Fossorial Commercial license terms. I
represent that I have the right to grant this license for all contributed content.

Description

Preface

I'm a cloud engineer with experience in Python and Bash; I do not have much experience with JS/TS. This is a follow-up to my previous #2120 fix. After 3 days of A/B testing in production on a t3a.micro with Claude, the previous fix turned out to be net-negative on small instances. This PR reverts those specific changes after empirical validation. No code or comments are left over from the previous attempt.

Summary

Removes the autoFinalizeStatement prepare() wrapper. better-sqlite3 11.x exposes no Statement.finalize() — the wrapper threw a TypeError on every query (silently swallowed by its own try/catch) and freed nothing, while adding +122% per-statement overhead.
Removes the explicit busy_timeout = 5000 PRAGMA. better-sqlite3 already arms sqlite3_busy_timeout(db, 5000) via its default timeout constructor option, so the pragma is redundant.
Removes cache_size = -65536 (64 MB) and mmap_size = 268435456 (256 MB) PRAGMAs. On small (~1 GB) instances they inflate RSS and cause page-cache thrashing — the exact shape that hits #2120.
Keeps the env-gated WAL block (journal_mode = WAL + synchronous = NORMAL when ENABLE_SQLITE_WAL_MODE=true). journal_mode is sticky in the DB file, so removing the block would strand databases that opted in.

Context

The previous fix assumed unbounded native sqlite3_stmt accumulation was the cause of #2120 and added a finalize-on-execute wrapper plus aggressive PRAGMA tuning. Re-investigation showed:

Statement.finalize() does not exist on better-sqlite3 11.x. Verified by logging Statement.finalize exists: undefined from the runner image — the wrapper's call landed in its catch block on every execution.
drizzle-orm prepares a fresh native statement per query (no statement cache), and better-sqlite3 frees sqlite3_stmt in the Statement destructor at GC, so statements cannot accumulate.
The wrapper added +122% per-statement overhead (3.90 → 8.66 µs/op, 200k-op in-container microbench) with no observable benefit.
The 64 MB page cache + 256 MB mmap region pushed RSS into territory that caused page-cache pressure on 1 GB instances, making the original symptom worse, not better.

With ENABLE_SQLITE_WAL_MODE unset, the driver is now runtime-identical to pre-1.18.3.

Changes

server/db/sqlite/driver.ts — remove autoFinalizeStatement wrapper, remove busy_timeout/cache_size/mmap_size PRAGMAs, keep env-gated WAL block.

Verification

Ran the wrapper-OFF build for 3 days on a t3a.micro (1 GB / 2 vCPU) with the same traffic profile as the prior wrapper-ON deployment (normal usage, 7 day request retention, Uptime Kuma checks). Container memory stable around ~335 MB with no PRAGMA tuning. No memory growth, no event-loop stalls, no OOM. No memory limits set in compose file.

Recommendation for small instances

Even with this fix, 1 GB of RAM is tight when host OS tasks spike alongside Pangolin's working set. I'd recommend enabling swap on instances at or below 1 GB — I added 1 GB of swap and it has been more than enough; less is likely fine. Without swap, transient OS pressure has nowhere to go and can OOM the Node process even when Pangolin itself is steady.

How to test?

Build docker container community version using sqlite database.
1GB 2vCPU AWS t3a.micro instance.

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/fosrl/pangolin/pull/3267 **Author:** [@Josh-Voyles](https://github.com/Josh-Voyles) **Created:** 6/13/2026 **Status:** 🔄 Open **Base:** `dev` ← **Head:** `memfix1-1.18.4` --- ### 📝 Commits (2) - [`d94af2b`](https://github.com/fosrl/pangolin/commit/d94af2b8ea81d8311ee53c946c76bffd65108c5f) fix(sqlite): remove cache_size and mmap_size PRAGMAs (#2120) - [`522ca67`](https://github.com/fosrl/pangolin/commit/522ca671b5a8025e124674742bbea2c75ac13dd6) fix: remove no-op autoFinalizeStatement wrapper and redundant busy_timeout (#2120) ### 📊 Changes **1 file changed** (+12 additions, -50 deletions) <details> <summary>View changed files</summary> 📝 `server/db/sqlite/driver.ts` (+12 -50) </details> ### 📄 Description ## Community Contribution License Agreement By creating this pull request, I grant the project maintainers an unlimited, perpetual license to use, modify, and redistribute these contributions under any terms they choose, including both the AGPLv3 and the Fossorial Commercial license terms. I represent that I have the right to grant this license for all contributed content. ## Description ### Preface I'm a cloud engineer with experience in Python and Bash; I do not have much experience with JS/TS. This is a follow-up to my previous #2120 fix. After 3 days of A/B testing in production on a t3a.micro with Claude, the previous fix turned out to be net-negative on small instances. This PR reverts those specific changes after empirical validation. No code or comments are left over from the previous attempt. ### Summary - Removes the `autoFinalizeStatement` `prepare()` wrapper. better-sqlite3 11.x exposes **no** `Statement.finalize()` — the wrapper threw a `TypeError` on every query (silently swallowed by its own `try`/`catch`) and freed nothing, while adding **+122% per-statement overhead**. - Removes the explicit `busy_timeout = 5000` PRAGMA. better-sqlite3 already arms `sqlite3_busy_timeout(db, 5000)` via its default `timeout` constructor option, so the pragma is redundant. - Removes `cache_size = -65536` (64 MB) and `mmap_size = 268435456` (256 MB) PRAGMAs. On small (~1 GB) instances they inflate RSS and cause page-cache thrashing — the exact shape that hits #2120. - Keeps the env-gated WAL block (`journal_mode = WAL` + `synchronous = NORMAL` when `ENABLE_SQLITE_WAL_MODE=true`). `journal_mode` is sticky in the DB file, so removing the block would strand databases that opted in. ### Context The previous fix assumed unbounded native `sqlite3_stmt` accumulation was the cause of #2120 and added a finalize-on-execute wrapper plus aggressive PRAGMA tuning. Re-investigation showed: 1. `Statement.finalize()` does not exist on better-sqlite3 11.x. Verified by logging `Statement.finalize exists: undefined` from the runner image — the wrapper's call landed in its `catch` block on every execution. 2. drizzle-orm prepares a fresh native statement per query (no statement cache), and better-sqlite3 frees `sqlite3_stmt` in the Statement destructor at GC, so statements cannot accumulate. 3. The wrapper added **+122% per-statement overhead** (3.90 → 8.66 µs/op, 200k-op in-container microbench) with no observable benefit. 4. The 64 MB page cache + 256 MB mmap region pushed RSS into territory that caused page-cache pressure on 1 GB instances, making the original symptom worse, not better. With `ENABLE_SQLITE_WAL_MODE` unset, the driver is now runtime-identical to pre-1.18.3. ### Changes - `server/db/sqlite/driver.ts` — remove `autoFinalizeStatement` wrapper, remove `busy_timeout`/`cache_size`/`mmap_size` PRAGMAs, keep env-gated WAL block. ### Verification Ran the wrapper-OFF build for 3 days on a t3a.micro (1 GB / 2 vCPU) with the same traffic profile as the prior wrapper-ON deployment (normal usage, 7 day request retention, Uptime Kuma checks). Container memory stable around ~335 MB with no PRAGMA tuning. No memory growth, no event-loop stalls, no OOM. No memory limits set in compose file. ### Recommendation for small instances Even with this fix, 1 GB of RAM is tight when host OS tasks spike alongside Pangolin's working set. I'd recommend enabling swap on instances at or below 1 GB — I added 1 GB of swap and it has been more than enough; less is likely fine. Without swap, transient OS pressure has nowhere to go and can OOM the Node process even when Pangolin itself is steady. ### How to test? Build docker container community version using sqlite database. 1GB 2vCPU AWS t3a.micro instance. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

GiteaMirror added the pull-request label 2026-06-18 22:11:01 -05:00

Sign in to join this conversation.

Branches Tags

main

crowdin_dev

dev

dependabot/npm_and_yarn/npm-dependencies-338850a190

dependabot/go_modules/install/go-install-dependencies-3804ca7238

dependabot/docker/docker-dependencies-4faa477378

dependabot/github_actions/github-actions-dependencies-6d79802a48

api-improvements

feat/remember-last-idp-on-smart-login-form

refactor/batch-status-requests

dependabot/npm_and_yarn/multi-5f1280885e

fix/non-semver-version-error

private-resource-page

resource-launcher

backhaul

exit-node-reconnect

feat/command-palette

ssh

delete-account

msg-delivery

org-only-idp

cicd

patch

site-targets-auto-login

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/pangolin#36921