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.

[rajeswari1301]

I went through Michigan and Illinois in detail. Confirmed the size difference between forms comes down to step count, and was about 11–25 KB per saved step, consistently, across every interview checked.

About 80-83% of that stored session data belongs to sessions nobody's touched in two weeks or more, on both Michigan and Illinois separately. One thing specific to Illinois is, there's a text-to-speech feature (speaklist) generating about 3.3 GB of data, growing almost in lockstep with the session data itself, and it's stale in the same way. Also checked the tempuser finding from earlier in this thread on both - 439K (MLH) and 2.25M (ILAO) rows aren't referenced anywhere.

And generated files follow the same pattern - sessions are averaging around 70 uploaded/generated files each, and about 80% of those also belong to old, untouched sessions.

Still open: every real interview costs about 9–10 KB the moment someone lands on question one, before answering anything.