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
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:063bef71-9e1c-8419-c302-581366d40f47 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | 9E48:BD772:AF0965:EFAA9E:6AFB5154 |
| html-safe-nonce | 41808c5353079eb8a1528becfb205f689ffc3d341e27ee3e198ebf1d28a8fccd |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiI5RTQ4OkJENzcyOkFGMDk2NTpFRkFBOUU6NkFGQjUxNTQiLCJ2aXNpdG9yX2lkIjoiNDc5Mzk1ODI4OTQzNTA3Nzk2IiwicmVnaW9uX2VkZ2UiOiJpYWQiLCJyZWdpb25fcmVuZGVyIjoiaWFkIn0= |
| visitor-hmac | 2f2c1a4175deea244cece3c870f55c26d7365a0ad92152b84c99db4b6376a634 |
| hovercard-subject-tag | pull_request:4847750574 |
| 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/8978/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 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_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | 7122bb414b542dba14d71a3f0b9f6fd2abce715b9f5912d082800563e6c806d1 |
| 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 | cbf66c024bb6d10e6955baa7413c54cf13a201bb |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
Viewport: width=device-width