...
| Page Properties |
|---|
| Submitted | |
|---|
| Approved |
|
|---|
| Status | |
|---|
| Impact | |
|---|
|
Context
Tracking
| Type | Ticket | Module |
|---|
| Module ticket | | Jira Legacy |
|---|
| server | System Jira |
|---|
| serverId | 01505d01-b853-3c2e-90f1-ee9b165564fc |
|---|
| key | MODAUD-155 |
|---|
|
| mod-audit |
| Arch ticket | | Jira Legacy |
|---|
| server | System Jira |
|---|
| serverId | 01505d01-b853-3c2e-90f1-ee9b165564fc |
|---|
| key | ARCH-51 |
|---|
|
|
|
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.
Circulation log search by item barcode slow when table contains millions of rows.
| # | Requirement | Description | Validation Approach | Remarks |
|---|
| 1 | Performance | Search on table with <X> records should be done in <Y> seconds | - Prepopulate table with X records
- Do search by itemBarcode
- Validate timing
| TBC |
- Steve Ellis - Tech Lead
- Gurleen Kaur1 - Dev Lead
- Irina Pokhylets - Business Analyst
| # | 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
|
| # | Question | Answer | Status |
|---|
| 1 | How many records per year clients produce? |
| |
| 2 | Do clients have any retention policy or regulations on circulation records? |
| |
Option 1 depends on client needs that might be hard to define for the whole community.
...
Proposed solution is option 2 due to minimal changes on backend and no changes at frontend
| # | 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
|
<TBD>