You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When investigating slow query logs in our database, we found that supabase_auth_admin accounts for the absolute majority of slow queries. One of the slow queries was the deletion of refresh_tokens by user_id.
Looking at the table definition, there is an index on instance_id + user_id. As the instance_id field is not part of the query, it seems to significantly slow down the queries.
Without instance id (35ms)
explain analyze delete from auth.refresh_tokens where user_id = 'd9e8a26c-5a31-43e8-833a-9fcb7ceda191'
Delete on refresh_tokens (cost=0.43..13318.67 rows=0 width=0) (actual time=34.693..34.693 rows=0 loops=1)
-> Index Scan using refresh_tokens_instance_id_user_id_idx on refresh_tokens (cost=0.43..13318.67 rows=31 width=6) (actual time=34.691..34.691 rows=0 loops=1)
Index Cond: ((user_id)::text = 'd9e8a26c-5a31-43e8-833a-9fcb7ceda191'::text)
Planning Time: 1.229 ms
Execution Time: 34.876 ms
With instance id (0.271ms)
Delete on refresh_tokens (cost=0.43..17.68 rows=0 width=0) (actual time=0.095..0.095 rows=0 loops=1)
-> Index Scan using refresh_tokens_instance_id_user_id_idx on refresh_tokens (cost=0.43..17.68 rows=31 width=6) (actual time=0.094..0.094 rows=0 loops=1)
Index Cond: ((instance_id = '00000000-0000-0000-0000-000000000000'::uuid) AND ((user_id)::text = 'd9e8a26c-5a31-43e8-833a-9fcb7ceda191'::text))
Planning Time: 1.192 ms
Execution Time: 0.271 ms
Screenshots
The text was updated successfully, but these errors were encountered:
…ueries (#1454)
## What kind of change does this PR introduce?
Use the nil instance ID so that we can leverage the compound index on
instance_id and user_id when performing search and deletion
Aims to address #1449 and #1398.
We note that `LogoutAllRefreshTokens` was marked as deprecated and may
be possible to remove if all access tokens now have a `sessionId`.
Additionally, `FindTokenBySessionID` can likely be replaced by using
`FindCurrentlyActiveRefreshToken`.
We will revisit these in a week or two but they are out of scope for
this PR.
Co-authored-by: joel <joel@joels-MacBook-Pro.local>
Bug report
Describe the bug
When investigating slow query logs in our database, we found that supabase_auth_admin accounts for the absolute majority of slow queries. One of the slow queries was the deletion of refresh_tokens by user_id.
Looking at the table definition, there is an index on
instance_id
+user_id
. As theinstance_id
field is not part of the query, it seems to significantly slow down the queries.Without instance id (35ms)
With instance id (0.271ms)
Screenshots
The text was updated successfully, but these errors were encountered: