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-profiling | 0 |
| react-import-map | react,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-controller | pull_requests |
| route-action | files |
| fetch-nonce | v2:d022bcb2-872a-85ba-6d47-1689b9cccbfe |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | E19A:1743E0:3C6380:5292B2:6AFB998C |
| html-safe-nonce | 52be53d87f1c355caf34d81985e5c923160c53e2c8e8965fe121cb05044f04e8 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJFMTlBOjE3NDNFMDozQzYzODA6NTI5MkIyOjZBRkI5OThDIiwidmlzaXRvcl9pZCI6IjQ3ODQyOTU3OTA4NjYzMDc0NjkiLCJyZWdpb25fZWRnZSI6ImlhZCIsInJlZ2lvbl9yZW5kZXIiOiJpYWQifQ== |
| visitor-hmac | 88aed4a87f52e7da8b6ba3bb3828346b3de3bb8ce66b6640641a758cfbedced9 |
| hovercard-subject-tag | pull_request:4689001957 |
| github-keyboard-shortcuts | repository,pull-request-list,pull-request-conversation,pull-request-files-changed,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | ///pull_requests/show/files |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/simstudioai/sim/pull/8460/files |
| twitter:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| twitter:card | summary_large_image |
| og:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| og:image:alt | 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... |
| og:site_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | 3cefd19a936819d82cfb5279d50fb56fe79cb9ec30f0f807e04e89b0c58ffae1 |
| turbo-cache-control | no-preview |
| diff-view | unified |
| go-import | github.com/simstudioai/sim git https://github.com/simstudioai/sim.git |
| octolytics-dimension-user_id | 199344406 |
| octolytics-dimension-user_login | simstudioai |
| octolytics-dimension-repository_id | 912559512 |
| octolytics-dimension-repository_nwo | simstudioai/sim |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 912559512 |
| octolytics-dimension-repository_network_root_nwo | simstudioai/sim |
| turbo-body-classes | logged-out env-production page-responsive full-width |
| disable-turbo | true |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | 606800bdb1b81d125fc845bdcc262c9fe063fc8f |
| ui-target | canary-2 |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
| Skip to content | https://github.com/simstudioai/sim/pull/8460/files#start-of-content |
| https://github.com/ |
| Sign in | https://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F8460%2Ffiles |
| GitHubRepository hosting, code review, and collaboration | https://github.com/platform |
| CopilotAI coding agent for tasks, projects, and automated work | https://github.com/features/copilot |
| ActionsCI/CD and automation | https://github.com/features/actions |
| Code QualityQuality analysis, fixes, and enforcement | https://github.com/features/code-quality |
| CodespacesCloud development environments | https://github.com/features/codespaces |
| Code SecurityVulnerability detection and remediation | https://github.com/security/advanced-security/code-security |
| Secret ProtectionSecret scanning and leak prevention | https://github.com/security/advanced-security/secret-protection |
| All Features | https://github.com/features |
| Documentation | https://docs.github.com/en |
| Training | https://learn.github.com/skills |
| Certifications | https://learn.github.com/credentials |
| Build with Copilot | https://github.com/features/ai |
| Customize Copilot | https://docs.github.com/en/copilot/how-tos/copilot-on-github/customize-copilot/customize-copilot-overview |
| Integrations | https://github.com/marketplace |
| Trending repos | https://github.com/trending |
| GitHub Sponsors | https://github.com/open-source/sponsors |
| Developer articles | https://github.com/resources/articles |
| ChangelogYour source for the latest features, improvements, and fixes across GitHub products, all in one place. | https://github.blog/changelog/ |
| Enterprises | https://github.com/enterprise |
| Team | https://github.com/team |
| Startups | https://github.com/enterprise/startups |
| Nonprofits | https://github.com/solutions/industry/nonprofits |
| Healthcare | https://github.com/solutions/industry/healthcare |
| Financial services | https://github.com/solutions/industry/financial-services |
| Manufacturing | https://github.com/solutions/industry/manufacturing |
| Government | https://github.com/solutions/industry/government |
| See all industries | https://github.com/solutions/industry |
| Why GitHub | https://github.com/why-github |
| Customer Stories | https://github.com/customer-stories |
| Business insights | https://github.com/solutions/executive-insights |
| Trust Center | https://github.com/trust-center |
| GitHub Advanced SecurityEnterprise-grade code security | https://github.com/security/advanced-security |
| Copilot for businessEnterprise-grade AI features | https://github.com/features/copilot/copilot-business |
| Premium SupportEnterprise-grade 24/7 support | https://github.com/enterprise/premium-support |
| Documentation | https://docs.github.com/en |
| Resource center | https://github.com/resources |
| Blog | https://github.blog/ |
| Events & webinars | https://github.com/resources/events |
| Community Forum | https://github.com/orgs/community/discussions |
| Customer Stories | https://github.com/customer-stories |
| Integrations | https://github.com/marketplace |
| Customer support | https://help.github.com/ |
| Training | https://learn.github.com/skills |
| GitHub Status | https://www.githubstatus.com/ |
| About | https://github.com/about |
| Partners | https://github.com/partners |
| Changelog | https://github.blog/changelog/ |
| GitHub UniverseThe event for developers, by developers — talks, workshops, and what’s next for GitHub. | https://githubuniverse.com/ |
| Pricing | https://github.com/pricing |
| Sign in | https://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F8460%2Ffiles |
| Sign up | https://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 |
| Reload | https://github.com/simstudioai/sim/pull/8460/files |
| Reload | https://github.com/simstudioai/sim/pull/8460/files |
| Reload | https://github.com/simstudioai/sim/pull/8460/files |
| simstudioai | https://github.com/simstudioai |
| sim | https://github.com/simstudioai/sim |
| Code | https://github.com/simstudioai/sim |
| Issues129 (129) | https://github.com/simstudioai/sim/issues |
| Pull requests199 (199) | https://github.com/simstudioai/sim/pulls |
| Actions | https://github.com/simstudioai/sim/actions |
| Projects | https://github.com/simstudioai/sim/projects |
| Security and quality | https://github.com/simstudioai/sim/security |
| Insights | https://github.com/simstudioai/sim/pulse |
| Sign up for GitHub
| https://github.com/signup?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose |
| terms of service | https://docs.github.com/terms |
| privacy statement | https://docs.github.com/privacy |
| Sign in | https://github.com/login?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose |
| waleedlatif1 | https://github.com/waleedlatif1 |
| staging | https://github.com/simstudioai/sim/tree/staging |
| fix/retire-embeddings-migration-timeout | https://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 page | https://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 page | https://github.com/simstudioai/sim/pull/8460/files |
| Please reload this page | https://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.ts | https://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 page | https://github.com/simstudioai/sim/pull/8460/files |
|
| https://github.com/simstudioai/sim/pull/8460/files#diff-804f8ab779efcc7c60d89becfb45d2a381b2c5a2f26df28673a0309aa318085e |
| Please reload this page | https://github.com/simstudioai/sim/pull/8460/files |
| Please reload this page | https://github.com/simstudioai/sim/pull/8460/files |
|
| https://github.com |
| Terms | https://docs.github.com/site-policy/github-terms/github-terms-of-service |
| Privacy | https://docs.github.com/site-policy/privacy-policies/github-privacy-statement |
| Security | https://github.com/security |
| Status | https://www.githubstatus.com/ |
| Community | https://github.community/ |
| Docs | https://docs.github.com/ |
| Contact | https://support.github.com?tags=dotcom-footer |
Viewport: width=device-width
URLs of crawlers that visited me.