Page Comparison

Context

Tracking

Technical details

The circulation log data is persisted in jsonb  format in FOLIO database and itemBarcode  field is part of the nested array items as each circulation log record can contain multiple items. No indexes are present for the table.

Problem Statement

Circulation log search by item barcode slow when table contains millions of rows.

Architecturally Significant Requirements

Stakeholders

• Tim Auger - Product Owner
• Steve Ellis - Tech Lead
• Gurleen Kaur1 - Dev Lead
• Irina Pokhylets - Business Analyst

Solution Options

...

#	Title	Description	Pros	Cons
1	Define retention policy (use

...

archiving or table partitioning)

...

Archive the old records (maybe not delete them) in another table or use PostgreSQL partitioning - like 1+ year old record. Create and add an index for createTimeStamp column. This will restrict the search to most recent actions that are taken on the item and improves the search performance.	Future-proof solution. Will have same response time in case of the same amount of records per time period	In case of unpredicted growth of transactional volume search can be slow Partitioning is not used in other modules, hence breaks the unity or common approach Search in longer date period may still be slow Not quite clear what is the common period for retention
2	Add gin index for nested items → itemBarcode field

...

Create gin index for itemBarcode field (see link)	Improves performance Requires minimal backend changes	May negatively affect performance of writing due to calculation of index. Has limitations
3	Limit search date range	Make UI from / to fields in search view mandatory Force maximum date range to one year If further search is required add controls to go to next or previous period	No backend changes required Can serve as a workaround	Limits user experience

Questions

Rationale

Option A 1 depends on client needs that might be hard to define for the whole community.

Option B 2 should be implemented in any case but requires additional performance testing for writing into circulation log.

Option C 3 requires minimal effort but have has tradeoffs. Can be implemented as quick fix.

Proposed solution is option 2 due to minimal changes on backend and no changes at frontend

SELECT id, jsonb
    FROM %schema%_mod_audit.circulation_logs 
where ((circulation_logs.jsonb->>'items') @@ ('BATTLE10')) LIMIT 100 OFFSET 0;

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;

...

LOE

...

Estimation

Decision

Proposed solution is option 2 - <TBC>