18 Commits
Author SHA1 Message Date
Tom Moor 72a72dc735 fix: Popularity score update blocks inserts that reference documents (#14558)
* fix: Use FOR NO KEY UPDATE when locking documents for popularity scores

* test: Cover popularity update with a key share lock held
2026-11-12 18:55:35 -05:00
Tom MoorandClaude Fable 5 0c2374dec1 perf: Index-friendly popularity score scan, spread runs over an hour (#13306)
* perf: Index-friendly candidate scan in popularity score task

The chunked scan that populates the working table combined "has a non-zero
score" and "has recent insights" into a single OR, which forced a scan of
every document in the partition with a correlated subquery per row. In
production this reached a p95 of 16.9s per chunk on the read replica, which
also serves user reads.

Query each source separately so both can use an index, then union. Limiting
each branch and then the union still yields the globally lowest ids, so keyset
pagination is unchanged. Validity is now a join rather than a predicate, with
the cursor tracked over candidates so filtered rows can't end the walk early.

Adds a partial index on documents(id) where the score is non-zero; the full
index on popularityScore was dropped as unused in 20251217161051 and would not
help this access pattern.

* perf: Drop per-batch COUNT(*) from popularity score task

Every batch issued a COUNT(*) over the working table purely to decide whether
to continue, doubling the round-trips for a loop that already runs thousands of
iterations. processBatch already reports how many rows it handled, so a short
batch is sufficient to detect a drained table.

setupWorkingTable now returns the row count it inserted, which removes the
remaining call site.

* perf: Spread popularity scoring across teams over six hours

The task partitioned by document id over a 30 minute window, which the cron
route expands to one partition per minute. Thirty partitions each took minutes
to run, so they piled up and saturated the database for the whole window.

Partition by team over six hours instead: 360 slices, one starting each minute,
each covering a fraction of the team space. Peak concurrency is bounded by how
long a slice takes rather than by the number of partitions.

Spreading past the hour breaks the interval gate, which read the wall clock at
execution time and so would skip every partition that ran in a later hour --
and worse, would let the next hour's cron schedule a second full run. Cron now
records when a run was scheduled and the gate reads that instead.

The partial index added for the score walk leads with teamId to match the new
access pattern; it is not deployed yet, so it is amended in place.

* perf: Re-key popularity partitions to document id, reduce spread to one hour

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

* fix: Compute scheduledAt once per cron run

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

---------

Co-authored-by: Claude Fable 5 <noreply@anthropic.com>
2026-08-05 07:12:11 -04:00
Tom Moor 66eee9007f chore: Upgrade to Typescript 6 (#12737)
* chore: Upgrade TypeScript to 6.0
2026-06-18 17:34:05 -04:00
Tom MoorandClaude Opus 4.7 56c3267186 Update popularity scoring to use document_insights table as data source (#12103)
* Update popularity scoring to use document_insights as data source

* Use UTC dates and guard against future-dated insights

Derive threshold/today as UTC day boundaries to match how document_insights.date is written, and add an upper bound to prevent future-dated rollups from collapsing the decay denominator.

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

---------

Co-authored-by: Claude Opus 4.7 <noreply@anthropic.com>
2026-05-08 23:22:00 -04:00
Tom Moor 62cfd4e9bc chore: Cleanup working tables left in db if midrun abort (#11786) 2026-03-17 08:05:32 -04:00
Copilotandtommoor 89cfc76d8f Remove artificial statement timeout from popularity score calculation (#11652)
* Initial plan

* fix: remove transaction wrapper and artificial statement timeout from calculateScoresForDocuments

The method was wrapping a read-only CTE query in a transaction solely to
SET LOCAL statement_timeout = '30000ms'. On instances with large tables this
30-second cap caused SequelizeDatabaseError: canceling statement due to
statement timeout. Removed the unnecessary transaction and the STATEMENT_TIMEOUT_MS
constant so the query runs unimpeded under the database server's own timeout policy.

Co-authored-by: tommoor <380914+tommoor@users.noreply.github.com>

---------

Co-authored-by: copilot-swe-agent[bot] <198982749+Copilot@users.noreply.github.com>
Co-authored-by: tommoor <380914+tommoor@users.noreply.github.com>
2026-03-05 08:11:48 -05:00
Tom MoorandCopilot c853917502 perf: More popularity scoring performance improvements (#11305)
* perf: More popularity scoring performance improvements

* Update server/queues/tasks/UpdateDocumentsPopularityScoreTask.ts

Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>

---------

Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>
2026-01-29 22:25:18 -05:00
Tom Moor dbfe9eb0e4 perf: More tweaks to popularity scoring job (#11293) 2026-01-27 20:06:34 -05:00
Tom Moor 00fb4d1af7 chore: Update node style imports (#11277)
- crypto → node:crypto
  - fs → node:fs
  - fs/promises → node:fs/promises
  - path → node:path
  - http → node:http
  - https → node:https
  - stream → node:stream
  - buffer → node:buffer
  - url → node:url
  - os → node:os
  - net → node:net
  - dns → node:dns
  - events → node:events
  - readline → node:readline
  - querystring → node:querystring
  - util → node:util
2026-01-26 20:51:50 -05:00
Tom Moor 041c4217b2 perf: Update documents popularity score (#10986) 2025-12-22 08:10:27 -05:00
Tom Moor bf45e97641 chore: Enforce type import consistency (#10968)
* Update types

* fix circular dep

* type imports

* lint type imports and --fix
2025-12-19 23:07:02 -05:00
Tom Moor 8cb07889ce perf: Further break up popularity batch querying (#10862) 2025-12-11 18:58:18 -05:00
Tom Moor 42959d66db chore: Add cron task partitioning (#10736)
* wip

* Implementation complete

* tidying

* test

* Address feedback

* Remove duplicative retry logic from UpdateDocumentsPopularityScoreTask.
Now that we're split across many runs this is not neccessary

* Refactor to subclass, config to instance

* Refactor BaseTask to named export

* fix: Missing partition

* tsc

* Feedback
2025-11-27 16:57:52 +01:00
Tom Moor b6a8986235 chore: Update UpdateDocumentsPopularityScoreTask to run 4 times per day (#10729)
* chore: Update UpdateDocumentsPopularityScoreTask to run 4 times per day

* chore: Add index to popularityScore column
2025-11-26 01:39:16 +01:00
Tom Moor ac820e4e2a fix: Speed up popularity score calculation further (#10728)
* fix: Speed up popularity score calculation further

* Add READ_ONLY database connection

* UNNEST performs better

* Move config to env
2025-11-26 01:06:24 +01:00
Tom Moor 9dfdf9a1ec fix: Refactor to decrease lock contention (#10727) 2025-11-25 23:32:55 +01:00
Tom Moor 8b2214fa5e fix: Popularity score should be calculated hourly, not daily (#10723) 2025-11-25 12:36:59 +00:00
Tom MoorandCopilot b51692cdc5 feat: Add popularity scoring (#10721)
* Simple first pass

* Use findAllInBatches

* Add comments,views,revisions

* Add 'popular' tab to Home

* Apply suggestions from code review

Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>

* Add 'Popular' tab to collections

* Boost search results based on popularityScore

* Move to unlogged temp table

---------

Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>
2025-11-25 13:11:05 +01:00