/
New additional indexes in instance table [ECS]

New additional indexes in instance table [ECS]

Overview

  • This document contains the results of the test with new indexes added to mod-inventory-storage.instance table on asptc [ECS]. The testing involved single and bulk POST create, update requests.

Jiras:

Summary

  • All tests completed successfully. No errors.

  • Additional indexes:

1

instance_creation_date_idx

2

instance_cataloged_date_idx

3

instance_publication_date_idx

4

instance_subjects_values_idx

5

instance_format_ids_idx

6

instance_identifiers_values_idx

7

instance_notes_instancenotesnotetypeid_idx

have minimal impact on single create/edit/delete operations, but significantly degrade concurrent bulk create/edit workflows

  • Minimal impact on single create/edit/delete operations (delta for create/edit was 2 ms)

  • Under 20 virtual users, average response time increased by 28–45%, and retesting showed even stronger degradation of 69–73%.

  • With indexes enabled, service CPU utilization increased significantly, especially for mod-search-b, mod-audit-b, and mod-linked-data-b. The run with indexes generated much higher service CPU pressure during bulk create/edit operations.

  • RDS CPU reached approximately 97% in both tests.

Test Runs & Flows 

Test flows

# of flows

Flow description

# of flows

Flow description

1

Single Create, Edit, Delete instance

2

Batch Create, Edit 500 instances per call by 20 vUsers

Test Results

This tables contain indexes impact response times results for all tested flows.

Indexes have almost no impact on single create/delete/edit operations, where average latency changes by only 0–2 ms. However, batch create/edit operations show visible degradation with indexes. Under 20 concurrent vUsers, average response time increased by 28–45% in the first run and by 69–73% in the retest.

Single create

With indexes test was stopped manually, no errors, response times were consistent so no need to retest it.

Transaction

Without indexes

With indexes

Delta Avg, ms

Requests, #

Average, ms

pct90

pct95

Requests, #

Average, ms

pct90

pct95

TC: Create

50000

35

55

77

47507

37

66

86

2

TC: Delete

50000

17

30

40

47507

17

31

42

0

TC: Edit instance

50000

48

76

114

47507

50

88

113

2

Batch create

1 vUser × 500 instances × 5 times

Transaction

Without indexes

With indexes

Delta Avg, ms

Delta Avg, %

Requests, #

Average, ms

pct90

pct95

Requests, #

Average, ms

pct90

pct95

TC: Create

5

3021

3197

4321

5

3398

3372

5248

377

12%

TC: Edit instance

5

1277

1291

1406

5

1490

1433

1830

213

17%

20 vUsers × 500 instances × 5 times

Transaction

Without indexes

With indexes

Delta Avg, ms

Delta Avg, %

Requests, #

Average, ms

pct90

pct95

Requests, #

Average, ms

pct90

pct95

TC: Create

100

11800

13484

14473

100

15125

17186

17369

3325

28%

TC: Edit instance

100

9260

10917

11134

100

13469

15122

20953

4209

45%

Retesting

20 vUsers × 500 instances × 5 times

The test was performed from load generator in US-east-1 zone, the same where testing environment located so that’s why we see difference in averages for tests without indexes while retesting.

Transaction

Without indexes

With indexes

Delta Avg, ms

Delta Avg, %

Requests, #

Average, ms

pct90

pct95

Requests, #

Average, ms

pct90

pct95

TC: Create

100

8262

11749

12245

100

13931

17409

28157

5669

69%

TC: Edit instance

100

7656

10257

10522

100

13236

15050

28772

5580

73%

 

Response times

Test flow with indexes

image-20260521-100508.png

 

Test flow without indexes

image-20260521-100348.png

Resource utilization

Service CPU Utilization Comparison

Service

With indexes CPU peak

Without indexes CPU peak

Delta

Growth,% in flow with indexes

Service

With indexes CPU peak

Without indexes CPU peak

Delta

Growth,% in flow with indexes

mod-audit-b

331.6%

196.4%

+135.2

+69%

mod-search-b

329.6%

159.8%

+169.8

+106%

mod-consortia-keycloak-b

161.9%

76.8%

+85.1

+111%

mod-linked-data-b

135.2%

49.0%

+86.2

+176%

mod-inn-reach-b

102.9%

69.0%

+33.9

+49%

mod-inventory-storage-b

68.9%

41.3%

+27.6

+67%

mod-source-record-storage-b

39.7%

39.5%

+0.2

~0%

kong-b

30.1%

30.9%

-0.8

-3%

mod-data-export-worker-b

24.5%

24.4%

+0.1

~0%

mod-pubsub-b

19.2%

19.1%

+0.1

~0%

mod-orders-storage-b

17.1%

16.6%

+0.5

+3%

Test flow with indexes

image-20260521-100703.png

Test flow without indexes

image-20260521-100741.png

Service Memory Utilization

Memory usage is mostly flat across the tests, no spikes or memory leaks.

image-20260521-112018.png

 

DB CPU Utilization

Bulk create/edit operations were performed with saturated database for a short time period. But it’s expected with this load.

Test window

Time range

DB CPU peak

Observation

Test window

Time range

DB CPU peak

Observation

With indexes

~09:51–09:56 UTC

~96–97%

DB CPU reached saturation

Without indexes

~10:04–10:09 UTC

~96–97%

image-20260521-112423.png

 

DB Connections

DB connections during all tests were around 1040 in both test flows

image-20260521-112705.png

 

DB load

Top SQL Comparison During Tests

Comparison of database load and query latency with indexes vs without indexes.

SQL / Operation

With indexes

Without indexes

Difference, Avg latency/%

AAS

Calls/sec

Avg latency, ms

AAS

Calls/sec

Avg latency, ms

INSERT INTO cs00000int_mod_inventory_storage.instance (id, jsonb) VALUES ($1, $2...)

7.37

185.88

27.47

7.65

247.77

18.94

62 ms/45%

SELECT upsert(?, $1::uuid, $2::jsonb)

6.76

211.32

24.64

6.89

254.21

17.29

7 ms/42%

select sie1_0.id, sie1_0.created_by, sie1_0.created_date, sie1_0.error, sie1_0...

0.71

192.35

3.01

0.47

170.05

1.95

-

INSERT INTO cs00000int_mod_audit.instance_audit (event_id, event_date, entity_id...)

0.53

407.99

0.17

0.29

342.39

0.14

-