CQL fails to upload 300K+ instances with 504 timeout in UI

Purpose/Overview:

Run data export job in https://pre-kiwi-cap1.int.aws.folio.org/ using source=MARC which returns 300K+ inventory instances

Requirements/Scope:

1. Data Export should upload 300K+ instances using CQL file 

Open

Approach:

• In Inventory App in UI select source=MARC in Inventory App

• Generate CQL file

• Use CQL file to run Data Export

Observation:

• When I try to upload cql file with cql query (source==“MARC”) sortby title, job fails with 504 after 10 minutes. Tried multiple times, see

Open

• I see timeout errors for upload API in nginx log

Open

• Database spikes when upload starts and fails

Open

• Postgresql query which takes 11+ minutes

  2021-11-05 18:07:53 UTC:10.23.10.95(39082):fs09000000_mod_inventory_storage@folio:[7652]:LOG: duration: 714122.988 ms execute 0000000/0d93dffc-f6f5-46c1-a76b-5f85b814da4b: SELECT jsonb,id FROM fs09000000_mod_inventory_storage.instance WHERE CASE WHEN length(lower('MARC')) <= 600 THEN left(lower(instance.jsonb->>'source'),600) LIKE lower('MARC') ELSE left(lower(instance.jsonb->>'source'),600) LIKE left(lower('MARC'),600) AND lower(instance.jsonb->>'source') LIKE lower('MARC') END ORDER BY left(lower(f_unaccent(instance.jsonb->>'title')),600), lower(f_unaccent(instance.jsonb->>'title')) LIMIT 2147483647 OFFSET 0

  See attached complete Postgresql log

Acceptance criteria:

• Data Export should Upload a CQL file with cql query source=MARC which returns 300K+ instances

• Migration scripts are provided (optional AC)