René's URL Explorer Experiment


Title: improvement(db): disable embedding FTS fastupdate and index delete-action foreign keys by waleedlatif1 · Pull Request #8943 · simstudioai/sim · GitHub

Open Graph Title: improvement(db): disable embedding FTS fastupdate and index delete-action foreign keys by waleedlatif1 · Pull Request #8943 · simstudioai/sim

X Title: improvement(db): disable embedding FTS fastupdate and index delete-action foreign keys by waleedlatif1 · Pull Request #8943 · simstudioai/sim

Description: Summary emb_content_fts_idx: SET (fastupdate = off), then a bounded gin_clean_pending_list drain. This is the 0364 / #7995 pattern. With fastupdate on, the insert that pushes the shared pending list past gin_pending_list_limit merges the whole list into the index in the foreground (ginInsertCleanup with work_mem). It does this while holding the document FOR UPDATE row lock. A 2-row chunk insert took 8.6s and the slowest insert this week took 53s, with no lock waits logged for those statements. Add concurrent indexes for the referential actions on user and workspace deletion. Without them, every deletion runs a sequential scan inside the deleting transaction: document.uploaded_by (SET NULL, partial IS NOT NULL): p50 24s per deletion execution_large_values.workspace_id (CASCADE): p50 21s. Its cleanup indexes lead with workspace_id but are partial on deleted_at, so workspace_id = $1 cannot use them. user_table_rows.created_by (SET NULL, partial IS NOT NULL): p50 10s user_table_definitions.created_by (CASCADE): about 100ms Other writers wait behind the deleting transaction's row locks. One upsert waited 14.6s. schema.ts mirrors all five changes. drizzle-kit generate produces nothing new after 0410. Side effects Insert latency: each chunk now inserts its lexemes (about 120 on average) straight into the index. This is a bounded cost per row instead of an occasional merge of every writer's pending entries, so the median goes up a little and the multi-second stalls go away. Slow large batches whose time scales with row count come from the synchronous search projection, not this index, and are out of scope here. WAL: with fastupdate on, every entry is written to WAL twice: once into the pending list and again when merged. Direct insertion writes it once. The one-time drain is the same as one routine flush. The index builds only scan the heap (read-only) and write their own small btree pages: two are partial on mostly-null columns, and the other two dedup on low-cardinality keys. Locks (PG17): ALTER INDEX ... SET (fastupdate) takes AccessExclusiveLock on the index only (checked in pg_locks; the table is not locked). That still blocks writes to embedding and planning of queries on it. It is bounded by the 5s lock_timeout plus the time-budgeted retry in migrate.ts. gin_clean_pending_list takes RowExclusiveLock and does not conflict with traffic. CREATE INDEX CONCURRENTLY takes ShareUpdateExclusiveLock and waits for older transactions to finish. Replay: everything after COMMIT; is idempotent. A second run is a no-op ALTER, an empty drain, and drop-and-recreate of only the new indexes (the 0407/0408 convention, which recovers an INVALID build). Compatibility: purely additive. The deployed app issues no queries that depend on these. Search semantics are unchanged, because fastupdate only changes when entries reach the main tree. HOT is unaffected, because the app never updates the newly indexed columns. Excluded: workspace_file_search_segment_workspace_content_trgm_idx. Nothing in the app inserts into that table anymore, so its write path has no tail to fix. Type of Change Improvement (performance) Testing Ran on a throwaway PG17 cluster: Applied the 0410 statements twice in a row; both runs succeeded. The first drain flushed pending pages and the second was a no-op. Confirmed the ALTER INDEX lock level. Used auto_explain with nested statements, including with plan_cache_mode = force_generic_plan, to confirm that all four RI action queries (... WHERE $1 = col) use the new indexes, the partial ones included. bunx drizzle-kit generate: no schema changes bun run check:migrations origin/staging: passes check:schema-mock and check:comment-hygiene: pass biome on schema.ts: clean Full lint, audits, type-check and tests are left to CI. Checklist Code follows project style guidelines Self-reviewed my changes Tests added/updated and passing (new tests pass the test-audit authoring gate) No new warnings introduced I confirm that I have read and agree to the terms outlined in the Contributor License Agreement (CLA)

Open Graph Description: Summary emb_content_fts_idx: SET (fastupdate = off), then a bounded gin_clean_pending_list drain. This is the 0364 / #7995 pattern. With fastupdate on, the insert that pushes the shared pending li...

X Description: Summary emb_content_fts_idx: SET (fastupdate = off), then a bounded gin_clean_pending_list drain. This is the 0364 / #7995 pattern. With fastupdate on, the insert that pushes the shared pending li...

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

X: @github

direct link

Domain: github.com

react-profiling0
react-import-mapreact,react-dom,react-dom/client,react-dom/profiling,react-is,react-reconciler,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@d5750093facf
route-pattern/:user_id/:repository/pull/:id/files(.:format)
route-controllerpull_requests
route-actionfiles
fetch-noncev2:91f9a1cd-1f98-c0d0-89e3-bcaeb2d50c02
current-catalog-service-hashae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b
request-idDDB0:AE1F3:106BD4:174325:6AFB4BF4
html-safe-nonceb04d843770a069752e2c54c926156341bb2119e97eddbc48efbeedfd33472a93
visitor-payloadeyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJEREIwOkFFMUYzOjEwNkJENDoxNzQzMjU6NkFGQjRCRjQiLCJ2aXNpdG9yX2lkIjoiNDczMzUxMzg2NzA4NDkwOTU1NiIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9
visitor-hmac8161c1bcb36d8d6310720bfdda1ade46a7bd204126177e73732846b92a5a53bd
hovercard-subject-tagpull_request:4835121688
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/8943/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 emb_content_fts_idx: SET (fastupdate = off), then a bounded gin_clean_pending_list drain. This is the 0364 / #7995 pattern. With fastupdate on, the insert that pushes the shared pending li...
og:site_nameGitHub
og:typeobject
hostnamegithub.com
expected-hostnamegithub.com
Noneacf7616d06cc051644705b379878c9bb13dc5e70ecf4ffd9b34140f8ac01d7e1
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
release26504db649b58525278f77a52b80ba5f10d5f4de
ui-targetfull
theme-color#1e2327
color-schemelight dark

Links:

Skip to contenthttps://github.com/simstudioai/sim/pull/8943/files#start-of-content
https://github.com/
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F8943%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%2F8943%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/8943/files
Reloadhttps://github.com/simstudioai/sim/pull/8943/files
Reloadhttps://github.com/simstudioai/sim/pull/8943/files
simstudioaihttps://github.com/simstudioai
simhttps://github.com/simstudioai/sim
Codehttps://github.com/simstudioai/sim
Issues129 (129)https://github.com/simstudioai/sim/issues
Pull requests197 (197)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
perf/gin-fastupdate-fk-indexeshttps://github.com/simstudioai/sim/tree/perf/gin-fastupdate-fk-indexes
Conversation 2 https://github.com/simstudioai/sim/pull/8943
Commits 1 https://github.com/simstudioai/sim/pull/8943/commits
Checks 47 https://github.com/simstudioai/sim/pull/8943/checks
Files changed https://github.com/simstudioai/sim/pull/8943/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8943/files
improvement(db): disable embedding FTS fastupdate and index delete-action foreign keys https://github.com/simstudioai/sim/pull/8943/files#top
Show all changes 1 commit https://github.com/simstudioai/sim/pull/8943/files
97558cd improvement(db): disable embedding FTS fastupdate and index delete-ac… waleedlatif1 Oct 12, 2026 https://github.com/simstudioai/sim/pull/8943/commits/97558cd76bcb0af8de7ba4d4afe725a746e050a9
Clear filters https://github.com/simstudioai/sim/pull/8943/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8943/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8943/files
0410_gin_fastupdate_fk_indexes.sql https://github.com/simstudioai/sim/pull/8943/files#diff-5afabf1ac5f642d762b19b4b9147ac4c7b825915c8a8238437676dfb35249178
0410_snapshot.json https://github.com/simstudioai/sim/pull/8943/files#diff-545ecbb801e52c82b400cd50504230494b2cef4e877259eaabc3dd7d96612f30
_journal.json https://github.com/simstudioai/sim/pull/8943/files#diff-37150cc65ad16ee6fbc5fa318fc096be7730e16d63eaf863e3df5521b7030649
schema.ts https://github.com/simstudioai/sim/pull/8943/files#diff-9a959e2ce3cb4c86957bbae021d06eae364f7b0cb18964f5873bab53220af928
packages/db/migrations/0410_gin_fastupdate_fk_indexes.sqlhttps://github.com/simstudioai/sim/pull/8943/files#diff-5afabf1ac5f642d762b19b4b9147ac4c7b825915c8a8238437676dfb35249178
View file https://github.com/simstudioai/sim/blob/97558cd76bcb0af8de7ba4d4afe725a746e050a9/packages/db/migrations/0410_gin_fastupdate_fk_indexes.sql
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/simstudioai/sim/pull/8943/{{ revealButtonHref }}
Please reload this pagehttps://github.com/simstudioai/sim/pull/8943/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8943/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.