citusdata
Enforce column uniqueness across all tenants
Why are we implementing it? (sales eng)
What are the typical use cases?
Some SaaS businesses use a unified login system across their tenants and would like to enforce that fields such as user_id or user_email are unique. Our current instructions for multi-tenant apps advises that all uniqueness constraints must include the distribution column. However this guarantees uniqueness only within, not between, tenants.
Communication goals (e.g. detailed howto vs orientation)
Point out the situation of globally unique values to let users know we support this, and advise the ways to implement it.
Good locations for content in docs structure
These sections need an update:
- Multi-tenant migration-section and
- DML add/create constraints section
How does this work? (devs)
There are two approaches. The more robust solution is using a reference table with a uniqueness constraint. Cross-tenant data is the purpose of reference tables. This does cost some extra disk space as the data must be replicated on every worker.
The second approach is to add application logic to first search for the value-to-be across all workers, then insert it it doesn't exist. This may require less application modification than changing the schema to add a reference table.
Example sql
Corner cases, gotchas
Does Citus support a transactional find-or-create for the application? Does the isolation level protect against phantom reads that would permit duplication for simultaneous user registration?
