[Onyx Audit] Inefficient DB operations

[fabioh8010]

Overview

This issue is part of our current effort to audit the performance and scalability of Onyx.

During the Step 1: Objective Analysis we gathered a list of Perceived Problems we have exclusively about Onyx, that is, the problems in Onyx we imagine or have some evidences that that are pain points / performance bottenecks to Onyx, but we need more analysis to confirm if they really are and, if so, how much of a problem they are currently.

The main goal right now is to deep dive into each Perceived Problem, investigate/audit it and come up with findings, which we will use for the next discussions.

Problem

At the moment we use SQLite (react-native-nitro-sqlite) in native platforms and LocalStorage (idb-keyval) in web to store user's data. In both providers, all data is stored in one table as key-value pairs. In the Native provider we use SQL operations to read/write data, but we noticed that they don't seem too much performant. For example, we measured in the past that a simple setItem() operation was taking longer than multiMerge()/mergeItem() even for simple objects, which seem contradictory as we are literally asking DB to fully replace that object and it is somehow slower than doing a merge operation.

Additionally, we don't have means to measure them automatically with unit/Reassure tests, so it's difficult to know if they are working as expected, how they are performing and if these providers need improvements or not.

Action Items

1. Audit the two providers and measure how they performing in each one of their operations.
2. Research if it's possible to create automated tests for them.

Relevant Links

Step 1 Canvas: https://expensify.slack.com/docs/T02P6RS2ZK7/F09B668M1MG

Slack thread: https://expensify.slack.com/archives/C05LX9D6E07/p1757416490706049

[fabioh8010]

Updates:

• Starting to look at this today!

[fabioh8010]

Updates:

• Didn't have much progress last week – was dragged into other more critical stuff. Resuming work this week.

[fabioh8010]

Updates:

• Working on the investigations, will wrap up it soon.
• Next steps will be investigating the Onyx errors that we are having sometimes.
• And this "Research if it's possible to create automated tests for them." but probably nothing will come out.

[fabioh8010]

Audit the two providers and measure how they performing in each one of their operations.

DB audit

For this audit I analysed the two providers we have (IDB and SQLite) and performed a series of measurements around the Storage functions we have. For each operation we have a table with the data type used to measure and the execution time of the functions involved from left to right.

IDBKeyValProvider (Web)

setItem (100 executions)

Data type	Storage.setItem (ms)	IDBKeyValProvider.setItem (ms)	IDBKeyValProvider.removeItem (ms)
null	1.15	0.93	0.57
string	0.73	0.46	-
number	0.76	0.48	-
empty object	0.70	0.44	-
randomized report action object	0.65	0.40	-

multiSet (1000 records; 100 executions)

Data type	Storage.multiSet (ms)	IDBKeyValProvider.multiSet (ms)
null	277.14	269.96
string	70.62	63.78
number	70.62	63.90
empty object	69.93	63.16
randomized report action object	89.35	82.42

mergeItem (100 executions)

Data type	Storage.mergeItem (ms)	IDBKeyValProvider.mergeItem (ms)	IDBKeyValProvider.multiMerge (ms)	IDBKeyValProvider.removeItem (ms)
null	0.66	0.48	0.38	0.59
string	0.82	0.66	0.55	-
number	0.86	0.68	0.57	-
empty object	0.78	0.61	0.50	-
randomized report action object	0.86	0.68	0.57	-

multiMerge (1000 records; 100 executions)

Data type	Storage.multiMerge (ms)	IDBKeyValProvider.multiMerge (ms)
string	82.63	75.91
number	82.39	75.64
empty object	79.55	72.94
randomized report action object	101.87	95.03

getItem (100 executions)

Data type	Storage.getItem (ms)	IDBKeyValProvider.getItem (ms)
null	0.47	0.26
string	0.52	0.28
number	0.49	0.25
empty object	0.49	0.25
randomized report action object	0.54	0.28

multiGet (1000 records; 100 executions)

Data type	Storage.multiGet (ms)	IDBKeyValProvider.multiGet (ms)
null	22.96	22.08
string	23.99	23.06
number	24.72	23.82
empty object	22	21.15
randomized report action object	29.26	25.26

SQLiteProvider (Native)

setItem (100 executions)

Data type	Storage.setItem (ms)	SQLiteProvider.setItem (ms)
null	2.28	1.50
string	3.23	2.10
number	2.82	1.78
empty object	2.82	1.78
randomized report action object	2.65	1.94

multiSet (1000 records; 100 executions)

Data type	Storage.multiSet (ms)	SQLiteProvider.multiSet (ms)
null	20.37	18.60
string	22.05	20.02
number	22.05	20.44
empty object	25.49	23.32
randomized report action object	39.90	39.21

mergeItem (100 executions)

Data type	Storage.mergeItem (ms)	SQLiteProvider.mergeItem (ms)	SQLiteProvider.multiMerge (ms)
null	2.86	1.68	1.03
string	2.87	1.82	1.19
number	2.37	1.23	0.63
empty object	2.69	1.37	0.71
randomized report action object	3.67	2.21	1.67

multiMerge (1000 records; 100 executions)

Data type	Storage.multiMerge (ms)	SQLiteProvider.multiMerge (ms)
null	18.87	17.28
string	19.70	18.24
number	19.85	18.17
empty object	4.07	2.04
randomized report action object	58.91	57.64

getItem (100 executions)

Data type	Storage.getItem (ms)	SQLiteProvider.getItem (ms)
null	2.38	0.88
string	2.60	1.27
number	1.93	0.86
empty object	2.23	0.84
randomized report action object	2.20	1.17

multiGet (1000 records; 100 executions)

Data type	Storage.multiGet (ms)	SQLiteProvider.multiGet (ms)
null	27.85	26.22
string	31.28	29.10
number	21.24	19.97
empty object	21.16	19.68
randomized report action object	46.04	45.16

---

First issues noticed

🐞 IDBKeyValProvider - setItem removes and redundantly sets the value to null

When passing null to setItem, it will remove the item in DB by calling removeItem and also set the item to null right after it. It's a minor issue because when removing keys we would call removeItem directly, but it's still wrong and it's pretty easy to fix – make a early return after the removeItem call. It might be worth to check if we are supposed to have null values being passed to multiMerge too.

🐞 IDBKeyValProvider - multiSet calls multiple removeItem's instead of delMany

When calling multiSet with some keys that we want to be removed (null value), this logic will call one separate removeItem for each key. This could be optimized to get the list of keys that we want to remove and then just call delMany passing all them, same as we do with setMany in the line below. This optimization could reduce the execution time for multiSet reported above.

🐞 IDBKeyValProvider - multiMerge calls multiple removeItem's instead of using the transaction

When calling multiMerge with some keys that we want to be removed (null value), this logic will call one separate removeItem for each key. This could be optimized to get the list of keys that we want to remove and then call store.delete for each one in that transaction, same as we do with store.put in the line below. It might be worth to check if we are supposed to have null values being passed to multiMerge too.

---

Early conclusions so far

• In the IDB provider, the execution times seem acceptable (expect the cases mentioned above). In the SQLite provider, it seems that SET operations are not way slower as I initially measured before, the difference with MERGE operations being irrelevant.

Next steps

• Investigate how effectively we are handling DB errors in Onyx (we know there are some problems with it).
• Investigate each SQL query of SQLiteProvider to check if they are sufficiently efficient for our needs, or if we have room for improvement here.
• Consider the idea of applying whole Onyx.update operations as a single transaction – possibly with a new Storage method dedicated to it. Measure the performance of having this approach.
• Research if it's possible to create automated tests for them.

[fabioh8010]

Updates:

Investigate how effectively we are handling DB errors in Onyx (we know there are some problems with it).

@VickyStash is handling this one, some early analysis here.

Investigate each SQL query of SQLiteProvider to check if they are sufficiently efficient for our needs, or if we have room for improvement here.

I started this one.

[fabioh8010]

Updates:

Investigate how effectively we are handling DB errors in Onyx (we know there are some problems with it).

Being mostly addressed here and here.

Investigate each SQL query of SQLiteProvider to check if they are sufficiently efficient for our needs, or if we have room for improvement here.

Went back to this one today, analysing some options and ideas with AI.

Research if it's possible to create automated tests for them.

For this one, the main challenge was to test the native provider (SQL) in Jest environment, but this might not be impossible anymore with https://www.react-native-harness.dev/ 🎉 It's an experimental library being developed at Callstack but it would provide means for us to execute tests in native environments, making possible test the native providers in Android and iOS.

For web (IDB), I think we can use https://github.com/dumbmatter/fakeIndexedDB to emulate IndexedDB in-memory and perform tests normally with Jest.

[fabioh8010]

SQL Improvements

I experimented some improvements in the SQL queries/provider logic (commit):

• multiGet optimization (chunked / temp-table join): Reduce query parsing and variable binding overhead from very long IN (?, ?, ...) clauses, and improve scalability for large batches.
• Removing json() wrappers from SQL statements: Eliminate redundant json() calls around parameters, which parse and re-encode JSON strings inside SQLite.

Operation (1000 records; 100 runs)	Before (ms)	After (ms)	Difference (%)
multiGet	43.64	44.32	+1.56%
multiMerge	62.37	50.03	-19.80%
multiSet	44.37	42.18	-4.93%

Operation (10000 records; 20 runs)	Before (ms)	After (ms)	Difference (%)
multiGet	394.49	345.81	-12.33%
multiMerge	4430.10	2583.76	-41.70%
multiSet	3175.12	2859.72	-9.92%

• The multiGet difference was insignificant in terms of performance for 1000 records, but we got good results when running operations with 10000 records. We need to double-check and validate the logic if we want to move with this, but I believe it makes sense and we could even remove the small batch part because measurements shown it was already irrelevant for 1000 records.
• We got very good results from multiMerge and multiSet in both measurements, due to the removal of the useless json(). They are not needed because we already perform JSON parsing/validation when stringifying the JSON.
• One interesting thing I noticed is that I had to reduce the number of runs for 10000 records because multiGet for example was making the app crash after some amount of runs.

[fabioh8010]

MMKV Storage

• I implemented a new provider using react-native-mmkv and did some benchmark (commit).

Operation (1000 records; 100 runs)	Before (ms)	After (ms)	Difference (%)
getItem	1.87	1.36	-27.27%
mergeItem	0.35	0.92	+162.86%
multiGet	43.28	24.40	-43.62%
multiMerge	52.01	82.03	+57.72%
setItem	1.93	0.61	-68.39%
multiSet	39.73	18.52	-53.39%

• ✅ getItem, multiGet, setItem and multiSet were considerably faster.
• ✅ Results in most operations were better even without a batching mechanism like we have in SQLite/IDB. If we had these mechanisms results could be even better!
• ❌ Although majority of the operations were faster, they completely blocked JS thread during measurements due to being synchronous.
• ❌ mergeItem and multiMerge got significantly worse, probably because I need to get each key from DB, merge the value with fastMerge and then set it again (like we do in IDBKeyValProvider). This summing with the fact we don't have batch operations in MMKV contributed to these results.

Conclusions:

• Many operations are faster and could be even more with a batching mechanism in MMKV, but they block the JS thread and this could nullify or worse the results in real life scenarios which weren't covered in these measurements.
• To have MMKV we either need to have this batching mechanism OR change the way we store collections (store the whole object instead of separate keys), but this would bring other issues too (what would be the best way get single collection keys?).
• Maybe if we move these operations to a separate thread (worklet) we could solve the JS blocked thread issue (assuming it's a problem in real-life scenarios), but I don't really know if this is feasible or if it even make sense (MMKV was designed to be sync).