Replace N+1 Document Lookups with Couchbase N1QL JOIN ON KEYS

The N+1 Problem in a Product Catalog

Fetching a product list and then issuing a separate request for each product's category is a classic N+1 pattern. With 50 products on a page, that's 51 round trips to the database. Couchbase's N1QL JOIN ... ON KEYS can collapse those requests into a single query when the relationship is stored as an array of document IDs.

Data Model That Makes the Join Work

Keep products and categories in the same keyspace (for example, catalog) and distinguish them with an entityType field. Each product document holds an array of category document IDs:

// product document
{
  "entityType": "product",
  "sku": "ABC-123",
  "name": "Widget",
  "categoryIds": ["cat::electronics", "cat::sale"]
}

// category document
{
  "entityType": "category",
  "name": "Electronics",
  "description": "..."
}

The IDs in categoryIds must exactly match the target category documents' _id values. No foreign-key inference happens; ON KEYS only follows the array you provide.

Index and Query

Create a partial index that covers the entity filter so the engine can quickly locate products and categories:

CREATE INDEX idx_catalog_entity ON catalog(entityType);

Run the join from the cbq shell or any SDK with query privileges (replace catalog with your keyspace name):

SELECT p.name AS productName,
       p.sku,
       c.name AS categoryName
FROM catalog AS p
JOIN catalog AS c ON KEYS p.categoryIds
WHERE p.entityType = "product"
  AND c.entityType = "category"
ORDER BY p.name
LIMIT 20;

Selecting only the needed fields keeps the payload small. Add a deterministic ORDER BY and LIMIT after confirming the index supports the sort (check with EXPLAIN).

What the Join Actually Does

• Scans products via the entityType index.
• For each product, looks up every ID in categoryIds directly by key (no secondary index needed for the right side).
• Filters the matched categories with c.entityType = "category".
• Returns one row per product-category pair; a product with two categories appears twice.

If a category ID is stale or missing, that product simply produces fewer rows—no error is raised. An empty categoryIds array yields zero rows for that product.

Trade-offs and When to Denormalize Instead

• Latency: The server still performs the same key lookups; a large categoryIds array or high fan-out can increase query time.
• Payload: Selecting whole documents (SELECT p, c) inflates network transfer and memory. Explicit projections are almost always better for list endpoints.
• Index fit: The example index works for the filter, but complex predicates may require a composite index. Always run EXPLAIN on production-like data.
• Simpler alternative: If category names rarely change, denormalizing categoryName onto the product document avoids the join entirely.

Verify Before You Ship

1. Create a test keyspace with a handful of products, shared categories, one stale category ID, and one product with an empty categoryIds array.
2. Run the example query and confirm each product receives only its intended categories; the stale and empty cases should return no category rows.
3. Run EXPLAIN on the query to verify index usage and join order.
4. Measure client request count, latency, and scanned-document metrics against the existing N+1 implementation using your actual SDK and Couchbase Server version (tested on 7.x).
5. Repeat with realistic category fan-out and document sizes before deciding.

Actionable Close

Prototype the JOIN ON KEYS query with representative data. Compare request count, latency, and scanned-document metrics against the N+1 path. Adopt the join only when the measured benefit justifies the added query complexity and model discipline. If the numbers don't improve, denormalizing a small field is often the simpler win.