Performance Issue On Local Database Query (~11s For ~2k Docs)

by ADMIN 62 views

Context

When working with databases in React Native applications, performance is crucial to ensure a seamless user experience. In this article, we will explore a performance issue related to querying a local PouchDB database using the pouchdb-adapter-react-native-sqlite adapter. This adapter is used to query a PouchDB database that has been replicated from a remote CouchDB database.

Setup

To better understand the issue, let's take a look at the setup used in this scenario:

  • React Native: 0.76.9
  • Expo: 52.0.46
  • Hermes: Enabled
  • New React Native architecture: Enabled
  • Adapter: react-native-sqlite via this library
  • Doc count: ~2162 documents

Problem

The problem arises when querying all documents from the local database using the localDB.allDocs({ include_docs: true }); method. This query takes approximately ~11 seconds, which is excessively slow for a small number of documents.

Additional insight

To gain a deeper understanding of the issue, we can look at some additional insights:

  • Resetting the remote database: When the remote database is reset, re-uploaded with the same 2k documents, and then replicated again, the query time drops to under 1 second.
  • Running maintenance operations: Running compact and other maintenance operations on the original remote database does not help improve query performance after replication.

Reproduction code

To reproduce the issue, we can use the following code:

import HttpPouch from "pouchdb-adapter-http";
import sqliteAdapter from "pouchdb-adapter-react-native-sqlite";
import PouchDB from "pouchdb-core";
import find from "pouchdb-find";
import mapreduce from "pouchdb-mapreduce";
import replication from "pouchdb-replication";
import pouchdbDebug from "pouchdb-debug";

export const loadPouchDBDatabases = async () => {
  let prefix = "";

  const PouchDBCore = PouchDB.plugin(HttpPouch)
    .plugin(replication)
    .plugin(mapreduce)
    .plugin(find)
    .plugin(sqliteAdapter)
    .plugin(pouchdbDebug);

  PouchDBCore.debug.enable("*");

  const user = {
    urlPlayDB: "xxxx",
    idPlayDB: "repdemoex",
    keyPlayDB:
      "VyCbpewmaiGsFgwloWvBOD0t0811fFSV0FN2Y40Lcy0ujw8IzfrtVwdzsEECdnGi",
    partner: "/partner/partners/30",
  };

  const remoteDb = new PouchDBCore(
    user.urlPlayDB + "/" + `${user.idPlayDB}_assortments_1-4-0`,
    {
      auth: {
        username: user.idPlayDB,
        password: user.keyPlayDB,
      },
    }
  );
  const localDB = new PouchDBCore(
    `${prefix}${user.idPlayDB}_assortments_1-4-0` + ".db",
    {
      adapter: "react-native-sqlite",
    }
  );

  await localDB.replicate.from(remoteDb);

  constDBInfo = await localDB?.info();
  const remoteDBInfo = await remoteDb?.info();
  console.log("assortment", {
    local: localDBInfo?.doc_count,
    remote: remoteDBInfo?.doc_count,
  });

  localDB.allDocs({
    include_docs: true,
  }); // <-- ~11s delay here
};

Expected behavior

The expected behavior is that querying all documents from the local database should return results within a reasonable time (<1s), even for 2000+ documents.

Questions

To better understand the issue and find a solution, we can ask the following questions:

  • Is this performance expected with this adapter?
  • Could the performance degradation be linked to revision metadata or deletion tombstones in the replicated database?
  • Is there any SQLite tuning or adapter configuration that could help?
  • Any suggestions on how to investigate or mitigate this further?

Possible solutions

Based on the insights gathered, we can explore the following possible solutions:

  • Optimize database replication: We can try to optimize the database replication process to reduce the number of revisions and tombstones.
  • Use a more efficient adapter: We can consider using a more efficient adapter, such as react-native-sqlite with a different configuration.
  • Implement caching: We can implement caching to reduce the number of queries made to the database.
  • Use a different database: We can consider using a different database, such as a local SQLite database, to reduce the overhead of the PouchDB adapter.

Conclusion

In conclusion, the performance issue related to querying a local PouchDB database using the pouchdb-adapter-react-native-sqlite adapter is a complex problem that requires a thorough investigation. By understanding the setup, problem, additional insights, reproduction code, expected behavior, and possible solutions, we can work towards finding a solution to this issue.

Q&A

Q: Is this performance expected with this adapter?

A: No, this performance is not expected with the pouchdb-adapter-react-native-sqlite adapter. The adapter is designed to provide a fast and efficient way to query a PouchDB database, and a query time of ~11 seconds for 2000+ documents is excessively slow.

Q: Could the performance degradation be linked to revision metadata or deletion tombstones in the replicated database?

A: Yes, it is possible that the performance degradation is linked to revision metadata or deletion tombstones in the replicated database. When a document is updated or deleted, a new revision is created, and the old revision is marked as deleted. This can lead to a large number of revisions and tombstones in the database, which can slow down queries.

Q: Is there any SQLite tuning or adapter configuration that could help?

A: Yes, there are several SQLite tuning and adapter configuration options that could help improve performance. Some possible options include:

  • Increasing the journal mode: The journal mode determines how SQLite writes data to the database. Increasing the journal mode to WAL (Write-Ahead Logging) can improve performance by reducing the number of disk writes.
  • Increasing the cache size: The cache size determines how much data is stored in memory. Increasing the cache size can improve performance by reducing the number of disk reads.
  • Using a more efficient adapter: The pouchdb-adapter-react-native-sqlite adapter is designed to provide a fast and efficient way to query a PouchDB database. However, there may be other adapters that are more efficient for certain use cases.

Q: Any suggestions on how to investigate or mitigate this further?

A: Yes, here are some suggestions on how to investigate or mitigate this further:

  • Use a database profiler: A database profiler can help identify performance bottlenecks in the database.
  • Use a query analyzer: A query analyzer can help identify performance bottlenecks in queries.
  • Optimize database replication: Optimizing database replication can help reduce the number of revisions and tombstones in the database.
  • Implement caching: Implementing caching can help reduce the number of queries made to the database.
  • Use a different database: Using a different database, such as a local SQLite database, can help reduce the overhead of the PouchDB adapter.

Q: What are some possible solutions to this issue?

A: Some possible solutions to this issue include:

  • Optimize database replication: Optimizing database replication can help reduce the number of revisions and tombstones in the database.
  • Use a more efficient adapter: Using a more efficient adapter, such as react-native-sqlite with a different configuration, can help improve performance.
  • Implement caching: Implementing caching can help reduce the number of queries made to the database.
  • Use a different database: Using a different database, such as a local SQLite database, can help reduce the overhead of the PouchDB adapter.

Q: How can I optimize database replication?

A: To optimize database replication, you can try the following:

  • Use a more efficient replication strategy: The pouchdb-adapter-react-native-sql adapter uses a default replication strategy that may not be the most efficient for your use case. You can try using a different replication strategy, such as replicate.from() with the live option set to true.
  • Increase the replication interval: Increasing the replication interval can help reduce the number of revisions and tombstones in the database.
  • Use a more efficient adapter: Using a more efficient adapter, such as react-native-sqlite with a different configuration, can help improve performance.

Q: How can I implement caching?

A: To implement caching, you can try the following:

  • Use a caching library: There are several caching libraries available for React Native, such as react-native-cache-manager and react-native-async-storage.
  • Implement a custom caching solution: You can implement a custom caching solution using a library such as react-native-async-storage.
  • Use a caching adapter: Some adapters, such as pouchdb-adapter-react-native-sqlite, have built-in caching support.

Q: How can I use a different database?

A: To use a different database, you can try the following:

  • Use a local SQLite database: You can use a local SQLite database instead of a PouchDB database.
  • Use a different database adapter: You can use a different database adapter, such as react-native-sqlite, instead of the pouchdb-adapter-react-native-sqlite adapter.
  • Use a different database library: You can use a different database library, such as react-native-sqlite, instead of the PouchDB library.