Skip to content

[Onyx Audit] Inefficient DB operations #71203

Closed

Description

@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

Issue OwnerCurrent Issue Owner: @mallenexpensify

Activity

  1. fabioh8010 commented on Sep 29, 2025

    @fabioh8010
    ContributorAuthor

    Updates:

    • Starting to look at this today!
  2. fabioh8010 commented on Oct 6, 2025

    @fabioh8010
    ContributorAuthor

    Updates:

    • Didn't have much progress last week – was dragged into other more critical stuff. Resuming work this week.
  3. fabioh8010 commented on Oct 8, 2025

    @fabioh8010
    ContributorAuthor

    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.
  4. fabioh8010 commented on Oct 13, 2025

    @fabioh8010
    ContributorAuthor

    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.
  5. fabioh8010 commented on Oct 27, 2025

    @fabioh8010
    ContributorAuthor

    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.

  6. fabioh8010 commented on Nov 3, 2025

    @fabioh8010
    ContributorAuthor

    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.

  7. fabioh8010 commented on Nov 14, 2025

    @fabioh8010
    ContributorAuthor

    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.
  8. fabioh8010 commented on Nov 16, 2025

    @fabioh8010
    ContributorAuthor

    MMKV Storage

    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).
  9. fabioh8010 commented on Nov 16, 2025

    @fabioh8010
    ContributorAuthor

    OP-SQLite

    • 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.
  10. 20 remaining items

  11. changed the title [-][Onyx Audit] Inefficient DB operations[/-] [+][Due for payment 2026-01-21] [Onyx Audit] Inefficient DB operations[/+] on Jan 14, 2026
  12. melvin-bot commented on Jan 14, 2026

    @melvin-bot

    Reviewing label has been removed, please complete the "BugZero Checklist".

  13. melvin-bot commented on Jan 14, 2026

    @melvin-bot

    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:

  14. changed the title [-][Due for payment 2026-01-21] [Onyx Audit] Inefficient DB operations[/-] [+][Onyx Audit] Inefficient DB operations[/+] on Jan 22, 2026
  15. fabioh8010 commented on Jan 22, 2026

    @fabioh8010
    ContributorAuthor

    @mountiny @tgolen

    Coming from here. Let's close this one:

    • Most of it was completed, except Performance improvements to SQLiteProvider and investigate SQL for web that we agreed to turn into 2 child issues.
    • We can also make the SQLiteProvider one critical, I have most of the code done I think.

    I will create the two child issues soon.

  16. mallenexpensify commented on Jun 19, 2026

    @mallenexpensify
    Contributor

    Payment Summary

    Contributor+: @shubham1206agra due $375 via NewDot

  17. JmillsExpensify commented on Jul 12, 2026

    @JmillsExpensify
    Contributor

    $375 approved for @shubham1206agra

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Labels

Awaiting PaymentAuto-added when associated PR is deployed to productionDailyKSv2Task

Type

No type

Projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions