I’m using the Couchbase Swift SDK connected to Capella with Sync Gateway. The bulk of my queries fetch documents by their ID and look like this:
SELECT meta().id, * FROM `SomeFooCollection` WHERE meta().id = "someuuidstring"
Is there a speed benefit if I use the Swift SDK’s API to create an index on meta().id for the SomeFooCollection?
I know this seems like a dumb, basic question, but the Internet is full of conflicting/dated information about this. Some posts describe this as a “Primary Index” that references only the document ID. Others say the Primary Index contains all document fields and is therefore inefficient and slow. Some advice says querying by the document ID shouldn’t require an index to be fast. Other docs say that Couchbase creates zero indexes by default, even for the document keys.
So, in 2025, what is the correct answer? If I intend to do 80% of my queries via document ID, should I creat an index on meta().id for each Collection? Thanks.
An index on meta.id() is created when the table is created (collection is created). And so you don’t have to manually create one. I have checked this by using query.explain(), without previously creating an index - USING INDEX sqlite_autoindex_kv_default_1
Some posts describe this as a “Primary Index” that references only the document ID.
True. meta.id() is the primary key, so it’s indexed by definition. It’s the key of the b-tree that holds the documents.
Others say the Primary Index contains all document fields and is therefore inefficient and slow.
Well … sort of, if by “primary index” they mean the main b-tree. It’s true that, since documents can be large, this b-tree is less compact than a typical secondary index, so it may take more I/O to search it than to search a smaller secondary index.
On the other hand, in any normal query (like yours) you are accessing document properties other than the ID, so the query has to traverse the main b-tree anyway to read those properties.
Some advice says querying by the document ID shouldn’t require an index to be fast.
True.
Other docs say that Couchbase creates zero indexes by default, even for the document keys.
False. There’s no such thing as a database without a primary-key index of some sort; the whole point of having a database is to be able to look stuff up faster than a linear scan.
It just occurred to me: Why are you using a query for this? That’s what getDocument does. Just ask the Database for the document with that ID. It’s more efficient.
Why are you using a query for this? That’s what getDocument does.
Yea, someone else pointed that out via direct message. Queries were just the first thing I reached for because I’d already implemented some other APIs that take WHERE clauses as Predicate objects. (I’m essentially building a SwiftData replacement backed by Couchbase—an entire ORM.)
I’ve since switched to collection.getDocument() and while I haven’t noticed a practical speed difference, I also have only a few hundred documents in the database right now. At scale, it will probably help.