Clarify primary keys, static columns vs. column families

[bra-fsn]

Exalate commented:

On https://www.cockroachlabs.com/docs/dev/column-families.html the example contains a table with one column as the primary key, the id:

CREATE TABLE test (
    id INT PRIMARY KEY,
    last_accessed TIMESTAMP,
    data BYTES,
    FAMILY f1 (id, last_accessed),
    FAMILY f2 (data)
);

The example then mixes never updated fields (id) with the frequently updated one last_accessed in one column family, putting the never modified data into f2.

For the first glance, it would be logical to group static (id and data) columns into one family and frequently modified ones to the other:

CREATE TABLE test (
    id INT PRIMARY KEY,
    last_accessed TIMESTAMP,
    data BYTES,
    FAMILY f1 (id, data),
    FAMILY f2 (last_accessed)
);

So could you please extend the current doc, explain and add more details about:

• why putting id and last_accessed into one CF is recommended
• what if there are more PK columns, should those be in the frequently modified CF in this case as well?

Jira Issue: DOC-462

[jseldess]

Thanks for the suggestion, @bra-fsn. We'll get this on our docs improvement backlog.

[jseldess]

@rmloveland, assigning this one to you because it's very much about how rows are stored on disk (kv). Feel free to reassign to @ericharmeling if you feel it's more primarily a SQL issue.

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB docs!