Primary Uses for Couchbase Primary Index

A couple of frequently asked questions on N1QL for query service:

1. When do we actually use a primary index?
2. Why the index advisor does not recommend the primary index when that may the only choice?

Read on…

Couchbase is a distributed database. It supports flexible data model using JSON. Each document in a bucket will have a user-generated unique document key. This uniqueness is enforced during the inserting or updating of the data. Here’s an example document.

Each Couchbase bucket can store data multiple types: customer, order, catalog, etc. When you load the “travel-sample” dataset, you load five distinct types of documents: airline, airport, hotel, route, landmarks.

But, by default, Couchbase does not have the equivalent of “full table scan” to scan all the documents from start to finish.  The primary index scan provides you the “full table scan” equivalent.

CREATE PRIMARY INDEX ix_customer_primary ON customer;

What is the Primary Index?

• • a sorted list of all the document keys of every document type within the bucket customer.
  • maintained asynchronously, just like other secondary indexes
  • keeps just the document key and nothing else
  • supports all the scan consistencies:
    • Unbounded
    • AT_PLUS
    • REQUEST_PLUS

The primary index allows the query engine to access all the documents, then do the filtering, joining, aggregation, etc operations on them.

EXPLAIN SELECT * from customer WHERE zip = 94040 name name = “joe” and type = “cx”;

This is slow. Very slow. Unnecessary document fetches; Unnecessary filtering. Wasted memory and CPU. Primary scans will retrieve ALL the documents of all types in the bucket whether or not your query eventually returns them to the user.  While I said primary scan is like a table scan, it’s much slower than your table scan since it has to scan all of the documents of all types.

You should not use primary indexes.  Do not use them.  Especially in production.

 Then why do we have primary indexes, to begin with?

1. When you’re starting to play with new sample data, you can run most queries without worrying about create specific indexes. At this point, your primary concern is to understand the data than tuning for throughput.
2. When you know the range of primary keys you want to scan.
   1. WHERE META().id between “cx:123” and “cx:458”
3. When you know the trailing META().id pattern like below
   1. WHERE META().id LIKE “cx:1%”
   2. DO NOT use: LIKE “%:123”. This will result in full scan
4. When you do know the full META().id or list of META().id, you can use the USE KEYS to directly fetch the document without consulting the primary index
   1. FROM customer USE KEYS [“cx:123”]
   2. FROM customer USE KEYS [“cx:123”, “cx:359”, “cx:948”]
   3. FROM customer USE KEYS (SELECT raw docid FROM mylist WHERE zip = 94501)

Primary Index

create the primary index on ‘travel-sample’;

The primary index is simply the index on the document key on the whole bucket. The Couchbase data layer enforces the uniqueness constraint on the document key. The primary index, like every other index in Couchbase, is maintained asynchronously.  You set the recency of the data by setting the consistency level for your query.

Here is the metadata for this index:

The metadata gives you additional information on the index: Where the index resides (datastore_id), its state (state) and the indexing method (using).
The primary index is used for full bucket scans (primary scans) when the query does not have any filters (predicates) or no other index or access path can be used.   In Couchbase, you store multiple keyspaces (documents of a different type, customer, orders, inventory, etc) in a single bucket.   So, when you do the primary scan, the query will use the index to get the document-keys and fetch all the documents in the bucket and then apply the filter.    So, this is VERY EXPENSIVE.

The document key design is somewhat like primary key design with multiple parts.

Lastname:firstname:customerid

Example: smith:john:X1A1849

In Couchbase, it’s a best practice to prefix the key with type of the document.  Since this is a customer document, let’s prefix with CX.  Now, the key becomes:

So, in the same bucket, there will be other types of documents.

These are simply best practices. There is no restriction on the format or structure of the document key in Couchbase, except they’ve to be unique within a bucket.

Now, if you have documents with various keys and have a primary index, you can use following queries to efficiently.

Example 1:  Looking for a specific document key.

If you do know the full document key, you can use the following statement and avoid the index access altogether.

SELECT * FROM sales USE KEYS [“CX:smith:john:X1A1849”]

You can get more than one document in a statement.

Example 2:  Look for a pattern.  Get ALL the customer documents.

Example 3:  Get all the customers with smith as their last name.

The following query uses the primary index efficiently, only fetching the customers with a particular range.  Note: This scan is case sensitive.  To do a case insensitive scan, you’ve create a secondary index with UPPER() or LOWER() of the document key.

Example 4:  It’s common for some applications to use use email address as part of the document key since they’re unique. In that case, you need to find out all of customers with gmail.com.  If this is a typical requirement, then, store the REVERSE of of the email address as the key and simply do the scan of leading string pattern.

Email:johnsmith@gmail.com;   key: reverse("johnsmith@gmail.com") => moc.liamg@htimsnhoj 

Email: janesnow@yahoo.com  key: reverse("janesnow@yahoo.com") => moc.oohay@wonsenaj

Named Primary Index

In Couchbase 5.0, you can create multiple replica of any index with a simple parameter to CREATE INDEX.  Following will create 3 copies of the index and there has to be minimum of 3 index nodes in the cluster.

You can also name the primary index. The rest of the features of the primary index are the same, except the index is named. A good side effect of this is that you can have multiple primary indices in Couchbase versions before 5.0 using different names. Duplicate indices help with high availability as well as query load distribution throughout them.  This is true for both primary indices and secondary indices.

Finally, in Couchbase 6.5, we’ve introduced the Index advisor.  It can analyze a single N1QL statement or workload.  Read the details at:

1. N1QL Index Advisor: Improve Query Performance and Productivity
2. Index Advisor for N1QL Query Statement
3. Index Advisor For Query Workload

This index advisor only advises suitable secondary indexes and never a primary index.  If you’ve read the article so far, you know why! Download Couchbase 6.5 and Try out all the new features!