Hi Franciszek,
Franciszek Stachura, Sep 02, 2026 at 20:51:
> Hello Robin,
>
> Thanks for the patch. I see that you went with the many-to-many
> approach, like I did in my patch for 3.x. However, I see that 4.x
> also includes optimizations similar to what I implemented my
> optimizations patch.
> I tested two approaches in the Django version: many-to-many and JSON. In
> the JSON approach, assigned labels are stored as a JSON array in the
> patch table.
> While the performance of the two versions was similar on 3.x, the JSON
> version was generally faster with my optimization patches applied.
>
> I went ahead and modified your patch to also store labels as a JSON array.
> I also implemented a data generator and generated ~100k patches with at
> most 5 labels.
>
> You can find my version on Github:
>
> https://github.com/fstachura/patchwork-next/tree/fstachura-main
>
> I'm not going to post it yet as it's in a pretty rough state, but let me
> know if you would like me to finish it.
Yes, please go ahead. It was your original idea. I just kickstarted it
for v4 but I would prefer if you worked on it.
>
> I tested the JSON version on a simple benchmark that crawls pages with
> various combinations of 0-2 labels in filters.
>
> Results: the average request time fell from 1.3 seconds to .3 seconds.
>
> The benchmark is in the repository, see locustfile.py.
>
> Another upside of this approach is that you can index on the
> patch-labels relationship (see GIN indexes). The benchmark was done
> without any extra indexes.
Wow, nice. I hadn't thought about using JSON columns. We should add GIN
indexes if possible.
>
> I'm not yet sure if it would be better to store label ids or label names
> in the array. Ids seem like a more natural choice to me, but storing
> names would make searching a bit simpler, especially in the API.
Names would definitely be better. In fact, we should probably use (name,
project_id) as primary key for the labels table. No need for an ID.
>
> On Postgres, native arrays can be used instead of JSON, MySQL only
> supports JSON.
We could have an abstraction layer that uses the most optimal underlying
column type depending on the database dialect.
postgres: jsonb or native arrays
mysql: json
sqlite: text with some tweaks to make searching possible
>
> Here is a blog post that measured performance of various tagging
> approaches on Postgres:
>
> https://www.databasesoup.com/2015/01/tag-all-things-part-3.html
>
> The author of the post stores an array of tags as a string, so maybe
> that's a clue.
>
> I also noticed that that the labels filter is a simple select, although
> the web path supports filtering by many labels. Django Patchwork uses
> selectize and I would advise finding another autocomplete library as
> selectize was archived recently. Maybe tom-select would be OK?
> It's basically a continuation of selectize. I also know of Choices,
> autocomplete.js and select2. But Selectize in my experience was mostly
> OK, so I think tom-select should be OK too.
I had not done anything special for the web UI. But I don't mind adding
"some" javascript as long as it remains lightweight and maintained.
Do we need to rely on a library for this? Most of the existing offerings
are completely overkill for the needs of patchwork. Maybe we could write
our own. Otherwise, autocomplete.js seems to be the smallest.
--
Robin
# May be too intense for some viewers.
_______________________________________________
Patchwork mailing list
[email protected]
https://lists.ozlabs.org/listinfo/patchwork
