...
| Type | Ticket | Module |
|---|
| Module ticket | | Jira Legacy |
|---|
| server | System JiraJIRA |
|---|
| serverId | 01505d01-b853-3c2e-90f1-ee9b165564fc |
|---|
| key | MODAUD-155 |
|---|
|
| mod-audit |
| Arch ticket | | Jira Legacy |
|---|
| server | System JiraJIRA |
|---|
| serverId | 01505d01-b853-3c2e-90f1-ee9b165564fc |
|---|
| key | ARCH-51 |
|---|
|
|
|
...
Proposed solution is option 2 due to minimal changes on backend and no changes at frontend
Update on option 2 (Apr 10th, 2023): - Gin indexes are already present in the table creation script
- Queries that are generated by
PostgresClient do not hit the indexes. The library auto-generated SQL Query example is:
| Code Block |
|---|
| SELECT id, jsonb
FROM %schema%_mod_audit.circulation_logs
where ((circulation_logs.jsonb->>'items') @@ ('BATTLE10')) LIMIT 100 OFFSET 0; |
- To enable the index in the search the query should be rewritten in this form:
| Code Block |
|---|
| select id, jsonb
from %schema%_mod_audit.circulation_logs
where %schema%_mod_audit.get_tsvector(%schema%_mod_audit.f_unaccent(jsonb ->> 'items'::text))@@ plainto_tsquery('%BATTLE10%') LIMIT 100 OFFSET 0; |
- Below are screenshots of query execution plan for both queries:
.png?version=1&modificationDate=1681120910000&cacheVersion=1&api=v2&height=150) Image Modified.png?version=1&modificationDate=1681120909000&cacheVersion=1&api=v2&height=150) Image Modified
|
Update on option 2 (Apr 18th, 2023): - The SQL generation by PostgresClient already contains options for invoking full-text search with
gin-indexes . The description of mechanism is located in https://dev.folio.org/faqs/explain-cql/. Every approach has its tradeoffs and possible solution may be to leave the choice for users. The available options are as following:- Exact search: requires input of full barcode (e.g. type "12345" to find "12345" barcode). Fastest
- Left-anchor search: requires input starting symbols of barcode (e.g. type "123" to find "12345" barcode).
- "Contains" search: requires input of any substring in barcode (e.g. type "234" to find "12345" barcode). Slowest option
|
Estimation
| # | Title | Estimation |
|---|
| 1 | Define retention policy (use archiving or table partitioning) | - POC - 1 sprint
- Implementation -2 sprints
- Performance testing - 1 sprint
|
| 2 | Add gin index for nested items → itemBarcode field | - Implementation - 1 sprint
- Performance testing - 1 spring
|
| 3 | Limit search date range | - Implementation - 1 sprint
- Performance testing - 1 spring
|
...