The PostgreSQL Payoff: ltree Hierarchies and jsonb Payloads

Eight parts in, a fair question: all this machinery — schema-aware contexts, replaced EF internals, provisioning services, fleet orchestration — delivers what, exactly? One table. This part is about that table, and why it justifies having picked PostgreSQL before any of the multi-tenancy decisions were made.

The domain is a crumb trail: every significant action in a tenant's system drops a crumb, crumbs form hierarchies (a batch job spawns steps, steps spawn retries), and each crumb carries an arbitrary payload. Hierarchies plus flexible payloads is exactly the combination that makes relational modellers reach for either a graph database or a shrug. PostgreSQL has native answers for both — ltree and jsonb — and they were the reason this platform was never going to be database-agnostic.

The entity: three trees and a document

[Table("crumb")]
public class Crumb
{
    [Key] [Column("id")]
    public Guid Id { get; set; } = Guid.NewGuid();

    [Column("parent_id")]
    public Guid? ParentId { get; set; }
    public Crumb? Parent { get; set; }
    public ICollection<Crumb> Children { get; set; } = new List<Crumb>();

    [Column("correlation_id", TypeName = "ltree")]
    public string CorrelationId { get; set; } = string.Empty;

    [Column("type", TypeName = "ltree")]
    public string Type { get; set; } = string.Empty;

    [Column("path", TypeName = "ltree")]
    public string Path { get; set; } = string.Empty;

    [Column("crumb_data", TypeName = "jsonb")]
    public string? CrumbData { get; set; }

    [Column("version")]
    public int Version { get; set; } = 1;

    [Column("root")]
    public bool Root { get; set; }
}

Three ltree columns is not decoration — each one answers a different query family. Path is the structural hierarchy (batch_42.step_3.retry_1): ancestors, descendants, depth. Type is a taxonomy (job.import.customers), so “all import crumbs of any kind” is a subtree match rather than a LIKE. CorrelationId is the cross-cutting trail — one logical operation's crumbs share a correlation prefix even when they land in different structural branches. Same data type, three different meanings; hierarchical matching is just the query idiom all three share.

Notice also what the entity does not have: no TenantId. Isolation is the schema's job (parts 1–4); the domain model stays clean of it. That is the whole architecture in one absence.

Indexes are the actual feature

ltree without a GiST index is a slow string column with delusions. The schema-aware migration from part 3 creates the right index types per column:

migrationBuilder.CreateIndex(
        name: "IX_crumb_path", table: "crumb",
        schema: schemaName, column: "path")
    .Annotation("Npgsql:IndexMethod", "gist");

migrationBuilder.CreateIndex(
        name: "IX_crumb_crumb_data", table: "crumb",
        schema: schemaName, column: "crumb_data")
    .Annotation("Npgsql:IndexMethod", "gin");

GiST for the trees, GIN for the document. The rule of thumb that has served me: GiST answers “is X inside/under Y” for hierarchies and geometries; GIN answers “does this document contain Y” for jsonb and full-text. Getting them backwards does not error — it just quietly refuses to be used by the planner, which is worse. And because these indexes are created inside the migration, every tenant schema gets them identically; there is no “we forgot the index on tenant 73” failure mode.

The ltree extension itself is installed once per database (extensions are database-scoped, as part 8 covered), by the very first migration in the set:

migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS ltree;");

The read side speaks PostgreSQL, not LINQ

Writes go through EF Core — change tracking and the unit of work earn their keep there. Reads go through Dapper, because the queries worth writing here are PostgreSQL-native and I wanted them verbatim, not negotiated with a LINQ provider. The repository reads like a tour of the operator set.

Subtree matching with lquery patterns — the ~ operator:

var sql = @"
    SELECT * FROM crumb
    WHERE path ~ @PathPattern
    ORDER BY path";

A pattern like batch_42.* matches the whole subtree; *.retry_1 finds every retry anywhere; job.*{1} matches exactly one level below job. Each of those, expressed relationally, is a recursive join; here it is an indexed WHERE clause.

Containment on the payload — jsonb's @> operator:

var sql = @"
    SELECT * FROM crumb
    WHERE crumb_data @> @JsonValue
    ORDER BY path";

var jsonValue = JsonSerializer.Serialize(
    new Dictionary<string, object> { { jsonPath, value } });

“Find crumbs whose payload contains {"status": "failed"}” — served by the GIN index, no schema migration required when someone adds a new payload field next sprint. This is the honest use case for jsonb: attributes you query by but do not join on. The moment a payload field needs a foreign key, it has earned a real column.

And one deliberate anachronism — ancestors are fetched with a recursive CTE over parent_id, not with ltree:

var sql = @"
    WITH RECURSIVE ancestors AS (
        SELECT c.*, 0 as level FROM crumb c WHERE c.id = @ChildId
        UNION ALL
        SELECT p.*, a.level + 1
        FROM crumb p
        INNER JOIN ancestors a ON p.id = a.parent_id
    )
    SELECT * FROM ancestors WHERE level > 0 ORDER BY level";

Why keep the adjacency-list FK at all when Path encodes the ancestry? Because the two disagree about change. Reparent a node and the FK update is one row; the ltree update is the whole subtree's paths. Keeping both — FK as the source of truth for structure, ltree as the indexed acceleration for reads — is a classic denormalisation, with the classic obligation attached: something must keep them in sync, and that something is application code. It is the least comfortable coupling in the data plane, and I would consider a trigger-maintained path column if I rebuilt it.

The multi-tenancy fine print

Two Dapper-specific gotchas, both earned the hard way.

Quoted identifiers. EF creates tables preserving case, so every hand-written SQL statement must quote the way EF does — the repo's convention (documented in DECISIONS.md, because it will trip the next person) is quoted "PascalCase" for control-plane tables and lower-case snake for the data plane. Dapper does not translate names; discipline does.

The schema has to come from somewhere. EF's queries are schema-qualified automatically — the model carries the schema (part 4 made sure the right model does). Dapper has no model. The read repository takes its connection from the same SchemaAwareDbContextFactory, but a raw SELECT * FROM crumb resolves via the connection's search_path, which by default is public. The current code leans on that default more than I like: it works in the deployed topology, but the robust pattern is either schema-qualifying every statement (SELECT * FROM "tenant_a1b2c3d4".crumb) or setting SearchPath on the connection string per tenant. Npgsql supports the latter in one line, and it is on part 10's list — because a hybrid ORM strategy means every multi-tenancy guarantee must be implemented twice, once per tool. That sentence is the real cost of EF-for-writes/Dapper-for-reads, and nobody puts it on the architecture diagram.

Was it worth it?

For this domain — write-heavy trails, subtree-shaped reads, payloads that evolve weekly — emphatically yes. The alternative designs all move the complexity somewhere worse: closure tables (a second table to keep consistent), materialised path strings with LIKE (no real index semantics), EAV for the payload (the less said the better). PostgreSQL's answer is two extensions, two index types, and queries that say what they mean.

The price is lock-in, and I paid it knowingly: ltree, jsonb operators and GiST indexes do not port to SQL Server, and even the migrations install extensions by name. This platform is married to PostgreSQL. Given that parts 3 through 8 were also built on PostgreSQL's schema semantics, the marriage was already consummated; the data plane just made it public.

One part remains: the retrospective — what I would keep, what I would rebuild, and the honest ledger of shortcuts this series has been footnoting all along.