opt: add support for "invisible" indexes #72576

RaduBerinde · 2021-11-09T16:54:35Z

The feature request is to add a way to create an index that is never used in queries unless specifically selected with a hint. This would be useful to reproduce issues around index corruption in production environments - we don't want the index affecting production queries but we want to try creating it and checking for corruption.

Epic: CRDB-11155

Jira issue: CRDB-11218

glennfawcett · 2022-02-09T23:21:21Z

It would be great if an index could be made "invisible" to a given fraction of the queries, so that one could induce just a partial outage instead of a full blown outage when experimenting. In other words, instead of a boolean "invisible" flag, one could set a float "invisible" flag between 0.0 and 1.0 (corresponding to 0% invisible and 100% invisible, allowing for all the shades in between)

This PR added an RFC draft for the invisible index feature. The main purpose of this RFC is to introduce the feature, to document different choices of SQL syntaxes, and ultimately to justify the decision. Related issue: cockroachdb#72576, cockroachdb#82363 Release Note: none

This PR takes the first step for the invisible index feature. The first commit added a boolean field `Hidden` to the struct `IndexDescriptor`. Since primary indexes cannot be invisible, this commit also added a check in `pkg/sql/catalog/tabledesc/validate.go` for that. The second commit added a new column `is_hidden` to `crdb_internal.table_indexes` and also to the output of following SQL statements: ``` SHOW INDEX FROM (table_name) SHOW INDEXES FROM(table_name) SHOW KEYS FROM (table_name) SHOW INDEX FROM DATABASE(database_name) SHOW INDEXES FROM DATABASE (database_name) SHOW KEYS FROM DATABASE (database_name) ``` Since the invisible index feature has not been introduced yet, all indexes created should be visible. It is expected for all test cases to output `false` for all `is_hidden` columns. See also: There will be more PR coming up to add the SQL statements `CREATE INDEX … NOT VISIBLE`, `CREATE TABLE … (INDEX NOT VISIBLE)`, `ALTER INDEX … NOT VISIBLE`. Next PR for invisible index feature: cockroachdb#83471 TODO (wenyihu6): link more related pr later on Fixes: cockroachdb#72576 Note that this issue has not been resolved yet, and this pr only takes the first step. Release note (sql change): A new column `is_hidden` has been added to the table `crdb_internal.table_indexes` and to the output of SQL statements related to `SHOW INDEX`, `SHOW INDEXES`, and `SHOW KEYS`. Since the invisible index feature has not been introduced yet, the output for this column will always be false for now. This will be changed soon.

This PR takes the first step for the invisible index feature. It added a boolean field `Hidden` to the struct `IndexDescriptor`. Since primary indexes cannot be hidden, this pr also added a check in `pkg/sql/catalog/tabledesc/validate.go` for that. In addition, this PR also added a new column `visible` to `crdb_internal.table_indexes` and also to the output of following SQL statements: ``` SHOW INDEX FROM (table_name) SHOW INDEXES FROM(table_name) SHOW KEYS FROM (table_name) SHOW INDEX FROM DATABASE(database_name) SHOW INDEXES FROM DATABASE (database_name) SHOW KEYS FROM DATABASE (database_name) ``` Since the invisible index feature has not been introduced yet, all indexes created should be visible. It is expected for all test cases to output `true` for all `visible` columns. See also: next PR for invisible index feature: cockroachdb#83471 TODO (wenyihu6): link more related pr later on Fixes: cockroachdb#72576 Note that this issue has not been resolved yet, and this pr only takes the first step. Release note (sql change): A new column `visible` has been added to the table `crdb_internal.table_indexes` and to the output of SQL statements related to `SHOW INDEX`, `SHOW INDEXES`, and `SHOW KEYS`. Since the invisible index feature has not been introduced yet, the output for the `visible` column will always be true for now. This will be changed soon.

This commit adds parsing support for ALTER INDEX … [VISIBLE | NOT VISIBLE]. Executing the command returns an `unimplemented error`. Assists: cockroachdb#72576 See also: cockroachdb#85794 Release note (sql change): Parser now supports altering an index to visible or not visible. But no implementation has done yet, and executing it returns an “unimplemented” error immediately. # Conflicts: # pkg/sql/sem/tree/stmt.go

85473: sql: add ALTER INDEX … NOT VISIBLE to parser r=wenyihu6 a=wenyihu6 This commit adds parsing support for ALTER INDEX … [VISIBLE | NOT VISIBLE]. Executing the command returns an `unimplemented error`. Assists: #72576 See also: #85794 Release note (sql change): Parser now supports altering an index to visible or not visible. But no implementation has done yet, and executing it returns an “unimplemented” error immediately. Co-authored-by: wenyihu3 <[email protected]>

86032: sql: support ALTER INDEX … NOT VISIBLE r=wenyihu6 a=wenyihu6 This commit adds support to execute ALTER INDEX … [VISIBLE | NOT VISIBLE]. Fixes: #72576 See also: #85473 Release note (sql change): Altering an index to visible or not visible using ALTER INDEX … VISIBLE | NOT VISIBLE is now supported. Co-authored-by: wenyihu3 <[email protected]>

Optimizer now supports creating invisible indexes after this [PR](cockroachdb#85794). An important use case for not visible indexes is to test the behaviour of dropping an index by marking the index invisible. However, there are certain cases where users cannot expect dropping an index to behave exactly the same as marking an index invisible. More specifically, NotVisible indexes may still be used to police unique or foreign key constraint check behind the scene. In those cases, dropping the index might behave different from marking the index invisible. Prior to this commit, users do not know about this without reading the documentation. This commit adds some user-friendly notices when users are dropping or changing a not visible index that might be helpful for constraint check. There are two cases where we are giving this notice: 1. if this index is unique. 2. if this index is on child table and may help with FK check. More details on how this decision was made in docs/RFCS/20220628_invisible_index.md. Assists: cockroachdb#72576 See also: cockroachdb#85794 Release justification: low risk to the existing functionality; this commit just adds notices. Release note: none

This commit adds an RFC for the invisible index feature. Related issue: cockroachdb#72576, cockroachdb#82363 Release justification: low risk to the existing functionality; this commit just adds rfc. Release Note: none

Optimizer now supports creating invisible indexes after this [PR](cockroachdb#85794). An important use case for not visible indexes is to test the behaviour of dropping an index by marking the index invisible. However, there are certain cases where users cannot expect dropping an index to behave exactly the same as marking an index invisible. More specifically, NotVisible indexes may still be used to police unique or foreign key constraint check behind the scene. In those cases, dropping the index might behave different from marking the index invisible. Prior to this commit, users do not know about this without reading the documentation. This commit adds some user-friendly notices when users are dropping or changing a not visible index that might be helpful for constraint check. There are two cases where we are giving this notice: 1. if this index is unique. 2. if this index is on child table and may help with FK check. More details on how this decision was made in docs/RFCS/20220628_invisible_index.md. Assists: cockroachdb#72576 See also: cockroachdb#85794 Release justification: low risk to the existing functionality; this commit just adds notices. Release note: none

83531: rfc: add rfc for invisible index feature r=wenyihu6 a=wenyihu6 This commit adds an RFC for the invisible index feature. Related issue: #72576, #82363 Release justification: low risk to the existing functionality; this commit just adds rfc. Release Note: none 86267: allocator: select a good enough store for decom/recovery r=lidorcarmel a=lidorcarmel Until now, when decommissioning a node, or when recovering from a dead node, the allocator tries to pick one of the best possible stores as the target for the recovery. Because of that, we sometimes see multiple stores recover replicas to the same store, for example, when decommissioning a node and at the same time adding a new node. This PR changes the way we select a destination store by choosing a random store out of all the stores that are "good enough" for the replica. The risk diversity is still enforced, but we may recover a replica to a store that is considered "over full", for example. Note that during upreplication the allocator will still try to use one of the "best" stores as targets. Fixes: #86265 Release note: None Release justification: a relatively small change, and it can be reverted by setting kv.allocator.recovery_store_selector=best. 86345: clusterversion: prevent upgrades from development versions r=ajwerner a=dt This change defines a new "unstableVersionsAbove" point on the cluster version line, above which any cluster versions are considered unstable development-only versions which are still subject to change. Performing an upgrade to a version while it is still unstable leaves a cluster in a state where it persists a version that claims it has done that upgrade and all prior, however those upgrades are still subject to change by nature of being unstable. If it subsequently upgraded to a stable version, this could result in subtle and nearly impossible to detect issues, as being at or above a particular version is used to assume that all subsequent version upgrades _as released_ were run; on a cluster that ran an earlier iteration of an upgrade this does not hold. Thus to prevent clusters which upgrade to development versions from subsequently upgrading to a stable version, we offset all development versions -- those above the unstableVersionsAbove point -- into the far future by adding one million to their major version e.g. v22.x-y becomes 1000022.x-y. This means an attempt to subsequently "upgrade" to a stable version -- such as v22.2 -- will look like a downgrade and be forbidden. On the release branch, prior to starting to publish upgradable releases, the unstableVersionsAbove value should be set to invalidVersionKey to reflect that all version upgrades in that release branch are now considered to be stable, meaning they must be treated as immutable and append-only. Release note (ops change): clusters that are upgraded to an alpha or other manual build from the development branch will not be able to be subsequently upgraded to a release build. Release justification: high-priority change to existing functionality, to allow releasing alphas with known version upgrade bugs while ensuring they do not subsequently upgrade into stable version but silently corrupted clusters. 86630: kvserver: add additional testing to multiqueue r=AlexTalks a=AlexTalks Add testing for cancelation of multi-queue requests and fix a bug where the channel wasn't closed on task cancelation. Release justification: Test-only change. Release note: None 86801: ttl: add queries to job details r=otan a=rafiss fixes #81905 This helps with observability so users can understand what the TTL job is doing behind the scenes. The job details in the DB console will show: ``` ttl for defaultdb.public.t -- for each range, iterate to find rows: SELECT id FROM [108 AS tbl_name] AS OF SYSTEM TIME '30s' WHERE <crdb_internal_expiration OR ttl_expiration_expression> <= $1 AND (id) > (<range start>) AND (id) < (<range end>) ORDER BY id LIMIT <ttl_select_batch_size> -- then delete with: DELETE FROM [108 AS tbl_name] WHERE <crdb_internal_expiration OR ttl_expiration_expression> <= $1 AND (id) IN (<rows selected above>) ``` Release note: None Release justification: low risk change 86876: kv: Include error information in `crdb_internal.active_range_feeds` r=miretskiy a=miretskiy Include error count, and the last error information in `crdb_internal.active_range_feeds` table whenever rangefeed disconnects due to an error. Release justification: observability improvement. Release note: None 86901: sql: fix cluster_execution_insights priority level r=j82w a=j82w This fixes the priority level and converts it to be a string. closes: #86900, closes #86867 Release justification: Category 2: Bug fixes and low-risk updates to new functionality Release note (sql change): Fix the insight execution priority to have correct value instead of always being default. Changed the column to be a string to avoid converting it in the ui. 86921: kvserver: incorporate sending queue priority into snapshot requests r=AlexTalks a=AlexTalks This change modifies the `(Delegated)SnapshotRequest` Raft RPCs in order to incorporate the name of the sending queue, as well as the sending queue's priority, in order to be used to prioritize queued snapshots on a receiving store. Release justification: Low-risk change to existing functionality. Release note: None 86923: dev: bump version r=yuzefovich a=yuzefovich This commit bumps version since there appears to have been a "merge skew" between #85095 and #86167, and somehow I had a `dev` binary that didn't include the benchmark fix from the latter. Release justification: test-only change. Release note: None Co-authored-by: wenyihu3 <[email protected]> Co-authored-by: Lidor Carmel <[email protected]> Co-authored-by: David Taylor <[email protected]> Co-authored-by: Alex Sarkesian <[email protected]> Co-authored-by: Rafi Shamim <[email protected]> Co-authored-by: Yevgeniy Miretskiy <[email protected]> Co-authored-by: j82w <[email protected]> Co-authored-by: Yahor Yuzefovich <[email protected]>

Optimizer now supports creating invisible indexes after this [PR](cockroachdb#85794). An important use case for not visible indexes is to test the behaviour of dropping an index by marking the index invisible. However, there are certain cases where users cannot expect dropping an index to behave exactly the same as marking an index invisible. More specifically, NotVisible indexes may still be used to police unique or foreign key constraint check behind the scene. In those cases, dropping the index might behave different from marking the index invisible. Prior to this commit, users do not know about this without reading the documentation. This commit adds some user-friendly notices when users are dropping or changing a not visible index that might be helpful for constraint check. There are two cases where we are giving this notice: 1. if this index is unique. 2. if this index is on child table and may help with FK check. More details on how this decision was made in docs/RFCS/20220628_invisible_index.md. Assists: cockroachdb#72576 See also: cockroachdb#85794 Release justification: low risk to the existing functionality; this commit just adds notices. Release note: none

…87158 85354: sql: notices for NotVisible Indexes r=wenyihu6 a=wenyihu6 Optimizer now supports creating invisible indexes after this [PR](#85794). An important use case for not visible indexes is to test the behaviour of dropping an index by marking the index invisible. However, there are certain cases where users cannot expect dropping an index to behave exactly the same as marking an index invisible. More specifically, NotVisible indexes may still be used to police unique or foreign key constraint check behind the scene. In those cases, dropping the index might behave different from marking the index invisible. Prior to this commit, users do not know about this without reading the documentation. This commit adds some user-friendly notices when users are dropping or changing a not visible index that might be helpful for constraint check. There are two cases where we are giving this notice: 1. if this index is unique. 2. if this index is on child table and may help with FK check. More details on how this decision was made in docs/RFCS/20220628_invisible_index.md. Assists: #72576 See also: #85794 Release justification: low risk to the existing functionality; this commit just adds notices. Release note: none 86592: kvserver: rework memory allocation in replicastats r=kvoli a=kvoli This patch removes some unused fields within the replica stats object. It also opts to allocate all the memory needed upfront for a replica stats object for better cache locality and less GC overhead. This patch also removes locality tracking for the other throughput trackers to reduce per-replica memory footprint. resolves #85112 Release justification: low risk, lowers memory footprint to avoid oom. Release note: None 87024: sql: Prevent primary region being same as secondary region r=rafiss a=e-mbrown fixes #86879 We found that the primary region could be assigned the same region as the secondary region. This commit adds an error to prevent that. Release justification: Low risk high benefit change to existing functionality Release note: None 87110: ui: fixes to high contention copy in insight workload pages r=ericharmeling a=ericharmeling Previously, the High Contention insight type was labeled "High Contention Time", and the waiting transactions list was labeled in the incorrect tense. This commit fixes those typos. Release justification: bug fix Release note: None 87135: build: remove newly-added node_modules/ trees in ui-maintainer-clean r=rickystewart a=sjbarag A few recent features [1, 2] introduced new node_modules/ trees for dependencies, but didn't update the ui-maintainer-clean Make target to remove them. This allowed those directories to leak between TeamCity builds with Docker user permissions, preventing a `yarn install` in those packages from properly laying out a `node_modules/.bin` directory for executables like `tsc`. Remove the recently-introduced `node_modules/` directories as part of `make ui-maintainer-clean`, to restore a clean state between jobs. [1] d28c072 (ui: add eslint-plugin-crdb package with custom eslint rules, 2022-05-27) [2] c58279d (ui: reintroduce end-to-end UI tests with cypress, 2022-08-12) Release justification: Non-production code changes 87149: sql: clean up physical planning for system tenant r=yuzefovich a=yuzefovich This commit audits a couple of methods around the health and version of DistSQL nodes that are used only for the system tenant to make that more explicit. Additionally, it unexports `NodeStatuses` map from the planning context as well as removes some unnecessary short-circuiting behavior around checking the node health and version (it was unnecessary because we already short-circuit in `checkInstanceHealthAndVersionSystem`). Release justification: low-risk cleanup. Release note: None 87153: ui: ux improvements on stmt details page r=maryliag a=maryliag This commit adds a few improvements and bug fixes: - Handles the case where we hit a timeout on statement details, so it doesn't crash anymore and you can still see the time picker to be able to select a new time interval. - Updates the error message, to clarify it was a timeout error and increase the timeout from 30s to 30m on the details endpoint. Fixes #78979 - Updates the last error for statement details with the proper value, which previously was using the error for all statements endpoint, instead of the specific for that fingerprint id. - Adds a message when page takes longer to load. - Uses a proper count formatting for execution count. Release justification: bug fixes and smaller improvements Release note (ui change): Proper formatting of execution count under Statement Details page. Increase timeout for Statement Details page and shows proper timeout error when it happens, no longer crashing the page. 87155: github-post: allow for finding the test in a parent directory of the pkg r=srosenberg,rail a=rickystewart In some cases the Bazel test runner "incorrectly" reports the package path for tests. For example, we have [issues](#85376) where the name of the test is reported as `pkg/.../package/package_test` rather than `pkg/.../package` as we might expect. I suspect this is confusing `github-post` when it tries to find tests in the `package_test` directory rather than the `package` directory. We address this by allowing `github-post` to search up the directory tree for the test rather than expecting it to be in one particular directory. Also update a repro command to use `dev test` rather than `make stressrace`. Closes #85420. Release justification: Non-production code changes Release note: None 87156: ci: disable sharding in random syntax tests r=srosenberg a=rickystewart The different shards were trampling each other's test.json.txt, preventing failures from being reported accurately. Release justification: Non-production code changes Release note: None 87158: sql: clean up node dialer fields r=yuzefovich a=yuzefovich This commit removes no longer used `nodeDialer` field (for SQL - KV communication) as well as renames some of the similarly named fields to `podNodeDialer` to indicate that its only a SQL - SQL dialer. Release justification: low-risk cleanup. Release note: None Co-authored-by: wenyihu3 <[email protected]> Co-authored-by: Austen McClernon <[email protected]> Co-authored-by: e-mbrown <[email protected]> Co-authored-by: Eric Harmeling <[email protected]> Co-authored-by: Sean Barag <[email protected]> Co-authored-by: Yahor Yuzefovich <[email protected]> Co-authored-by: Marylia Gutierrez <[email protected]> Co-authored-by: Ricky Stewart <[email protected]>

RaduBerinde added C-wishlist A wishlist feature. E-quick-win Likely to be a quick win for someone experienced. labels Nov 9, 2021

blathers-crl bot added the T-sql-queries SQL Queries Team label Nov 9, 2021

mgartner mentioned this issue Nov 11, 2021

sql: add invisible indexes #72664

Closed

ajwerner mentioned this issue Jun 2, 2022

sql: allow fine-grained control of index visibility #82363

Open

wenyihu6 self-assigned this Jun 15, 2022

This was referenced Jun 26, 2022

sql: added Hidden to index descriptor and SHOW INDEX #83388

Closed

sql: added CREATE … NOT VISIBLE to parser #83471

Closed

wenyihu6 mentioned this issue Jun 28, 2022

rfc: add rfc for invisible index feature #83531

Merged

craig bot closed this as completed in 15613bb Aug 13, 2022

mgartner added this to SQL Queries Jul 24, 2023

mgartner moved this to Done in SQL Queries Jul 24, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

opt: add support for "invisible" indexes #72576

opt: add support for "invisible" indexes #72576

RaduBerinde commented Nov 9, 2021 •

edited by cockroach-jira-scripts

Loading

glennfawcett commented Feb 9, 2022

opt: add support for "invisible" indexes #72576

opt: add support for "invisible" indexes #72576

Comments

RaduBerinde commented Nov 9, 2021 • edited by cockroach-jira-scripts Loading

glennfawcett commented Feb 9, 2022

RaduBerinde commented Nov 9, 2021 •

edited by cockroach-jira-scripts

Loading