{"action":"created","comment":{"url":"https://api.github.com/repos/cockroachdb/docs/pulls/comments/82428725","pull_request_review_id":3337560,"id":82428725,"diff_hunk":"@@ -0,0 +1,172 @@\n+---\n+title: INTERLEAVE IN PARENT\n+summary: Interleaving tables improves query performance by optimizing the key-value structure of closely related table's data.\n+toc: false\n+toc_not_nested: true\n+---\n+\n+Interleaving tables improves query performance by optimizing the key-value structure of closely related tables, attempting to keep data on the same [key-value range](frequently-asked-questions.html#how-does-cockroachdb-scale) if it's likely to be read and written together.\n+\n+{{site.data.alerts.callout_info}}Interleaving tables does not affect their behavior within SQL.{{site.data.alerts.end}}\n+\n+<div id=\"toc\"></div>\n+\n+## How do interleaved tables work?\n+\n+In the key-value store, interleaving tables writes data from one table (known as the **child**) directly into another (known as the **parent**) by matching the child table's Primary Key to the parent's.\n+\n+### Interleave Prefix\n+\n+For interleaved tables to have Primary Keys that can be matched, the child table must use the parent table's entire Primary Key as a prefix of its own Primary Key––these matching columns are referred to as the **interleave prefix**. It's easiest to think of these columns as representing the same data, which is usually implemented with Foreign Keys.\n+\n+{{site.data.alerts.callout_success}}To formally enforce the relationship between each table's interleave prefix columns, we recommend using <a href=\"constraints.html#foreign-keys\">Foreign Key constraints</a>.{{site.data.alerts.end}}\n+\n+For example, if you want to interleave `orders` into `customers` and the Primary Key of customers is `id`, you need to create a column representing `customers.id` as the first column in the Primary Key of `orders`&mdash;e.g., with a column called `customer`. So the data representing `customers.id` is the interleave prefix, which exists in the `orders` table as the `customer` column.\n+\n+### Key-Value Structure\n+\n+When you write data into the child table, it is inserted into the key-value store immediately after the parent table's key matching the interleave prefix.\n+\n+For example, if you interleave `orders` into `customers`, the `orders` data is written directly within the `customers` table in the key-value store. The following is a crude, illustrative example of what the keys would look like in this structure:\n+\n+~~~\n+/customers/1\n+/customers/1/orders/1000\n+/customers/1/orders/1002\n+/customers/2\n+/customers/2/orders/1001\n+/customers/2/orders/1003\n+...\n+/customers/n/\n+/customers/n/orders/<order belonging to n>\n+~~~\n+\n+By writing data in this way, related data is more likely to remain on the same key-value range, which can make it much faster to read from and write to. Using the above example, all of customer 1's data is going to be written to the same range, including its representation in both the `customers` and `orders` tables.\n+\n+## When should I interleave tables?\n+\n+You're most likely to benefit from interleaved tables when:\n+\n+  - Your tables form a [hierarchy](#interleaved-hierarchy)\n+  - Queries maximize the [benefits of interleaving](#benefits)\n+  - Queries do not suffer too greatly from interleaving's [tradeoffs](#tradeoffs)\n+\n+### Interleaved Hierarchy\n+\n+Interleaved tables typically work best when the tables form a hierarchy. For example, you could interleave the table `orders` (as the child) into the table `customers` (as the parent, which represents the people who placed the orders). You can extend this example by also interleaving the tables `invoices` (as a child) and `packages` (as a child) into `orders` (as the parent).\n+\n+The entire set of these relationships is referred to as the **interleaved hierarchy**, which are related through the interleave prefix columns.","path":"interleave-in-parent.md","position":58,"original_position":58,"commit_id":"803bc0e0dc981233af4e66590258aa3910cdfce1","original_commit_id":"803bc0e0dc981233af4e66590258aa3910cdfce1","user":{"login":"jseldess","id":15893490,"avatar_url":"https://avatars.githubusercontent.com/u/15893490?v=3","gravatar_id":"","url":"https://api.github.com/users/jseldess","html_url":"https://github.com/jseldess","followers_url":"https://api.github.com/users/jseldess/followers","following_url":"https://api.github.com/users/jseldess/following{/other_user}","gists_url":"https://api.github.com/users/jseldess/gists{/gist_id}","starred_url":"https://api.github.com/users/jseldess/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jseldess/subscriptions","organizations_url":"https://api.github.com/users/jseldess/orgs","repos_url":"https://api.github.com/users/jseldess/repos","events_url":"https://api.github.com/users/jseldess/events{/privacy}","received_events_url":"https://api.github.com/users/jseldess/received_events","type":"User","site_admin":false},"body":"Grammatically, \"which are related\" directly modifies \"interleaved hierarchy\", which is awkward due: \"are\" doesn't match the singular \"hierarchy\". Maybe something like:\r\n\r\n> The entire set of these relationships is referred to as the **interleave hierarchy** and is based on common interleave prefix columns.\r\n\r\nAlso, might help to link \"interleave prefix\" up to the corresponding section. \r\n\r\nAlternately, I think you could just leave out that final phrase, since the mechanics are explained earlier.","created_at":"2016-10-07T17:01:02Z","updated_at":"2016-10-07T17:01:02Z","html_url":"https://github.com/cockroachdb/docs/pull/632#discussion_r82428725","pull_request_url":"https://api.github.com/repos/cockroachdb/docs/pulls/632","_links":{"self":{"href":"https://api.github.com/repos/cockroachdb/docs/pulls/comments/82428725"},"html":{"href":"https://github.com/cockroachdb/docs/pull/632#discussion_r82428725"},"pull_request":{"href":"https://api.github.com/repos/cockroachdb/docs/pulls/632"}}},"pull_request":{"url":"https://api.github.com/repos/cockroachdb/docs/pulls/632","id":84369049,"html_url":"https://github.com/cockroachdb/docs/pull/632","diff_url":"https://github.com/cockroachdb/docs/pull/632.diff","patch_url":"https://github.com/cockroachdb/docs/pull/632.patch","issue_url":"https://api.github.com/repos/cockroachdb/docs/issues/632","number":632,"state":"open","locked":false,"title":"Interleave tables","user":{"login":"sploiselle","id":19260003,"avatar_url":"https://avatars.githubusercontent.com/u/19260003?v=3","gravatar_id":"","url":"https://api.github.com/users/sploiselle","html_url":"https://github.com/sploiselle","followers_url":"https://api.github.com/users/sploiselle/followers","following_url":"https://api.github.com/users/sploiselle/following{/other_user}","gists_url":"https://api.github.com/users/sploiselle/gists{/gist_id}","starred_url":"https://api.github.com/users/sploiselle/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sploiselle/subscriptions","organizations_url":"https://api.github.com/users/sploiselle/orgs","repos_url":"https://api.github.com/users/sploiselle/repos","events_url":"https://api.github.com/users/sploiselle/events{/privacy}","received_events_url":"https://api.github.com/users/sploiselle/received_events","type":"User","site_admin":false},"body":"Document for `CREATE TABLE... INTERLEAVE IN PARENT...`\r\nIncludes updates to SQL Grammar for supporting `INTERLEAVE`\r\n\r\n@paperstreet Can you review to make sure this is in the right direction?\r\n\r\nCloses #465\r\n\r\n### Future Work\r\n- Need to expand document to include interleaving secondary indexes.\r\n- Change nav structure related to #622\n\n<!-- Reviewable:start -->\n---\nThis change is [<img src=\"https://reviewable.io/review_button.svg\" height=\"34\" align=\"absmiddle\" alt=\"Reviewable\"/>](https://reviewable.io/reviews/cockroachdb/docs/632)\n<!-- Reviewable:end -->\n","created_at":"2016-09-07T20:37:13Z","updated_at":"2016-10-07T17:44:34Z","closed_at":null,"merged_at":null,"merge_commit_sha":"9bcf0cf365391446290eda46ed0d735c579b0c9f","assignee":{"login":"jseldess","id":15893490,"avatar_url":"https://avatars.githubusercontent.com/u/15893490?v=3","gravatar_id":"","url":"https://api.github.com/users/jseldess","html_url":"https://github.com/jseldess","followers_url":"https://api.github.com/users/jseldess/followers","following_url":"https://api.github.com/users/jseldess/following{/other_user}","gists_url":"https://api.github.com/users/jseldess/gists{/gist_id}","starred_url":"https://api.github.com/users/jseldess/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jseldess/subscriptions","organizations_url":"https://api.github.com/users/jseldess/orgs","repos_url":"https://api.github.com/users/jseldess/repos","events_url":"https://api.github.com/users/jseldess/events{/privacy}","received_events_url":"https://api.github.com/users/jseldess/received_events","type":"User","site_admin":false},"assignees":[{"login":"jseldess","id":15893490,"avatar_url":"https://avatars.githubusercontent.com/u/15893490?v=3","gravatar_id":"","url":"https://api.github.com/users/jseldess","html_url":"https://github.com/jseldess","followers_url":"https://api.github.com/users/jseldess/followers","following_url":"https://api.github.com/users/jseldess/following{/other_user}","gists_url":"https://api.github.com/users/jseldess/gists{/gist_id}","starred_url":"https://api.github.com/users/jseldess/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jseldess/subscriptions","organizations_url":"https://api.github.com/users/jseldess/orgs","repos_url":"https://api.github.com/users/jseldess/repos","events_url":"https://api.github.com/users/jseldess/events{/privacy}","received_events_url":"https://api.github.com/users/jseldess/received_events","type":"User","site_admin":false}],"milestone":null,"commits_url":"https://api.github.com/repos/cockroachdb/docs/pulls/632/commits","review_comments_url":"https://api.github.com/repos/cockroachdb/docs/pulls/632/comments","review_comment_url":"https://api.github.com/repos/cockroachdb/docs/pulls/comments{/number}","comments_url":"https://api.github.com/repos/cockroachdb/docs/issues/632/comments","statuses_url":"https://api.github.com/repos/cockroachdb/docs/statuses/803bc0e0dc981233af4e66590258aa3910cdfce1","head":{"label":"cockroachdb:interleave-in-parent","ref":"interleave-in-parent","sha":"803bc0e0dc981233af4e66590258aa3910cdfce1","user":{"login":"cockroachdb","id":6748139,"avatar_url":"https://avatars.githubusercontent.com/u/6748139?v=3","gravatar_id":"","url":"https://api.github.com/users/cockroachdb","html_url":"https://github.com/cockroachdb","followers_url":"https://api.github.com/users/cockroachdb/followers","following_url":"https://api.github.com/users/cockroachdb/following{/other_user}","gists_url":"https://api.github.com/users/cockroachdb/gists{/gist_id}","starred_url":"https://api.github.com/users/cockroachdb/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cockroachdb/subscriptions","organizations_url":"https://api.github.com/users/cockroachdb/orgs","repos_url":"https://api.github.com/users/cockroachdb/repos","events_url":"https://api.github.com/users/cockroachdb/events{/privacy}","received_events_url":"https://api.github.com/users/cockroachdb/received_events","type":"Organization","site_admin":false},"repo":{"id":48004987,"name":"docs","full_name":"cockroachdb/docs","owner":{"login":"cockroachdb","id":6748139,"avatar_url":"https://avatars.githubusercontent.com/u/6748139?v=3","gravatar_id":"","url":"https://api.github.com/users/cockroachdb","html_url":"https://github.com/cockroachdb","followers_url":"https://api.github.com/users/cockroachdb/followers","following_url":"https://api.github.com/users/cockroachdb/following{/other_user}","gists_url":"https://api.github.com/users/cockroachdb/gists{/gist_id}","starred_url":"https://api.github.com/users/cockroachdb/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cockroachdb/subscriptions","organizations_url":"https://api.github.com/users/cockroachdb/orgs","repos_url":"https://api.github.com/users/cockroachdb/repos","events_url":"https://api.github.com/users/cockroachdb/events{/privacy}","received_events_url":"https://api.github.com/users/cockroachdb/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/cockroachdb/docs","description":"CockroachDB user documentation","fork":false,"url":"https://api.github.com/repos/cockroachdb/docs","forks_url":"https://api.github.com/repos/cockroachdb/docs/forks","keys_url":"https://api.github.com/repos/cockroachdb/docs/keys{/key_id}","collaborators_url":"https://api.github.com/repos/cockroachdb/docs/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/cockroachdb/docs/teams","hooks_url":"https://api.github.com/repos/cockroachdb/docs/hooks","issue_events_url":"https://api.github.com/repos/cockroachdb/docs/issues/events{/number}","events_url":"https://api.github.com/repos/cockroachdb/docs/events","assignees_url":"https://api.github.com/repos/cockroachdb/docs/assignees{/user}","branches_url":"https://api.github.com/repos/cockroachdb/docs/branches{/branch}","tags_url":"https://api.github.com/repos/cockroachdb/docs/tags","blobs_url":"https://api.github.com/repos/cockroachdb/docs/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/cockroachdb/docs/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/cockroachdb/docs/git/refs{/sha}","trees_url":"https://api.github.com/repos/cockroachdb/docs/git/trees{/sha}","statuses_url":"https://api.github.com/repos/cockroachdb/docs/statuses/{sha}","languages_url":"https://api.github.com/repos/cockroachdb/docs/languages","stargazers_url":"https://api.github.com/repos/cockroachdb/docs/stargazers","contributors_url":"https://api.github.com/repos/cockroachdb/docs/contributors","subscribers_url":"https://api.github.com/repos/cockroachdb/docs/subscribers","subscription_url":"https://api.github.com/repos/cockroachdb/docs/subscription","commits_url":"https://api.github.com/repos/cockroachdb/docs/commits{/sha}","git_commits_url":"https://api.github.com/repos/cockroachdb/docs/git/commits{/sha}","comments_url":"https://api.github.com/repos/cockroachdb/docs/comments{/number}","issue_comment_url":"https://api.github.com/repos/cockroachdb/docs/issues/comments{/number}","contents_url":"https://api.github.com/repos/cockroachdb/docs/contents/{+path}","compare_url":"https://api.github.com/repos/cockroachdb/docs/compare/{base}...{head}","merges_url":"https://api.github.com/repos/cockroachdb/docs/merges","archive_url":"https://api.github.com/repos/cockroachdb/docs/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/cockroachdb/docs/downloads","issues_url":"https://api.github.com/repos/cockroachdb/docs/issues{/number}","pulls_url":"https://api.github.com/repos/cockroachdb/docs/pulls{/number}","milestones_url":"https://api.github.com/repos/cockroachdb/docs/milestones{/number}","notifications_url":"https://api.github.com/repos/cockroachdb/docs/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/cockroachdb/docs/labels{/name}","releases_url":"https://api.github.com/repos/cockroachdb/docs/releases{/id}","deployments_url":"https://api.github.com/repos/cockroachdb/docs/deployments","created_at":"2015-12-14T22:29:27Z","updated_at":"2016-09-11T15:14:02Z","pushed_at":"2016-10-07T15:13:53Z","git_url":"git://github.com/cockroachdb/docs.git","ssh_url":"git@github.com:cockroachdb/docs.git","clone_url":"https://github.com/cockroachdb/docs.git","svn_url":"https://github.com/cockroachdb/docs","homepage":"https://cockroachlabs.com/docs/","size":50597,"stargazers_count":14,"watchers_count":14,"language":"HTML","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":true,"forks_count":23,"mirror_url":null,"open_issues_count":148,"forks":23,"open_issues":148,"watchers":14,"default_branch":"gh-pages"}},"base":{"label":"cockroachdb:gh-pages","ref":"gh-pages","sha":"f946383d7fb4955a824ddd97a7e2d5c6c9806187","user":{"login":"cockroachdb","id":6748139,"avatar_url":"https://avatars.githubusercontent.com/u/6748139?v=3","gravatar_id":"","url":"https://api.github.com/users/cockroachdb","html_url":"https://github.com/cockroachdb","followers_url":"https://api.github.com/users/cockroachdb/followers","following_url":"https://api.github.com/users/cockroachdb/following{/other_user}","gists_url":"https://api.github.com/users/cockroachdb/gists{/gist_id}","starred_url":"https://api.github.com/users/cockroachdb/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cockroachdb/subscriptions","organizations_url":"https://api.github.com/users/cockroachdb/orgs","repos_url":"https://api.github.com/users/cockroachdb/repos","events_url":"https://api.github.com/users/cockroachdb/events{/privacy}","received_events_url":"https://api.github.com/users/cockroachdb/received_events","type":"Organization","site_admin":false},"repo":{"id":48004987,"name":"docs","full_name":"cockroachdb/docs","owner":{"login":"cockroachdb","id":6748139,"avatar_url":"https://avatars.githubusercontent.com/u/6748139?v=3","gravatar_id":"","url":"https://api.github.com/users/cockroachdb","html_url":"https://github.com/cockroachdb","followers_url":"https://api.github.com/users/cockroachdb/followers","following_url":"https://api.github.com/users/cockroachdb/following{/other_user}","gists_url":"https://api.github.com/users/cockroachdb/gists{/gist_id}","starred_url":"https://api.github.com/users/cockroachdb/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cockroachdb/subscriptions","organizations_url":"https://api.github.com/users/cockroachdb/orgs","repos_url":"https://api.github.com/users/cockroachdb/repos","events_url":"https://api.github.com/users/cockroachdb/events{/privacy}","received_events_url":"https://api.github.com/users/cockroachdb/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/cockroachdb/docs","description":"CockroachDB user documentation","fork":false,"url":"https://api.github.com/repos/cockroachdb/docs","forks_url":"https://api.github.com/repos/cockroachdb/docs/forks","keys_url":"https://api.github.com/repos/cockroachdb/docs/keys{/key_id}","collaborators_url":"https://api.github.com/repos/cockroachdb/docs/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/cockroachdb/docs/teams","hooks_url":"https://api.github.com/repos/cockroachdb/docs/hooks","issue_events_url":"https://api.github.com/repos/cockroachdb/docs/issues/events{/number}","events_url":"https://api.github.com/repos/cockroachdb/docs/events","assignees_url":"https://api.github.com/repos/cockroachdb/docs/assignees{/user}","branches_url":"https://api.github.com/repos/cockroachdb/docs/branches{/branch}","tags_url":"https://api.github.com/repos/cockroachdb/docs/tags","blobs_url":"https://api.github.com/repos/cockroachdb/docs/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/cockroachdb/docs/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/cockroachdb/docs/git/refs{/sha}","trees_url":"https://api.github.com/repos/cockroachdb/docs/git/trees{/sha}","statuses_url":"https://api.github.com/repos/cockroachdb/docs/statuses/{sha}","languages_url":"https://api.github.com/repos/cockroachdb/docs/languages","stargazers_url":"https://api.github.com/repos/cockroachdb/docs/stargazers","contributors_url":"https://api.github.com/repos/cockroachdb/docs/contributors","subscribers_url":"https://api.github.com/repos/cockroachdb/docs/subscribers","subscription_url":"https://api.github.com/repos/cockroachdb/docs/subscription","commits_url":"https://api.github.com/repos/cockroachdb/docs/commits{/sha}","git_commits_url":"https://api.github.com/repos/cockroachdb/docs/git/commits{/sha}","comments_url":"https://api.github.com/repos/cockroachdb/docs/comments{/number}","issue_comment_url":"https://api.github.com/repos/cockroachdb/docs/issues/comments{/number}","contents_url":"https://api.github.com/repos/cockroachdb/docs/contents/{+path}","compare_url":"https://api.github.com/repos/cockroachdb/docs/compare/{base}...{head}","merges_url":"https://api.github.com/repos/cockroachdb/docs/merges","archive_url":"https://api.github.com/repos/cockroachdb/docs/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/cockroachdb/docs/downloads","issues_url":"https://api.github.com/repos/cockroachdb/docs/issues{/number}","pulls_url":"https://api.github.com/repos/cockroachdb/docs/pulls{/number}","milestones_url":"https://api.github.com/repos/cockroachdb/docs/milestones{/number}","notifications_url":"https://api.github.com/repos/cockroachdb/docs/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/cockroachdb/docs/labels{/name}","releases_url":"https://api.github.com/repos/cockroachdb/docs/releases{/id}","deployments_url":"https://api.github.com/repos/cockroachdb/docs/deployments","created_at":"2015-12-14T22:29:27Z","updated_at":"2016-09-11T15:14:02Z","pushed_at":"2016-10-07T15:13:53Z","git_url":"git://github.com/cockroachdb/docs.git","ssh_url":"git@github.com:cockroachdb/docs.git","clone_url":"https://github.com/cockroachdb/docs.git","svn_url":"https://github.com/cockroachdb/docs","homepage":"https://cockroachlabs.com/docs/","size":50597,"stargazers_count":14,"watchers_count":14,"language":"HTML","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":true,"forks_count":23,"mirror_url":null,"open_issues_count":148,"forks":23,"open_issues":148,"watchers":14,"default_branch":"gh-pages"}},"_links":{"self":{"href":"https://api.github.com/repos/cockroachdb/docs/pulls/632"},"html":{"href":"https://github.com/cockroachdb/docs/pull/632"},"issue":{"href":"https://api.github.com/repos/cockroachdb/docs/issues/632"},"comments":{"href":"https://api.github.com/repos/cockroachdb/docs/issues/632/comments"},"review_comments":{"href":"https://api.github.com/repos/cockroachdb/docs/pulls/632/comments"},"review_comment":{"href":"https://api.github.com/repos/cockroachdb/docs/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/cockroachdb/docs/pulls/632/commits"},"statuses":{"href":"https://api.github.com/repos/cockroachdb/docs/statuses/803bc0e0dc981233af4e66590258aa3910cdfce1"}}}}
{"actor":{"display_login":"jseldess"}}