PostgreSQL Provider

The PostgreSQL provider is the primary search provider for Orleans.Search, offering robust full-text search and excellent query performance.

Installation

dotnet add package TGHarker.Orleans.Search.PostgreSql

Configuration

Basic Setup

builder.Services.AddOrleansSearch()
    .UsePostgreSql("Host=localhost;Database=orleanssearch;Username=postgres;Password=postgres");

With Custom Options

builder.Services.AddOrleansSearch()
    .UsePostgreSql(options =>
    {
        options.UseNpgsql(connectionString, npgsqlOptions =>
        {
            npgsqlOptions.EnableRetryOnFailure(3);
            npgsqlOptions.CommandTimeout(30);
        });
    });

From Configuration

// appsettings.json
{
  "ConnectionStrings": {
    "SearchIndex": "Host=localhost;Database=orleanssearch;Username=postgres;Password=postgres"
  }
}
 
// Program.cs
builder.Services.AddOrleansSearch()
    .UsePostgreSql(builder.Configuration.GetConnectionString("SearchIndex")!);

Connection String Format

Standard Npgsql connection string format:

Host=hostname;Port=5432;Database=dbname;Username=user;Password=password

Parameter	Description	Default
`Host`	Server hostname or IP	Required
`Port`	Server port	`5432`
`Database`	Database name	Required
`Username`	PostgreSQL user	Required
`Password`	User password	Required
`SSL Mode`	SSL connection mode	`Prefer`

Database Setup

Automatic Schema Creation

The PostgreSQL provider uses EF Core, which automatically creates tables on first use. No manual migration is required.

Manual Database Creation

If you prefer to create the database manually:

CREATE DATABASE orleanssearch;

Tables are created automatically when the application starts.

PostgreSqlSearchContext

The provider uses PostgreSqlSearchContext, a custom DbContext that:

Auto-discovers entities - Scans assemblies for ISearchEntity implementations
Applies configurations - Finds and applies all IEntityTypeConfiguration<T> types
Creates indexes - Sets up indexes for queryable and full-text searchable properties

Entity Discovery

Generated entities are automatically registered:

// Generated by source generator
public class UserStateEntity : ISearchEntity
{
    [Key]
    public string GrainId { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string DisplayName { get; set; } = string.Empty;
    public bool IsActive { get; set; }
    public string? SearchVector { get; set; }
    public long Version { get; set; }
    public DateTime LastUpdated { get; set; }
}

The DbContext discovers this entity at runtime - no manual DbSet<T> declaration needed.

Full-Text Search

PostgreSQL’s full-text search is supported via the [FullTextSearchable] attribute.

How It Works

Properties marked [FullTextSearchable] are concatenated into a SearchVector column
A GIN index is created on the SearchVector for fast searching
Queries using .Contains() leverage PostgreSQL’s text search

Example

[Searchable(typeof(IProductGrain))]
public class ProductState
{
    [Queryable]
    [FullTextSearchable(Weight = 2.0)]
    public string Name { get; set; } = string.Empty;
 
    [Queryable]
    [FullTextSearchable(Weight = 1.0)]
    public string Description { get; set; } = string.Empty;
}
 
// Searches both Name and Description
var products = await client.Search<IProductGrain>()
    .Where(p => p.Name.Contains("laptop"))
    .ToListAsync();

Indexing

Automatic Indexes

Properties marked with [Queryable(Indexed = true)] get database indexes:

[Queryable(Indexed = true)]
public string Category { get; set; }

Generates:

CREATE INDEX IX_ProductState_Category ON "ProductStates" ("Category");

Custom Index Names

[Queryable(Indexed = true, IndexName = "IX_Product_Cat")]
public string Category { get; set; }

Connection Pooling

Npgsql manages connection pooling automatically:

Host=localhost;Database=orleanssearch;Username=postgres;Password=postgres;Minimum Pool Size=10;Maximum Pool Size=100

Parameter	Default	Description
`Minimum Pool Size`	0	Minimum connections to keep open
`Maximum Pool Size`	100	Maximum concurrent connections
`Connection Idle Lifetime`	300	Seconds before idle connection is closed

Cloud Providers

Azure Database for PostgreSQL

.UsePostgreSql("Host=myserver.postgres.database.azure.com;Database=orleanssearch;Username=admin@myserver;Password=mypassword;SSL Mode=Require")

AWS RDS PostgreSQL

.UsePostgreSql("Host=myinstance.xxxx.us-east-1.rds.amazonaws.com;Database=orleanssearch;Username=postgres;Password=mypassword;SSL Mode=Require")

Google Cloud SQL

.UsePostgreSql("Host=/cloudsql/project:region:instance;Database=orleanssearch;Username=postgres;Password=mypassword")

Troubleshooting

Connection Refused

Npgsql.NpgsqlException: Failed to connect to localhost:5432

Verify PostgreSQL is running
Check host and port
Ensure firewall allows connections

Authentication Failed

Npgsql.PostgresException: password authentication failed for user "postgres"

Verify username and password
Check pg_hba.conf authentication settings

SSL Required

Npgsql.PostgresException: SSL connection is required

Add SSL Mode=Require to connection string.

Performance Tips

Use indexes - Mark frequently-filtered properties with [Queryable(Indexed = true)]
Limit full-text fields - Only mark essential text properties with [FullTextSearchable]
Size connection pool - Match pool size to expected concurrency
Monitor queries - Use PostgreSQL’s pg_stat_statements extension

Next Steps

Creating Custom Providers - Build providers for other databases
Query API - Learn query methods

Overview Creating Custom Providers