Versions Compared

Version Old Version 13 New Version Current
Changes made by

Kalibek Turgumbayev

Kalibek Turgumbayev

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

TypeTicketModule
Module ticket

Jira Legacy
serverSystem JiraJIRA
serverId01505d01-b853-3c2e-90f1-ee9b165564fc
keyMODAUD-155

mod-audit
Arch ticket

Jira Legacy
serverSystem JiraJIRA
serverId01505d01-b853-3c2e-90f1-ee9b165564fc
keyARCH-51


...

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
languagesql
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
languagesql
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:

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

#TitleEstimation
1Define retention policy (use archiving or table partitioning)
  1. POC - 1 sprint
  2. Implementation -2 sprints
  3. Performance testing - 1 sprint
2Add gin index for nested items → itemBarcode field
  1. Implementation - 1 sprint
  2. Performance testing - 1 spring
3Limit search date range
  1. Implementation - 1 sprint
  2. Performance testing - 1 spring

...