You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
Audit the two providers and measure how they performing in each one of their operations.
Research if it's possible to create automated tests for them.
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.
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.
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.
✅ 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).
I implemented a new provider using op-sqlite and did some benchmark (commit).
Operation (1000 records; 100 runs)
Before (ms)
After (ms)
Difference (%)
getItem
1.87
2.82
+50.80%
mergeItem
0.35
0.30
-14.29%
multiGet
43.28
40.00
-7.58%
multiMerge
52.01
81.50
+56.70%
multiSet
39.73
109.47
+175.53%
setItem
1.93
5.20
+169.43%
❌ mergeItem, multiGet were a bit faster, but insignificantly.
❌ All the other operations were considerably worse in performance.
Conclusions:
This one got me puzzled because they claim to be faster than Nitro SQLite in their GH page, which got me thinking whether I implemented this very wrongly or if it's something else 😅.
I didn't want to dig deeper on this one but I think it's something to have in mind and we could experiment going back to it on Onyx v2, in case this library has some feature Nitro SQLite doesn't.
changed the title [-][Onyx Audit] Inefficient DB operations[/-][+][Due for payment 2026-01-21] [Onyx Audit] Inefficient DB operations[/+]on Jan 14, 2026
The solution for this issue has been 🚀 deployed to production 🚀 in version 9.3.1-1 and is now subject to a 7-day regression period 📆. Here is the list of pull requests that resolve this issue:
changed the title [-][Due for payment 2026-01-21] [Onyx Audit] Inefficient DB operations[/-][+][Onyx Audit] Inefficient DB operations[/+]on Jan 22, 2026
Activity
fabioh8010 commentedon Sep 29, 2025
Updates:
fabioh8010 commentedon Oct 6, 2025
Updates:
fabioh8010 commentedon Oct 8, 2025
Updates:
fabioh8010 commentedon Oct 13, 2025
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)
multiSet (1000 records; 100 executions)
mergeItem (100 executions)
multiMerge (1000 records; 100 executions)
getItem (100 executions)
multiGet (1000 records; 100 executions)
SQLiteProvider (Native)
setItem (100 executions)
multiSet (1000 records; 100 executions)
mergeItem (100 executions)
multiMerge (1000 records; 100 executions)
getItem (100 executions)
multiGet (1000 records; 100 executions)
First issues noticed
🐞 IDBKeyValProvider - setItem removes and redundantly sets the value to null
When passing
nulltosetItem, it will remove the item in DB by callingremoveItemand also set the item to null right after it. It's a minor issue because when removing keys we would callremoveItemdirectly, but it's still wrong and it's pretty easy to fix – make a early return after theremoveItemcall. 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
multiSetwith some keys that we want to be removed (nullvalue), this logic will call one separateremoveItemfor each key. This could be optimized to get the list of keys that we want to remove and then just calldelManypassing all them, same as we do withsetManyin the line below. This optimization could reduce the execution time formultiSetreported above.🐞 IDBKeyValProvider - multiMerge calls multiple removeItem's instead of using the transaction
When calling
multiMergewith some keys that we want to be removed (nullvalue), this logic will call one separateremoveItemfor each key. This could be optimized to get the list of keys that we want to remove and then callstore.deletefor each one in that transaction, same as we do withstore.putin 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
Next steps
Onyx.updateoperations as a single transaction – possibly with a new Storage method dedicated to it. Measure the performance of having this approach.fabioh8010 commentedon Oct 27, 2025
Updates:
@VickyStash is handling this one, some early analysis here.
I started this one.
fabioh8010 commentedon Nov 3, 2025
Updates:
Being mostly addressed here and here.
Went back to this one today, analysing some options and ideas with AI.
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 commentedon Nov 14, 2025
SQL Improvements
I experimented some improvements in the SQL queries/provider logic (commit):
multiGetoptimization (chunked / temp-table join): Reduce query parsing and variable binding overhead from very longIN (?, ?, ...)clauses, and improve scalability for large batches.json()wrappers from SQL statements: Eliminate redundantjson()calls around parameters, which parse and re-encode JSON strings inside SQLite.multiGetdifference 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.multiMergeandmultiSetin both measurements, due to the removal of the uselessjson(). They are not needed because we already perform JSON parsing/validation when stringifying the JSON.multiGetfor example was making the app crash after some amount of runs.fabioh8010 commentedon Nov 16, 2025
MMKV Storage
getItem,multiGet,setItemandmultiSetwere considerably faster.mergeItemandmultiMergegot significantly worse, probably because I need to get each key from DB, merge the value withfastMergeand 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:
fabioh8010 commentedon Nov 16, 2025
OP-SQLite
mergeItem,multiGetwere a bit faster, but insignificantly.Conclusions:
20 remaining items
[-][Onyx Audit] Inefficient DB operations[/-][+][Due for payment 2026-01-21] [Onyx Audit] Inefficient DB operations[/+]melvin-bot commentedon Jan 14, 2026
Reviewinglabel has been removed, please complete the "BugZero Checklist".melvin-bot commentedon Jan 14, 2026
The solution for this issue has been 🚀 deployed to production 🚀 in version 9.3.1-1 and is now subject to a 7-day regression period 📆. Here is the list of pull requests that resolve this issue:
If no regressions arise, payment will be issued on 2026-01-21. 🎊
For reference, here are some details about the assignees on this issue:
[-][Due for payment 2026-01-21] [Onyx Audit] Inefficient DB operations[/-][+][Onyx Audit] Inefficient DB operations[/+]fabioh8010 commentedon Jan 22, 2026
@mountiny @tgolen
Coming from here. Let's close this one:
I will create the two child issues soon.
mallenexpensify commentedon Jun 19, 2026
Payment Summary
Contributor+: @shubham1206agra due $375 via NewDot
JmillsExpensify commentedon Jul 12, 2026
$375 approved for @shubham1206agra