Aggregates

In this guide, we'll explore efficient aggregations in Convex. You'll learn to set up TableAggregate for O(log n) counts and sums, integrate with triggers for automatic maintenance, and implement common patterns like leaderboards, follower counts, and dashboard metrics.

Overview

@convex-dev/aggregate provides efficient O(log n) aggregations instead of O(n) queries:

Feature	Description
Counts	Total users, likes per post, followers per user
Sums	Total revenue, points per user
Rankings	Leaderboards, sorted lists, percentiles
Random access	Get document at specific index

Let's set it up.

Always Use Triggers

Important: NEVER manually update aggregates in mutations. ALWAYS use triggers for automatic maintenance.

// ✅ CORRECT: Register trigger once, aggregates stay in sync automatically
triggers.register('scores', scoresAggregate.trigger());

// Just insert - trigger handles aggregate!
await ctx.table('scores').insert({ score: 100 });

// ❌ WRONG: Manual updates in every mutation (easy to forget!)
await ctx.table('scores').insert({ score: 100 });
await scoresAggregate.insert(ctx, doc); // Don't do this!

Installation

First, install the aggregate package:

bun add @convex-dev/aggregate

Then add components to convex/functions/convex.config.ts:

convex/functions/convex.config.ts

import aggregate from '@convex-dev/aggregate/convex.config';
import { defineApp } from 'convex/server';

const app = defineApp();

// Add one component per aggregate
app.use(aggregate, { name: 'aggregatePostLikes' });
app.use(aggregate, { name: 'aggregateTotalUsers' });
app.use(aggregate, { name: 'aggregateFollowers' });

export default app;

Setup

Now let's create an aggregates file. This is where you'll define all your aggregate configurations:

convex/functions/aggregates.ts

import { TableAggregate } from '@convex-dev/aggregate';
import { components } from './_generated/api';
import type { DataModel } from './_generated/dataModel';

// Count likes per post
export const aggregatePostLikes = new TableAggregate<{
  DataModel: DataModel;
  Key: null;              // No sorting, just counting
  Namespace: string;      // postId
  TableName: 'postLikes';
}>(components.aggregatePostLikes, {
  namespace: (doc) => doc.postId,
  sortKey: () => null,
});

Aggregate Types

Let's explore the different types of aggregates you can create.

Count by Namespace

Count items grouped by a key:

// Likes per post
export const aggregatePostLikes = new TableAggregate<{
  DataModel: DataModel;
  Key: null;
  Namespace: string;      // postId
  TableName: 'postLikes';
}>(components.aggregatePostLikes, {
  namespace: (doc) => doc.postId,
  sortKey: () => null,
});

// Usage
const likeCount = await aggregatePostLikes.count(ctx, {
  namespace: postId,
  bounds: {},
});

Global Count

Count all rows in a table:

// Total users
export const aggregateTotalUsers = new TableAggregate<{
  DataModel: DataModel;
  Key: null;
  Namespace: string;      // Always "global"
  TableName: 'user';
}>(components.aggregateTotalUsers, {
  namespace: () => 'global',
  sortKey: () => null,
});

// Usage
const totalUsers = await aggregateTotalUsers.count(ctx, {
  namespace: 'global',
  bounds: {},
});

Multiple Aggregates on Same Table

For bidirectional relationships like followers/following:

// Followers (people following this user)
export const aggregateFollowers = new TableAggregate<{
  DataModel: DataModel;
  Key: null;
  Namespace: string;      // followingId (user being followed)
  TableName: 'follows';
}>(components.aggregateFollowers, {
  namespace: (doc) => doc.followingId,
  sortKey: () => null,
});

// Following (people this user follows)
export const aggregateFollowing = new TableAggregate<{
  DataModel: DataModel;
  Key: null;
  Namespace: string;      // followerId
  TableName: 'follows';
}>(components.aggregateFollowing, {
  namespace: (doc) => doc.followerId,
  sortKey: () => null,
});

Sorted Aggregates

For rankings and top-N queries:

// Scores sorted by value
export const aggregateScoresByValue = new TableAggregate<{
  DataModel: DataModel;
  Key: [number, string];  // [score desc, name asc]
  Namespace: string;
  TableName: 'scores';
}>(components.aggregateScoresByValue, {
  namespace: (doc) => doc.userId,
  sortKey: (doc) => [
    -doc.value, // Negative for descending
    doc.name,
  ],
});

// Get top 5 scores
const topScores = await aggregateScoresByValue.paginate(ctx, {
  namespace: userId,
  limit: 5,
});

Key Selection Patterns

Choosing the right keys is critical for query performance.

Simple Keys

// By number (scores, rankings)
sortKey: (doc) => doc.score

// By string (usernames, alphabetical)
sortKey: (doc) => doc.username

// By timestamp (chronological)
sortKey: (doc) => doc._creationTime

// No sorting (random access, counting only)
sortKey: () => null

Composite Keys

Group and query with prefixes:

// Sort by game, then username, then score
sortKey: (doc) => [doc.game, doc.username, doc.score]

// Query with prefix
const gameCount = await aggregate.count(ctx, {
  bounds: { prefix: [game] },
});
const userGameCount = await aggregate.count(ctx, {
  bounds: { prefix: [game, username] },
});

Key Ordering

Note: Sort order matters for composite keys:

[game, username, score] - query by game OR game+username
[game, score] - find highest score per game
[username, score] - find user's highest score

Bounds for Null Keys

Important: When using sortKey: () => null, you must provide bounds:

// ❌ WRONG: Missing bounds parameter
const count = await aggregate.count(ctx, {
  namespace: itemId,
});

// ✅ CORRECT: Include empty bounds
const count = await aggregate.count(ctx, {
  namespace: itemId,
  bounds: {},
});

This also fixes the TypeScript "Type instantiation is excessively deep" error.

Trigger Integration

convex/lib/triggers.ts

import { Triggers } from 'convex-helpers/server/triggers';
import {
  aggregatePostLikes,
  aggregateFollowers,
  aggregateFollowing,
  aggregateTotalUsers,
} from '../aggregates';

export const registerTriggers = () => {
  const triggers = new Triggers<DataModel>();

  // Auto-maintain counts on insert/delete
  triggers.register('postLikes', aggregatePostLikes.trigger());
  triggers.register('user', aggregateTotalUsers.trigger());

  // Multiple triggers for same table
  triggers.register('follows', aggregateFollowers.trigger());
  triggers.register('follows', aggregateFollowing.trigger());

  return triggers;
};

Usage

Here's how to use aggregates in your queries and mutations.

Get Count

const likeCount = await aggregatePostLikes.count(ctx, {
  namespace: postId,
  bounds: {},
});

Get Sum

// For aggregates with sumValue configured
const totalPoints = await aggregatePoints.sum(ctx, {
  namespace: userId,
});

Statistical Operations

// Average score
const sum = await aggregate.sum(ctx, { namespace: gameId });
const count = await aggregate.count(ctx, { namespace: gameId, bounds: {}});
const average = count > 0 ? sum / count : 0;

// 95th percentile
const p95Index = Math.floor(count * 0.95);
const p95Result = await aggregate.at(ctx, p95Index, { namespace: gameId });
const p95Score = p95Result?.key ?? null;

// User ranking (position for a given score)
const rank = await aggregate.indexOf(ctx, userScore, { namespace: gameId });

Random Access

Get a random document from a table:

const randomAggregate = new TableAggregate<{
  Key: null;
  DataModel: DataModel;
  TableName: 'songs';
}>(components.randomAggregate, {
  sortKey: () => null, // No sorting = random by _id
});

// Get random song
const count = await randomAggregate.count(ctx, { bounds: {}});
if (count === 0) return null;

const randomIndex = Math.floor(Math.random() * count);
const result = await randomAggregate.at(ctx, randomIndex);
const song = result ? await ctx.table('songs').get(result.doc._id) : null;

Paginate

const results = await aggregateSkillsByLevel.paginate(ctx, {
  namespace: characterId,
  limit: 10,
  cursor: paginationCursor, // Optional
});

Bounded Queries

// Count scores in range
const highScoreCount = await aggregate.count(ctx, {
  namespace: gameId,
  bounds: {
    lower: { key: 100, inclusive: true },
    upper: { key: 1000, inclusive: true },
  },
});

// Get max/min
const topScore = await aggregate.max(ctx, { namespace: gameId });
const lowestScore = await aggregate.min(ctx, { namespace: gameId });

Batch Operations

Reduce function call overhead with batch versions:

// Instead of multiple individual calls
const counts = await Promise.all([
  aggregate.count(ctx, { namespace: id1, bounds: {} }),
  aggregate.count(ctx, { namespace: id2, bounds: {} }),
  aggregate.count(ctx, { namespace: id3, bounds: {} }),
]);

// Use batch for better performance
const counts = await aggregate.countBatch(ctx, [
  { namespace: id1, bounds: {} },
  { namespace: id2, bounds: {} },
  { namespace: id3, bounds: {} },
]);

// Also available: sumBatch, atBatch
const sums = await aggregate.sumBatch(ctx, [
  { namespace: id1 },
  { namespace: id2 },
]);
const items = await aggregate.atBatch(ctx, [0, 1, 2], { namespace: gameId });

Backfill and Repair

When adding aggregates to existing data, you'll need to backfill.

Initial Backfill

For existing data when adding a new aggregate:

export const backfillAggregate = privateMutation
  .input(z.object({
    cursor: z.string().nullable(),
    batchSize: z.number().default(100),
  }))
  .mutation(async ({ ctx, input }) => {
    const results = await ctx.db
      .query('scores')
      .paginate({ numItems: input.batchSize, cursor: input.cursor });

    // Idempotent - won't duplicate if already exists
    for (const doc of results.page) {
      await aggregate.insertIfDoesNotExist(ctx, doc);
    }

    if (!results.isDone) {
      await ctx.scheduler.runAfter(0, internal.aggregates.backfillAggregate, {
        cursor: results.continueCursor,
        batchSize: input.batchSize,
      });
    }
  });

Repair Aggregate

If aggregate gets out of sync:

export const repairAggregate = privateMutation
  .mutation(async ({ ctx }) => {
    // Clear and rebuild from source of truth
    await aggregate.clear(ctx);

    const docs = await ctx.db.query('scores').collect();
    for (const doc of docs) {
      await aggregate.insert(ctx, doc);
    }
  });

Common Use Cases

Let's look at battle-tested patterns you can copy into your project.

Star/Like Counts

// Define aggregate
export const aggregatePostLikes = new TableAggregate<{
  DataModel: DataModel;
  Key: null;
  Namespace: string;
  TableName: 'postLikes';
}>(components.aggregatePostLikes, {
  namespace: (doc) => doc.postId,
  sortKey: () => null,
});

// Register trigger
triggers.register('postLikes', aggregatePostLikes.trigger());

// Query count
const likeCount = await aggregatePostLikes.count(ctx, {
  namespace: postId,
  bounds: {},
});

Admin Dashboard Stats

const stats = {
  totalUsers: await aggregateTotalUsers.count(ctx, {
    namespace: 'global',
    bounds: {},
  }),
  totalPosts: await aggregateTotalPosts.count(ctx, {
    namespace: 'global',
    bounds: {},
  }),
};

Follower/Following Counts

const followerCount = await aggregateFollowers.count(ctx, {
  namespace: userId,
  bounds: {},
});
const followingCount = await aggregateFollowing.count(ctx, {
  namespace: userId,
  bounds: {},
});

Best Practices

Here are key practices to follow when using aggregates.

Always Use Triggers

// ✅ BEST: Triggers handle everything
triggers.register('scores', scoresAggregate.trigger());
await ctx.table('scores').insert(data); // Done!

// ❌ AVOID: Manual updates are error-prone
const id = await ctx.table('scores').insert(data);
const doc = await ctx.table('scores').get(id);
await aggregate.insert(ctx, doc!); // Easy to forget!

Choose Keys for Query Patterns

// ✅ GOOD: Keys match how you query
sortKey: (doc) => [doc.category, doc.priority, doc.createdAt]
// Allows: by category, category+priority, full ordering

// ❌ BAD: Keys don't match access patterns
sortKey: (doc) => doc.internalId
// Can't query by meaningful attributes

Use Namespaces for Isolation

// ✅ GOOD: Namespace prevents interference between tenants
namespace: (doc) => doc.tenantId,
sortKey: (doc) => doc.score

// ❌ BAD: No namespace causes contention
sortKey: (doc) => [doc.tenantId, doc.score]

Handle Edge Cases

// Always handle division by zero
const average = count > 0 ? sum / count : 0;

Bounded Queries Reduce Conflicts

// ✅ GOOD: Bounded query has fewer dependencies
const recentCount = await aggregate.count(ctx, {
  bounds: {
    lower: { key: Date.now() - 86400000, inclusive: true },
  },
});

// ❌ AVOID: Unbounded queries cause more conflicts
const allCount = await aggregate.count(ctx);

Limitations

Consideration	Guideline
Document count	Works best with large tables (thousands+)
Update frequency	High-frequency updates to nearby keys cause contention
Key size	Keep composite keys reasonable (3-4 components max)
Namespace count	Each namespace has overhead
Query patterns	Design keys for actual needs, not hypothetical

When to Use Aggregates

Scenario	Standard Query	Aggregate
Small tables (under 1000 rows)	O(n) is fine	Not needed
Large tables, frequent reads	O(n) per read	O(log n)
Real-time counts	Slow, blocks UI	Fast
Dashboard metrics	Very slow	Essential

Configuration Reference

Each aggregate needs a component in convex.config.ts:

convex/functions/convex.config.ts

import aggregate from '@convex-dev/aggregate/convex.config';
import { defineApp } from 'convex/server';

const app = defineApp();

// One per aggregate
app.use(aggregate, { name: 'aggregatePostLikes' });
app.use(aggregate, { name: 'aggregateFollowers' });
app.use(aggregate, { name: 'aggregateFollowing' });
app.use(aggregate, { name: 'aggregateTotalUsers' });
app.use(aggregate, { name: 'aggregateTotalPosts' });

export default app;

Advanced

Let's explore advanced aggregate patterns for complex use cases.

DirectAggregate

For aggregating data not stored in tables (manual management):

import { DirectAggregate } from '@convex-dev/aggregate';

const aggregate = new DirectAggregate<{
  Key: number;
  Id: string;
}>(components.aggregate);

// Insert manually
await aggregate.insert(ctx, {
  key: Date.now(),
  id: `${userId}-${Date.now()}`,
  sumValue: value,
});

// Replace (update key or value)
await aggregate.replace(
  ctx,
  { key: oldTimestamp, id: eventId },
  { key: Date.now(), id: eventId, sumValue: newValue }
);

Multiple Sort Orders

Define multiple aggregates for different access patterns on the same table:

// By score for rankings
const byScore = new TableAggregate<{
  Key: number;
  DataModel: DataModel;
  TableName: 'players';
}>(components.byScore, {
  sortKey: (doc) => doc.score,
});

// By username for alphabetical listing
const byUsername = new TableAggregate<{
  Key: string;
  DataModel: DataModel;
  TableName: 'players';
}>(components.byUsername, {
  sortKey: (doc) => doc.username,
});

// By activity for recency
const byActivity = new TableAggregate<{
  Key: number;
  DataModel: DataModel;
  TableName: 'players';
}>(components.byActivity, {
  sortKey: (doc) => doc.lastActiveAt,
});

Composite Aggregation Patterns

Multi-dimensional leaderboards with regional queries:

const leaderboard = new TableAggregate<{
  Namespace: string; // game mode
  Key: [string, number, number]; // [region, score, timestamp]
  DataModel: DataModel;
  TableName: 'matches';
}>(components.leaderboard, {
  namespace: (doc) => doc.gameMode,
  sortKey: (doc) => [doc.region, doc.score, doc.timestamp],
});

// Regional high scores
const regionalHighScore = await leaderboard.max(ctx, {
  namespace: 'ranked',
  bounds: { prefix: ['us-west'] },
});

// Count players per region
const usWestCount = await leaderboard.count(ctx, {
  namespace: 'ranked',
  bounds: { prefix: ['us-west'] },
});

Cascade Deletes with Aggregates

Aggregates update automatically when triggers handle cascade deletes:

// Register aggregate triggers
triggers.register('characterStars', aggregateCharacterStars.trigger());
triggers.register('characters', aggregateCharacters.trigger());

// Cascade delete - aggregates update automatically
triggers.register('user', async (ctx, change) => {
  if (change.operation === 'delete') {
    const characters = await ctx.table('characters', 'userId', (q) =>
      q.eq('userId', change.id)
    );

    for (const char of characters) {
      // Aggregate trigger fires on each delete!
      await ctx.table('characters').getX(char._id).delete();
    }
  }
});

Lazy Aggregation Configuration

Configure aggregation tree for different workloads:

// High-write scenarios: larger nodes, lazy root
await aggregate.clear(ctx, 32, true);

// High-read scenarios: smaller nodes, eager root
await aggregate.clear(ctx, 16, false);

Time-Based Aggregations

Bucket data by time periods:

const activityByHour = new TableAggregate<{
  Key: [number, string]; // [hour, userId]
  DataModel: DataModel;
  TableName: 'activities';
}>(components.activityByHour, {
  sortKey: (doc) => [
    Math.floor(doc.timestamp / 3600000), // Hour bucket
    doc.userId,
  ],
});

// Get hourly activity counts
const now = Date.now();
const results = [];

for (let i = 0; i < 24; i++) {
  const hour = Math.floor((now - i * 3600000) / 3600000);
  const count = await activityByHour.count(ctx, {
    bounds: { prefix: [hour] },
  });
  results.push({ hour, count });
}

Leaderboard with User Stats

Here's a complete real-world example:

export const getUserStats = authQuery
  .input(z.object({ gameId: zid('games') }))
  .output(z.object({
    highScore: z.number(),
    averageScore: z.number(),
    gamesPlayed: z.number(),
    globalRank: z.number().nullable(),
    percentile: z.number(),
  }))
  .query(async ({ ctx, input }) => {
    // User's scores in this game
    const gameScores = await ctx.table('scores', 'user_game', (q) =>
      q.eq('userId', ctx.userId).eq('gameId', input.gameId)
    );

    if (gameScores.length === 0) {
      return { highScore: 0, averageScore: 0, gamesPlayed: 0, globalRank: null, percentile: 0 };
    }

    const highScore = Math.max(...gameScores.map((s) => s.score));
    const averageScore = gameScores.reduce((sum, s) => sum + s.score, 0) / gameScores.length;

    // Global stats from aggregate
    const globalRank = await leaderboardByGame.indexOf(ctx, highScore, {
      namespace: input.gameId,
    });

    const totalPlayers = await leaderboardByGame.count(ctx, {
      namespace: input.gameId,
      bounds: {},
    });

    const percentile = globalRank && totalPlayers > 0
      ? ((totalPlayers - globalRank) / totalPlayers) * 100
      : 0;

    return { highScore, averageScore, gamesPlayed: gameScores.length, globalRank, percentile };
  });

Testing Aggregates

import { expect, test } from 'vitest';
import { ConvexTestingHelper } from 'convex-test';

test('aggregate maintains consistency', async () => {
  const t = new ConvexTestingHelper();

  // Insert test data
  const scoreId = await t.mutation(api.scores.addScore, {
    gameId: 'game1',
    score: 100,
  });

  // Verify aggregate
  const stats = await t.query(api.scores.getStats, { gameId: 'game1' });
  expect(stats.gameCount).toBe(1);

  // Update and verify count unchanged
  await t.mutation(api.scores.updateScore, { scoreId, newScore: 200 });
  const newStats = await t.query(api.scores.getStats, { gameId: 'game1' });
  expect(newStats.gameCount).toBe(1);
});

Error Handling

Handle aggregate-specific errors gracefully:

export const deleteScore = authMutation
  .input(z.object({ scoreId: zid('scores') }))
  .mutation(async ({ ctx, input }) => {
    const doc = await ctx.table('scores').getX(input.scoreId);

    try {
      await aggregate.delete(ctx, doc);
    } catch (error) {
      if (error.message.includes('not found in aggregate')) {
        // Document wasn't in aggregate, safe to continue
        console.warn('Document not in aggregate:', input.scoreId);
      } else {
        throw error;
      }
    }

    await ctx.table('scores').getX(input.scoreId).delete();
  });

Aggregates

Triggers

Rate Limiting

@convex-dev/aggregate

On this page