...
| # | 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)
Update (Apr 4th, 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 Removed.png?version=1&modificationDate=1681120909000&cacheVersion=1&api=v2&height=150) Image Removed
| - 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
|
...
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:
- Image AddedImage Added
|
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
|
...