Kjac.SearchProvider.PostgreSql 1.0.0-alpha.1

Umbraco search provider for PostgreSQL

This repo contains an alternative search provider for Umbraco search, based on PostgreSQL 14+.

Prerequisites

A PostgreSQL 14+ instance with a database must be available and running 😛

Intended use

Umbraco Search rather favors document databases (NoSQL). While this search provider is fully functional, it cannot match the performance of document databases like Elasticsearch and Typesense.

Local benchmarks show this search provider handling queries over 1000 pages with a handful of facets in ~15-20ms on average (P90). When increasing the number of pages to 5000, the average query time increases to ~60-80ms (P90).

The same benchmarks show Elasticsearch and Typesense handling queries over 1000 pages at ~6-8ms, and a significantly smaller incline in query time for 5000 pages.

Performance has many factors. The number of pages is one, the structure of your content model and the subsequent complexity of your queries is another. And of course, the resources available on your PostgreSQL instance matters too.

In conclusion: You probably should not use this search provider on large content sets. Also, make sure to benchmark your solution, to ensure it lives up to your expectations.

Installation

The package is installed from NuGet:

dotnet add package Kjac.SearchProvider.PostgreSql

Once installed, add the search provider to Umbraco by means of composition:

using Kjac.SearchProvider.PostgreSql.DependencyInjection;
using Umbraco.Cms.Core.Composing;

namespace My.Site;

public class SiteComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
        => builder.AddPostgreSqlSearchProvider();
}

Connecting to your PostgreSQL database

You'll need to configure the search provider, so it can connect to your PostgreSQL database.

This is done either via appsettings.json:

{
  "PostgreSqlSearchProvider": {
    "Client": {
      "ConnectionString": "[your PostgreSQL connection string]"
    }
  }
}

...or using IOptions:

using Kjac.SearchProvider.PostgreSql.Configuration;
using Kjac.SearchProvider.PostgreSql.DependencyInjection;
using Umbraco.Cms.Core.Composing;

namespace My.Site;

public class SiteComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.AddPostgreSqlSearchProvider();

        builder.Services.Configure<ClientOptions>(options =>
        {
            options.ConnectionString = "[your PostgreSQL connection string]";
        });
    }
}

Hosting multiple environments on a single PostgreSQL instance

You can use an Environment discriminator to host multiple environments on the same PostgreSQL engine. In essence, this creates environment specific databases by post-fixing the value of Environment to each of the tables created by the search provider.

This is configured either via appsettings.json:

{
  "PostgreSqlSearchProvider": {
    "Client": {
      "Environment": "[your environment alias ('dev', 'test' etc.)]"
    }
  }
}

...or using IOptions:

builder.Services.Configure<ClientOptions>(options =>
{
    options.Environment = "[your environment alias ('dev', 'test' etc.)]";
});

Custom content indexes

If you need custom content indexes, register them with the RegisterPostgreSqlContentIndex extension method from the IndexOptionsExtensions. This ensures correct registration, specially in load balanced setups:

namespace My.Site;

public class AdditionalIndexesComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
        => builder.Services.Configure<IndexOptions>(options
            => options.RegisterPostgreSqlContentIndex<IPublishedContentChangeStrategy>(
                "MyPublishedContentIndex",
                UmbracoObjectTypes.Document
            )
        );
}

Configuration

These are the configuration options for this search provider.

Enabling indexing for suggestions

The search provider supports suggestions, but this feature is disabled by default. Enabling it increases the size of your database, and also slows down indexing a little:

builder.Services.Configure<IndexerOptions>(options =>
{
    // enable suggestion indexing
    options.UseSuggestions = true;
});

Changing the suggestions in the index

Suggestions are generated from the most relevant text fields (all TextsR1 values, e.g. the document names) by default. You can change this by explicitly defining which fields to use as the source for suggestions (all Text* values will be included from these fields):

builder.Services.Configure<IndexerOptions>(options =>
{
    // set the source of suggestions to the document name (core constant) and the title and description properties
    options.SuggestionFields = [Umbraco.Cms.Search.Core.Constants.FieldNames.Name, "title", "description"];
});

Tweaking score boosting for textual relevance

Umbraco search allows for multiple textual relevance options within a single field. You can change the default boost factors of the search provider:

builder.Services.Configure<SearcherOptions>(options =>
{
    // boost the highest relevance text by a factor 100 (default is 6)
    options.BoostFactorTextR1 = 100f;
    // boost the second-highest relevance text by a factor 10 (default is 4)
    options.BoostFactorTextR2 = 10f;
    // do not boost the third-highest relevance text at all (default is 2)
    options.BoostFactorTextR3 = 1f;
});

Allowing for more facet values

By default, the search provider allows for a maximum of 100 facet values returned per facet in a search result. You can change that the upper limit:

builder.Services.Configure<SearcherOptions>(options =>
{
    // allow fetching 200 facet values per facet
    options.MaxFacetValues = 200;
});

Allowing for more suggestions

By default, the search provider allows for a maximum of 50 suggestions per query, to avoid unbounded requests due to malformed queries. You can change the upper limit:

builder.Services.Configure<SearcherOptions>(options =>
{
    // allow fetching up to 100 suggestions per query
    options.MaxSuggestionsCap = 100;
});

Changing the full-text search configuration

PostgreSQL uses a configuration when stemming and tokenizing for full-text search. The search provider uses the "simple" configuration by default, to be as generic as possible. You can change this:

builder.Services.Configure<IndexerOptions>(options =>
{
    // use the "english" configuration instead of "simple" for full-text search
    options.FtsConfiguration = "english";
});

Extendability

Generally, you should look to Umbraco search for extension points. There are however a few notable extension points in this search provider as well.

Client connectivity

If you need more control over how the underlying data source for PostgreSQL connectivity is created, you can swap out the IPostgreSqlConnectionFactory implementation with a custom one:

using Kjac.SearchProvider.PostgreSql.Services;
using Umbraco.Cms.Core.Composing;

namespace My.Site;

public class MyPostgreSqlConnectionFactory : IPostgreSqlConnectionFactory
{
    // ...
}

public class MyConnectionFactoryComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
        => builder.Services.AddUnique<IPostgreSqlConnectionFactory, MyPostgreSqlConnectionFactory>();
}

Optimizing server resources

The default Examine indexes from Umbraco CMS are no longer in use, if this search provider powers all things search - that is:

• Frontend search.
• Backoffice search.
• The Delivery API (if applicable on your site).

However, Umbraco CMS continues to keep them up-to-date with content changes. Since this is a waste of server resources, the default Examine indexes can be explicitly disabled by means of composition:

using Umbraco.Cms.Core.Composing;
using Kjac.SearchProvider.PostgreSql.DependencyInjection;

namespace My.Site;

public class DisableDefaultIndexesComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
        => builder.DisableDefaultExamineIndexes();
}

Contributing

Yes, please ❤️

When raising an issue, please make sure to include plenty of context, steps to reproduce and any other relevant information in the issue description 🥺

If you're submitting a PR, please:

1. Also include plenty of context and steps to reproduce.
2. Make sure your code follows the provided editor configuration.
3. If at all possible, create tests that prove the issue has been fixed.
   • You'll find instructions on running the tests here.