search

citusdata / citus

Distributed PostgreSQL as an extension
https://www.citusdata.com
GNU Affero General Public License v3.0
10.57k stars 670 forks source link

Separate SEQUENCE objects for distributed table shards #6168

Open pbav opened 2 years ago

pbav commented 2 years ago

Having one auto-incremental ID for records belonging to different tenants (located on different shards) is a potential bottleneck for INSERT. A possible solution is to use a UUID or shorter alternative, which is still longer than a 4 or 8-byte integer.

Is there any drawback in having separate SEQUENCE objects (all starting with 1) for different shards of a distributed table?

E.g., table_name column_name column_default
obj_102284 id nextval('obj_id_seq_102284')
obj_102285 id nextval('obj_id_seq_102285')
...

Is it possible with Citus? I assume that all queries use a composite key (tenant_id, object_id).

marcocitus commented 2 years ago

Interesting idea! So far, sequences are global objects. They do get created and assigned a different range on each node, but that is used only when doing the insert via that worker node and not tied to any shard.