[Sharding] Global, monotonic, int32 sequence

[levkk]

We currently have two ways to generate unique integer IDs:

1. timestamp-based: https://docs.pgdog.dev/features/sharding/unique-ids/
2. sequence-based (shard-local): https://docs.pgdog.dev/features/sharding/sequences/

We need a 3rd way, which creates global, monotonic, int32 numbers. This will be used with omnisharded tables and on deployments that can't migrate to bigint.

No suggested implementation. There are a few options, all suboptimal: either have a single point of failure or are slow because of required consensus.

[ygxio]

are we trying to come up with completely new approach here?

[levkk]

Potentially yup

[ygxio]

Ok will try thinking

[levkk]

We thought about using one of the shards (shard 0) for pulling it from a sequence. I started a PR but got distracted. It would need to be integrated with the schema manager:

pgdog/pgdog/src/backend/schema/mod.rs

Lines 150 to 168 in 4331c08

	for table in tables {
	for column in table.columns().iter().filter(|column| {
	column.1.is_primary_key // Only primary keys.
	&& matches!(column.1.data_type.as_str(), "bigint" | "int8") // Only BIGINT.
	// Only the ones that rely on a sequence.
	}) {
	info!(
	"[schema] creating sharded sequence for \"{}\".\"{}\".\"{}\"",
	column.1.table_schema, column.1.table_name, column.1.column_name,
	);
	let query = format!(
	"SELECT pgdog.install_sharded_sequence('{}', '{}', '{}')",
	column.1.table_schema, column.1.table_name, column.1.column_name,
	);
	server.execute_checked(&query).await?;
	}
	}

Another thing to consider: this needs to work only with omnisharded tables, not sharded ones! Sharded ones can use sequences on each respective shard, meanwhile omnisharded tables need globally unique, monotonic, IDs that don't repeat and have the same value on all shards, so must come from one source.

[ygxio]

It does have single point failure righr

[levkk]

That's correct but so do omnishard inserts, so...two wrongs make a right?

[ygxio]

yes that is true, that can be done, one optimisation can be that instead of consulting shard0 again n again for a sequence id, it can handover a small range to pgdog hence we can save some network roundtrips