René's URL Explorer Experiment


Title: fix(search): bound Search retirement pages so the migration finishes under its statement timeout by waleedlatif1 · Pull Request #8460 · simstudioai/sim · GitHub

Open Graph Title: fix(search): bound Search retirement pages so the migration finishes under its statement timeout by waleedlatif1 · Pull Request #8460 · simstudioai/sim

X Title: fix(search): bound Search retirement pages so the migration finishes under its statement timeout by waleedlatif1 · Pull Request #8460 · simstudioai/sim

Description: Summary The Search retirement script migration (0029_retire_all_search_embeddings, which runs the 0027 retirement code) failed during a deploy with canceling statement due to statement timeout. Each retirement page read 25,000 IDs and updated or deleted every target row among them in a single statement. The slow part is the writes, not the scan: Documents: retiring a document is a non-HOT update, because user_excluded appears in partial-index predicates, so every index on document gets a new entry. Chunks: deleting a chunk cascades into its vector, keyword and provenance projections. When one captured knowledge base holds most of a table, nearly every row in a page needs to be written. Those writes, plus checkpoint full-page writes and the synchronous-replication commit wait, pushed a page past the migration's own two-minute statement_timeout. The query plan is fine: the scan portion of a page is a primary-key range scan that takes a few seconds. Changes Row cap per page. Pages mutate at most a row limit of target rows and read at most four IDs per row of that limit (never more than 25,000), so re-reading after a capped page stays proportional to the work even when the limit shrinks. A capped page advances the cursor only to its last mutated row. Already-retired documents never count against the limit. Adaptive limit. Each page is timed from the start of its transaction through commit. The limit starts at 2,000 rows. It halves after a page slower than 30 s. It doubles, up to 8,000, after a page under 7.5 s. The floor is 25 rows. Pacing. After each page the migration pauses for as long as the page took, up to 5 s, to leave the primary headroom. Timeout handling. Only a timeout from the page's mutating statement triggers a retry with a smaller page. That page rolls back with its cursor. Any other statement timeout, such as a completion recheck, fails the run at once instead of shrinking the page several times for nothing. The completion rechecks, which walk every captured knowledge base once, get a 30-minute timeout, including the revalidation when a run resumes after retirement already completed. Logging. The periodic progress log now includes phase, cursor, rows mutated and row limit. The migration also logs slow-page halvings, phase changes and the start of the completion recheck. Docs. search-embedding-retirement.md is updated to match. The retirement stays idempotent and resumes from its saved cursor. 0029 is edited in place, following the earlier 0027 fixes: the runner records script migrations by name only, and deployments that already completed 0029 do not need to run it again. Tests Real-PostgreSQL integration tests in 0027_retire_search_embeddings.integration.ts: Split pages. Statement-level triggers raise the real statement-timeout error for any statement touching more than 300 rows. On the previous code the migration fails exactly as it did in the deploy. With this change it: completes; never commits a statement over the bound; never mutates a row twice; leaves ordinary rows untouched. Timeout outside the mutation. The completion checkpoint times out. The run fails after exactly one attempt; the previous code retried eight times. Already-retired documents. A run of retired documents longer than the row limit is crossed in a single page. Without the unretired filter the test fails, taking four pages instead of two. Scan window. With the row limit pinned at its floor, the rows read across all pages stay a small constant per document; a fixed 25,000-ID window re-reads the rest of the table on every attempt. Resumed completion. Revalidating a completed retirement on resume runs under the 30-minute recheck timeout rather than the two-minute page timeout. Rollback test. It now asserts a geometry-independent invariant: every committed delete is at or behind the cursor, and a failed page leaves every row past it. Exact counts no longer work because page boundaries now adapt. Unblocking the release The remaining cleanup, plus the index rebuild and vacuum that follow it, will likely take longer than the migrations job's five-hour limit. The retirement is resumable and safe to kill at any point: Every page commits together with its cursor. An interrupted page rolls back when its connection closes. An interrupted REINDEX CONCURRENTLY leaves invalid _ccnew/_ccold indexes, which the next run drops before retrying. Completed rebuild and vacuum steps are checkpointed. Nothing is recorded in script_migrations until retirement and maintenance both finish. Option A: run it out of band first (recommended, so it does not hold up deploys) Merge this PR to staging. From a checkout that contains this change, run the standalone entry point. It uses the same resolution as the deploy runner (MIGRATION_DATABASE_URL, falling back to DATABASE_URL): bun install --frozen-lockfile MIGRATION_DATABASE_URL='' \ bun --no-env-file run packages/db/script-migrations/0027_retire_search_embeddings.ts The DSN must be a direct or session-pooled connection. PgBouncer transaction pooling is not supported. --no-env-file stops Bun from loading a local .env into the process. The command resumes the saved cursor. It runs retirement and then maintenance through 0029_retire_all_search_embeddings, and on success records 0029 and the names it supersedes, exactly as the deploy runner would. It can be stopped and re-run as often as needed. While it runs, do not start the migrations workflow. The standalone command does not take the deploy runner's advisory lock, so a concurrent deploy would retire pages in parallel. That is correct, because pages serialize on the progress row, but it doubles the load, and the second maintenance worker fails on the maintenance lock. Stop the standalone run before any deploy, then resume it afterwards. Once 0029_retire_all_search_embeddings is recorded, cut the release to main. The migrations job skips the completed script migration. Option B: let the deploy run it Merge this PR to staging. Cut a new release to main that includes it. Re-running the failed migrations workflow run will not work, because it checks out the old commit. The migrations job resumes 0029 from its saved cursor. Pages completed before the failure stay committed and are not redone. If the job hits its five-hour limit, re-run the migrations workflow for the same release. Expect one or two re-runs. In both cases, keep writers to the captured Search knowledge bases stopped until 0029_retire_all_search_embeddings is recorded. Progress can be inspected with SELECT * FROM search_embedding_cleanup_progress, and the migration logs its phase, cursor and rows mutated every ten pages. Test plan 0027_retire_search_embeddings.integration.ts against PostgreSQL 17 with pgvector packages/db unit tests bun run type-check (packages/db) bun run lint bun run check:audits

Open Graph Description: Summary The Search retirement script migration (0029_retire_all_search_embeddings, which runs the 0027 retirement code) failed during a deploy with canceling statement due to statement timeout. Eac...

X Description: Summary The Search retirement script migration (0029_retire_all_search_embeddings, which runs the 0027 retirement code) failed during a deploy with canceling statement due to statement timeout. Eac...

Opengraph URL: https://github.com/simstudioai/sim/pull/8460

X: @github

direct link

Domain: github.com

react-profiling0
react-import-mapreact,react-dom,react-dom/client,react-dom/profiling,react-is,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@b8488ee2c322
route-pattern/:user_id/:repository/pull/:id/files(.:format)
route-controllerpull_requests
route-actionfiles
fetch-noncev2:d022bcb2-872a-85ba-6d47-1689b9cccbfe
current-catalog-service-hashae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b
request-idE19A:1743E0:3C6380:5292B2:6AFB998C
html-safe-nonce52be53d87f1c355caf34d81985e5c923160c53e2c8e8965fe121cb05044f04e8
visitor-payloadeyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJFMTlBOjE3NDNFMDozQzYzODA6NTI5MkIyOjZBRkI5OThDIiwidmlzaXRvcl9pZCI6IjQ3ODQyOTU3OTA4NjYzMDc0NjkiLCJyZWdpb25fZWRnZSI6ImlhZCIsInJlZ2lvbl9yZW5kZXIiOiJpYWQifQ==
visitor-hmac88aed4a87f52e7da8b6ba3bb3828346b3de3bb8ce66b6640641a758cfbedced9
hovercard-subject-tagpull_request:4689001957
github-keyboard-shortcutsrepository,pull-request-list,pull-request-conversation,pull-request-files-changed,copilot
google-site-verificationApib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I
octolytics-urlhttps://collector.github.com/github/collect
analytics-location///pull_requests/show/files
fb:app_id1401488693436528
apple-itunes-appapp-id=1477376905, app-argument=https://github.com/simstudioai/sim/pull/8460/files
twitter:imagehttps://avatars.githubusercontent.com/u/40672544?s=400&v=4
twitter:cardsummary_large_image
og:imagehttps://avatars.githubusercontent.com/u/40672544?s=400&v=4
og:image:altSummary The Search retirement script migration (0029_retire_all_search_embeddings, which runs the 0027 retirement code) failed during a deploy with canceling statement due to statement timeout. Eac...
og:site_nameGitHub
og:typeobject
hostnamegithub.com
expected-hostnamegithub.com
None3cefd19a936819d82cfb5279d50fb56fe79cb9ec30f0f807e04e89b0c58ffae1
turbo-cache-controlno-preview
diff-viewunified
go-importgithub.com/simstudioai/sim git https://github.com/simstudioai/sim.git
octolytics-dimension-user_id199344406
octolytics-dimension-user_loginsimstudioai
octolytics-dimension-repository_id912559512
octolytics-dimension-repository_nwosimstudioai/sim
octolytics-dimension-repository_publictrue
octolytics-dimension-repository_is_forkfalse
octolytics-dimension-repository_network_root_id912559512
octolytics-dimension-repository_network_root_nwosimstudioai/sim
turbo-body-classeslogged-out env-production page-responsive full-width
disable-turbotrue
browser-stats-urlhttps://api.github.com/_private/browser/stats
browser-errors-urlhttps://api.github.com/_private/browser/errors
release606800bdb1b81d125fc845bdcc262c9fe063fc8f
ui-targetcanary-2
theme-color#1e2327
color-schemelight dark

Links:

Skip to contenthttps://github.com/simstudioai/sim/pull/8460/files#start-of-content
https://github.com/
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F8460%2Ffiles
GitHubRepository hosting, code review, and collaborationhttps://github.com/platform
CopilotAI coding agent for tasks, projects, and automated workhttps://github.com/features/copilot
ActionsCI/CD and automationhttps://github.com/features/actions
Code QualityQuality analysis, fixes, and enforcementhttps://github.com/features/code-quality
CodespacesCloud development environmentshttps://github.com/features/codespaces
Code SecurityVulnerability detection and remediationhttps://github.com/security/advanced-security/code-security
Secret ProtectionSecret scanning and leak preventionhttps://github.com/security/advanced-security/secret-protection
All Featureshttps://github.com/features
Documentationhttps://docs.github.com/en
Traininghttps://learn.github.com/skills
Certificationshttps://learn.github.com/credentials
Build with Copilothttps://github.com/features/ai
Customize Copilothttps://docs.github.com/en/copilot/how-tos/copilot-on-github/customize-copilot/customize-copilot-overview
Integrationshttps://github.com/marketplace
Trending reposhttps://github.com/trending
GitHub Sponsorshttps://github.com/open-source/sponsors
Developer articleshttps://github.com/resources/articles
ChangelogYour source for the latest features, improvements, and fixes across GitHub products, all in one place.https://github.blog/changelog/
Enterpriseshttps://github.com/enterprise
Teamhttps://github.com/team
Startupshttps://github.com/enterprise/startups
Nonprofitshttps://github.com/solutions/industry/nonprofits
Healthcarehttps://github.com/solutions/industry/healthcare
Financial serviceshttps://github.com/solutions/industry/financial-services
Manufacturinghttps://github.com/solutions/industry/manufacturing
Governmenthttps://github.com/solutions/industry/government
See all industrieshttps://github.com/solutions/industry
Why GitHubhttps://github.com/why-github
Customer Storieshttps://github.com/customer-stories
Business insightshttps://github.com/solutions/executive-insights
Trust Centerhttps://github.com/trust-center
GitHub Advanced SecurityEnterprise-grade code securityhttps://github.com/security/advanced-security
Copilot for businessEnterprise-grade AI featureshttps://github.com/features/copilot/copilot-business
Premium SupportEnterprise-grade 24/7 supporthttps://github.com/enterprise/premium-support
Documentationhttps://docs.github.com/en
Resource centerhttps://github.com/resources
Bloghttps://github.blog/
Events & webinarshttps://github.com/resources/events
Community Forumhttps://github.com/orgs/community/discussions
Customer Storieshttps://github.com/customer-stories
Integrationshttps://github.com/marketplace
Customer supporthttps://help.github.com/
Traininghttps://learn.github.com/skills
GitHub Statushttps://www.githubstatus.com/
Abouthttps://github.com/about
Partnershttps://github.com/partners
Changeloghttps://github.blog/changelog/
GitHub UniverseThe event for developers, by developers — talks, workshops, and what’s next for GitHub.https://githubuniverse.com/
Pricinghttps://github.com/pricing
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F8460%2Ffiles
Sign uphttps://github.com/signup?ref_cta=Sign+up&ref_loc=header+logged+out&ref_page=%2F%3Cuser-name%3E%2F%3Crepo-name%3E%2Fpull_requests%2Fshow%2Ffiles&source=header-repo&source_repo=simstudioai%2Fsim
Reloadhttps://github.com/simstudioai/sim/pull/8460/files
Reloadhttps://github.com/simstudioai/sim/pull/8460/files
Reloadhttps://github.com/simstudioai/sim/pull/8460/files
simstudioaihttps://github.com/simstudioai
simhttps://github.com/simstudioai/sim
Codehttps://github.com/simstudioai/sim
Issues129 (129)https://github.com/simstudioai/sim/issues
Pull requests199 (199)https://github.com/simstudioai/sim/pulls
Actionshttps://github.com/simstudioai/sim/actions
Projectshttps://github.com/simstudioai/sim/projects
Security and qualityhttps://github.com/simstudioai/sim/security
Insightshttps://github.com/simstudioai/sim/pulse
Sign up for GitHub https://github.com/signup?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose
terms of servicehttps://docs.github.com/terms
privacy statementhttps://docs.github.com/privacy
Sign inhttps://github.com/login?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose
waleedlatif1https://github.com/waleedlatif1
staginghttps://github.com/simstudioai/sim/tree/staging
fix/retire-embeddings-migration-timeouthttps://github.com/simstudioai/sim/tree/fix/retire-embeddings-migration-timeout
Conversation 16 https://github.com/simstudioai/sim/pull/8460
Commits 5 https://github.com/simstudioai/sim/pull/8460/commits
Checks 31 https://github.com/simstudioai/sim/pull/8460/checks
Files changed https://github.com/simstudioai/sim/pull/8460/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8460/files
fix(search): bound Search retirement pages so the migration finishes under its statement timeout https://github.com/simstudioai/sim/pull/8460/files#top
Show all changes 5 commits https://github.com/simstudioai/sim/pull/8460/files
c3b2ba9 fix(search): bound retirement pages by mutated rows so they finish un… waleedlatif1 Sep 30, 2026 https://github.com/simstudioai/sim/pull/8460/commits/c3b2ba9900ed2468bf103bfedcda6a2024e06d59
a921532 fix(search): shrink only timed-out page mutations and pace Search ret… waleedlatif1 Sep 30, 2026 https://github.com/simstudioai/sim/pull/8460/commits/a921532a6f15f2e202e7c1fcf12443d143a65434
404e365 test(search): prove retired documents never spend the retirement row … waleedlatif1 Sep 30, 2026 https://github.com/simstudioai/sim/pull/8460/commits/404e365ca5c81448dc02df37deaecb164d090cdb
ee1d868 fix(search): scale the retirement scan window with the row limit and … waleedlatif1 Sep 30, 2026 https://github.com/simstudioai/sim/pull/8460/commits/ee1d868f292cd362d4b8f8a2bd0c45af6099d798
1e7d6fd fix(search): never grow a Search retirement page back to a size that … waleedlatif1 Sep 30, 2026 https://github.com/simstudioai/sim/pull/8460/commits/1e7d6fd546ec3fcb705ebd0b95761540499415de
Clear filters https://github.com/simstudioai/sim/pull/8460/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8460/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8460/files
0027_retire_search_embeddings.integration.ts https://github.com/simstudioai/sim/pull/8460/files#diff-804f8ab779efcc7c60d89becfb45d2a381b2c5a2f26df28673a0309aa318085e
0027_retire_search_embeddings.ts https://github.com/simstudioai/sim/pull/8460/files#diff-96715460a502d59053405cf39229bf0663576a2d865174d5cbdf25e3d6d01bdc
search-embedding-retirement.md https://github.com/simstudioai/sim/pull/8460/files#diff-35b3bda34ec67653fcd46b1b2f93525a0282f6bb7407a7c2533d97ae4a7cc689
packages/db/script-migrations/0027_retire_search_embeddings.integration.tshttps://github.com/simstudioai/sim/pull/8460/files#diff-804f8ab779efcc7c60d89becfb45d2a381b2c5a2f26df28673a0309aa318085e
View file https://github.com/simstudioai/sim/blob/1e7d6fd546ec3fcb705ebd0b95761540499415de/packages/db/script-migrations/0027_retire_search_embeddings.integration.ts
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/simstudioai/sim/pull/8460/{{ revealButtonHref }}
https://github.com/simstudioai/sim/pull/8460/files#diff-804f8ab779efcc7c60d89becfb45d2a381b2c5a2f26df28673a0309aa318085e
https://github.com/simstudioai/sim/pull/8460/files#diff-804f8ab779efcc7c60d89becfb45d2a381b2c5a2f26df28673a0309aa318085e
https://github.com/simstudioai/sim/pull/8460/files#diff-804f8ab779efcc7c60d89becfb45d2a381b2c5a2f26df28673a0309aa318085e
Please reload this pagehttps://github.com/simstudioai/sim/pull/8460/files
https://github.com/simstudioai/sim/pull/8460/files#diff-804f8ab779efcc7c60d89becfb45d2a381b2c5a2f26df28673a0309aa318085e
Please reload this pagehttps://github.com/simstudioai/sim/pull/8460/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8460/files
https://github.com
Termshttps://docs.github.com/site-policy/github-terms/github-terms-of-service
Privacyhttps://docs.github.com/site-policy/privacy-policies/github-privacy-statement
Securityhttps://github.com/security
Statushttps://www.githubstatus.com/
Communityhttps://github.community/
Docshttps://docs.github.com/
Contacthttps://support.github.com?tags=dotcom-footer

Viewport: width=device-width


URLs of crawlers that visited me.