Couchbase: SQL++ Queries, Key-Value & Clustering

Couchbase

Couchbase is a distributed NoSQL database that combines a fast key-value store with a JSON document database and a SQL-compatible query language. Its memory-first architecture keeps frequently used documents in an integrated cache, giving sub-millisecond key lookups, while SQL++ (formerly N1QL) queries, secondary indexes, full-text search, analytics, and event-driven functions run as separate services in the same cluster. Couchbase scales horizontally by distributing data across nodes automatically, replicates across data centers with XDCR, and syncs to mobile and edge devices with Couchbase Lite.

Despite the similar name, Couchbase is a different system from Apache CouchDB: it shares some history, but its architecture, query model, and replication approach differ substantially.

TL;DR

Data lives in buckets, organized into scopes and collections (like databases, schemas, and tables).
Key-value operations by document ID are the fastest path; sub-document operations update parts of a document.
SQL++ queries JSON with SQL syntax; production queries need global secondary indexes (GSI).
Services — Data, Query, Index, Search, Analytics, Eventing — can run on separate nodes and scale independently.
Durability levels and multi-document ACID transactions trade latency for stronger guarantees.
Data is partitioned into vBuckets with replicas; XDCR replicates between clusters; Capella is the managed service.

Quick Example

Key-value and query access with the Python SDK:

The index that keeps that query fast:

Core Concepts

Data Organization

Key design matters: meaningful, prefixed keys such as order::o-123 enable direct lookups without queries.

Memory-First Data Service

The Data service keeps active documents in memory with asynchronous persistence to disk and replication to replica copies. The resident ratio — the share of active data in memory — strongly affects latency; size memory quotas for the working set. Storage engines include Couchstore and Magma, the latter designed for datasets much larger than memory.

Access Paths

CAS values provide optimistic concurrency: updates can require that the document has not changed since it was read.

Indexes

SQL++ queries rely on global secondary indexes created explicitly. Composite indexes matching query predicates, partial indexes (WHERE clauses), covering indexes that include returned fields, and array indexes for nested arrays keep queries efficient. A primary index allows full scans and should not be relied on in production.

Durability and Transactions

By default, writes are acknowledged once in memory on the active node. Durability levels — majority, majorityAndPersistActive, persistToMajority — wait for replication and persistence before acknowledging, protecting against node failures at the cost of latency. Multi-document ACID transactions coordinate changes across documents and collections when atomicity matters.

Clustering and Replication

vBuckets: each bucket is split into 1,024 partitions distributed across Data nodes, with up to three replicas; client SDKs route requests directly to the right node.
Rebalancing redistributes partitions when nodes are added or removed, online.
Auto-failover promotes replicas when a node fails.
Multi-dimensional scaling: run Query, Index, and Search on dedicated nodes sized for their workloads.
XDCR replicates buckets or collections between clusters for disaster recovery, geographic distribution, or migrations, with conflict resolution by revision or timestamp.

Mobile, Managed Service, and Operations

Couchbase Lite is an embedded database for mobile and edge devices; Sync Gateway or App Services synchronize it with the server, with per-user access rules.
Capella is the managed cloud service, removing cluster operations.
Backups use cbbackupmgr (or Capella backups) with incremental backups and restore testing.
Monitoring: track resident ratio, disk write queue, operations per second, query latency, index fragmentation, and XDCR lag.

Best Practices

Design Keys and Collections Deliberately

Use predictable, prefixed keys for direct access, and collections per document type so indexes and permissions stay focused.

Prefer Key-Value Access on Hot Paths

When the key is known, a get is far cheaper than a query. Reserve SQL++ for genuine searches and aggregations.

Create Covering Indexes for Critical Queries

Check query plans with EXPLAIN and add indexes that cover predicates and returned fields.

Choose Durability per Operation

Use stronger durability for financial or critical writes and default durability for data that can be rebuilt.

Common Mistakes

Relying on a Primary Index

Undersized Memory Quotas

Whole-Document Rewrites for Small Changes

FAQ

What is Couchbase used for?

High-performance operational workloads that need fast key-value access, flexible JSON documents, and SQL-style queries — such as user profiles, sessions, catalogs, and real-time applications — often with mobile sync or multi-region replication.

Is Couchbase the same as CouchDB?

No. They share early history, but Couchbase is a distinct database with a memory-first architecture, SQL++ queries, separate services, and different replication. CouchDB focuses on HTTP access and multi-master replication with conflict handling.

What is SQL++?

Couchbase's query language, formerly called N1QL: SQL extended for JSON, supporting nested data, arrays, joins, aggregation, and data modification.

Does Couchbase support transactions?

Yes. It supports multi-document ACID transactions across documents, collections, and buckets, alongside single-document atomicity with CAS and configurable durability levels.

How does Couchbase scale?

Data is partitioned into vBuckets distributed across nodes and rebalanced as nodes are added. Query, Index, Search, and Analytics services can run on separate nodes and scale independently.