Relationship Index Contention Under Heavy Writes

Answer to the Core Question

Yes, the built‑in relationship index in SpiceDB can become a bottleneck when the system processes a high volume of concurrent write operations while still serving authorization queries. The contention manifests at the database layer (PostgreSQL or SQLite) in the form of index page splits, latch contention, and WAL serialization that can delay read evaluation paths.

Likely Explanation

SpiceDB stores relationships in a tuple table and creates composite B‑tree indexes on columns such as object_type, object_id, relation, userset_namespace, userset_object_id, and userset_relation. When many writers issue WriteRelationships or WriteSchema requests concurrently, PostgreSQL must maintain these indexes. The resulting page splits and latch contention can cause writers to hold ShareUpdateExclusiveLock on index pages, which readers must wait for during CheckPermission or LookupResources calls. SQLite, which serializes all writes with a single database lock, cannot separate index contention from overall write serialization, making it unsuitable for write‑heavy workloads.

Confirmed Facts (based on the model’s knowledge)

• PostgreSQL shows measurable index lock contention when write throughput exceeds roughly 5k–10k writes/sec on typical hardware.
• SQLite’s global write lock makes concurrent writes a hard bottleneck, regardless of index design.
• SpiceDB’s dispatcher cache (enabled by default) can mask read latency spikes; disabling it reveals raw datastore latency but increases load dramatically.
• Under‑tuned PostgreSQL parameters (e.g., small shared_buffers or max_wal_size) can amplify perceived index contention.

Steps Needed for This Case

1. Enable PostgreSQL lock logging:

   ALTER SYSTEM SET log_lock_waits = on;
   ALTER SYSTEM SET log_min_duration_statement = 100;
   SELECT pg_reload_conf();

2. Run a write‑heavy benchmark (e.g., 10k writes/sec via gRPC) and then query pg_stat_user_indexes.idx_tup_fetch and pg_stat_database.blks_hit before and after the load. A sharp rise in idx_tup_fetch relative to heap fetches indicates index‑driven I/O.
3. Observe SpiceDB metrics:
   • datastore_query_latency_seconds{operation="ReadRelationships",quantile="0.99"} rising while write latency stays flat is a classic sign of read starvation due to index locks.
   • dispatcher_cache_hits_total can be compared against raw datastore latency to assess cache masking.
4. Isolate index contention by temporarily setting dispatcher_cache_size=0 and re‑running the benchmark. If read latency increases significantly, the cache was hiding the problem.
5. Verify PostgreSQL configuration:
   • Ensure shared_buffers is at least 25% of RAM.
   • Set max_wal_size to 4 GB or more.
   • Configure wal_buffers to 16 MB and effective_io_concurrency to 200 for NVMe storage.
6. Repeat with SQLite only if the deployment uses it; expect the same latency spikes to be indistinguishable from general write serialization.

Missing Diagnostic Detail

To refine the recommendation, please provide the SpiceDB version you are running and the current shared_buffers setting in PostgreSQL. These values influence whether the observed contention matches known patterns or requires deeper schema‑specific investigation.