Building on the landmark 4.0 release, which greatly expanded the type of document database use cases Couchbase can support, Couchbase Server 4.1 brings massive improvements to query functionality and performance, that will benefit both developers and the enterprise.

 

It is a testament to our engineering team that Couchbase Server 4.1 — coming only three months after Couchbase Server 4.0 was released, includes over 200 improvements that will result in greater scalability and better query performance. We wanted the 4.1 release to quickly enhance N1QL, as many of our customers are already using N1QL to build new use cases on Couchbase.

Following are the key highlights of the 4.1 release  —

 

N1QL: Complete SQL CRUD Support with N1QL for Batch and OLTP Applications

Common SQL statements, such as INSERT, UPDATE, DELETE, MERGE and UPSERT are now fully supported in Couchbase Server 4.1.  With this benefit, you can now manipulate JSON documents with N1QL, in the same manner as you would manipulate tables with SQL and support all query requirements of enterprise Web, Mobile, and IoT applications.

The N1QL INSERT statement allows both singleton and multi-document inserts. UPDATE and DELETE enable the manipulation of a set of documents based on specific criteria.  Both of these statements use available indices to efficiently identify and update documents. The UPSERT and MERGE statements merge two sets of documents based on given criteria.  All of these traditional SQL statements have been adapted to support JSON documents.

The SQL statements are fully supported in Couchbase SDKs, REST APIs and Simba’s JDBC and ODBC drivers.

 

Covering Indexes

By definition, a Covering Index is an index that contains at minimum all the fields required by a specific query.  What this means for your application, is that covering indexes will help a query run faster. With a standard index, a query execution flow will initially scan the index service for data, but would then have to spend additional time to scan the data service to complete the query.  By using a Covering Index, only the scan of that Covering Index is required for data access.  The result is lower query latency and a faster performing application.

N1QL queries use available indices to efficiently execute the query. Depending on the use-case, we have seen over 2-3x improvement in query latency and throughput. This is because, when a query only needs to reference the key attributes of the chosen index, it can avoid fetching the full document to answer the query. The query will then take fewer resources and run faster.  In addition, the N1QL query engine exploits the index ordering to avoid data sorting when the index keys match the key attributes in ORDER BY clause.

The N1QL optimizer automatically recognizes if only an index can be used to answer a query. Performance gained by using covering indexes is typically great for queries that process a large number of rows.  For example, in the case of queries that do aggregation as below —

CREATE INDEX idxstatecountry ON beer-sample(state, country) USING GSI

SELECT country, max(state)

FROM beer-sample USE INDEX (idxstatecountry)

WHERE STATE LIKE 'C%'

GROUP BY country

 

In cases where ORDER BY is used for sorting, covering indexes can help as well since the index is already sorted, and the query engine can directly use the sorted covering index to produce results.

 

Prepared Statements

A prepared statement enables execution of the same, or similar, query many times with high efficiency by avoiding repeated query parsing and preparation.  Many applications require repeated execution of predefined queries with only different parameters.  Performing these repeated queries as ad-hoc statements requires query parsing, planning and execution each time.  Using a Prepared Statement template, where the execution plan is fixed and only specific variables are substituted each time the query is run, removes the overhead required to parse and compile an execution plan for these often-repeated queries.  The result is lower latency and reduced CPU cycles – and a faster performing application.

We have already seen several customers replace queries that previously relied on map-reduce and views with N1QL – as well as build new applications that require more complex queries than Couchbase previously supported.

 

Additional Supported Platforms

Couchbase Server 4.1 is certified to run on both Windows 10 and the OSX El Capitan platforms.

 

Get Started Now

4.1 GA has arrived, and here are some resources to get your started –  

 

I would like to thank our awesome community for trying out the developer preview bits, and for constantly testing and providing feedback, and specifically on N1QL. We hope you enjoy 4.1 and as always provide us your feedback.

Good luck building your applications with Couchbase 4.1!

Posted by Don Pinto

Don Pinto is a Principal Product Manager at Couchbase and is currently focused on advancing the capabilities of Couchbase Server. He is extremely passionate about data technology, and in the past has authored several articles on Couchbase Server including technical blogs and white papers. Prior to joining Couchbase, Don spent several years at IBM where he maintained the role of software developer in the DB2 information management group and most recently as a program manager on the SQL Server team at Microsoft. Don holds a master's degree in computer science and a bachelor's in computer engineering from the University of Toronto, Canada.

Leave a reply