Can we reduce the session size per-interview

[nonprofittechy]

Intuition: our interviews generate a lot of session data. Can we reduce the size somehow, by optimizing our largest in-session store datastructures?

[This is sparked by how big our database dumps are getting, which leads to trouble with migrating to hosted databases as well]

One I know is bad is the MACourts repo.

Any other designs to revisit?

Potentially look at more modularity - making more of the different interview pieces we offer optional. Note that it's not about separate YAMLs as much as smaller code blocks, so not everything runs every time.

Strategy: look at a random subset of real interview sessions at the database table layer to see what really is making the database size grow the most.

[nonprofittechy]

Also what really matters is how compressible the data is--duplication might not actually matter if the repeated rows are compressible (but maybe they're not because it's a memory dump storage format?)

[nonprofittechy]

Docassemble DB Growth Summary (March 2, 2026)

Scope

This summarizes observed growth patterns on interviews.lemmalegal.com and proposes cleanup strategies focused on reducing backup size.

Key Findings

1. userdict dominates growth.

• Table size: ~439 MB total.
• Snapshot payload (sum of octet_length(dictionary)): ~560 MB.
• Behavior: full interview-state snapshots are saved; growth is versioned full-state, not diffs.
• Concentration:
  • docassemble.playground47MODivorceForms:main.yml -> ~264 MB
  • docassemble.playground47MODivorceTESTONLY:main.yml -> ~171 MB

2. Version-history overhead is very large.

• Rows beyond latest version per (filename, key): ~494 MB payload.
• If capped to keep only 10 newest snapshots/session: estimated savings 410 MB payload (73% of snapshot payload).

3. Most snapshot payload is old/stale.

• Sessions with latest activity older than 14 days account for ~437 MB payload.

4. Playground interviews are the largest source.

• filename LIKE 'docassemble.playground%': ~468 MB payload.
• Non-playground total: ~92 MB.

5. Non-userdict finding with highest impact: tempuser.

• Table size: ~43 MB (20 MB heap + 23 MB indexes).
• Rows with no references in userdictkeys/uploadsuserauth/chatlog/globalobjectstorage: ~504,659.
• Cause: TempUser rows are created often for anonymous users, but there is no routine global GC pass for unreferenced rows.

6. Medium/low-priority non-userdict targets:

• jsonstorage: ~21 MB, mostly long-lived records (~60k rows older than 30 days).
• speaklist: ~13 MB, phrase payload ~9.2 MB.
• userdictkeys: ~14 MB, mostly valid references (not a high-impact cleanup target).

Growth Characterization

• Total growth is mostly linear in number of saved snapshots:
  • total ~= sum(snapshot_size_i).
• It appears superlinear for some interviews because snapshot size also grows over time (large nested structures + many saves).

Trimming Size of an Individual Interview

Per-interview growth is driven by two levers:

• versions_per_session (how often a full snapshot is appended)
• avg_row_kb (how large each serialized user_dict snapshot is)

Observed examples on this server:

• docassemble.playground47MODivorceForms:main.yml:
  • versions_per_session ~46.68
  • avg_row_kb ~123.4
  • total payload ~264.4 MB
• docassemble.playground47MODivorceTESTONLY:main.yml:
  • versions_per_session ~76.11
  • avg_row_kb ~127.4
  • total payload ~170.5 MB
• docassemble.welcome:data/questions/welcome.yml (control-like baseline):
  • versions_per_session ~1.00
  • avg_row_kb ~1.2
  • total payload ~25.9 MB despite many sessions

Why this happens in code (specific references)

• Snapshot writes are full-state:
  • save_user_dict() stores serialized dictionary in UserDict.dictionary:
    • docassemble_webapp/docassemble/webapp/server.py:2916
    • docassemble_webapp/docassemble/webapp/server.py:2942
    • docassemble_webapp/docassemble/webapp/server.py:2960
• Serialization uses pickle/base64 of the full interview dictionary (not diffs):
  • docassemble_webapp/docassemble/webapp/backend.py:794
  • docassemble_webapp/docassemble/webapp/backend.py:805
• API update route defaults to append behavior unless overwrite is requested:
  • post_setting = not overwrite:
    • docassemble_webapp/docassemble/webapp/server.py:20270
  • passed through as changed=post_setting:
    • docassemble_webapp/docassemble/webapp/server.py:20848

Interview-level optimization patterns (highest impact first)

1. Keep bulky transient structures out of persistent user_dict.

• Avoid storing large nested lists/dicts (e.g., big lookup tables, expanded row caches, document previews) in interview state.
• Recompute/load from source when needed, or store compact identifiers instead of full objects.

2. Reduce snapshot frequency where undo history is not required.

• For API-driven updates, prefer overwrite semantics when safe (so latest row is updated instead of appending).
• Reserve append/version behavior for true undo checkpoints.

3. Bound list/dict growth in-session.

• Cap repeating collections when only recent entries are needed.
• Store derived display-only structures in temporary/local variables, not persisted interview state.

4. Split very large workflows.

• Move heavy subflows into separate interviews/sessions when possible to prevent one giant long-lived dictionary.

5. Track per-interview footprint continuously.

• Monitor avg(octet_length(dictionary)) and versions_per_session by filename.
• Use thresholds/alerts for interviews crossing expected size or version depth.

Highest-Impact Cleanup Strategy (Backup-Focused)

1. Retain only recent undo history for stale sessions.

• Keep 10 newest userdict rows per (filename, key) if latest access > 14 days.

2. Add periodic tempuser garbage collection.

• Delete tempuser rows with no references anywhere.

3. Prune or reset heavy playground* sessions.

• Especially old testing sessions.

4. Add policy cleanup for jsonstorage and speaklist.

• Age-based retention (e.g., 90/180 days) for non-essential historical entries.

Proposed SQL Snippet (Dry Run + Delete)

-- 1) DRY RUN: estimate stale history rows to prune from userdict
WITH ranked AS (
  SELECT
    indexno,
    octet_length(dictionary)::bigint AS bytes,
    row_number() OVER (PARTITION BY filename, key ORDER BY indexno DESC) AS rn,
    max(modtime) OVER (PARTITION BY filename, key) AS last_mod
  FROM userdict
)
SELECT
  count(*) AS rows_to_delete,
  pg_size_pretty(sum(bytes)) AS payload_to_delete
FROM ranked
WHERE rn > 10
  AND last_mod < now() - interval '14 days';

-- 2) DELETE: keep newest 10 snapshots per stale session
WITH ranked AS (
  SELECT
    indexno,
    row_number() OVER (PARTITION BY filename, key ORDER BY indexno DESC) AS rn,
    max(modtime) OVER (PARTITION BY filename, key) AS last_mod
  FROM userdict
)
DELETE FROM userdict u
USING ranked r
WHERE u.indexno = r.indexno
  AND r.rn > 10
  AND r.last_mod < now() - interval '14 days';

-- 3) DRY RUN: count tempuser rows with no references anywhere
SELECT count(*) AS tempusers_with_no_refs_anywhere
FROM tempuser t
LEFT JOIN userdictkeys k ON k.temp_user_id = t.id
LEFT JOIN uploadsuserauth ua ON ua.temp_user_id = t.id
LEFT JOIN globalobjectstorage g ON g.temp_user_id = t.id
LEFT JOIN chatlog c1 ON c1.temp_user_id = t.id
LEFT JOIN chatlog c2 ON c2.temp_owner_id = t.id
WHERE k.indexno IS NULL
  AND ua.id IS NULL
  AND g.id IS NULL
  AND c1.id IS NULL
  AND c2.id IS NULL;

-- 4) DELETE: remove unreferenced tempuser rows
DELETE FROM tempuser t
WHERE NOT EXISTS (SELECT 1 FROM userdictkeys k WHERE k.temp_user_id = t.id)
  AND NOT EXISTS (SELECT 1 FROM uploadsuserauth ua WHERE ua.temp_user_id = t.id)
  AND NOT EXISTS (SELECT 1 FROM globalobjectstorage g WHERE g.temp_user_id = t.id)
  AND NOT EXISTS (SELECT 1 FROM chatlog c1 WHERE c1.temp_user_id = t.id)
  AND NOT EXISTS (SELECT 1 FROM chatlog c2 WHERE c2.temp_owner_id = t.id);

Operational Notes

• Run deletes in batches during low traffic to reduce lock/WAL spikes.
• Take backup/snapshot before first production cleanup run.
• For sustained control: schedule these jobs nightly/weekly and monitor deleted row counts.