Fluent API for creating Indexes on jsonb

[leonkosak]

My use case:
Jsonb column stores json documents, which are:

• properties only on 1st level
• only decimals (and some integers), few booleans and very rare strings (up to 7 characters)

There are no nested objects.
When document is inserted in jsonb, there is little chance that properties are updated. The main operation is reading.

There has to be index on every property (because of real-time overview in application with filter applied) - real-time datagrid widget and map.
Ther will be many properties inside json document (100+).

My question is the following:
Is possible to create (define) indexes for properties inside jsonb column in EF Core migrations (builder)?

Are there more optimal solutions for my use case? I would like to stay away from Elasticsearch for now (because data copying adds some delay and this is not acceptable).

Thank you for explanations.

[roji]

@leonkosak while the EF Core Npgsql provider provides various features for creating your index (e.g. GIN indexes which are well-adapted to jsonb), even if it lacks something, it's always possible (and encouraged) to use raw SQL in your migrations to perform the exact migration operation you want (see the EF docs).

So I'd concentrate on the PostgreSQL side, and on exactly which kind of index (or indexes) you want to create - EF Core will not be a blocking factor for applying any indexing strategy.

For the PG side, start by looking at the docs. Adding a GIN index for each and every one of your properties may be a bit heavy, as indexes must be kept up to date as your add and modify JSON documents (this may or may not be a problem depending on how often you change your data). All that should be part of your database schema/indexing design, rather than anything related to Npgsql or EF Core.

[roji]

Using the issue to track looking into a Fluent API that could be used to create jsonb indexes.