Performance issues #5
Replies: 1 comment 1 reply
-
Hi Jeff, Thank you so much for the positive feedback on Verifica 🙏 I'm not well-versed in the GIN index and Postgres nuances, but I can shed light on how we employed Verifica in 2 similar projects I recently worked on. In both, Elasticsearch was central to the architecture. Beyond just authorization queries, it powered user-driven free-text searches and faceted searches where authorization was seamlessly integrated. To illustrate, let's look at your Students table:
One of the standout features of this method is the ability to incorporate user-generated search queries while ensuring only authorized results are shown. For instance, a user might search for Students with the name "John*", but will only see the authorized entries. Something like this: {
"filtered": {
"query": {
"query_string": {
"query": "John*"
}
},
"filter": {
"bool": {
"should": [{
"terms": {
"read_allow_sids": ["sid_1", "sid_2", "etc."]
}
}],
"must_not": [{
"terms": {
"read_deny_sids": ["sid_1", "sid_2", "etc."]
}
}]
}
}
}
} Certainly, you could achieve something similar using just Postgres. However, in my experience, Elasticsearch offered better performance for these queries and was more straightforward to handle. Of course, results might differ based on your specific circumstances. Hope this helps! |
Beta Was this translation helpful? Give feedback.
-
Greetings, and first off thank you @maximgurin for the verifica gem 👏
I was drawn to use your work because it's great combination of flexibility and simplicity--or, in a word, elegance. I was also excited that you'd already tested it in production envs with good performance outcomes.
The Question
Which brings us to my recent issues. I'm getting much worse performance than expected in my application. To summarize the problem:
read_allow_sids
columncurrent_user
's sids may also commonly be hundreds of elements longwhere(read_allow_sids && ARRAY[...]).not(read_deny_sids && ARRAY[...])
).read_allow_sids
andread_deny_sids
. This makes the query execute in 200ms. Nice, that would probably be good enough.SET enable_seqscan TO OFF
, which is obviously not actually a viable option.Conclusion
Have you encountered any performance issues like this? How did you overcome them? Do you know how to get postgres to respect the GIN index?
Thanks for your time, and all the work you put into making this gem!
For reference, this question is also described in this stack overflow question.
Beta Was this translation helpful? Give feedback.
All reactions