by making multiple calls to SpiceDB I hope to emulate voting by subject in a limited fashion. With two calls I can implement positive voting (union of permissions) and negative voting (intersection of permissions) but I need 3 calls and an extra permission to say "can do this only if someone else votes yes"

I'd define your own enum type

we don't guarantee that unspecified may not be used for something "real" in the future (although its very unlikely)

Something like

definition object {
  permission view
  permission view_if_granted
  permission grant_view
}

has_permission = checkPermission("object:1", "view", "user:2")
if not has_permission then
  has_permission = checkPermission("object:1", "view_if_granted", "user:2")
               and checkPermission("object:1", "grant_view", "application:3")
end

This may be totally outside the design philosophy of spicedb but I actually really like the high-impedance metaphor (Z) from electronics. Y+N=exception, but Y+Y=Y, N+N=N, Z+Y=Y, Z+N=N. This is in the case where you'd query two permissions and combine them.

I'm still seeing this insanely high CPU usage for this query on postgres:

SELECT MIN(id), MAX(id) FROM relation_tuple_transaction WHERE timestamp >= $1;

My DB CPU is 100% maxed out by this one query (nothing else is using this DB instance), latency of the query is in minutes. I'm having the same problem on RDS Postgres and RDS Aurora, on an instance that should definitely be big enough (

t4g.large

). The EXPLAIN ANALYZE doesn't look problematic. This is what I get reproducing the query now:

Aggregate  (cost=8.38..8.39 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)
  ->  Index Scan using ix_relation_tuple_transaction_by_timestamp on relation_tuple_transaction  (cost=0.42..8.37 rows=1 width=8) (actual time=0.005..0.006 rows=0 loops=1)
        Index Cond: ("timestamp" >= '2022-03-17 11:17:34'::timestamp without time zone)
Planning Time: 0.183 ms
Execution Time: 0.035 ms

Which seems all OK to me. If I set the timestamp to be a bit more in the past though (like the 15th of March), I do get a different query plan which is much much slower (query plan here: https://discord.com/channels/844600078504951838/844600078948630559/953363762109349938), matching the slowness I'm observing on my live system. I'm guessing that for some reason, Postgres is using the wrong query plan. I've tried playing with the

--datastore-revision-fuzzing-duration

setting to reduce it to 1s, to no avail.

ANALYZE

hasn't helped either. I'd really appreciate help, it's obviously a big blocker to me and given I haven't done any specific configuration and I can reproduce on 2 databases, it's likely a SpiceDB issue? I have traces if that's helpful, but it's not telling me much apart from "this query took X minutes" which I know

do you know (roughly) where it starts using the bad query plan?

also, what happens if you break it into two querys, one with MIN(id) and one with MAX(id)? (just curious)

maybe the inclusion of both is causing postgres to switch to a worse plan

> do you know (roughly) where it starts using the bad query plan? Sorry I don't understand the question 🤔 > also, what happens if you break it into two querys, one with MIN(id) and one with MAX(id)? (just curious) Same problem

> If I set the timestamp to be a bit more in the past though (like the 15th of March), I do get a different query plan which is much much slower I'm curious to know where/when it starts using the slower plan

Postgres driver should have GC that deletes older entries after a point

> I'm curious to know where/when it starts using the slower plan Always, as far as I can see

I have the start of a theory, it involves deep Postgres fun query planning, let me write that down

hmm

it should just be walking the btree index

It seems that with prepared statements (possibly not only prepared statements), "PostgreSQL has a built-in heuristics form statements whose plans can be cached: it plans them with the actual parameter values for the first 5 times, and then it switches to a generic plan if that plan promises not to perform worse. Unfortunately the generic plan doesn't keep the promise in this case." (source here: https://dba.stackexchange.com/questions/289130/postgresql-changes-same-query-plan-at-runtime) So, in my case it's possible that PG takes into account the actual value of the

timestamp

parameter for the first 5 execution: that'll be fast. From the 6th execution onwards, it doesn't consider what value

timestamp

actually has and always applies a generic query plan: this generic query plan is much much slower for the values of

timestamp

we actually use

Whats the recommended way to get my relationships into SpiceDB?

(for production)

@User really depends on your application design: https://authzed.com/blog/writing-relationships-to-spicedb/

there is also the outbox pattern

I can reproduce this >5 execution plan change with a prepared statement for this min/max query indeed. I'm not sure SpiceDB uses prepared statements though, but I'm hopeful this is zeroing in on the root cause. Maybe this behaviour is not only used for prepared statements

@User interesting... I wonder if there is a way we can hint or force Postgres to not do that (if it is indeed the issue)

like... try adding

and timestamp <= (some value that is a bit later than the timestamp given)

theoretically, that should be slower (since it needs to do two filters)

but maybe it is enough to trick postgres

Storing Data

@User do you know if whatever PG lib is used in SpiceDB creates prepared statements under the hood? I'm not seeing anything suggesting it in https://github.com/authzed/spicedb/blob/main/internal/datastore/postgres/postgres.go

If so, there's a

plan_cache_mode

setting that can be set to

force_custom_plan

we use pgx; not sure what it does