Indexing Options in Marten

The road to Marten 1.0 continues with a discussion of the indexing options that we directly support against Postgresql.

We’re aiming to make Marten be usable for a wide range of application scenarios, and an obvious one is to make querying faster. To that end, Marten has direct support for adding a couple different flavors of indexes to optimize querying.

In all cases, Marten’s schema migration support can detect changes, additions, and removals of index definitions.

Calculated Index

After getting some feedback from a 2nd Quadrant consultant, the recommended path for optimizing queries against JSON documents in Marten is to use a Postgresql calculated index, which Marten can build for you with:

    var store = DocumentStore.For(_ =>
    {
        _.Connection(ConnectionSource.ConnectionString);
        _.Schema.For<Issue>().Index(x => x.Number);
    });

Marten creates this index behind the scenes against the Issue storage table:

CREATE INDEX mt_doc_issue_idx_number ON public.mt_doc_issue 
    ((CAST(data ->> 'Number' as integer)));

The advantages of using a calculated index are that you’re not duplicating storage and you’re causing fewer database schema changes as compared to our original “Duplicated Field” approach that’s described in the next section.

It’s not shown here, but there is some ability to configure how the calculated index is created. See the documentation on calculated indexes for an example of that usage.

I’ve been asked several times what it would take for Sql Server to add before it could support Marten. The calculated index feature as applicable to the JSONB data type isn’t explicitly necessary, but it’s a big advantage that Postgresql has over Sql Server at the moment.

Duplicated Field

Marten’s original approach was to optimize querying against designated fields by just duplicating the value within the JSON document into a separate database table column, and indexing that column. Marten does this behind the scenes when you use the Foreign Key option. Some of our users will opt for a duplicated field if they want to issue their own queries against the document table without having to worry about JSON locators.

To make a field duplicated, you can either use the [Duplicated] attribute:

    public class Team
    {
        public Guid Id { get; set; }

        [DuplicateField]
        public string Name { get; set; }
    }

Or you can specify the duplicated fields in the StoreOptions for your document store:

    using (var store = DocumentStore.For(_ =>
    {
        _.Connection(ConnectionSource.ConnectionString);

        _.Schema.For<User>().Duplicate(x => x.UserName);
    }))
    {

    }

If you decide to add a duplicated field to an existing document type, Marten’s schema migration support is good enough to add the column and fill in the values from the JSON document as part of patching. Even so, we will recommend the computed index approach in the section above to simplify your schema migrations.

It’s not shown here, but you have quite a bit of flexibility in configuring exactly what index type and applicability. See the documentation on duplicated fields for an example.

Gin Index

If you’re needing to issue a lot of variable adhoc queries against a Marten document, you may want to opt for a Gin index. A gin index against a Postgresql JSONB object creates a generalized index of key/value pairs and arrays within the parsed JSON document. To add a Gin index to a Marten document type, you need to explicitly configure that document type like this:

    var store = DocumentStore.For(_ =>
    {
        _.Schema.For<Issue>().GinIndexJsonData();
    });

You can also decorate your document class with the [GinIndexed] attribute. It’s not shown above, but there are options to customize the index generated.

When the DDL for the Issue document is generated, you would see a new index added to its table like this one:

CREATE INDEX mt_doc_issue_idx_data ON public.mt_doc_issue USING gin ("data" jsonb_path_ops);

Do note that using a Gin index against a document type will result in slightly slower inserts and updates to that table. From our testing, it’s not that big of a hit, but still something to be aware of.