Couchbase 5.5: Overview of Query and Indexing Features

DZone 's Guide to

Couchbase 5.5: Overview of Query and Indexing Features

This article gives an overview of Couchbase 5.5 including the query and indexing features as well as the language and performance features.

· Database Zone ·
Free Resource

We just announced Couchbase 5.5. This includes important query and index improvements. This article gives you an overview of additional details in the book: A Guide to N1QL in Couchbase 5.5.
Image title

Language Features:

JOIN is one of the foundational operations in SQL. Hence, N1QL has implemented INNER JOIN and LEFT OUTER JOIN from the first release. In addition, N1QL has added NEST and UNNEST operations to work with arrays, a commonly used data structure in JSON. So far, the join expression was limited to equality child-to-parent or parent-to-child documents.

In Couchbase 5.5, you can join on any complex expression just like SQL joins. We’ve also added limited support for RIGHT OUTER joins.

So far, the join execution used a method called Block Nested Loop Join. This algorithm works fine when the number of documents in the outer table is limited. When the number of documents is high (e.g. reporting queries), the query latency can be high.

In Couchbase 5.5, we introduce hash join to improve the performance of join queries with a large number of documents. The article on “ANSI JOINs in N1QL” describes the details on join language and performance improvements.

Performance Features:

One of the common ways to improve query performance is to create an index that covers the query. For queries which require grouping and aggregation, even when the query is covered by an index, we fetch all of the qualified document keys, index keys to the query service, then group and aggregate on that data. This process copies the data multiple times, from the indexer, over the network to the indexer client, to the query service. If the scan result is large, we end up using backfill to write the results into the file and read it later on.

After selecting the index for the query, Couchbase 5.5 automatically evaluates the possibility of the indexer evaluating the grouping and aggregation. If it’s feasible, the scan request will include additional requests to group and aggregate within the indexer. Couchbase 5.5’s index service can now do runtime grouping and aggregation. That means, the indexer can scan, group, and aggregate the data within the indexer, eliminating multiple data copy operations and improving both the latency and throughput of these queries.

The article, “Understanding Index Grouping And Aggregation in Couchbase N1QL Query” describes this process in details.

In Couchbase, data is always partitioned using the consistent hash value of the document key into vBuckets, which are stored in multiple nodes. For indexer, however, you have to partition them manually using the WHERE clause on the CREATE INDEX. This manual partitioning is cumbersome to manage and requires queries to be written carefully to match one of the predicates in the WHERE clause.

In Couchbase 5.5, you can simply partition the index using the hash strategy. The hash partitioned index will increase the index capacity by creating multiple indexes on a single large collection of documents easily. For query processing, for simple queries with equality predicates on hash key, you get uniform distribution of the workload on these multiple indexes. For complex queries including the grouping & aggregation we discussed above, the scans, partial aggregations are done in parallel, improving the query latency. The article “index partitioning” will describe this feature in detail.

Security and Infrastructure Features:

N1QL Auditing helps customers implement regulatory compliance like HIPPA, and GDPR by auditing every N1QL statement executed on the system. You can configure the auditing to auditing specific or all users and/or roles, specific or all statements. The article “N1QL Auditing” will explain the feature and it's use case in detail.

Couchbase 5.5 supports using certificate authority signed certificates for public key certificates used in the TLS (Transport Layer Security). The article “N1QL Support for X.509” describes the feature in N1QL.

ALTER INDEX helps you to move the indexes from one node to the other, change the number of replica for the index, and generally improve the index manageability. See ALTER INDEX documentation for details.

Index scans are efficient and fast. When an index scan request returns a large number of qualified keys, query service may not be able to consume at the same speed. To avoid any backlog of these index scan results in the indexer, the query service (actually the index client within the query service) fetches rest of the results and writes into a temporary file. These backfill files will be purged once the query completes. The location of these files was always /tmp on Linux and TEMPDIR on Windows. In Couchbase 5.5, you can change the location of the directory where the backfill files are created and set a quote for maximum storage that can be used. See Query Temp Disk Path documentation for details.

Query Workbench Features

Couchbase 5.0 added visual explain to easily understand the query plan and debug performance issues by looking at the profile statistics on each of the query operators. The picture below should say it all!

In addition, you can now run prepared statements with different parameters by setting different query specific parameters, timeouts, and profiling parameters for each run.

Last, but not the least, we’ve made it easier to copy the results from query workbench to Excel or any other tool expecting tabular output. The picture below shows tabular output and then simply click on the copy icon next to Query results.

Simply paste into Excel or Google Sheets. It’ll copy like magic :-)

Next Reading: Each of these features are described in detail in the book, "A Guide to N1QL in Couchbase 5.5". You can download the book here clicking on the image.

Image title

Click on the image below to DOWNLOAD the PDF of the SECOND EDITION. Covers up to Couchbase 5.0.



  1. What’s new in Couchbase 5.5? https://developer.couchbase.com/documentation/server/5.5/introduction/whats-new.html
  2. Couchbase blogs: https://blog.couchbase.com/
  3. Couchbase Documentation: https://developer.couchbase.com/documentation/server/5.5/introduction/intro.html

sql ,nosql ,json ,couchbase ,query ,index ,performance ,application ,n1ql ,database

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}