SQL++ Query: Skip Sync Gateway Documents in Results

SQL++
,
1

I’m using Capella with Sync Gateway. When I query, I want Couchbase to completely ignore the sync metadata documents in a collection. In other words, if Couchbase created the document instead of me, I never want to see it.

Question:

What is the most performant way for me to ensure that when a query would otherwise return these documents, such as below, they get skipped? Testing Meta().id for a leading _sync seems slow.

Screenshot 2025-02-15 at 01.20.27
Screenshot 2025-02-15 at 01.20.272272×2326 435 KB

2

So far, this is what I have:

SELECT *, Meta().id FROM MasterCue WHERE (`blaz` IS MISSING AND Meta().id NOT LIKE "\\_%")

But that takes 1.1 seconds with just a single MasterCue document in the collection. There must be a more performant way to exclude the sync metadata documents, right?

(It actually caught me off guard that Couchbase throws them into the same Collection as my documents. Other systems store the sync metadata in separate, sister collections so that it doesn’t pollute user-generated documents.)

3

All your indexes create with WHERE META().id NOT LIKE “_sync%”;
Add exactly same predicate on the queries.

example:

 create index ix20 ON default(META().id) WHERE META().id NOT LIKE "_sync%";
SELECT d.* FROM default AS d  WHERE META().id NOT LIKE "_sync%";

 create index ix21 ON default(title) WHERE META().id NOT LIKE "_sync%";
SELECT d.* FROM default AS d  WHERE META().id NOT LIKE "_sync%" AND title > "abc";
4

Yea, I know I can speed things up with an index. But I have 30+ fields on the documents in this collection alone and about 20 total collections. That’s a lot of indexes.

I was hoping for some kind of configuration option that would make the sync metadata transparent for queries.