René's URL Explorer Experiment


Title: improvement(tables): bump rows version once at commit and serialize same-value unique writes by waleedlatif1 · Pull Request #8335 · simstudioai/sim · GitHub

Open Graph Title: improvement(tables): bump rows version once at commit and serialize same-value unique writes by waleedlatif1 · Pull Request #8335 · simstudioai/sim

X Title: improvement(tables): bump rows version once at commit and serialize same-value unique writes by waleedlatif1 · Pull Request #8335 · simstudioai/sim

Description: Summary bump_user_table_rows_version ran as a statement-level trigger inside every row-update statement, so each writer locked its table's user_table_definitions row across the rest of its transaction (executions patch, provenance write and capture, synchronous commit), and concurrent writers to one table queued behind each other. Provenance-only updates bumped it too, even though they don't change the table's content The UPDATE bump is now a row-level DEFERRABLE INITIALLY DEFERRED constraint trigger AFTER UPDATE OF data, order_key, with a WHEN on real changes. It bumps once per table per transaction at commit, deduped through one transaction-local setting. INSERT/DELETE statement bumps are unchanged. The only readers (the CSV snapshot cache and mount safety) read outside write transactions, so a commit-time bump keeps their read, scan, re-read checks intact user_table_rows.table_id → user_table_definitions FK becomes DEFERRABLE INITIALLY DEFERRED (catalog-only). The write path updates a row twice in one transaction (data, then the provenance marker, which the rolling-deploy demote trigger requires to be separate), and the second update re-checked the FK, taking a key-share lock on the definition row that the commit-time bump then waited behind. ON DELETE CASCADE stays immediate, and nothing catches this FK's error inline The trigger swap runs inside an explicit BEGIN, so it stays atomic when an earlier migration in the same deploy used a COMMIT breakpoint CREATE STATISTICS (dependencies) ON workspace_id, table_id plus ANALYZE, so the planner stops underestimating per-table row counts and serves data @> filters from the tenant GIN Unique columns are an application-level check, so two concurrent writes of the same value could both pass it and both commit. Every write path now takes transaction-scoped advisory locks (tag user_table_unique_value) before its check: the table's unique lock shared, then an exclusive lock per value, keyed on the same normalization the check uses (so '8' and 8 share a key). Writers of different values, on any column, never wait on each other Each transaction takes all its locks at once, in one sorted order. The global order is schema lock, then unique locks, then row-order lock, then definition row A json object or array value, or a transaction that would take more than 64 value locks, takes the table's unique lock exclusively instead. Whole-table replace and every import batch always do. A transaction holds at most 1 + 64 unique locks, however many unique columns the table has. 64 is the default per-connection budget of Postgres's shared lock table, so wide schemas and large batches can't exhaust it and fail unrelated queries The unique check for updateRow, batchUpdateRows, and single-row filter updates now runs inside the write transaction, under the locks. batchUpdateRows also rejects two updates in one batch that set the same value, and it locks and checks only the unique columns each update changes Tables without unique columns take no extra round trips Type of Change Improvement Testing A/B against staging on identical fresh DBs, real service functions, runs alternated: 20 concurrent updateRow writers on one table: 943–1071 → 1585–1606 ops/s, p99 77–107 → ~18 ms, lock wait 521–525 → 4–8 backend-s 50 writers: 886–937 → 1398–1450 ops/s, p99 274–292 → 124–159 ms Provenance-only writes: 5185–5482 → 7979–8713 ops/s, p99 ~15 → ~4 ms Inserts with a unique column and executions-only writes: within noise Large single-transaction rewrites are ~20% faster, with ~0.6s of commit-time trigger work at 1M rows Filter queries on 4,000 tables with skewed sizes: large-table data @> filters 1.7–5.7 → 0.5 ms Migration-during-writes: 0 missed bumps across 3 runs (without the BEGIN, 40/40 missed) row-writes.integration.ts: data and reorder edits bump once per transaction, including two tables in one transaction; provenance-only, timestamp-only, no-op, and executions-only writes don't bump; a second writer commits while the first is uncommitted; the snapshot re-keys when a writer commits mid-materialization. These fail on the old trigger Fresh DB migrate plus replay, secret-provenance and table integration suites, table unit suites, type-check, lint, check:audits, and check:migrations origin/staging pass Unique races (deterministic, driven by pg_locks wait states), all ending with one row: same-value single inserts '8' vs 8 batch vs single insert updateRow vs insert import batch vs insert a batch over the value-lock cap vs a per-value writer a replace adding the first unique column vs a writer on the older schema (no deadlock) a 100-unique-column table holding a single lock two updates in one batch setting the same value Different values don't wait on each other. Each race test fails on the pre-fix code Unique-column throughput vs staging is within noise: distinct-value inserts, batch inserts, and batch upserts at 10 and 50 writers. updateRow keeps the gains above Migration is 0390 on top of staging Checklist Code follows project style guidelines Self-reviewed my changes Tests added/updated and passing 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 bump_user_table_rows_version ran as a statement-level trigger inside every row-update statement, so each writer locked its table's user_table_definitions row across the rest of its tra...

X Description: Summary bump_user_table_rows_version ran as a statement-level trigger inside every row-update statement, so each writer locked its table's user_table_definitions row across the rest of its...

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

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:064f9d91-8374-23a1-a327-4518f0d04b23
current-catalog-service-hashae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b
request-idEC9E:9464C:D82ED1:1276850:6AFB5A84
html-safe-noncec13ef7095480149562c438dc9debbd5074cf45f33a6d892ca6e8ad57ea55ddca
visitor-payloadeyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJFQzlFOjk0NjRDOkQ4MkVEMToxMjc2ODUwOjZBRkI1QTg0IiwidmlzaXRvcl9pZCI6IjMyMzc5NzY4MDcyOTU3ODExNiIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9
visitor-hmacd8c10c8aaff1ec98752d350310eeb0f90cb53e19ebb26d1a6c482519bada93ba
hovercard-subject-tagpull_request:4648718230
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/8335/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 bump_user_table_rows_version ran as a statement-level trigger inside every row-update statement, so each writer locked its table's user_table_definitions row across the rest of its tra...
og:site_nameGitHub
og:typeobject
hostnamegithub.com
expected-hostnamegithub.com
Noneea33d13c96d0ff66bd8fa906b193d2213536b78cb06b0c50a4632c58b1418f94
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
release59ef97164c52cf59bd10558ddec9c4f482763988
ui-targetfull
theme-color#1e2327
color-schemelight dark

Links:

Skip to contenthttps://github.com/simstudioai/sim/pull/8335/files#start-of-content
https://github.com/
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F8335%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%2F8335%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/8335/files
Reloadhttps://github.com/simstudioai/sim/pull/8335/files
Reloadhttps://github.com/simstudioai/sim/pull/8335/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
improvement/table-rows-version-at-commithttps://github.com/simstudioai/sim/tree/improvement/table-rows-version-at-commit
Conversation 71 https://github.com/simstudioai/sim/pull/8335
Commits 7 https://github.com/simstudioai/sim/pull/8335/commits
Checks 31 https://github.com/simstudioai/sim/pull/8335/checks
Files changed https://github.com/simstudioai/sim/pull/8335/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8335/files
improvement(tables): bump rows version once at commit and serialize same-value unique writes https://github.com/simstudioai/sim/pull/8335/files#top
Show all changes 7 commits https://github.com/simstudioai/sim/pull/8335/files
65895bf improvement(tables): bump a table's rows version once at commit waleedlatif1 Sep 27, 2026 https://github.com/simstudioai/sim/pull/8335/commits/65895bfafde4118da5a8c3ebfed9bfaa7c8be720
49d4904 fix(tables): serialize concurrent writes of the same unique value wit… waleedlatif1 Sep 28, 2026 https://github.com/simstudioai/sim/pull/8335/commits/49d490452f5868028c1594db6fe29958cb04d9a3
16ee1d8 fix(tables): take the batched advisory locks on a transaction waleedlatif1 Sep 28, 2026 https://github.com/simstudioai/sim/pull/8335/commits/16ee1d85537836e33bdfc857da84b47bd8af0c77
68bba64 fix(tables): cap value locks at the per-connection lock budget and lo… waleedlatif1 Sep 28, 2026 https://github.com/simstudioai/sim/pull/8335/commits/68bba6465868e1002cdb6940478d1848856602fa
97cb317 chore(tables): share one unique-value key between the check and the l… waleedlatif1 Sep 28, 2026 https://github.com/simstudioai/sim/pull/8335/commits/97cb317952f5e0f8d62300e6ddf8d1450063a213
fbc3db1 fix(tables): lock a table's unique values under one table key so wide… waleedlatif1 Sep 28, 2026 https://github.com/simstudioai/sim/pull/8335/commits/fbc3db126bd74686197545d0b4ff734e77ae2dc5
1c00cf5 fix(tables): key unique JSON values by canonical form so key order ne… waleedlatif1 Sep 28, 2026 https://github.com/simstudioai/sim/pull/8335/commits/1c00cf55fbb0cccaae87adb9bcd6eb26dc14b2bb
Clear filters https://github.com/simstudioai/sim/pull/8335/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8335/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8335/files
advisory-locks.ts https://github.com/simstudioai/sim/pull/8335/files#diff-481317cc7041bc61369f69747cc0046bd77765aa07dd85ca4a60e9c9296ee382
import-data.ts https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
ordering.ts https://github.com/simstudioai/sim/pull/8335/files#diff-ae68362b6a8201eef72124d4fa40ebc0332664f66d7487e04d435623e408658f
row-writes.integration.ts https://github.com/simstudioai/sim/pull/8335/files#diff-653e2260e6bc1166f12f87c1cd10329f4ae89e569dc2f54ade8cf6b57c027468
service.ts https://github.com/simstudioai/sim/pull/8335/files#diff-bb7e45c4b7c8d660db1d230975eb35231ae2616360572a342fd56d37296bb23f
unique-locks.ts https://github.com/simstudioai/sim/pull/8335/files#diff-7789d73b180c071e0909c0066680f335dcd29d2d1b4b95b6de904f20a813c135
snapshot-cache.ts https://github.com/simstudioai/sim/pull/8335/files#diff-289ccd68dde4b22893a4c19810d33a9fddda96f6402a7a15dbe74865414d2d9b
update-row.test.ts https://github.com/simstudioai/sim/pull/8335/files#diff-7ec760a0597423be488477c6f96b641c36f1a13d1d9b74a46f68b6458fc979e6
validation.ts https://github.com/simstudioai/sim/pull/8335/files#diff-4afe0c5257b85a0e6c183bc2a7cd3db262dc445d8f341964364c751f6aa34f04
0390_table_rows_version_at_commit.sql https://github.com/simstudioai/sim/pull/8335/files#diff-f396ba2aaf602ae86059acf5c7ae0e74984cfa72fbfab97b942dd2ad5351700f
0390_snapshot.json https://github.com/simstudioai/sim/pull/8335/files#diff-172ddfa3ce91150f364e2e1e7c7539e063b941742867bbe53ef15a0330755b0d
_journal.json https://github.com/simstudioai/sim/pull/8335/files#diff-37150cc65ad16ee6fbc5fa318fc096be7730e16d63eaf863e3df5521b7030649
schema.ts https://github.com/simstudioai/sim/pull/8335/files#diff-9a959e2ce3cb4c86957bbae021d06eae364f7b0cb18964f5873bab53220af928
apps/sim/lib/db/advisory-locks.tshttps://github.com/simstudioai/sim/pull/8335/files#diff-481317cc7041bc61369f69747cc0046bd77765aa07dd85ca4a60e9c9296ee382
View file https://github.com/simstudioai/sim/blob/1c00cf55fbb0cccaae87adb9bcd6eb26dc14b2bb/apps/sim/lib/db/advisory-locks.ts
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/simstudioai/sim/pull/8335/{{ revealButtonHref }}
https://github.com/simstudioai/sim/pull/8335/files#diff-481317cc7041bc61369f69747cc0046bd77765aa07dd85ca4a60e9c9296ee382
apps/sim/lib/table/import-data.tshttps://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
View file https://github.com/simstudioai/sim/blob/1c00cf55fbb0cccaae87adb9bcd6eb26dc14b2bb/apps/sim/lib/table/import-data.ts
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/simstudioai/sim/pull/8335/{{ revealButtonHref }}
https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
https://github.com/simstudioai/sim/pull/8335/files#diff-bd07d4e05a01d98126156b48ff15cfbaa9e6cb5cf636878cf4f78225a57fd2fa
apps/sim/lib/table/rows/ordering.tshttps://github.com/simstudioai/sim/pull/8335/files#diff-ae68362b6a8201eef72124d4fa40ebc0332664f66d7487e04d435623e408658f
View file https://github.com/simstudioai/sim/blob/1c00cf55fbb0cccaae87adb9bcd6eb26dc14b2bb/apps/sim/lib/table/rows/ordering.ts
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/simstudioai/sim/pull/8335/{{ revealButtonHref }}
https://github.com/simstudioai/sim/pull/8335/files#diff-ae68362b6a8201eef72124d4fa40ebc0332664f66d7487e04d435623e408658f
https://github.com/simstudioai/sim/pull/8335/files#diff-ae68362b6a8201eef72124d4fa40ebc0332664f66d7487e04d435623e408658f
https://github.com/simstudioai/sim/pull/8335/files#diff-ae68362b6a8201eef72124d4fa40ebc0332664f66d7487e04d435623e408658f
Please reload this pagehttps://github.com/simstudioai/sim/pull/8335/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8335/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.