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
Domain: github.com
| react-profiling | 0 |
| react-import-map | react,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-controller | pull_requests |
| route-action | files |
| fetch-nonce | v2:91f9a1cd-1f98-c0d0-89e3-bcaeb2d50c02 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | DDB0:AE1F3:106BD4:174325:6AFB4BF4 |
| html-safe-nonce | b04d843770a069752e2c54c926156341bb2119e97eddbc48efbeedfd33472a93 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJEREIwOkFFMUYzOjEwNkJENDoxNzQzMjU6NkFGQjRCRjQiLCJ2aXNpdG9yX2lkIjoiNDczMzUxMzg2NzA4NDkwOTU1NiIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9 |
| visitor-hmac | 8161c1bcb36d8d6310720bfdda1ade46a7bd204126177e73732846b92a5a53bd |
| hovercard-subject-tag | pull_request:4835121688 |
| 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 | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/simstudioai/sim/pull/8943/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 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_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | acf7616d06cc051644705b379878c9bb13dc5e70ecf4ffd9b34140f8ac01d7e1 |
| 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 | 26504db649b58525278f77a52b80ba5f10d5f4de |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
Viewport: width=device-width