René's URL Explorer Experiment


Title: perf(tables): keep the batch unique check on per-value GIN probes by waleedlatif1 · Pull Request #8978 · simstudioai/sim · GitHub

Open Graph Title: perf(tables): keep the batch unique check on per-value GIN probes by waleedlatif1 · Pull Request #8978 · simstudioai/sim

X Title: perf(tables): keep the batch unique check on per-value GIN probes by waleedlatif1 · Pull Request #8978 · simstudioai/sim

Description: Summary checkBatchUniqueConstraintsDb runs inside the batch insert and import write transactions, after the unique locks are taken. It built a single query, table_id = $1 AND (data @> $a OR data @> $b …) LIMIT n, with up to 1000 ORed values per column. Because of the LIMIT, the planner assumed it could stop early. So instead of probing the tenant GIN index (user_table_rows_tenant_data_gin_idx), it walked the table's rows through a btree on table_id and checked every value as a filter on each row. A slow check holds the unique-value and schema locks for its whole duration, so writers to the same table queue up behind it. This PR splits the values into UNION ALL branches. Each branch ORs at most 25 predicates and has no LIMIT. Each value now plans as its own GIN bitmap probe. Predicates are unchanged. Every value is still built by uniqueValuePredicate, including the composite object/array equality. Cast-compared (ttl) columns stay one pass. Their COALESCE(cast = …) predicate is a filter that no index serves, so splitting it into groups would mean one table walk per group. New equalityCast(column) in column-types/comparison-sql.ts. It decides whether a column compares by containment or by cast, and fieldPredicate, columnTextForEquality and the batch check now all use it. Unchanged: null and in-batch duplicate handling, error messages, the result shape, and lock order (lock, then check, then insert). Plan evidence Production, plain EXPLAIN with synthetic values against a large tenant: Query shape Plan 200 ORed values + LIMIT 200 (before) Limit -> Index Scan using user_table_rows_workspace_table_idx, with the 200 values as a Filter 200 ORed values, no LIMIT BitmapOr of 200 GIN probes, but ANDed with a GIN scan of the tenant's whole table_id entry 50 ORed values, no LIMIT Plain BitmapOr of GIN probes (planning 3.3 ms) UNION ALL of single-value branches One GIN bitmap probe per branch Local Postgres 17 replica of the table and its indexes, with a 20k-row tenant and 1000 values: Plan Execution time Before Btree walk 889 ms After (40 groups) 1000 GIN probes 34 ms (planning 11 ms) Failure modes covered A value already stored: the error names its one-based row position. A duplicate within the batch: reported once, against the first row. Composite values: an object whose stored row is a superset does not conflict; the same object with a different key order does conflict; an array with a different order does not conflict, while the same array does. Null and missing cells never conflict. Conflicts in the first, middle and last of 11 probe groups are all found (260 rows). ttl (valueForEquality): the same instant written with a different offset conflicts, and the check makes exactly one scan of the table rather than one per group. Plan: on a 40k-row tenant with 260 values, the transaction-local scan counters show exactly 260 GIN index scans, no other index scans and no sequential scans. On the pre-fix query this shows one btree walk instead (reproduced locally). Two concurrent batches above the value-lock cap that share a value still serialize: one succeeds and the value is stored once. Risks No LIMIT. The result is bounded by the rows that actually match. A unique column cannot hold duplicates, because enabling unique rejects them, so this matches the old row bound. A legacy duplicate would now be reported for every matching row rather than an arbitrary subset. Group size is a planner heuristic. 25 stays well under the size where a no-LIMIT OR started adding the whole-tenant table_id intersection (seen at 200, absent at 50). Planning cost. At most 40 branches per column, measured at about 11 ms for 1000 values. The plan test depends on cost estimates. It relies on the planner choosing GIN probes for a 40k-row tenant, which held across 10k–40k rows in local runs.

Open Graph Description: Summary checkBatchUniqueConstraintsDb runs inside the batch insert and import write transactions, after the unique locks are taken. It built a single query, table_id = $1 AND (data @> $a OR data...

X Description: Summary checkBatchUniqueConstraintsDb runs inside the batch insert and import write transactions, after the unique locks are taken. It built a single query, table_id = $1 AND (data @> $a OR ...

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

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:063bef71-9e1c-8419-c302-581366d40f47
current-catalog-service-hashae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b
request-id9E48:BD772:AF0965:EFAA9E:6AFB5154
html-safe-nonce41808c5353079eb8a1528becfb205f689ffc3d341e27ee3e198ebf1d28a8fccd
visitor-payloadeyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiI5RTQ4OkJENzcyOkFGMDk2NTpFRkFBOUU6NkFGQjUxNTQiLCJ2aXNpdG9yX2lkIjoiNDc5Mzk1ODI4OTQzNTA3Nzk2IiwicmVnaW9uX2VkZ2UiOiJpYWQiLCJyZWdpb25fcmVuZGVyIjoiaWFkIn0=
visitor-hmac2f2c1a4175deea244cece3c870f55c26d7365a0ad92152b84c99db4b6376a634
hovercard-subject-tagpull_request:4847750574
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/8978/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 checkBatchUniqueConstraintsDb runs inside the batch insert and import write transactions, after the unique locks are taken. It built a single query, table_id = $1 AND (data @> $a OR data...
og:site_nameGitHub
og:typeobject
hostnamegithub.com
expected-hostnamegithub.com
None7122bb414b542dba14d71a3f0b9f6fd2abce715b9f5912d082800563e6c806d1
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
releasecbf66c024bb6d10e6955baa7413c54cf13a201bb
ui-targetfull
theme-color#1e2327
color-schemelight dark

Links:

Skip to contenthttps://github.com/simstudioai/sim/pull/8978/files#start-of-content
https://github.com/
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F8978%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%2F8978%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/8978/files
Reloadhttps://github.com/simstudioai/sim/pull/8978/files
Reloadhttps://github.com/simstudioai/sim/pull/8978/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/table-unique-probehttps://github.com/simstudioai/sim/tree/perf/table-unique-probe
Conversation 17 https://github.com/simstudioai/sim/pull/8978
Commits 4 https://github.com/simstudioai/sim/pull/8978/commits
Checks 50 https://github.com/simstudioai/sim/pull/8978/checks
Files changed https://github.com/simstudioai/sim/pull/8978/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8978/files
perf(tables): keep the batch unique check on per-value GIN probes https://github.com/simstudioai/sim/pull/8978/files#top
Show all changes 4 commits https://github.com/simstudioai/sim/pull/8978/files
feaf714 perf(tables): keep the batch unique check on per-value GIN probes waleedlatif1 Oct 13, 2026 https://github.com/simstudioai/sim/pull/8978/commits/feaf7141fec107993d765bc8ba7793a47fdcdbbc
7474048 chore(tables): fold the unique-check test helpers into one waleedlatif1 Oct 13, 2026 https://github.com/simstudioai/sim/pull/8978/commits/747404886abfb2bb2f06fb6a3b8bf62ff0fa52c9
b21d12f fix(tables): coalesce a null equality cast to undefined waleedlatif1 Oct 13, 2026 https://github.com/simstudioai/sim/pull/8978/commits/b21d12fbd8c3a735348c977a18a561d63a515631
d619895 fix(tables): measure the unique-check scans by delta on a flushed GIN… waleedlatif1 Oct 13, 2026 https://github.com/simstudioai/sim/pull/8978/commits/d61989551fb0cd9e1c783a5d7508d364ecb7e79e
Clear filters https://github.com/simstudioai/sim/pull/8978/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8978/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8978/files
comparison-sql.ts https://github.com/simstudioai/sim/pull/8978/files#diff-5ceedf710fdee094a3e8a5f9db3e0efa6e25c1d0e36f3c5b6875637e023ea5e0
row-writes.integration.ts https://github.com/simstudioai/sim/pull/8978/files#diff-653e2260e6bc1166f12f87c1cd10329f4ae89e569dc2f54ade8cf6b57c027468
sql.ts https://github.com/simstudioai/sim/pull/8978/files#diff-6bdc8e3314777d023d081101b83c0458072aef721da07cf8b8881fd5d0fab5b3
validation.ts https://github.com/simstudioai/sim/pull/8978/files#diff-4afe0c5257b85a0e6c183bc2a7cd3db262dc445d8f341964364c751f6aa34f04
apps/sim/lib/table/column-types/comparison-sql.tshttps://github.com/simstudioai/sim/pull/8978/files#diff-5ceedf710fdee094a3e8a5f9db3e0efa6e25c1d0e36f3c5b6875637e023ea5e0
View file https://github.com/simstudioai/sim/blob/d61989551fb0cd9e1c783a5d7508d364ecb7e79e/apps/sim/lib/table/column-types/comparison-sql.ts
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/simstudioai/sim/pull/8978/{{ revealButtonHref }}
https://github.com/simstudioai/sim/pull/8978/files#diff-5ceedf710fdee094a3e8a5f9db3e0efa6e25c1d0e36f3c5b6875637e023ea5e0
apps/sim/lib/table/rows/row-writes.integration.tshttps://github.com/simstudioai/sim/pull/8978/files#diff-653e2260e6bc1166f12f87c1cd10329f4ae89e569dc2f54ade8cf6b57c027468
View file https://github.com/simstudioai/sim/blob/d61989551fb0cd9e1c783a5d7508d364ecb7e79e/apps/sim/lib/table/rows/row-writes.integration.ts
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/simstudioai/sim/pull/8978/{{ revealButtonHref }}
https://github.com/simstudioai/sim/pull/8978/files#diff-653e2260e6bc1166f12f87c1cd10329f4ae89e569dc2f54ade8cf6b57c027468
https://github.com/simstudioai/sim/pull/8978/files#diff-653e2260e6bc1166f12f87c1cd10329f4ae89e569dc2f54ade8cf6b57c027468
https://github.com/simstudioai/sim/pull/8978/files#diff-653e2260e6bc1166f12f87c1cd10329f4ae89e569dc2f54ade8cf6b57c027468
https://github.com/simstudioai/sim/pull/8978/files#diff-653e2260e6bc1166f12f87c1cd10329f4ae89e569dc2f54ade8cf6b57c027468
https://github.com/simstudioai/sim/pull/8978/files#diff-653e2260e6bc1166f12f87c1cd10329f4ae89e569dc2f54ade8cf6b57c027468
https://github.com/simstudioai/sim/pull/8978/files#diff-653e2260e6bc1166f12f87c1cd10329f4ae89e569dc2f54ade8cf6b57c027468
https://github.com/simstudioai/sim/pull/8978/files#diff-653e2260e6bc1166f12f87c1cd10329f4ae89e569dc2f54ade8cf6b57c027468
https://github.com/simstudioai/sim/pull/8978/files#diff-653e2260e6bc1166f12f87c1cd10329f4ae89e569dc2f54ade8cf6b57c027468
apps/sim/lib/table/sql.tshttps://github.com/simstudioai/sim/pull/8978/files#diff-6bdc8e3314777d023d081101b83c0458072aef721da07cf8b8881fd5d0fab5b3
View file https://github.com/simstudioai/sim/blob/d61989551fb0cd9e1c783a5d7508d364ecb7e79e/apps/sim/lib/table/sql.ts
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/simstudioai/sim/pull/8978/{{ revealButtonHref }}
https://github.com/simstudioai/sim/pull/8978/files#diff-6bdc8e3314777d023d081101b83c0458072aef721da07cf8b8881fd5d0fab5b3
https://github.com/simstudioai/sim/pull/8978/files#diff-6bdc8e3314777d023d081101b83c0458072aef721da07cf8b8881fd5d0fab5b3
https://github.com/simstudioai/sim/pull/8978/files#diff-6bdc8e3314777d023d081101b83c0458072aef721da07cf8b8881fd5d0fab5b3
https://github.com/simstudioai/sim/pull/8978/files#diff-6bdc8e3314777d023d081101b83c0458072aef721da07cf8b8881fd5d0fab5b3
apps/sim/lib/table/validation.tshttps://github.com/simstudioai/sim/pull/8978/files#diff-4afe0c5257b85a0e6c183bc2a7cd3db262dc445d8f341964364c751f6aa34f04
View file https://github.com/simstudioai/sim/blob/d61989551fb0cd9e1c783a5d7508d364ecb7e79e/apps/sim/lib/table/validation.ts
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/simstudioai/sim/pull/8978/{{ revealButtonHref }}
https://github.com/simstudioai/sim/pull/8978/files#diff-4afe0c5257b85a0e6c183bc2a7cd3db262dc445d8f341964364c751f6aa34f04
https://github.com/simstudioai/sim/pull/8978/files#diff-4afe0c5257b85a0e6c183bc2a7cd3db262dc445d8f341964364c751f6aa34f04
https://github.com/simstudioai/sim/pull/8978/files#diff-4afe0c5257b85a0e6c183bc2a7cd3db262dc445d8f341964364c751f6aa34f04
https://github.com/simstudioai/sim/pull/8978/files#diff-4afe0c5257b85a0e6c183bc2a7cd3db262dc445d8f341964364c751f6aa34f04
https://github.com/simstudioai/sim/pull/8978/files#diff-4afe0c5257b85a0e6c183bc2a7cd3db262dc445d8f341964364c751f6aa34f04
https://github.com/simstudioai/sim/pull/8978/files#diff-4afe0c5257b85a0e6c183bc2a7cd3db262dc445d8f341964364c751f6aa34f04
https://github.com/simstudioai/sim/pull/8978/files#diff-4afe0c5257b85a0e6c183bc2a7cd3db262dc445d8f341964364c751f6aa34f04
https://github.com/simstudioai/sim/pull/8978/files#diff-4afe0c5257b85a0e6c183bc2a7cd3db262dc445d8f341964364c751f6aa34f04
Please reload this pagehttps://github.com/simstudioai/sim/pull/8978/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.