Table settings are mostly undocumented

[mgartner]

Marcus Gartner (mgartner) commented:

Searching the docs for "table settings" does not return relevant results. The only mentions of table settings that I've found are:

1. The docs for opt_with_storage_parameter_list in the CREATE TABLE docs, which only mention geo-spatial index options (which is incorrect in this context) and not table-level settings.
2. The optimizer docs mention some table settings related to automatic stats collection.

I think we need a docs page dedicate to table-level settings that is searchable, explains what they are, how to set them on new and existing tables, and lists all possible table settings.

Jira Issue: DOC-9182

[exalate-issue-sync[bot]]

Shannon Bradshaw (shannonbradshaw) commented: Marcus Gartner is there a customer or field interaction that motivated this DOC issue? Can you link it here?

[exalate-issue-sync[bot]]

Marcus Gartner (mgartner) commented: Yes, see this ticket for one example: https://github.com/cockroachlabs/support/issues/2695#issuecomment-1793282312

[exalate-issue-sync[bot]]

Shannon Bradshaw (shannonbradshaw) commented: Rupert Harwood does it make sense to link this back to https://cockroachdb.zendesk.com/agent/tickets/19204 ?

Thanks, Marcus Gartner !

[exalate-issue-sync[bot]]

Marcus Gartner (mgartner) commented: No please don’t do that, it’s not the direct cause of the ticket. Documenting table settings is a more general issue. We need them to be documented in order to answer tickets like this.

[exalate-issue-sync[bot]]

Rupert Harwood (rupertharwood-crl) commented: Marcus Gartner this is actually the perfect reason to add this issue to the ticket

{quote}We need them to be documented in order to answer tickets like this.{quote}

Basically if this Jira would have helped this ticket, then it should be added. Not only if this Jira is the root cause. I am happy to hear opposing sentiment though if you feel strongly.

[exalate-issue-sync[bot]]

Ryan Kuo (taroface) commented: Marcus Gartner Thank you for raising this! I’m not entirely familiar with this topic, but are the settings here relevant? https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v24.1/misc/table-storage-parameters.md This table is included on the {{ALTER TABLE}} and {{WITH (storage parameter)}} docs (under the headings “Table storage parameters” and “Table parameters” respectively), and also has a link to a larger table of TTL storage parameters found here: https://www.cockroachlabs.com/docs/v24.1/row-level-ttl#ttl-storage-parameters .

[mgartner]

Thanks for digging that up. It does look like ALTER TABLE properly documents the storage parameters. I think CREATE TABLE is lacking the same info though.

[This table] mentions storage parameters, but only in the context of "spatial index tuning parameters", not "table storage parameters" like ALTER TABLE does.

Here's a CREATE TABLE example:

CREATE TABLE t (k INT PRIMARY KEY) WITH (sql_stats_automatic_collection_enabled = false);