Performance issue on local database query (~11s for ~2k docs)

[Pierre-AlexandreTavel]

🐌 Performance issue on local database query (~11s for ~2k docs)

Context

I'm using the pouchdb-adapter-react-native-sqlite adapter with React Native to query PouchDB database that has been replicate from a remote CouchDB database.

I was previously using pouchdb-adapter-react-native-sqlite with react-native-quick-websql and pouchDB 8
I followed the following doc to upgrade everything because I switched to the new arch of react native: https://www.devas.life/the-fastest-way-to-use-pouchdb-on-react-native-0-73/

Setup

• 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

After replicating the remote DB locally, querying all documents with:

localDB.allDocs({ include_docs: true });

takes ~11 seconds, which seems excessively slow for such a small number of documents.

With a previous implementation (react-native-quick-websql) not using this adapter, the same query was significantly faster (almost instant).

---

Additional insight

• I tried resetting the remote database, re-uploading the same 2k documents, then replicating again.
  → In that case, the query time dropped to under 1 second.
• I also tried running compact and other maintenance operations on the original remote database,
  → but that did not help improve query performance after replication.

---

Reproduction 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);

  const localDBInfo = 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

Querying all documents from the local database should return results within a reasonable time (<1s), even for 2000+ documents.

---

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?

[Pierre-AlexandreTavel]

After further investigation, the problem seems to lie with the number of document revisions stored on the CouchDB side.

After applying a revs_limit and running a compact on the CouchDB database, the response times returned to normal, but only after reseting the local PouchDB database

This raises a few questions:

• While pouchdb-core allows setting a revs_limit during database initialization, it doesn’t seem to impact the replication process (i.e. creating the local database → replicating → compacting → querying).
• Does the local database keep accumulating revisions over time during replication?
• For example:
  If my revs_limit is set to 100 on the CouchDB side and also 100 on the PouchDB side, but documents on CouchDB reach revision 150 (so 50 to 150), will the replicated documents on the local PouchDB also contain 150 revisions (0 to 150, as the compaction with revs_limit seems to not work)?

Not knowing exactly which library (opsqlite, pouchdb-adapter-react-native-sqlite, pouchdb, ...) is concerned by this question, don't hesitate to redirect me if necessary.

[Pierre-AlexandreTavel]

I may have found where the problem is coming from, but I don't know if it's deliberate or if there are side effects that I can't control, but at this point in the core file:

api._bulkDocs = async (req, reqOpts, callback) => {
    _debug.logger.debug('**********bulkDocs!!!!!!!!!!!!!!!!!!!');
    try {
      const response = await (0, _bulkDocs.default)({
        revs_limit: undefined,
      }, req, reqOpts, api, transaction, sqliteChanges);
      callback(null, response);
    } catch (err) {
      (0, _utils.handleSQLiteError)(err, callback);
    }
  };

revs_limit is set to undefined instead of opts.revs_limit

api._bulkDocs = async (req, reqOpts, callback) => {
    _debug.logger.debug('**********bulkDocs!!!!!!!!!!!!!!!!!!!');
    try {
      const response = await (0, _bulkDocs.default)({
        revs_limit: opts.revs_limit, //<------- change here
      }, req, reqOpts, api, transaction, sqliteChanges);
      callback(null, response);
    } catch (err) {
      (0, _utils.handleSQLiteError)(err, callback);
    }
  };

With this correction, the documents recovered via replication have a revs_limit limited to the number defined in the initialization of the pouchdb database (this restricts the number of revs in revs_tree in document-store).

And as a result the findAll response times are good, but I don't know if this change has an impact on reliability.

[craftzdog]

Thanks for debugging this. I'll look into it later

[craftzdog]

Can you check how other adapters deal with the revs_limit param?

[Pierre-AlexandreTavel]

Can you think of an example? LocalStorage for example?

[craftzdog]

like, the official leveldown adapter
https://github.com/pouchdb/pouchdb/tree/master/packages/node_modules/pouchdb-adapter-leveldb

[Pierre-AlexandreTavel]

From leveldb adapter, they send directly revs_limit option to processDocs (pouchdb-adapter-utils) from api._bulkDocs

Leveldb: LevelPouch -> api._bulkDocs -> processDocs
On your side: SQLPouch -> api._bulkDocs -> sqliteBulkDocs (broken propagation of revs_limit) -> websqlProcessDocs -> processDocs

Propagation of revs_limit seems to be done with leveldb adapter

I also check with pouched-adapter-idb, and it's the same process

[craftzdog]

hmm, I don't remember why added revs_limit: undefined.
I guess it should add revs_limit option to our adapter as LevelPouch does:

function LevelPouch(opts, callback) {
  opts = clone(opts);
  var api = this;
  var instanceId;
  var stores = {};
  var revLimit = opts.revs_limit;