Versions Compared

Version Old Version 3 New Version 4
Changes made by

Kalibek Turgumbayev

Kalibek Turgumbayev

Saved on

Key

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

...

Page Properties


Submitted

 

Approved
Status

Status
titleDRAFT

ImpactMEDIUM


Context

Tracking

...

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

Architecturally Significant Requirements

#RequirementDescriptionValidation ApproachRemarks
1PerformanceSearch on table with <X> records should be done in <Y> seconds 
  1. Prepopulate table with X records
  2. Do search by itemBarcode
  3. Validate timing
TBC

Stakeholders

Solution Options

...

#TitleDescriptionProsCons
1Define retention policy (use

...

archiving or table partitioning)

...

  1. Archive the old records (maybe not delete them) in another table or use PostgreSQL partitioning - like 1+ year old record.
  2. 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.
  1. Future-proof solution. Will have same response time in case of the same amount of records per time period
  1. In case of unpredicted growth of transactional volume search can be slow
  2. Partitioning is not used in other modules, hence breaks the unity or common approach
  3. Search in longer date period may still be slow
  4. Not quite clear what is the common period for retention
2Add gin index for nested items → itemBarcode field

Option C - add index for creation date and limit search date range

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


Questions

#QuestionAnswerStatus
1

How many records per year clients produce?


Status
colourRed
titleOpen

2

Do clients have any retention policy or regulations on circulation records?


Status
colourRed
titleOpen

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.

LOE

...

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

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

Decision

<TBD>