Skip to main content

Optimize CloudBase Cloud Database Query Performance

In one sentence: CloudBase's document database is based on the MongoDB protocol. This recipe walks through the sequence of identify → add Indexes → restructure → change Pagination, covering common Slow Query scenarios, explaining the mechanism behind each step (why it works) and its limits as data grows — without fabricated millisecond numbers.

Estimated time: 35 minutes | Difficulty: Advanced

Applicable Scenarios​

  • Applicable: Already able to read and write the database normally (completing add-database-wechat-miniprogram is sufficient) and starting to care about query performance
  • Applicable: Data volume growing from a few thousand records to hundreds of thousands or millions, causing queries to slow down
  • Applicable: Typical read-heavy scenarios like daily reports / lists / search
  • Not applicable: Data exceeding tens of millions of records with complex cross-collection joins and strong relational requirements. Document databases are not suited for relational operations at this scale — see Step 7 "When to switch to a relational database"
  • Not applicable: Scenarios where write performance is the bottleneck (this recipe focuses on reads). Adding too many Indexes actually slows down writes — Step 2 covers this

Prerequisites​

DependencyVersion
CloudBase environmentAny — document database is supported by default
@cloudbase/js-sdk or @cloudbase/node-sdkAny current version
Console access"Document Database → Collection Management / Index Management / Slow Query" must be accessible

Step 1: Find Slow Queries first, then decide what to optimize​

The first principle of Performance Optimization: prove it's slow before optimizing. Blindly adding Indexes often targets the wrong place and actually degrades write performance.

Three entry points for identification:

1. Console Slow Query logs

Console → Environment Overview → Advanced → CLS Logs (ensure enabled) → search for module:database AND eventType:MongoSlowQuery. This shows query conditions, Index hit status, and documents scanned for all high-latency queries.

If you prefer pulling logs via SDK:

const CloudBase = require('@cloudbase/manager-node');
const manager = CloudBase.init({ secretId, secretKey, envId });

const res = await manager.log.searchClsLog({
  queryString: 'module:database AND eventType:MongoSlowQuery',
  StartTime: '2024-04-01 00:00:00',
  EndTime: '2024-04-01 23:59:59',
  Limit: 100,
  Sort: 'desc',
});

for (const log of res.Results || []) {
  console.log(log.Timestamp, log.Content);
}

Full interface signature: searchClsLog.

2. Measure individual call latency

In a Cloud Function, use Date.now() to bracket a time window and log the RequestId. If the response is slow, look up the specific execution details by that RequestId.

3. Console "Monitoring & Alerts"

Console → Database → Monitoring & Alerts shows read/write latency, Slow Query ratio, connection count, and other metrics as global trend charts — more useful than individual samples for seeing the scope of a problem.

How to define "slow": don't get hung up on "how many ms is slow". Look at relative values — if one type of query takes 5-10x longer than most queries on the same collection, or if the number of documents scanned is far greater than the number returned (e.g., returning 20 but scanning 100,000), it's worth investigating.

Step 2: Add Indexes​

Without an Index on the field used in a where clause, a document database performs a Full Collection Scan — time grows linearly with collection size. With an Index, this drops to logarithmic time (B-Tree or similar structure lookup).

Adding Indexes in Console​

Console → Database → Collection → Index Management → Create Index:

  • Single-field Index: for queries/sorts on one field. Example: where({status: 'pending'}) — add a single-field Index on status
  • Compound Index: for queries on multiple fields. Example: where({userId, status}).orderBy('createdAt', 'desc') — add a three-field compound Index on (userId, status, createdAt)
  • Unique Index: in addition to speeding up queries, enforces a uniqueness constraint on writes

Full rules: data-index.

The "leftmost prefix" rule for Compound Indexes​

A compound Index on (a, b, c) can be used for:

Query conditionIndex used?
where({a: x})Yes
where({a: x, b: y})Yes
where({a: x, b: y, c: z})Yes
where({b: y})No
where({a: x, c: z})Partial (only a)

Mechanism: the Index is an ordered structure keyed by field concatenation; skipping a leading field makes it impossible to navigate the structure.

When designing compound Indexes, put equality conditions first and range conditions last (gt / lt / in):

// Recommended: userId equality first, createdAt range last
await db.collection('orders')
  .where({
    userId: 'u1',                       // equality
    createdAt: db.command.gte(yesterday), // range
  })
  .orderBy('createdAt', 'desc')
  .get();

// Index: (userId, createdAt)

More Indexes is not always better​

  • Every Index must be updated on write; Index count directly affects write latency
  • Indexes consume storage; under memory pressure, overall performance degrades
  • CloudBase documentation recommends no more than 20 Indexes per collection — see the Index count limit section in data-index
  • Delete unused Indexes promptly. Console "Index Management" shows each Index's hit frequency

Sort direction must match​

A compound Index on (age: ascending, score: descending) can be used for:

  • orderBy('age', 'asc').orderBy('score', 'desc') (exact match)
  • orderBy('age', 'desc').orderBy('score', 'asc') (fully reversed — Index can be traversed in reverse)

Cannot be used for:

  • orderBy('age', 'asc').orderBy('score', 'asc') (mixed directions)

See the sort direction section in data-index.

Step 3: Avoid Full Collection Scans​

The most common cause of Slow Queries is an unintentional Full Collection Scan:

Anti-pattern: missing limit

// Collection has 500,000 records — this pulls all of them
const res = await db.collection('logs').where({ level: 'error' }).get();

Correct approach — always limit:

const res = await db
  .collection('logs')
  .where({ level: 'error' })
  .orderBy('createdAt', 'desc')
  .limit(50)
  .get();

Anti-pattern: large skip values

// Page 1000, 20 records per page
const res = await db.collection('items').skip(20000).limit(20).get();

skip still reads from the beginning and discards those records internally. Larger skip = slower query. Use cursor-based Pagination instead (Step 6).

Anti-pattern: regular expressions

// Regex cannot use Indexes
await db.collection('users').where({ name: /^张/ }).get();

For fuzzy search, switch to PostgreSQL full-text Index, or store the leading character / pinyin as a redundant field with its own Index at write time.

Step 4: Replace multiple queries with an Aggregation Pipeline​

A common pattern in business code is: "query a set of A, use A's results to query B, then merge":

// Anti-pattern: N+1 queries
const orders = await db.collection('orders').where({ userId }).get();
const productIds = orders.data.map((o) => o.productId);
// One RPC per product — N network round trips
const products = [];
for (const id of productIds) {
  const p = await db.collection('products').doc(id).get();
  products.push(p.data[0]);
}

One Aggregation Pipeline is much more network-efficient:

// Run server-side (Web SDK does not support aggregate; Mini Programs don't either — wrap in a Cloud Function)
const cloudbase = require('@cloudbase/node-sdk');
const app = cloudbase.init();
const db = app.database();

const res = await db
  .collection('orders')
  .aggregate()
  .match({ userId })
  .lookup({
    from: 'products',
    localField: 'productId',
    foreignField: '_id',
    as: 'product',
  })
  .project({
    _id: 1,
    productId: 1,
    product: { $arrayElemAt: ['$product', 0] },
    amount: 1,
  })
  .end();

Key points:

  • aggregate is only supported in the Node SDK (Cloud Functions / server side) document database; the frontend SDK (@cloudbase/js-sdk) does not support it. To use it from the frontend, wrap it in a Cloud Function
  • If match is at the start of the Pipeline, it can use Indexes (same Index utilization rules as where)
  • Full Pipeline operators: aggregate API
  • Aggregation is more complex to execute than a single where. Use lookup (essentially a join) with caution on large data volumes

Sort memory limit is 100MB (MongoDB default). For large data volumes, either add match / limit before sort to reduce the working set, or add an Index on the sort field so sort can use the Index. See the FAQ in aggregate.md.

Step 5: Field projection — only fetch what you need​

// Anti-pattern: document has 30 fields; only 3 are needed, but all are fetched
const res = await db
  .collection('orders')
  .where({ userId })
  .limit(50)
  .get();
// Recommended: use field to fetch only required fields
const res = await db
  .collection('orders')
  .where({ userId })
  .field({ _id: true, status: true, amount: true, createdAt: true })
  .limit(50)
  .get();

Mechanism: network transfer bytes are directly proportional to the number and size of returned fields. If a document has large fields (description with thousands of characters, an images array, nested objects), not fetching them saves significant bandwidth and deserialization time.

Note: field is a whitelist — listed fields are included, others are excluded. _id is returned by default even if not listed.

Step 6: Cursor-based Pagination for large datasets​

skip + limit is slow on large collections: page 1000 requires reading and discarding 19,999 records before returning 20.

Cursor-based Pagination uses the last record's ordered field value from the previous page as the starting point for the next:

async function listOrders(userId, lastCursor) {
  let query = db
    .collection('orders')
    .where({ userId })
    .orderBy('createdAt', 'desc')
    .orderBy('_id', 'desc'); // add _id as tiebreaker to prevent missing/duplicate records when createdAt values are equal

  if (lastCursor) {
    const _ = db.command;
    query = query.where({
      // Full or-logic needed here — see below
      ...
    });
  }

  return query.limit(20).get();
}

The full cursor logic handles the "same createdAt, break ties by _id" case — verbose but not complex:

import { db } from './cloudbase';

const _ = db.command;

async function listOrdersCursor(userId, cursor) {
  const base = { userId };

  // cursor = { createdAt, _id } from the last record of the previous page
  let where;
  if (cursor) {
    where = _.or([
      { ...base, createdAt: _.lt(cursor.createdAt) },
      { ...base, createdAt: cursor.createdAt, _id: _.lt(cursor._id) },
    ]);
  } else {
    where = base;
  }

  const res = await db
    .collection('orders')
    .where(where)
    .orderBy('createdAt', 'desc')
    .orderBy('_id', 'desc')
    .limit(20)
    .get();

  const last = res.data[res.data.length - 1];
  return {
    items: res.data,
    nextCursor: last ? { createdAt: last.createdAt, _id: last._id } : null,
  };
}

Key points:

  • The Index must include (userId, createdAt, _id) so the where + sort above can fully use the Index
  • There is no "jump to page N" capability — only next/previous page. If the business requires page jumping, accept the slow skip, or implement client-side caching

Step 7: When to switch to a relational database​

Document databases are well-suited for:

  • Self-contained documents (order + nested items / user profile + nested address list)
  • Variable fields / frequently changing schema
  • Read-heavy + single-document / single-collection queries

Not well-suited for:

  • Multi-table joins with many related fields. lookup is a performance sink in document databases — latency spikes sharply at scale
  • Strong cross-collection transactions (atomically credit A and debit B). Document databases have transactions but cross-collection performance is poor
  • Complex analytics (group by + having + multi-level joins)

For these scenarios, CloudBase provides PostgreSQL / TDSQL integration. Enable it in Console under "Database → Relational Database" and access it via the PG RESTful API or direct SQL.

Hybrid setups are common: document database for primary data, relational database for analytics reports. But hybrid introduces complexity costs too. For small projects, start with a single choice; consider migration when data reaches millions of records.

Verification​

  1. In Console → CLS Logs, run a search for module:database AND eventType:MongoSlowQuery and note the current Slow Query count
  2. Find a Slow Query, inspect its where conditions, and add the corresponding compound Index in Console "Index Management"
  3. Wait a few minutes (Index builds in the background; may take tens of seconds for large collections), then trigger the same query — it should no longer appear in Slow Query logs
  4. Run the Aggregation-replacing-N+1 example; on the Cloud Function side, use console.time / console.timeEnd to measure latency — there should be an order-of-magnitude difference
  5. After switching to cursor-based Pagination, the latency for fetching page N should no longer increase with N

Common Errors​

Error SymptomCauseFix
Query still slow after adding IndexIndex field order does not match query conditions, or sort direction is inconsistentSee Step 2 "leftmost prefix" and "sort direction must match"
Index creation is very slow or times outLarge collection (millions of records)Build Indexes during off-peak hours; CloudBase builds in the background — collection remains readable and writable
aggregate reports Sort exceeded memory limit of 104857600 bytesSort fields are large or the working set was not reduced firstAdd match / limit earlier in the Pipeline to shrink the set; add Index on sort fields; reduce sort field count
aggregate errors when called from the frontendFrontend SDK does not support aggregateWrap in a Cloud Function; call from frontend via callFunction
skip(N) Pagination gets slower and slowerLarge skip is inherently slowSwitch to cursor-based Pagination (Step 6)
Added compound Index but some queries are still slowSome query conditions are not in the Index prefixCheck specific query conditions — may need to add another Index with a different field order (keep total under 20)
Unique Index creation reports "duplicate values"Existing data contains duplicatesClean up duplicate data before creating the unique Index

Next Steps​