FaunaDB Compound Indexes: Creation, Querying, and Pitfalls

Why Compound Indexes Matter

When you query a collection in FaunaDB, the database can either scan every document or jump straight to the relevant rows using an index. A compound index lets you filter on one field and sort or filter on another in a single operation. This reduces the number of documents Fauna reads, cuts query cost to almost one per matching entry, and keeps write latency predictable.

Creating the Index

Indexes are first‑class objects in FaunaDB. They are defined in the console or via FQL with CreateIndex. Below is a concrete example that creates a compound index on the author and publishedAt fields of a books collection.

// Run in the FaunaDB console or via a client with admin privileges
CreateIndex({
  name: 'books_by_author_date',
  source: Collection('books'),
  terms: [
    { field: ['data', 'author'] } // filter by author
  ],
  values: [
    { field: ['data', 'publishedAt'] },
    { field: ['ref'] } // optional: return the reference
  ],
  unique: false,
  serialized: true
});

Key points:

• terms define the fields you can filter on with Match.
• values specify the order or additional fields the index returns.
• serialized:true tells Fauna to store the index in a way that speeds reads but makes writes slightly slower.
• Once created, an index’s terms and values cannot be changed without dropping and recreating it.

Using the Index in a Query

With the index in place, you can retrieve all books by a specific author and sort them by publication date in a single, efficient query.

// Example query: get Alice’s books sorted newest first
Match(Index('books_by_author_date'), 'Alice')
  .OrderBy(Index('books_by_author_date'))
  .Limit(10); // optional pagination

Explanation of the steps:

• Match(Index(...), 'Alice') filters the collection to documents where author equals "Alice".
• Because the index already contains publishedAt as a value, OrderBy can sort without an extra scan.
• The query cost is roughly 1 per matched document, plus a tiny overhead for the OrderBy operation.

Understanding Query Costs

FaunaDB charges per index entry accessed. In the example above, if Alice has 50 books, the cost will be close to 50. A full‑collection scan would cost the size of the collection, which can be orders of magnitude higher. Monitoring the cost field in the query console helps confirm that the index is being used.

Limits and Common Mistakes

Immutability After Creation

Changing terms or values after creation forces a drop-and-recreate cycle. Dropping an index locks writes to the source collection until the new index is built, which can stall applications that rely on high write throughput.

Over‑Indexing

Each index consumes storage (about 1 MB per 10,000 entries) and adds a small write penalty. Creating indexes that are never used can inflate costs unnecessarily. Review query patterns before adding new indexes.

Missing Serialized Flag

If you expect heavy read traffic, omitting serialized:true will keep writes faster but make reads slower. Balance your read/write mix accordingly.

Wrong Field Paths

Fauna stores document fields under data. A common error is specifying author instead of data.author in terms. Such misconfigurations cause the index to be empty and queries to fall back to a full scan.

Practical Checklist Before Deployment

1. Verify that the index’s terms and values match your query patterns.
2. Set serialized:true if the index will be read often.
3. Run a test insert and query to confirm the cost is low.
4. Monitor storage usage: fauna index list shows size per index.
5. Document the index purpose so future developers know why it exists.

Diagram

The following diagram labels illustrate the flow of a compound index query in FaunaDB.

Collection	Index	Query	Result
books	books_by_author_date	Match + OrderBy	Filtered & sorted list