Dexie & IndexedDB

A friend is looking into in-browser databases and posted a snippet on codepen of some code using Dexie wondering why the performance wasn’t as good as they expected.I’d never heard of IndexedDB or Dexie before this, and I don’t know javascript terribly well, nor async JS (which Dexie uses) hardly at all.

This is my catnip.

I forked the snippet and started playing around. Fairly quickly it was clear that the very slow performance they were seeing was a one-time thing, some sort of database initialization, maybe building indexes. My friend agreed, but the general performance still seemed on the slow side, and they said they were thinking about looking into paging, so naturally I started looking into paging. (It’s been so long since I’ve felt really capable of learning that I am happily letting my brain sink its teeth into whatever it wants. As it were.)

I found some posts suggesting using offset for paging, something like this:

results = await db.books
        .where('authorId')
        .equals(1)
        .offset(N * pageSize)
        .limit(pageSize)
        .toArray()

but while this works, the performance is poor: query time increases almost linearly with the offset value. This suggests that internally Dexie (or IndexedDB?) is iterating over the full result set every time until it reaches the offset and then returning the following results. And indeed, the Dexie documentation for Collection.offset()) says that offset sometimes has to iterate over the collection. But hm! Under “Examples where offset() will be fast”, they have

db.[table].where(index).between(value).offset(N)

which is more or less exactly what I tried and found to be slow. Curious! Is this because of the compound index? I wouldn’t expect that to matter - it’s an index, after all.

Looking into the Dexie source for Collection.offset(), the “simple” criterion looks to be implemented by isPlainKeyRange, which returns true for this query. The offset functionality is applied as a replayFilter, and checking in the codepen console it looks like that’s happening correctly:

db.table.where(<index>).equals(1)._ctx

does not have a .replayFilter property, but

db.table.where(<index>).equals(1).offset(100)._ctx

does, and (as far as I can tell) it looks like it should work correctly.

I found an issue about offset() performance on Dexie’s github page] and updated it with a link to a new codepen demonstration of the issue I was seeing.

Next thing to investigate: does IndexedDB’s advance() function show the same behavior?

The next morning I did some more digging and indeed it does. I made another demo that shows that IDBCursor.advance(offset) takes longer the larger the offset. (You’ll need to run the previous demo first to create the database this one uses.) You can see that running firstByIndexCursor(offset) takes longer the higher the offset.

I updated the github issue with this info. I still think Dexie’s documentation could use a note clarifying the behavior of offset in the “fast” cases. Update: And, a day later, the maintainer agreed. I submitted a PR to the docs which was accepted. \o/ clarity!