N1QL Enhancements in Couchbase Server 5.0

But here’s how to do it with a GRANT N1QL command:

After, you can navigate over to the “Security” section of Couchbase Console to see that “myuser” has the new permission.

You can find all of the role names (like “data_writer”) in the 5.0 documentation.

REVOKE works the same way, but in reverse.

There are a couple of new system keyspaces that are part of the 5.0 N1QL enhancements. SELECT * FROM system:user_info will return information about each user and their roles.

You can use SELECT * FROM system:applicable_roles keyspace to get a list of all the role information.

Note that only Adminstrators and users with a “Query System Catalog” role themselves have access to these keyspaces.

In previous versions of Couchbase, if you wanted to index combinations of fields, you had to create an index for each combination. For example, if I plan to write queries that check “type” and “name” field together or the “type” and “age” field together, that would require two compound indexes: one for type,name and one for type,state.

Consider this query:

In the default travel-sample bucket, the type field is indexed, but not the state. This query works, but notice how flat the query plan is:

Also notice that the “fetch” and “filter” steps have to process almost 4500 documents.

Adaptive indexes make it easier to write indexes that better support adhoc querying. They can be used to index specified fields or all fields of a document.

A new keyword introduced by this round of N1QL enhancements is SELF. It can create an adaptive index on all fields in all ‘landmark’ documents in the travel-sample bucket like so:

You can also specify individual fields you want to index instead of SELF.

After creating this index, run the above SELECT query again.

Notice the extra index scanning that’s occurring to accommodate the state field that it’s querying on. Also notice that “filter” and “fetch” are working through fewer documents: only 1900. With the relative small size of the “travel-sample” bucket, this is an overall small savings. With a real bucket containing millions of documents, the time saved can be significant.

Further, as your queries grow more complex, adaptive indexes will save you work while still giving you a performance boost.

If you are making heavy use of N1QL in your application, you may want to create multiple indexes that get distributed across your cluster. These N1QL enhancements can improve performance, load balancing, and availability.

Until Couchbase Server 5, the way you did this was to create multiple identical indexes with different name. As Venkat covered in this earlier blog post on index replicas, there are some drawbacks to that.

So, in Couchbase Server 5, index replicas have been introduced. When creating an index, simply use a num_replica setting to specify how many replica indexes you want to create.

Here’s an index on the state field that I was using earlier in the “travel-sample” bucket. In this case, I’m going to replicate it twice.

Note that in order for this to work, you’ll need at least three nodes running the index service (1 for the index, 2 for the replicas).

After running the above query (on a 3 node cluster), click “Indexes”. You should see “ix_state” show up 3 times on three nodes. Two of them are marked “replica”.

You can also specify the exact nodes you want the replicas on by specifying IP addresses in CREATE INDEX.

Couchbase will take care of the rest for you. Index replicas will be used for incoming queries.

If you’re not new to Couchbase, check out this guide on transitioning from “equivalent indexes” to index replicas.

For more details, definitely check out Venkat’s blog post on index replicas and the Couchbase Server 5.0 documentation on index replication.