An Index With Its Keys Backwards

A compound index is not a set of fields; it is an ordered list, and a query can only use it if the fields it filters on form a prefix of that list. Get the order wrong and you have paid for the write cost of an index that no read will ever touch, while explain() quietly reports a collection scan. Part 5 worked through the machinery that wraps every command in Trill's Stories service. This part goes one layer further down, to the two indexes the service creates at boot and the one query that needed a third.

Everything the service indexes

Trill.Services.Stories.Infrastructure/Mongo/Extensions.cs is the whole persistence-tuning story, forty-five lines, run once from UseInfrastructure():

public static IApplicationBuilder UseMongo(this IApplicationBuilder builder)
{
    using var scope = builder.ApplicationServices.CreateScope();
    var users = scope.ServiceProvider.GetService<IMongoRepository<UserDocument, Guid>>().Collection;
    var userBuilder = Builders<UserDocument>.IndexKeys;
    Task.Run(async () => await users.Indexes.CreateManyAsync(
        new[]
        {
            new CreateIndexModel<UserDocument>(userBuilder.Ascending(i => i.Name),
                new CreateIndexOptions { Unique = true })
        }));

    var ratings = scope.ServiceProvider.GetService<IMongoDatabase>()
        .GetCollection<StoryRatingDocument>("ratings");
    var ratingBuilder = Builders<StoryRatingDocument>.IndexKeys;
    Task.Run(async () => await ratings.Indexes.CreateManyAsync(
        new[]
        {
            new CreateIndexModel<StoryRatingDocument>(
                ratingBuilder.Ascending(i => i.UserId).Descending(i => i.StoryId),
                new CreateIndexOptions { Unique = true }),
        }));

    return builder;
}

Two indexes. A unique index on users.Name, and a unique compound index on ratings keyed { UserId: 1, StoryId: -1 }. There is no index on the stories collection at all — AddMongoRepository<StoryDocument, long>("stories") gets the free _id index and nothing else.

What the ratings collection is actually asked

StoryRatingDocument has three fields — StoryId (long), UserId (Guid), Rate (int) — and four query sites in the service:

One of the four uses both fields. The other three filter on StoryId alone — and StoryId is the second key. MongoDB can use an index for a query whose predicate covers a leading subset of the key list; it cannot skip the leading key. { UserId: 1, StoryId: -1 } is sorted by user first, so the documents for one story are scattered across the whole B-tree. Every rating read in the service is a collection scan, and the one index that exists on that collection is what makes it look like they are not.

The unique constraint itself is correct and worth keeping: one rating per user per story is exactly the rule StoryRatingId encodes. That is the trap. A unique index enforces a set — order is irrelevant to uniqueness — so the developer who wrote Ascending(UserId).Descending(StoryId) got the constraint they wanted and had no signal that they had also chosen a read plan. A unique compound index does two jobs, and only one of them is order-insensitive.

The fix is one word: swap the keys. { StoryId: 1, UserId: 1 } enforces exactly the same uniqueness, serves SetAsync's two-field equality just as well, and serves all three StoryId-only reads as an index scan over a contiguous range. There is no query in the service that wants UserId first.

And the collection nobody indexed

stories is the busier collection, and it has no index beyond _id. BrowseStoriesHandler is the service's primary read path:

var now = query.Now.ToUnixTimeMilliseconds();
var documents = _database.GetCollection<StoryDocument>("stories")
    .AsQueryable()
    .Where(x => x.From <= now && x.To >= now);
...
var result = await documents.OrderByDescending(x => x.CreatedAt).PaginateAsync(query);

A range predicate on two unindexed fields, then a sort on a third unindexed field. On a collection of any size that is a full scan followed by an in-memory sort, and MongoDB's 32 MB sort-memory limit is the thing that eventually turns it into an error rather than a slow query. { From: 1, To: 1, CreatedAt: -1 } — or, given the equality-sort-range guidance, { CreatedAt: -1, From: 1, To: 1 } depending on selectivity — would cover it.

There is a second, subtler problem in the same handler. The search branch:

documents = documents.Where(x =>
    x.Title.Contains(input) || x.Author.Name.Contains(input) || x.Tags.Contains(input));

Three clauses, three different semantics. x.Title.Contains(input) and x.Author.Name.Contains(input) translate to unanchored regular expressions, which no B-tree index can serve and which pass unescaped user input into the regex engine. x.Tags.Contains(input) is an array-element match — an exact equality on one element of Tags, not a substring test. So ?query=dot matches a story titled “dotnet basics” and does not match a story tagged dotnet, and no part of the API tells the caller that.

The one index that is right, and the assumption inside it

The other index in that file is a unique ascending index on users.Name, and structurally it is exactly what you want: the service replicates a UserDocument in from the Users service's UserCreated event, names must be unique, and the database is the right place to say so.

It carries an assumption, though, and the assumption is wrong one repository over. A Mongo index is byte-comparison by default — no collation is specified here — so Kumar and kumar are two distinct keys and both may exist. The Users service creates the same unique index on the same field, and its repository looks names up with .ToLowerInvariant() applied to the input while its entity stores them merely trimmed. Part 8 follows what that does to sign-in; the point here is that a uniqueness index and the guard in front of it must agree about what “the same” means, and the only place to state that agreement is the index's collation. Neither service states it.

If I were rewriting this file, the index set for the Stories service would be four lines rather than two:

users   { Name: 1 }                     unique, collation strength 2
stories { CreatedAt: -1, From: 1, To: 1 }
ratings { StoryId: 1, UserId: 1 }       unique
ratings { UserId: 1 }                   for "what have I rated?" if that query ever arrives

Two of those four exist today, and both of the two are the wrong shape for the queries that run against them.

The startup race nobody would see

Both Task.Run calls are worth reading on their own terms.

• They are not awaited. UseMongo returns immediately. Any exception inside — a permissions failure, a conflicting existing index, an unreachable server — is captured in a Task that nobody observes and nobody logs. The service starts up healthy with no indexes.
• They race their own DI scope. The scope is created with using var scope, which disposes it when UseMongo returns — while the two background tasks are still holding services resolved from it. IMongoRepository<UserDocument, Guid> and IMongoDatabase are both scoped in Convey's registration, so this is a genuine use-after-dispose window on every single boot. It usually wins the race; “usually” is doing all the work in that sentence.
• They race the first request. Nothing sequences index creation before the endpoints start serving. On a cold start against an empty database, the first sign-up can insert a duplicate name before the unique index exists.

The identical code, with the same two Task.Run calls and the same disposed scope, is in Trill.Services.Users.Core/Mongo/Extensions.cs — two more indexes, same shape. And in Analytics, whose entire indexing story is one unique index on tags.Name, also inside an unawaited Task.Run over a scope that is about to be disposed. Three services, one copy-pasted hazard, which is part 15's theme in miniature.

Making it safe is not hard: make UseMongo async, await both CreateManyAsync calls, and let a failure crash the host. An index that silently does not exist is worse than a startup that loudly refuses to begin.

Two persistence idioms in one service

One last structural detail, because it explains why the ratings index had to be created the awkward way. Infrastructure/Extensions.cs:105-106 registers exactly two Mongo repositories:

.AddMongoRepository<StoryDocument, long>("stories")
.AddMongoRepository<UserDocument, Guid>("users")

StoryRatingDocument is not registered. So StoryRatingMongoRepository cannot take an IMongoRepository<StoryRatingDocument, …>; it takes a raw IMongoDatabase and calls GetCollection<StoryRatingDocument>("ratings") itself. The literal "ratings" consequently appears five times across four files — the repository twice, the two query handlers once each, and the index-creation code once. Rename the collection and the compiler helps you with none of it.

Two persistence idioms in one service is not a crime; the crime is that the choice is invisible. A reader looking at IStoryRatingRepository sees the same abstraction as IStoryRepository and has no reason to suspect that one is backed by Convey's generic repository and the other by a string literal.

Next, the query handler that materialises every rating document in the database to compute a single integer, when the correct query already exists twenty lines away: a million documents for one integer.