Use random ROWID for inserted rows

[losfair]

With an optimistic VFS, sequentially increasing ROWIDs is a primary source of contention, and causes significant INSERT slowdown in my benchmark. We should provide a table-level option to allocate ROWIDs randomly.

This is possible in upstream SQLite by having a special row with rowid == max(int64). But not all applications like this special row.

The syntax might look like the WITHOUT ROWID table one:

CREATE TABLE t (...) RANDOM ROWID;

[AngeloR]

Wouldn't exclusively random id allocation require consensus across the cluster anyway leading to some lock?

I would be in favour of some kind of vector clock based addition to the randomized row id to avoid this - that way we wouldn't be totally reliant on randomness of one machine dictating id's across the cluster.

We could go a step further and just introduce support for uuid7, but then we're just relying on system clock which is also a potential issue..

[losfair]

@AngeloR

Wouldn't exclusively random id allocation require consensus across the cluster anyway leading to some lock?

In an optimistic transactional distributed storage there is either no lock during data plane operation (FoundationDB) or a 2PC lock as an implementation detail (TiKV). The behavior exposed to the client is retry instead of lock: when there does not exist a possible serializable (and in some systems, linearizable) order between two concurrent transactions, one of them is rejected and the client has to retry.

Counter increment is a typical case: there never exists a serializable order for two concurrent transactions that both read the counter, increment it by one, and write it back.

Exclusive random ID though only generates a conflict when two concurrent transactions are attempting to acquire the same ID.