Recommend against using autoincremented integer primary keys

[rmloveland]

... unless it's absolutely necessary for a specific use case, in which case you should be aware of the effects on INSERT performance (and possibly time required for range rebalancing across nodes? I'm much murkier on this point).

We do mention this issue obliquely in the performance best practices docs, but in terms of general transaction contention.

However, this issue keeps coming up in the forum in one guise or another:

• cockroachDB single hot node
• is this insert performance normal
• performance of JDBC batch inserts & upserts (potentially JDBC issue, but the schema looks very sequential-integer-y)
• how does cockroachdb scale
• cockroachdb - what are the read/write perf goals?

Therefore, in addition to our current docs on this issue, we should state explicitly in the primary key docs, the CREATE TABLE docs, and possibly others that most users should use random values as primary keys most of the time. Users who are aware of and OK with the tradeoffs that will result from using sequential values can still do so of course, but we should describe those tradeoffs.

Perhaps a variation of @knz's comment on the last forum post in the list above would suffice, i.e.,

Meanwhile, for this type of use case, you can tune write performance effectively with CockroachDB by using some random value as PK and use a secondary index on the time column. In this way, inserts are properly scattered through the cluster, resulting in better throughput scalability of writes (more inserts/sec when you increase the number of nodes), at the cost of slightly higher latency for individual read queries.

(Aside: the latter point about read latency may be mitigated somewhat in a magical follower reads future, AIUI, but that is beside the point at this time.)

(cc @tim-o FYI)

[rmloveland]

Also, this FAQ entry on generated IDs should be updated to deemphasize / discourage INT PKs.

[rmloveland]

I drafted this recently while working on updates to the INT docs for #4183, but it doesn't belong in that unit of work. Stashing here for when this issue gets worked on - I think this should be a new section on the INT page (note: links don't work in this context).

---

Integer primary keys

If you are planning to use a primary key of type INT, please note that it will lead to performance degradation due to write contention (since sequential values write to contiguous areas in the same and/or adjacent ranges on disk). If fast range scans are important to your app's design, but INSERT/UPDATE performance is not, then using INT primary keys can work.

If INSERT/UPDATE performance is important for your application, we recommend using UUID primary keys. Performance is better because writes can be spread more evenly across the cluster's ranges, avoiding hot spots. For more information, see:

• Unique ID best practices
• Definition of ranges in the CockroachDB Architecture glossary