Introduction

pg_mentat is a PostgreSQL extension that implements Datomic's data model and query language entirely within PostgreSQL. It provides an entity-attribute-value store with immutable history, Datalog query execution, a pull API, temporal queries, and ACID transaction processing -- all accessible through standard SQL function calls.

What pg_mentat Provides

• Datomic-compatible schema -- define attributes with value types, cardinalities, uniqueness constraints, and component semantics
• Datalog queries -- full :find, :where, :in, :with support compiled to efficient SQL
• Pull API -- declarative entity retrieval with nested navigation, recursion, and cycle detection
• Time travel -- query the database as-of any past transaction, or view full history
• Immutable audit trail -- every fact ever asserted or retracted is retained
• Multi-store isolation -- run multiple independent databases within a single PostgreSQL instance
• mentatd HTTP server -- Datomic client protocol-compatible HTTP interface

Key Facts

Why pg_mentat?

Datomic pioneered the idea of an immutable, accumulate-only database with first-class time semantics. pg_mentat brings that model into PostgreSQL, which means:

1. No separate infrastructure -- your EAV store lives in the same database as your relational data.
2. SQL interop -- join Datalog query results with regular tables, use PostgreSQL's full ecosystem of tools.
3. Operational simplicity -- backup, replication, monitoring, and access control all use standard PostgreSQL mechanisms.
4. Performance -- queries compile to native SQL executed through PostgreSQL's SPI, benefiting from its query optimizer and index infrastructure.

How It Works

pg_mentat stores facts (datoms) in nine type-specific narrow tables, each with four covering indexes (EAVT, AEVT, AVET, VAET). Datalog queries are parsed from EDN, compiled into SQL with parameterized bindings, and executed via PostgreSQL's Server Programming Interface (SPI). Results are returned as JSON.

-- Install the extension
CREATE EXTENSION pg_mentat;

-- Define a schema attribute
SELECT mentat.t('[
  {:db/ident       :person/name
   :db/valueType   :db.type/string
   :db/cardinality :db.cardinality/one}
]');

-- Assert a fact
SELECT mentat.t('[{:person/name "Alice"}]');

-- Query
SELECT mentat.q('[:find ?name :where [?e :person/name ?name]]');

-- Pull all attributes for the entity
SELECT mentat.pull('[*]', 10001);

Note: mentat.t, mentat.q, and mentat.pull are convenience aliases for the underlying mentat.mentat_transact, mentat.mentat_query, and mentat.mentat_pull functions. The mentat_ prefix exists so names read naturally when the extension is installed into a non-default schema (e.g., myapp.mentat_query(...)). See SQL Function Reference for the full mapping.

Getting Started

Installation

From Source

Prerequisites:

• Rust 1.88+ (install via rustup)
• PostgreSQL 15-17 with development headers
• LLVM/Clang development libraries (for pgrx bindgen)
• pkg-config, build-essential (or equivalent)

On Debian/Ubuntu:

sudo apt-get install -y postgresql-16 postgresql-server-dev-16 \
    libpq-dev build-essential pkg-config libclang-dev clang

On Fedora/RHEL:

sudo dnf install -y postgresql16-server postgresql16-devel \
    clang-devel llvm-devel pkg-config

On macOS (Homebrew):

brew install postgresql@16 llvm pkg-config

Then build and install:

git clone https://github.com/gburd/pg_mentat.git
cd pg_mentat

# Install cargo-pgrx (the PostgreSQL extension build tool)
cargo install --locked cargo-pgrx --version 0.17.0

# Tell pgrx where your PostgreSQL is installed
cargo pgrx init --pg16 $(which pg_config)

# Build and install the extension into your PostgreSQL
cd pg_mentat
cargo pgrx install --release --no-default-features --features pg16

Docker

docker build -t pg_mentat .
docker run -d --name pg_mentat \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  pg_mentat

Or use Docker Compose for the full stack (pg_mentat + mentatd + Prometheus + Grafana):

docker compose -f docker/docker-compose.yml up -d

From Source (Nix)

If you use Nix, the provided flake handles all dependencies:

git clone https://github.com/gburd/pg_mentat.git
cd pg_mentat
nix develop

# Everything is pre-configured — just build
cd pg_mentat
cargo pgrx install --release --no-default-features --features pg16

Quick Start

1. Create the Extension

CREATE EXTENSION pg_mentat;

This creates the mentat schema with all required tables, types, indexes, and functions.

2. Define Schema Attributes

Schema attributes describe the shape of your data. Every attribute needs an ident (namespaced keyword), a value type, and a cardinality.

SELECT mentat.t('[
  {:db/ident       :person/name
   :db/valueType   :db.type/string
   :db/cardinality :db.cardinality/one
   :db/unique      :db.unique/identity}

  {:db/ident       :person/age
   :db/valueType   :db.type/long
   :db/cardinality :db.cardinality/one}

  {:db/ident       :person/email
   :db/valueType   :db.type/string
   :db/cardinality :db.cardinality/many}

  {:db/ident       :person/friends
   :db/valueType   :db.type/ref
   :db/cardinality :db.cardinality/many}
]');

3. Transact Data

Use tempids (string identifiers) to create new entities. References between entities in the same transaction resolve automatically.

SELECT mentat.t('[
  {:db/id "alice"
   :person/name "Alice"
   :person/age 30
   :person/email "alice@example.com"}

  {:db/id "bob"
   :person/name "Bob"
   :person/age 25
   :person/friends "alice"}
]');

The return value is a JSON report containing the transaction ID, timestamp, and tempid resolution map.

4. Query with Datalog

-- Find all people over 25
SELECT mentat.q('
  [:find ?name ?age
   :where [?e :person/name ?name]
          [?e :person/age ?age]
          [(> ?age 25)]]
');

-- Find friends-of-friends (with input binding)
SELECT mentat.q('
  [:find ?friend-name
   :in $ ?name
   :where [?e :person/name ?name]
          [?e :person/friends ?f]
          [?f :person/name ?friend-name]]
', '["Alice"]');

5. Pull Entity Data

-- Pull all attributes for entity 10001
SELECT mentat.pull('[*]', 10001);

-- Pull specific attributes with nested navigation
SELECT mentat.pull(
  '[:person/name :person/age {:person/friends [:person/name]}]',
  10001
);

6. Time Travel

-- Query the database as it was at transaction 1000
SELECT mentat.q('[:find ?name :where [?e :person/name ?name]]', '[]', 1000, NULL);

-- View transaction log
SELECT mentat.log('default', 1000, 1010);

-- What changed between two transactions?
SELECT mentat.diff('default', 1000, 1005);

Next Steps

• Architecture -- understand how pg_mentat stores and queries data
• Datalog Query Language -- complete query syntax reference
• SQL Function Reference -- all available SQL functions
• Schema Reference -- attribute definitions and value types

Architecture

pg_mentat implements the Datomic data model within PostgreSQL using pgrx (Rust PostgreSQL extension framework). This chapter describes the storage layout, query compilation pipeline, transaction processing, and pull architecture.

Storage Model

Narrow Per-Type Tables

pg_mentat stores datoms (facts) in nine type-specific narrow tables. Each table stores values of exactly one PostgreSQL native type, enabling the query optimizer to use type-appropriate comparison operators and indexes without casts.

Each table has columns: e (entity), a (attribute entid), v (typed value), tx (transaction ID), added (boolean: assert or retract).

Compatibility View

A mentat.datoms view is provided as a compatibility shim over the nine narrow tables with INSTEAD OF triggers. This allows legacy code to INSERT INTO mentat.datoms or DELETE FROM mentat.datoms -- the triggers route to the appropriate narrow table. New code should use the narrow tables directly.

Covering Indexes

Each narrow table has four covering indexes, mirroring Datomic's index model:

This is 4 indexes per table x 9 tables = 36 indexes total. The VAET index is only useful on datoms_ref_new but is maintained on all tables for uniformity.

Supporting Tables

Query Compilation Pipeline

A Datalog query flows through four stages:

EDN text --> Parse --> Compile --> SQL text --> SPI Execute --> JSON result

Stage 1: EDN Parse

The edn crate (workspace member) parses the EDN query string into a ParsedQuery struct containing:

• find -- find specification (relation, collection, tuple, scalar)
• where_clauses -- pattern matches, predicates, function calls, not/or clauses
• in_bindings -- input parameter bindings (scalar, collection, tuple, relation)
• with -- additional grouping variables
• rules -- named rule definitions

Stage 2: Datalog Compile

The query compiler (pg_mentat/src/functions/query.rs) transforms the parsed query into SQL:

1. Schema resolution -- resolve keyword attributes (:person/name) to entid integers via the schema cache
2. Variable binding -- assign SQL aliases to each pattern variable (?e -> t0.e)
3. Table selection -- route each pattern to the correct narrow table based on the attribute's value type
4. Join generation -- unify shared variables across patterns via JOIN ... ON t0.e = t1.e
5. Predicate translation -- convert Datalog predicates to SQL WHERE conditions with type guards
6. NOT/OR compilation -- not becomes NOT EXISTS (subquery), or becomes UNION
7. Aggregate wrapping -- wrap the base query in GROUP BY with aggregate functions
8. Find spec projection -- shape the output (array of arrays, single value, etc.)

All string constants use parameterized bindings ($1, $2, ...) via the SqlBuilder to prevent SQL injection.

Stage 3: SQL Execution

The generated SQL is executed through PostgreSQL's SPI (Server Programming Interface). Results are collected into a serde_json::Value and returned as JSONB.

Stage 4: Result Shaping

The JSON result is shaped according to the find specification:

• Relation [:find ?x ?y] -- array of arrays: [[1, "Alice"], [2, "Bob"]]
• Collection [:find [?x ...]] -- flat array: [1, 2, 3]
• Tuple [:find [?x ?y]] -- single array: [1, "Alice"]
• Scalar [:find ?x .] -- single value: 1

Transaction Pipeline

Transaction processing (pg_mentat/src/functions/transact.rs) follows these steps:

EDN tx data --> Parse --> Schema detect --> Tempid allocate -->
  Type route --> Upsert resolve --> Insert/Retract --> Commit

Steps in Detail

1. Parse -- EDN transaction data is parsed into assertion/retraction operations (:db/add, :db/retract, map forms, :db/retractEntity, :db.fn/cas)

2. Schema detection -- operations targeting schema attributes (:db/ident, :db/valueType, etc.) are identified; these will update the schema table and invalidate caches

3. Transaction ID allocation -- a new transaction ID is obtained from the mentat.transactions sequence

4. Tempid resolution -- string tempids ("tempid-1") are mapped to freshly allocated entity IDs from the partition sequence

5. Lookup ref resolution -- lookup refs like [:person/email "alice@example.com"] are resolved to entity IDs

6. Upsert handling -- for attributes with :db/unique :db.unique/identity, existing entities are found and reused rather than creating duplicates

7. Type routing -- each datom is inserted into the appropriate narrow table based on the attribute's value type

8. CAS validation -- :db.fn/cas operations verify the current value matches the expected value before asserting the new value

9. Commit -- the transaction record is written to mentat.transactions; if schema changed, the cache generation counter is bumped

Speculative Transactions

mentat_with() wraps the transaction in a savepoint and rolls back after computing the result. This lets you see what a transaction would do without persisting it.

Pull Architecture

The pull API (pg_mentat/src/functions/pull.rs) implements Datomic's declarative entity retrieval:

Pull pattern --> Parse --> Recursive fetch --> Cycle detection --> JSON assembly

Key implementation details:

• Recursive navigation -- forward refs (:person/friends) and reverse refs (:person/_friends) are followed to arbitrary depth
• Cycle detection -- a visited-entity set prevents infinite loops in cyclic reference graphs
• Bounded recursion -- {:person/friends 3} limits traversal depth
• Component auto-expansion -- attributes marked :db/isComponent true are automatically pulled recursively
• Wildcard -- [*] retrieves all attributes for an entity
• Default values -- (default :person/age 0) provides fallbacks for missing attributes
• Limits -- (limit :person/email 5) caps cardinality-many results

Schema Cache

pg_mentat maintains a process-local LRU schema cache (SchemaCache) per store that maps attribute keywords to their metadata (entid, value type, cardinality, etc.). This avoids repeated schema table lookups during query compilation.

Cache invalidation uses a generation counter stored in mentat.cache_generation:

• After a schema-affecting transaction, the counter is bumped
• Before query compilation, the local cache checks its generation against the table
• If stale, the cache is reloaded from mentat.schema

This ensures cross-backend consistency when multiple PostgreSQL backends modify schema concurrently.

Multi-Store Architecture

pg_mentat supports multiple isolated stores within a single PostgreSQL database. Each store gets its own PostgreSQL schema (e.g., mentat_mystore) containing independent copies of all tables and indexes. The default store uses the mentat schema.

SELECT mentat.create_store('analytics');
SELECT mentat.t('analytics', '[{:db/ident :event/type ...}]');
SELECT mentat.q('analytics', '[:find ?e :where [?e :event/type "click"]]', '{}');

Datalog Query Language

pg_mentat implements the Datomic dialect of Datalog. Queries are written in EDN (Extensible Data Notation) and passed to mentat_query() as text strings.

Query Structure

A query has the general form:

[:find <find-spec>
 :in <input-bindings>       ;; optional
 :with <with-vars>          ;; optional
 :where <clauses>]

Find Specifications

The :find clause determines the shape of the result.

Relation (default)

Returns a collection of tuples (array of arrays):

SELECT mentat_query('[:find ?name ?age
                      :where
                      [?e :person/name ?name]
                      [?e :person/age ?age]]', '{}');
-- Result: {"columns":["name","age"],"results":[["Alice",30],["Bob",25]]}

Collection

Returns a flat array of values (single variable, all matches):

SELECT mentat_query('[:find [?name ...]
                      :where [?e :person/name ?name]]', '{}');
-- Result: {"results":["Alice","Bob","Carol"]}

Tuple

Returns a single tuple (first match only):

SELECT mentat_query('[:find [?name ?age]
                      :where
                      [?e :person/name ?name]
                      [?e :person/age ?age]]', '{}');
-- Result: {"results":["Alice",30]}

Scalar

Returns a single value (first match, first variable):

SELECT mentat_query('[:find ?name .
                      :where [?e :person/name ?name]]', '{}');
-- Result: {"results":"Alice"}

Where Clauses

Basic Pattern

A pattern matches datoms against the EAV model:

[?entity :attribute ?value]
[?entity :attribute "literal"]
[?entity :attribute]          ;; value ignored (existence check)

Each position can be a variable (?x), a literal, or blank (_).

SELECT mentat_query('[:find ?e
                      :where
                      [?e :person/name "Alice"]]', '{}');

Multiple Patterns (Implicit AND)

Multiple patterns in :where are joined -- a variable must unify across all patterns:

SELECT mentat_query('[:find ?name ?email
                      :where
                      [?e :person/name ?name]
                      [?e :person/email ?email]]', '{}');

NOT Clauses

Exclude results matching a pattern:

[:find ?name
 :where
 [?e :person/name ?name]
 (not [?e :person/age 25])]

Compiles to NOT EXISTS (subquery).

NOT-JOIN

Explicit variable binding for NOT:

[:find ?name
 :where
 [?e :person/name ?name]
 (not-join [?e]
   [?e :person/retired true])]

OR Clauses

Match any of several alternatives:

[:find ?name
 :where
 [?e :person/name ?name]
 (or [?e :person/role "admin"]
     [?e :person/role "superuser"])]

Compiles to UNION.

OR-JOIN

Explicit variable binding for OR:

[:find ?name
 :where
 [?e :person/name ?name]
 (or-join [?e]
   [?e :person/active true]
   [?e :person/admin true])]

Predicates

Predicates filter results using comparison operators. They appear in the :where clause with function-call syntax:

[(> ?age 21)]
[(< ?price 100.0)]
[(<= ?start ?end)]
[(>= ?score 90)]
[(= ?status "active")]
[(!= ?role "banned")]

Arithmetic

Arithmetic expressions bind their result to a variable:

[:find ?name ?total
 :where
 [?e :person/name ?name]
 [?e :order/price ?price]
 [?e :order/quantity ?qty]
 [(* ?price ?qty) ?total]
 [(> ?total 1000)]]

Supported operators: +, -, *, /.

String Predicates (Extension)

pg_mentat extends Datomic's predicate set with PostgreSQL's pattern matching:

[(like ?name "Ali%")]
[(ilike ?email "%@EXAMPLE.COM")]

Type Safety

Predicates that compare incompatible types (e.g., comparing a string variable against an integer) produce an empty result set rather than an error. This matches Datomic's behavior.

Query Functions

ground

Bind a constant value to a variable:

[:find ?name
 :where
 [(ground 42) ?answer]
 [?e :person/age ?answer]
 [?e :person/name ?name]]

get-else

Return a default value when an attribute is missing:

[:find ?name ?age
 :where
 [?e :person/name ?name]
 [(get-else $ ?e :person/age 0) ?age]]

Compiles to LEFT JOIN ... COALESCE(v, default).

missing?

Test that an entity lacks an attribute:

[:find ?name
 :where
 [?e :person/name ?name]
 [(missing? $ ?e :person/email)]]

Compiles to NOT EXISTS subquery.

Aggregates

Aggregate functions wrap find variables:

[:find (count ?e)
 :where [?e :person/name]]

[:find ?dept (avg ?salary) (max ?salary)
 :where
 [?e :person/department ?dept]
 [?e :person/salary ?salary]]

Supported Aggregates

Non-aggregated variables in the find spec become the GROUP BY columns automatically.

Rules

Rules are named, reusable query fragments. Define them in the :in clause with the % symbol and provide rule definitions as the corresponding input.

Basic Rules

SELECT mentat_query(
  '[:find ?name
    :in $ %
    :where (adult ?e)
           [?e :person/name ?name]]',
  '{"inputs": [null, [["(adult ?person)", "[?person :person/age ?age]", "[(>= ?age 18)]"]]]}'
);

Recursive Rules

Rules can reference themselves for graph traversal:

-- Find all ancestors (transitive parent-of)
SELECT mentat_query(
  '[:find ?ancestor
    :in $ % ?person
    :where (ancestor ?person ?ancestor)]',
  '{"inputs": [null, [
    ["(ancestor ?p ?a)", "[?p :person/parent ?a]"],
    ["(ancestor ?p ?a)", "[?p :person/parent ?mid]", "(ancestor ?mid ?a)"]
  ], 10001]}'
);

Recursive rules compile to PostgreSQL WITH RECURSIVE CTEs with a depth limit controlled by the mentat.max_recursion_depth GUC (default 100).

Multi-head Rules

Multiple rule definitions with the same name act as alternatives (logical OR):

[[(related ?a ?b) [?a :person/friends ?b]]
 [(related ?a ?b) [?a :person/colleagues ?b]]]

Input Bindings

The :in clause declares external parameters. The implicit first input is always $ (the database).

Scalar Binding

[:find ?name
 :in $ ?min-age
 :where
 [?e :person/name ?name]
 [?e :person/age ?age]
 [(>= ?age ?min-age)]]

SELECT mentat_query(
  '[:find ?name :in $ ?min-age :where [?e :person/name ?name] [?e :person/age ?age] [(>= ?age ?min-age)]]',
  '{"inputs": [null, 21]}'
);

Collection Binding

Match against a set of values:

[:find ?name
 :in $ [?city ...]
 :where
 [?e :person/name ?name]
 [?e :person/city ?city]]

SELECT mentat_query(
  '[:find ?name :in $ [?city ...] :where [?e :person/name ?name] [?e :person/city ?city]]',
  '{"inputs": [null, ["NYC", "SF", "LA"]]}'
);

Compiles to IN (...) or a values join.

Tuple Binding

Bind multiple variables at once:

[:find ?name
 :in $ [?first ?last]
 :where
 [?e :person/first-name ?first]
 [?e :person/last-name ?last]
 [?e :person/name ?name]]

Relation Binding

Pass a table of values:

[:find ?name ?score
 :in $ [[?name ?score]]
 :where
 [?e :person/name ?name]
 [(> ?score 80)]]

SELECT mentat_query(
  '[:find ?name ?score :in $ [[?name ?score]] :where [?e :person/name ?name] [(> ?score 80)]]',
  '{"inputs": [null, [["Alice", 95], ["Bob", 72], ["Carol", 88]]]}'
);

Compiles to a VALUES join.

With Clause

The :with clause includes variables in grouping without including them in results. This prevents unwanted deduplication:

[:find (count ?name)
 :with ?e
 :where [?e :person/name ?name]]

Without :with ?e, duplicate names would be counted once. With it, each entity contributes separately.

Query Options

Options are passed in the JSON inputs parameter:

SELECT mentat_query(
  '[:find ?name :where [?e :person/name ?name]]',
  '{"as_of": 1000, "limit": 10}'
);

Pull API

The Pull API provides a declarative way to retrieve structured data from entities. Instead of writing Datalog queries, you specify a pattern describing which attributes to retrieve and how to navigate references.

Basic Usage

-- Pull specific attributes
SELECT mentat_pull('[:person/name :person/age]', 10001);
-- {"person/name": "Alice", "person/age": 30}

-- Pull all attributes
SELECT mentat_pull('[*]', 10001);
-- {"db/id": 10001, "person/name": "Alice", "person/age": 30, "person/email": ["a@b.com"]}

Pattern Syntax

A pull pattern is an EDN vector containing attribute specs:

[<attr-spec> ...]

Where each <attr-spec> can be:

Attribute Selection

Simple Attributes

SELECT mentat_pull('[:person/name :person/age :person/email]', 10001);

Returns only the specified attributes. Missing attributes are omitted from the result.

Wildcard

SELECT mentat_pull('[*]', 10001);

Returns all attributes for the entity, including :db/id. Reference attributes return entity IDs (not expanded).

Wildcard with Overrides

Combine wildcard with specific navigation for refs:

SELECT mentat_pull('[* {:person/friends [:person/name]}]', 10001);

Reference Navigation

Forward References (Map Specs)

Navigate a reference attribute and pull sub-attributes from the referenced entity:

SELECT mentat_pull(
  '[{:person/friends [:person/name :person/age]}]',
  10001
);
-- {"person/friends": [{"person/name": "Bob", "person/age": 25}]}

Map specs can be nested arbitrarily deep:

SELECT mentat_pull(
  '[{:person/friends [:person/name {:person/friends [:person/name]}]}]',
  10001
);

Reverse References

Use the _ prefix on a reference attribute to find entities that reference the target entity:

-- Find who has entity 10001 as a friend
SELECT mentat_pull('[:person/name :person/_friends]', 10001);
-- {"person/name": "Alice", "person/_friends": [{"db/id": 10002}]}

Reverse references can also use map specs:

SELECT mentat_pull('[{:person/_friends [:person/name]}]', 10001);

Recursion

Unbounded Recursion

Use ... to traverse a reference attribute to arbitrary depth. Cycle detection prevents infinite loops.

SELECT mentat_pull(
  '[:person/name {:person/manager ...}]',
  10001
);
-- Returns the full management chain up to the root

Bounded Recursion

Specify a maximum depth as an integer:

SELECT mentat_pull(
  '[:person/name {:person/friends 2}]',
  10001
);
-- Navigates friends-of-friends (2 levels) then stops

Cycle Detection

When traversing cyclic graphs (e.g., mutual friendships), pg_mentat tracks visited entity IDs and stops when a cycle is detected. Cyclic references appear as {:db/id <id>} without further expansion.

Modifiers

Default Values

Provide a fallback value when an attribute is missing:

SELECT mentat_pull(
  '[(default :person/nickname "N/A") :person/name]',
  10001
);
-- {"person/nickname": "N/A", "person/name": "Alice"}

Rename (:as)

Rename an attribute in the output:

SELECT mentat_pull(
  '[(:person/name :as :name) (:person/age :as :years)]',
  10001
);
-- {"name": "Alice", "years": 30}

Limit

Cap the number of values returned for cardinality-many attributes:

SELECT mentat_pull(
  '[(limit :person/email 3)]',
  10001
);
-- Returns at most 3 email addresses

Component Auto-Expansion

Attributes marked with :db/isComponent true in the schema are automatically expanded (pulled recursively) without needing an explicit map spec:

-- If :order/line-items is a component attribute:
SELECT mentat_pull('[*]', 20001);
-- Line items are fully expanded, not just entity IDs

Pull Many

Pull the same pattern for multiple entities:

SELECT mentat_pull_many(
  '[:person/name :person/age]',
  ARRAY[10001, 10002, 10003]
);
-- [{"person/name":"Alice","person/age":30}, {"person/name":"Bob","person/age":25}, ...]

Entities that do not exist return empty maps {} in their position.

Pull in Queries

While pg_mentat does not currently support pull expressions directly inside :find clauses (as Datomic does), you can compose queries and pulls:

-- First, find entity IDs
WITH people AS (
  SELECT jsonb_array_elements(
    (SELECT mentat_query('[:find [?e ...] :where [?e :person/age ?a] [(> ?a 21)]]', '{}'))->'results'
  )::bigint AS eid
)
-- Then pull full details
SELECT mentat_pull('[:person/name :person/age :person/email]', eid)
FROM people;

Performance Considerations

• Wildcard queries scan all nine type tables for the entity -- use specific attributes when you know what you need.
• Deep recursion can generate many SPI calls. Use bounded recursion in production.
• Pull many is more efficient than calling mentat_pull in a loop because it batches schema lookups.
• The pull implementation uses the schema cache, so the first call after a schema change may be slightly slower.

Time Travel

pg_mentat maintains a complete history of every assertion and retraction. Unlike conventional databases that overwrite data in place, pg_mentat is accumulate-only: retracting a fact records the retraction as a new event rather than deleting the original assertion.

This immutable history enables temporal queries -- looking at the database as it existed at any past transaction.

Concepts

Transaction IDs

Every transaction is assigned a monotonically increasing 64-bit integer (tx). Transaction IDs serve as both:

• A unique identifier for the transaction
• A logical timestamp (total ordering of all changes)

Basis-t

The "basis-t" is the most recent transaction ID in the database. It represents the current state.

Datom Lifecycle

A datom [entity attribute value tx added] has five components:

• added = true means the fact was asserted at transaction tx
• added = false means the fact was retracted at transaction tx

The "current" value of an attribute is determined by finding the most recent assertion that has not been subsequently retracted.

As-Of Queries

Query the database as it appeared at a specific transaction. Only datoms with tx <= target are visible, and retracted datoms are excluded.

Via Query Input

SELECT mentat_query(
  '[:find ?name ?age
    :where
    [?e :person/name ?name]
    [?e :person/age ?age]]',
  '{"as_of": 1050}'
);

Via Dedicated Function

SELECT mentat_as_of('default', 1050,
  '[:find ?name :where [?e :person/name ?name]]',
  '{}');

Use Cases

• Audit -- "What did the data look like when we made that decision?"
• Debugging -- "When did this value change?"
• Reproducibility -- "Re-run analysis against the same data snapshot"
• Undo exploration -- "What was the state before this transaction?"

Since Queries

Query only datoms asserted after a specific transaction. This shows what has changed.

SELECT mentat_query(
  '[:find ?e ?name
    :where
    [?e :person/name ?name]]',
  '{"since": 1050}'
);

Via Dedicated Function

SELECT mentat_since('default', 1050,
  '[:find ?e ?name :where [?e :person/name ?name]]',
  '{}');

Use Cases

• Change detection -- "What entities were modified since my last sync?"
• Incremental processing -- "Process only new data since last batch"
• Event sourcing -- "What happened since transaction T?"

History Queries

View the full history of an entity-attribute pair, including all assertions and retractions:

SELECT mentat_history('default', 10001, ':person/name');

Returns:

[
  {"value": "Alice Smith", "tx": 1001, "added": true},
  {"value": "Alice Smith", "tx": 1050, "added": false},
  {"value": "Alice Johnson", "tx": 1050, "added": true}
]

This shows that:

1. "Alice Smith" was asserted at tx 1001
2. "Alice Smith" was retracted at tx 1050
3. "Alice Johnson" was asserted at tx 1050 (a name change)

Transaction Range

View all datoms across a range of transactions:

SELECT mentat_tx_range('default', 1000, 1100);

Returns: All datoms (assertions and retractions) in transactions 1000 through 1100.

Transaction Log

Get structured transaction log entries with metadata:

SELECT mentat.log('default', 1000, 1010);

Returns: Array of transaction objects, each containing:

• tx -- transaction ID
• tx_instant -- timestamp
• datoms -- array of [e, a, v, tx, added] tuples

Diff

Compare two points in time:

SELECT mentat.diff('default', 1000, 1050);

Returns: Object with added and retracted arrays showing the net changes between the two transactions.

How Time Travel Works Internally

As-Of Implementation

As-of queries add a tx <= ?target_tx filter to every pattern in the generated SQL. The query compiler:

1. Adds AND d.tx <= $as_of to each table join
2. Applies retraction filtering: excludes datoms where a later retraction exists with tx <= target

Since Implementation

Since queries add AND d.tx > $since_tx to include only newer assertions.

History Implementation

History queries bypass the current-value filter entirely, returning all datoms (both assertions and retractions) for the specified entity-attribute pair, ordered by transaction ID.

Combining Time Travel with Other Features

Pull with As-Of

Currently, the pull API always operates on the current state. To pull at a specific point in time, use a Datalog query with as_of and then pull individual entities:

-- Find entities as of tx 1000
WITH historical_people AS (
  SELECT (jsonb_array_elements(
    (SELECT mentat_query(
      '[:find [?e ...] :where [?e :person/name]]',
      '{"as_of": 1000}'
    ))->'results')
  )::bigint AS eid
)
SELECT mentat_pull('[*]', eid) FROM historical_people;

Subscriptions with Since

Subscriptions notify on new transactions. Combine with since to process missed changes after reconnection:

-- On reconnect, catch up from last known tx
SELECT mentat_query(
  '[:find ?e ?name :where [?e :person/name ?name]]',
  '{"since": 1050}'
);

Excision

Excision is the only operation that breaks the immutability guarantee. It permanently removes entities and all their history from the database. See SQL Function Reference for details.

Excision is gated by a per-partition flag (allow_excision) and protected against accidentally excising schema entities (entid < 10000).

Schema Reference

In pg_mentat, schema is data. Attributes are defined by transacting schema entities with special built-in attributes. There is no DDL -- all schema changes are regular transactions.

Defining Attributes

An attribute is defined by transacting a map with :db/ident and at minimum :db/valueType and :db/cardinality:

SELECT mentat_transact('[
  {:db/ident       :person/name
   :db/valueType   :db.type/string
   :db/cardinality :db.cardinality/one}
]');

Multiple attributes can be defined in a single transaction:

SELECT mentat_transact('[
  {:db/ident       :person/name
   :db/valueType   :db.type/string
   :db/cardinality :db.cardinality/one
   :db/unique      :db.unique/identity
   :db/doc         "A persons full name"}

  {:db/ident       :person/age
   :db/valueType   :db.type/long
   :db/cardinality :db.cardinality/one
   :db/index       true}

  {:db/ident       :person/email
   :db/valueType   :db.type/string
   :db/cardinality :db.cardinality/many
   :db/unique      :db.unique/value}

  {:db/ident       :person/friends
   :db/valueType   :db.type/ref
   :db/cardinality :db.cardinality/many}

  {:db/ident       :order/line-items
   :db/valueType   :db.type/ref
   :db/cardinality :db.cardinality/many
   :db/isComponent true}
]');

Schema Attributes

Required

Optional

Value Types

pg_mentat supports nine value types, each stored in a dedicated narrow table with native PostgreSQL types:

Type Examples

SELECT mentat_transact('[
  ;; Boolean
  {:db/ident :user/active :db/valueType :db.type/boolean :db/cardinality :db.cardinality/one}

  ;; Long (integer)
  {:db/ident :user/login-count :db/valueType :db.type/long :db/cardinality :db.cardinality/one}

  ;; Double (floating point)
  {:db/ident :sensor/reading :db/valueType :db.type/double :db/cardinality :db.cardinality/one}

  ;; String
  {:db/ident :article/body :db/valueType :db.type/string :db/cardinality :db.cardinality/one}

  ;; Keyword
  {:db/ident :item/status :db/valueType :db.type/keyword :db/cardinality :db.cardinality/one}

  ;; Ref (entity reference)
  {:db/ident :item/category :db/valueType :db.type/ref :db/cardinality :db.cardinality/one}

  ;; Instant (timestamp)
  {:db/ident :event/timestamp :db/valueType :db.type/instant :db/cardinality :db.cardinality/one}

  ;; UUID
  {:db/ident :session/id :db/valueType :db.type/uuid :db/cardinality :db.cardinality/one}

  ;; Bytes
  {:db/ident :file/content :db/valueType :db.type/bytes :db/cardinality :db.cardinality/one}
]');

Cardinality

:db.cardinality/one

The attribute holds at most one value per entity. Asserting a new value for an entity implicitly retracts the old value.

-- Sets name to "Alice"
SELECT mentat_transact('[[:db/add 10001 :person/name "Alice"]]');
-- Implicitly retracts "Alice", asserts "Alicia"
SELECT mentat_transact('[[:db/add 10001 :person/name "Alicia"]]');

:db.cardinality/many

The attribute holds a set of values per entity. Asserting a value adds to the set; explicit retraction is required to remove values.

SELECT mentat_transact('[
  [:db/add 10001 :person/email "alice@work.com"]
  [:db/add 10001 :person/email "alice@home.com"]
]');
-- Entity 10001 now has both emails

Uniqueness

:db.unique/value

No two entities can have the same value for this attribute. Attempting to assert a duplicate value raises an error.

{:db/ident :person/ssn
 :db/valueType :db.type/string
 :db/cardinality :db.cardinality/one
 :db/unique :db.unique/value}

:db.unique/identity

Same uniqueness guarantee as :db.unique/value, but additionally enables upsert semantics: if a transaction asserts a value that already exists, the existing entity is reused instead of creating a new one.

{:db/ident :person/email
 :db/valueType :db.type/string
 :db/cardinality :db.cardinality/one
 :db/unique :db.unique/identity}

-- Creates entity if email is new; updates existing entity if email exists
SELECT mentat_transact('[
  {:person/email "alice@example.com"
   :person/name "Alice Updated"}
]');

Component Attributes

When :db/isComponent is true on a ref attribute:

1. Cascade retraction -- retracting the parent entity also retracts all component children (recursively)
2. Pull auto-expansion -- [*] pull patterns automatically expand component references
3. Ownership semantics -- the referenced entity is logically "owned" by the referencing entity

{:db/ident :order/line-items
 :db/valueType :db.type/ref
 :db/cardinality :db.cardinality/many
 :db/isComponent true}

Fulltext Attributes

Attributes with :db/fulltext true support full-text search with BM25 relevance scoring:

{:db/ident :article/body
 :db/valueType :db.type/string
 :db/cardinality :db.cardinality/one
 :db/fulltext true}

No-History Attributes

Attributes with :db/noHistory true do not retain historical values. Only the current assertion is kept; past values are not available via time-travel queries. Use this for high-churn data where history is not valuable (e.g., session tokens, ephemeral state).

{:db/ident :user/last-seen
 :db/valueType :db.type/instant
 :db/cardinality :db.cardinality/one
 :db/noHistory true}

Schema Alteration

Existing attribute properties can be modified by transacting against the attribute's entity ID:

-- Add an index to an existing attribute
SELECT mentat_transact('[
  {:db/id :person/age
   :db/index true}
]');

Restrictions:

• :db/valueType cannot be changed after data exists (would require data migration)
• :db/cardinality change from many-to-one requires that no entity has multiple values

Inspecting Schema

-- View the entire schema
SELECT mentat_schema();

-- Query schema as data
SELECT mentat_query(
  '[:find ?ident ?type
    :where
    [?a :db/ident ?ident]
    [?a :db/valueType ?type]]',
  '{}'
);

Namespacing Conventions

Attribute idents use namespaced keywords by convention:

• :person/name -- the name attribute in the person namespace
• :order/total -- the total attribute in the order namespace
• :db/ident -- built-in attributes use the db namespace

Namespaces are purely organizational -- they have no runtime semantics. Use them to group related attributes and avoid naming collisions.

Built-in Attributes

pg_mentat bootstraps these system attributes (entid < 100):

Cookbook: JOIN Datalog Results With Regular Postgres Tables

What this gives you

pg_mentat stores datoms in ordinary PostgreSQL tables (mentat.datoms_ref_new, mentat.datoms_text_new, mentat.datoms_keyword_new, ...). They are not opaque blobs, not a foreign data wrapper, and not a separate process you talk to over a socket. Any other table in the same database can join against them at the SQL level, in the same transaction, with the same MVCC snapshot. Datomic, XTDB, and Datalevin require two round trips and an in-application join to mix datalog results with relational data; pg_mentat does it in a single SQL statement and a single transaction.

This page shows the pattern. The schemas, queries, and outputs below are run against a live cookbook_demo database (Postgres 16.13, pg_mentat 1.3.0).

The example domain

Two halves of the same application:

• app.users and app.subscriptions are normal Postgres tables. They hold billing state that the rest of the codebase already reads with plain SQL, with foreign keys, sequences, and uniqueness constraints.
• The issue tracker lives in pg_mentat as datoms (:issue/title, :issue/state, :issue/assignee, :issue/created-at). Issues are an append-only event stream with cross-references; the EAV layout fits.

Setup

-- pg_mentat side: schema for users and issues
SELECT mentat_transact('[
  {:db/ident :user/email
   :db/valueType :db.type/string
   :db/cardinality :db.cardinality/one
   :db/unique :db.unique/identity}
  {:db/ident :user/display-name
   :db/valueType :db.type/string
   :db/cardinality :db.cardinality/one}
  {:db/ident :issue/title
   :db/valueType :db.type/string
   :db/cardinality :db.cardinality/one}
  {:db/ident :issue/state
   :db/valueType :db.type/keyword
   :db/cardinality :db.cardinality/one}
  {:db/ident :issue/assignee
   :db/valueType :db.type/ref
   :db/cardinality :db.cardinality/one}
  {:db/ident :issue/created-at
   :db/valueType :db.type/instant
   :db/cardinality :db.cardinality/one}
]');

-- Relational side: billing tables
CREATE SCHEMA app;

CREATE TABLE app.users (
  id                 BIGSERIAL PRIMARY KEY,
  email              TEXT NOT NULL UNIQUE,
  stripe_customer_id TEXT NOT NULL UNIQUE,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE app.subscriptions (
  stripe_customer_id TEXT PRIMARY KEY,
  plan               TEXT NOT NULL,
  status             TEXT NOT NULL,
  current_period_end TIMESTAMPTZ NOT NULL
);

(Skipped here: the obvious INSERTs into app.users/app.subscriptions and mentat_transact(...) calls that create users and issues. The full sample in docs/examples/cookbook-postgres-join.sql populates four users — Alice, Bob, Carol, Dan — with matching billing rows and five issues.)

The mixed query

The question: "Of all open issues, which ones are assigned to a user whose Stripe subscription is currently active?" Half of that lives in datoms, half in relational tables.

Pattern A: pure SQL JOIN against the narrow datom tables

SELECT
  title.v   AS issue_title,
  state.v   AS issue_state,
  uemail.v  AS assignee_email,
  s.plan,
  s.status
FROM mentat.datoms_ref_new      asg
JOIN mentat.datoms_text_new     title  ON title.e  = asg.e
                                       AND title.a  = (SELECT entid FROM mentat.idents
                                                       WHERE ident = ':issue/title')
                                       AND title.added
JOIN mentat.datoms_keyword_new  state  ON state.e  = asg.e
                                       AND state.a  = (SELECT entid FROM mentat.idents
                                                       WHERE ident = ':issue/state')
                                       AND state.added
JOIN mentat.datoms_text_new     uemail ON uemail.e = asg.v
                                       AND uemail.a = (SELECT entid FROM mentat.idents
                                                       WHERE ident = ':user/email')
                                       AND uemail.added
JOIN app.users         u  ON u.email = uemail.v
JOIN app.subscriptions s  ON s.stripe_customer_id = u.stripe_customer_id
WHERE asg.a    = (SELECT entid FROM mentat.idents WHERE ident = ':issue/assignee')
  AND asg.added
  AND s.status = 'active'
  AND state.v  = 'issue.state/open'
ORDER BY issue_title;

Output:

            issue_title            |   issue_state    |  assignee_email   | plan | status
-----------------------------------+------------------+-------------------+------+--------
 Indexes missing on accounts.email | issue.state/open | bob@example.com   | team | active
 Login is broken on Safari         | issue.state/open | alice@example.com | pro  | active
(2 rows)

The query plan (truncated) shows the partial covering indexes doing the work:

Nested Loop
  -> Index Only Scan using idx_datoms_keyword_new_vaet on datoms_keyword_new state
       Index Cond: ((v = 'issue.state/open') AND (a = $1))
  -> Index Only Scan using idx_datoms_ref_new_vaet on datoms_ref_new asg
       Index Cond: ((a = $3) AND (e = state.e))
  -> Index Only Scan using idx_datoms_text_new_eavt on datoms_text_new title
       Index Cond: ((e = asg.e) AND (a = $0))
  -> Index Only Scan using idx_datoms_text_new_eavt on datoms_text_new uemail
       Index Cond: ((e = asg.v) AND (a = $2))
  -> Index Scan using users_email_key on users u
       Index Cond: (email = uemail.v)
  -> Index Scan using subscriptions_pkey on subscriptions s
       Index Cond: (stripe_customer_id = u.stripe_customer_id)

Every datom touch is an index-only scan against an EAVT or VAET index; the user and subscription rows resolve through their existing primary/unique indexes.

Pattern B: let the datalog engine do the EAV part, then SQL-join the rest

WITH q AS (
  SELECT mentat_query(
    '[:find ?title ?state ?email
      :where
        [?i :issue/title ?title]
        [?i :issue/state ?state]
        [?i :issue/assignee ?u]
        [?u :user/email ?email]]',
    '{}'::jsonb
  ) AS j
),
issues AS (
  SELECT (r->>0) AS title,
         (r->>1) AS state,
         (r->>2) AS assignee_email
  FROM   q, jsonb_array_elements(q.j -> 'results') r
)
SELECT i.title         AS issue_title,
       i.state         AS issue_state,
       i.assignee_email,
       s.plan,
       s.status
FROM   issues i
JOIN   app.users         u ON u.email = i.assignee_email
JOIN   app.subscriptions s ON s.stripe_customer_id = u.stripe_customer_id
WHERE  i.state = ':issue.state/open'
  AND  s.status = 'active'
ORDER BY i.title;

Output is identical to Pattern A.

Which to use

Pattern A is one query plan. The optimiser sees the whole graph, picks join order across datoms and relational tables, and chooses index-only scans. Use it when the predicate against app.subscriptions (or any other relational filter) is selective — pushing the status = 'active' filter into the join graph eliminates entities the datalog engine would have materialised.

Pattern B is two query plans. The datalog engine materialises all matching issues into JSON, then SQL filters and joins. Use it when the EAV side is the bottleneck and would benefit from datalog features the SQL form can't easily express — not/or clauses, rules, recursion, aggregates with :find. The materialised CTE is a coarse-grained boundary; the planner cannot push the s.status = 'active' predicate through it.

Note one wart: mentat_query keyword results render as :issue.state/open (with the leading colon), but raw datom storage in datoms_keyword_new strips the colon (issue.state/open). Pattern B compares against the JSON form, Pattern A against the storage form.

Cross-database scope: datalog meets a foreign-data-wrapper table

The same JOIN works against anything Postgres can see — postgres_fdw, file_fdw, pg_partman cold partitions, a logical replica, a different schema. The datalog engine produces a row source; SQL composes it with the rest of the database.

Setup: a "warehouse" Postgres holds resolved-issue archives, exposed locally through postgres_fdw. (In the working example we point both ends at the same cluster for reproducibility; substitute a real warehouse host in production.)

CREATE EXTENSION postgres_fdw;
CREATE SERVER warehouse_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'warehouse.internal', port '5432', dbname 'warehouse');
CREATE USER MAPPING FOR CURRENT_USER
  SERVER warehouse_server OPTIONS (user 'app_ro');
IMPORT FOREIGN SCHEMA warehouse_local
  FROM SERVER warehouse_server INTO public;
-- public.issue_archive(archived_issue_eid bigint, resolution text,
--                      resolved_at timestamptz, hours_open numeric)

Then the report — "for every issue I have closed in the EAV store, fetch the post-resolution metrics from the warehouse":

WITH closed_issues AS (
  SELECT (r->>0)::bigint AS issue_eid,
         (r->>1)         AS title,
         (r->>2)         AS assignee_email
  FROM   jsonb_array_elements(
           (mentat_query(
             '[:find ?i ?title ?email
               :where
                 [?i :issue/title ?title]
                 [?i :issue/state :issue.state/closed]
                 [?i :issue/assignee ?u]
                 [?u :user/email ?email]]',
             '{}'::jsonb
           ) -> 'results')
         ) r
)
SELECT c.title,
       c.assignee_email,
       a.resolution,
       a.resolved_at,
       a.hours_open
FROM   closed_issues c
JOIN   public.issue_archive a ON a.archived_issue_eid = c.issue_eid
ORDER BY a.resolved_at;

Output:

           title           |  assignee_email   | resolution |      resolved_at       | hours_open
---------------------------+-------------------+------------+------------------------+------------
 OAuth token refresh fails | carol@example.com | fixed      | 2025-10-12 14:00:00-04 |      50.00
(1 row)

The same structure works with pg_partman-managed time-tiered storage: substitute public.issue_archive with the partitioned parent table and let partition pruning skip cold partitions when the datalog side has already narrowed the entity-id set.

Single-transaction guarantee

A datalog write and a relational write share one MVCC snapshot:

BEGIN;
INSERT INTO app.users (email, stripe_customer_id)
  VALUES ('eve@example.com', 'cus_eve');
INSERT INTO app.subscriptions
  VALUES ('cus_eve', 'pro', 'active', now() + interval '14 days');
SELECT mentat_transact('[
  {:db/id "u-eve" :user/email "eve@example.com" :user/display-name "Eve"}
  {:issue/title "Slack integration is silently failing"
   :issue/state :issue.state/open
   :issue/assignee "u-eve"}
]');
-- A mixed JOIN here sees the freshly inserted user *and* the freshly
-- transacted issue. ROLLBACK undoes both; COMMIT publishes both.
COMMIT;

There is no second connection, no two-phase commit, no compensating transaction. mentat_transact runs inside the surrounding BEGIN/COMMIT exactly like any other function call; the narrow tables participate in WAL, replication, and logical decoding the same way every other Postgres table does.

Mechanics: the table layout you are joining against

Each datom value type lives in its own narrow table. All have the same column shape:

mentat.datoms_<type>_new (
  store_id BIGINT NOT NULL DEFAULT 0,
  e        BIGINT NOT NULL,        -- entity id
  a        BIGINT NOT NULL,        -- attribute entid (resolve via mentat.idents)
  v        <type> NOT NULL,        -- bigint / text / boolean / timestamptz / ...
  tx       BIGINT NOT NULL,        -- transaction id
  added    BOOLEAN NOT NULL DEFAULT true
)

The nine type tables are datoms_ref_new, datoms_long_new, datoms_double_new, datoms_text_new, datoms_keyword_new, datoms_boolean_new, datoms_instant_new, datoms_uuid_new, datoms_bytes_new. Picking the correct table is the only piece of schema awareness Pattern A requires — you choose it from the attribute's :db/valueType.

Indexes

Every narrow table carries four covering indexes, all WHERE added (partial):

Because every index has a WHERE added predicate, always include <alias>.added in your JOIN/WHERE conditions. Without it the planner falls back to the primary key, which lacks added as a leading column, and you lose the index-only scan.

Resolving keywords to entids

mentat.idents(ident TEXT, entid BIGINT) is a regular two-column lookup table with a unique index on each column. The pattern

(SELECT entid FROM mentat.idents WHERE ident = ':issue/title')

is a single index probe. The planner runs it once as an InitPlan and reuses the constant. If you write the same query many times, you can hard-code the entid once you know it (a = 10002), but the lookup is cheap enough that hard-coding is rarely worth the loss of legibility.

mentat.schema carries the rest of the attribute metadata (value_type, cardinality, unique_constraint, indexed, fulltext, component, no_history). Join it with mentat.idents if you need to choose the narrow table dynamically rather than statically.

Caveats

• Use the narrow tables, not mentat.datoms. The mentat.datoms view is a UNION ALL over all nine narrow tables, kept for compatibility with code that pre-dates the per-type split. A query against the view fans out to nine Append branches and nine index probes; the same query against datoms_text_new is one. We measured a single-attribute count: the view takes 41 plan rows and visits eight empty tables; the narrow form is 9 rows. Always know the value type and use the matching table.

• Always include WHERE added (or AND <alias>.added). All four covering indexes are partial on added = true. Predicates without it fall back to the primary key and pay for a full row fetch.

• The datalog engine does not see your app.* tables. mentat_query compiles to SQL that references the narrow tables and mentat.idents only; it has no awareness of app.users or app.subscriptions. If you want bidirectional queries — e.g., "find me datoms whose :issue/assignee is in app.users WHERE created_at > now() - interval '7 days'" — you have two paths:

  1. Project the relational data into datoms. Maintain :user/stripe-customer-id, :user/created-at, etc. as datoms (via a trigger on app.users or a periodic sync). The datalog engine can then filter on it natively.

  2. JOIN at the SQL layer. This is the path described on this page. The datalog engine produces an entity set; SQL filters it against the relational table. Use it when the relational data is large, mutable, or authoritative elsewhere — copying it into datoms would create a consistency burden you do not want.

• Keyword rendering differs by surface. Datoms in datoms_keyword_new store the value without a leading colon (issue.state/open); the JSON produced by mentat_query reattaches it (:issue.state/open); EDN input to mentat_transact of course uses the colon form. Match the surface in whichever predicate you are writing.

• The compatibility view's INSTEAD OF triggers do not apply to JOINs. Reading from the view works (slowly); inserting through it routes to the correct narrow table. JOINing through it gets you the slow Append plan with no upside.

Approximate-Regex Search via pg_tre (optional)

pg_mentat integrates with pg_tre, a PostgreSQL 18+ index access method that adds approximate-regex matching with configurable edit distance to PostgreSQL. Once pg_tre is installed and you have built a TRE index on a :db.type/string attribute, you can run Datalog queries that find values matching a regex with up to k typos per sub-expression in sub-millisecond time.

pg_tre is an optional dependency. Nothing in pg_mentat requires it to be installed. If you do not need approximate-regex search, ignore this page; the (fulltext ...) where-fn (backed by tsvector/GIN) and LIKE/ILIKE predicates remain available without any extra setup.

When to use this

Use (fuzzy-match ...):

• Your text values come from typo-prone sources: user names, log lines, scanned-document OCR, manual data entry.
• You want edit-distance semantics, not just trigram similarity. pg_trgm scores database and databse as similar; pg_tre asserts the edit distance is exactly 1.
• You need {~k} per-phrase edit budgets, e.g. (error){~1}.*(42[0-9]){~0} — "the word error with up to 1 typo followed by a 3-digit number starting with 42, exactly".

Use (fulltext ...) instead if you want exact-token full-text search with stemming and ranking.

Prerequisites

• PostgreSQL 18 or newer.
• pg_tre built and installed against the same pg_config:

  git clone --recurse-submodules https://codeberg.org/gregburd/pg_tre
  cd pg_tre
  make PG_CONFIG=/path/to/pg18/bin/pg_config
  make PG_CONFIG=/path/to/pg18/bin/pg_config install
  

• shared_preload_libraries = 'pg_tre' in postgresql.conf. Restart the server after editing.
• CREATE EXTENSION pg_tre; in the database where pg_mentat lives.

Step-by-step example

CREATE EXTENSION pg_mentat;
CREATE EXTENSION pg_tre;

-- Define a string attribute as usual.
SELECT mentat.t('[
  {:db/ident :doc/body
   :db/valueType :db.type/string
   :db/cardinality :db.cardinality/one}
]');

-- Insert some data with deliberate typos.
SELECT mentat.t('[
  {:db/id "d1" :doc/body "the database error happens at scale"}
  {:db/id "d2" :doc/body "the databse error happens at scale"}
  {:db/id "d3" :doc/body "an unrelated row about cats"}
  {:db/id "d4" :doc/body "datbase error in production"}
]');

-- Build a TRE index on this attribute. Idempotent; safe to re-run.
SELECT mentat.create_tre_index(':doc/body');
-- -> "TRE index idx_datoms_text_new_tre_a10000 created on
--    mentat.datoms_text_new for attribute :doc/body (entid 10000).
--    Use (fuzzy-match $ :doc/body \"pattern\" k) to query."

-- Exact match (k = 0) returns only "database".
SELECT mentat.q('[:find ?val
                  :where [(fuzzy-match $ :doc/body "database" 0) [[?e ?val]]]]');
-- -> [["the database error happens at scale"]]

-- One typo (k = 1) returns the canonical row plus "databse" and "datbase".
SELECT mentat.q('[:find ?val
                  :where [(fuzzy-match $ :doc/body "database" 1) [[?e ?val]]]]');
-- -> [["the database error happens at scale"],
--     ["the databse error happens at scale"],
--     ["datbase error in production"]]

The (fuzzy-match ...) where-fn

Syntax:

(fuzzy-match $ :attr "pattern" k)

Binding shape (relation):

[(fuzzy-match $ :attr "pat" k) [[?e ?val]]]

• ?e is the entity id (cast to text in the result row).
• ?val is the matched string value.

The compiled SQL filters on attribute, store, and added = TRUE, then applies pg_tre's %~~ operator. If the attribute has a TRE index, the planner picks it; otherwise pg_tre's heap-recheck path runs.

Helper functions

The index is partial: WHERE store_id = 0 AND a = <attr_entid> AND added. That keeps it small (only live datoms of the chosen attribute) and lets the planner use it for mentat_query calls automatically.

Errors and how to fix them

What this does NOT give you

• Relevance scoring. pg_tre's %~~ is a boolean operator. Use (fulltext ...) if you need ranked results.
• PG13–17 support. pg_tre is PG18-only. If you target older PostgreSQL versions, (fuzzy-match ...) will fail with tre_pattern does not exist even at runtime.
• Multi-store scoping. mentat.create_tre_index builds the partial index against store_id = 0. Other stores work via the heap-recheck fallback (correct, but no index speedup). Multi-store TRE indexes are tracked as future work.

Caveats

• The TRE index is a partial index per-attribute. If you index N attributes, you get N indexes and N corresponding storage costs. Index only what you actually query.
• pg_tre requires shared_preload_libraries, which means a server restart to enable. Plan accordingly.
• pg_tre and pg_mentat are independently versioned. The integration in this version of pg_mentat targets pg_tre 1.0.0+. Newer pg_tre versions may add operators (e.g. score-returning variants) that pg_mentat does not yet expose; please open an issue.

Phonetic and Edit-Distance Functions via fuzzystrmatch

pg_mentat integrates with the built-in PostgreSQL contrib extension fuzzystrmatch to expose four scalar phonetic and edit-distance functions as Datalog where-fns. Unlike the more powerful pg_tre integration (which requires PG18+ and shared_preload_libraries), fuzzystrmatch is contrib — already on disk in any standard PostgreSQL build from PG13 onward, with no preload required and no restart needed.

fuzzystrmatch is an optional dependency. If it is not installed in the database, queries that use these where-fns fail at execution with the standard PostgreSQL error "function levenshtein(...) does not exist". Use mentat.has_fuzzystrmatch() to detect availability.

When to use this vs (fuzzy-match ...)

For a thousand-row table, fuzzystrmatch is fine. For a million-row table where you want "rows whose body matches this regex with up to k typos," use pg_tre.

Quick start

CREATE EXTENSION pg_mentat;
CREATE EXTENSION fuzzystrmatch;          -- contrib, ships with PG

-- Define a string attribute as usual.
SELECT mentat.t('[
  {:db/ident :person/name
   :db/valueType :db.type/string
   :db/cardinality :db.cardinality/one}
]');

SELECT mentat.t('[
  {:db/id "a" :person/name "Alice"}
  {:db/id "b" :person/name "Alyce"}
  {:db/id "c" :person/name "Robert"}
]');

-- Levenshtein: edit distance from each name to "Alice".
SELECT mentat.q('[:find ?n ?d
                  :where [?e :person/name ?n]
                         [(levenshtein ?n "Alice") ?d]]');
-- [["Alice", 0], ["Alyce", 1], ["Robert", 6]]

-- Soundex: groups homophones. "Alice" and "Alyce" both hash to "A420".
SELECT mentat.q('[:find ?n ?h
                  :where [?e :person/name ?n]
                         [(soundex ?n) ?h]]');
-- [["Alice", "A420"], ["Alyce", "A420"], ["Robert", "R163"]]

Where-fns

[(levenshtein ?a ?b) ?d]

Edit distance between two text values. Both args may be variables or text constants. Result is an integer.

[(levenshtein ?name "Alice") ?d]   ;; ?d = distance from ?name to "Alice"
[(levenshtein ?a ?b) ?d]           ;; ?d = distance between two bound vars

[(soundex ?s) ?code]

The classic 4-character American Soundex code for the input. Useful for grouping name homophones.

[(soundex ?surname) ?sx]

[(metaphone ?s ?max) ?code]

Metaphone phonetic encoding, truncated to ?max characters. Better than Soundex for non-Anglo names. ?max must be an integer literal.

[(metaphone ?surname 5) ?mp]

[(daitch-mokotoff ?s) ?codes]

Daitch-Mokotoff Soundex — better for Slavic and Yiddish surnames. Returns a text[] of codes; the binding receives a Postgres text representation like "{367460}" or "{394000,367460}".

[(daitch-mokotoff ?surname) ?dm]

Helper

mentat.has_fuzzystrmatch() → boolean returns true when the contrib extension is loaded. Use it as a guard in test suites and application code that needs to fall back gracefully when the extension isn't available.

Composing with predicates and other where-fns

Where-fn output variables can flow into other where-fns:

:where [?e :person/name ?n]
       [(levenshtein ?n "Alice") ?d]
       [(* ?d 2) ?da]               ;; arithmetic chain works

There is a known limitation: where-fn output variables (?d, ?da, etc.) are not yet usable inside (< ...)-style predicates. This is a pre-existing gap in the predicate compiler that affects all where-fn bindings (arithmetic too), not just fuzzystrmatch's. As a workaround, use a wrapping query that filters at the SQL level, or do the comparison entirely with arithmetic where-fns:

;; Instead of [(<= ?d 2)], do:
[(- 3 ?d) ?diff]                    ;; ?diff > 0 iff ?d < 3

Tracked as a future query-compiler improvement; pull requests welcome.

Errors

What this does NOT give you

• Soundex / Metaphone variants other than the four above. The contrib extension itself only ships those.
• Trigram similarity. That's pg_trgm — see pg_trgm integration.
• Edit-distance with regex semantics. That's pg_tre — see fuzzy-search.
• Index acceleration. These are scalar functions; there is no index for "names within 2 edits of X." For that workload use pg_tre or a precomputed Soundex column.

Trigram Similarity via pg_trgm

pg_mentat integrates with pg_trgm, a built-in PostgreSQL contrib extension that provides trigram-based similarity matching. Where fuzzystrmatch's Levenshtein answers "how many edits apart?", pg_trgm answers "how many overlapping 3-grams?" — better at matching reordered tokens, partial substrings, and typos that preserve trigrams.

pg_trgm is an optional dependency. Detect with mentat.has_pg_trgm(). Without it, queries that use (similar-to ...) fail at execution with the standard PostgreSQL error "function similarity(...) does not exist".

When to use this vs other fuzzy options

Quick start

CREATE EXTENSION pg_mentat;
CREATE EXTENSION pg_trgm;          -- contrib

SELECT mentat.t('[
  {:db/ident :issue/title
   :db/valueType :db.type/string
   :db/cardinality :db.cardinality/one}
]');

-- Index recommendation: partial GIN on the attribute.
SELECT mentat.create_trgm_index(':issue/title');

-- Find titles similar to "databse" with score >= 0.3.
SELECT mentat.q('[
  :find ?title ?score
  :where [(similar-to $ :issue/title "databse" 0.3) [[?e ?title ?score]]]
  :order [?score :desc]
]');

The similar-to where-fn

[(similar-to $ <:attr> <"needle"> <threshold>) [[?e ?val ?score]]]

Arguments:

Binding shape [[?e ?val ?score]] (relation):

• ?e — entid of each matching datom.
• ?val — the actual stored value.
• ?score — pg_trgm similarity(stored, needle) in [0.0, 1.0].

Indexing — mentat.create_trgm_index

SELECT mentat.create_trgm_index(':issue/title');
-- => 'datoms_text_trgm_<entid>'

Creates a partial GIN trigram index keyed by the attribute's entid:

CREATE INDEX datoms_text_trgm_<entid>
    ON mentat.datoms_text_new USING GIN (v gin_trgm_ops)
    WHERE a = <entid> AND added = true;

The partial WHERE keeps the index small even in workspaces with hundreds of string attributes. The function is idempotent — calling it again with the same attribute is a no-op. To remove:

SELECT mentat.drop_trgm_index(':issue/title');
-- => true if the index existed and was dropped, false otherwise

The compiled SQL filters with similarity(v, needle) >= threshold. PostgreSQL's planner uses the GIN index for the equivalent v % needle filter when the trigram-similarity GUC matches; otherwise it falls back to a partial-index scan plus recheck. Verify with EXPLAIN ANALYZE on a representative query.

Errors

Worked example: dedupe near-duplicate names

(def people
  [{:db/id "a" :p/name "Alice Henderson"}
   {:db/id "b" :p/name "Alyce Henderson"}
   {:db/id "c" :p/name "Alice Hendersn"}
   {:db/id "d" :p/name "Bob Smith"}])

(d/q '[:find ?name ?score
       :where [(similar-to $ :p/name "Alice Henderson" 0.5) [[?e ?name ?score]]]
       :order [?score :desc]]
     db)
;; =>
;; [["Alice Henderson"  1.0]
;;  ["Alyce Henderson"  0.6875]
;;  ["Alice Hendersn"   0.6363...]]
;; "Bob Smith" filtered out.

Composing with the rest of Datalog

similar-to plays the same role as fulltext and fuzzy-match in a clause: the bound ?e joins back into the rest of the where-graph.

:where
  [(similar-to $ :issue/title "databse" 0.3) [[?issue ?title ?score]]]
  [?issue :issue/status :status/open]            ; restrict to open
  [?issue :issue/assignee ?dev]
  [?dev :user/name ?dev-name]
:find ?title ?dev-name ?score
:order [?score :desc]

What this does NOT give you

• Tokenization or stop-word handling. That's to_tsvector / tsquery territory — see Postgres full-text search.
• Phonetic matching. That's fuzzystrmatch.
• Approximate-regex with bounded edits. That's pg_tre.
• Cross-attribute similarity in one call. Each similar-to binds one attribute; combine with OR clauses to search across attributes.

BM25-style Ranked Search via rum

PostgreSQL's stock GIN-indexed tsvector @@ tsquery is great for filtering, but to rank matches by relevance the planner has to do a post-fetch scan and call ts_rank_cd per row. This is fine for tens of matches, painful for millions.

rum is a PostgreSQL-licensed (permissive!) GIN-derived index access method from PostgresPro that stores positional information alongside lexemes. Combined with the <=> distance operator and rum_ts_score() ranking function, it returns top-K relevance-ordered documents directly from the index. It is the closest permissive alternative to BM25 indexing in PostgreSQL — pg_mentat's choice over the AGPL-licensed pg_search (ParadeDB).

rum is an optional dependency. The (rum-fulltext ...) where-fn produces SQL that uses standard @@ for filtering and rum_ts_score(...) for ranking — both work without the rum extension installed (against a sequential scan), but the partial RUM index is where the speed-up lives.

When to use this vs other fulltext options

If you don't have rum installed and the dataset is < 100k rows, stay on (fulltext ...). Above that scale, install rum and switch to (rum-fulltext ...).

Quick start

# Build rum from source (one-time, ~30 sec).
git clone https://github.com/postgrespro/rum
cd rum
make USE_PGXS=1 PG_CONFIG=/path/to/your/pg_config
make USE_PGXS=1 PG_CONFIG=/path/to/your/pg_config install

CREATE EXTENSION pg_mentat;
CREATE EXTENSION rum;

-- Define a fulltext-tagged attribute.
SELECT mentat.t('[
  {:db/ident :issue/body
   :db/valueType :db.type/string
   :db/cardinality :db.cardinality/one
   :db/fulltext true}
]');

-- Create a partial RUM index on this attribute (idempotent).
SELECT mentat.create_rum_fulltext_index(':issue/body');
-- => 'datoms_text_rum_<entid>_english'

-- Top-K ranked search.
SELECT mentat.q('[
  :find ?body ?score
  :where [(rum-fulltext $ :issue/body "database crash") [[?e ?body ?score]]]
  :order (desc ?score)
]');

The rum-fulltext where-fn

[(rum-fulltext $ <:attr> <"search-text">) [[?e ?val ?score]]]

Binding shape [[?e ?val ?score]] (relation):

• ?e — entid of each matching datom.
• ?val — the stored text value.
• ?score — rum_ts_score(...) in [0.0, 1.0]-ish range. Higher = more relevant.

Plain text uses plainto_tsquery; double-quoted text uses phraseto_tsquery for proximity matching.

Index helpers

-- Idempotent. Returns the deterministic index name.
SELECT mentat.create_rum_fulltext_index(':issue/body');
-- => 'datoms_text_rum_<entid>_english'

-- Same attribute, different language config.
SELECT mentat.create_rum_fulltext_index(':issue/body', 'spanish');
-- => 'datoms_text_rum_<entid>_spanish'

-- Drop. Returns true if the index existed.
SELECT mentat.drop_rum_fulltext_index(':issue/body');
SELECT mentat.drop_rum_fulltext_index(':issue/body', 'spanish');

The index DDL is partial:

CREATE INDEX datoms_text_rum_<entid>_<lang>
    ON mentat.datoms_text_new
    USING rum (to_tsvector('<lang>', v) rum_tsvector_ops)
    WHERE a = <entid> AND added = true;

The WHERE a = <entid> AND added = true clause keeps the index small even in workspaces with many string attributes, and excludes retracted datoms automatically.

How rum's ranking differs from ts_rank_cd

ts_rank_cd (cover density) only knows lexeme presence and document length. rum_ts_score also knows lexeme positions, so it ranks:

• Phrase matches higher than disjoint matches of the same lexemes.
• Closer co-occurrences higher than distant ones.
• Documents where query lexemes appear early higher than late.

This is closer in spirit to BM25's term-frequency × inverse-document- frequency × proximity scoring than ts_rank_cd is, though it is not algorithmically identical to BM25.

Errors

Worked example: bug-tracker search

(d/q '[:find ?title ?score
       :where
         [(rum-fulltext $ :issue/title "memory leak") [[?e ?title ?score]]]
         [?e :issue/status :status/open]
         [?e :issue/priority :priority/high]
       :order (desc ?score)
       :limit 20]
     db)

Compiles to a query plan that:

1. Hits the partial RUM index on :issue/title for ranked top matches.
2. Joins the result back to the :issue/status and :issue/priority attribute datoms via the entid.
3. Returns the top 20 by descending rum_ts_score.

If the RUM index is sized appropriately and the status/priority datoms are also indexed, the entire pipeline is index-driven — no sequential scan over the issue body text.

License caveat

ParadeDB's pg_search is AGPL-3.0, which makes it unsuitable for many commercial deployments without source-disclosure. rum is PostgreSQL license — same terms as PostgreSQL itself. If you ship a SaaS or proprietary product, rum is the only credible permissive choice for index-backed BM25-style ranking in PostgreSQL today.

What this does NOT give you

• True BM25. rum's score is positional + length-aware but is not the algorithm Lucene/Tantivy implement. Close, not identical.
• Faceted search, aggregations, learned ranking. That's Elasticsearch/Tantivy territory.
• Cross-attribute ranking in one call. Each rum-fulltext binds one attribute. Combine with OR for multi-attribute search; the resulting plan does index lookups per attribute then unions.
• Fuzzy / typo-tolerant matching. Use pg_trgm's (similar-to) or pg_tre's (fuzzy-match) for that.

Vector Search via pgvector

pg_mentat integrates with pgvector, the standard PostgreSQL vector-similarity extension. Use this for semantic search, embedding-based recommendations, or any workload where you need K-nearest-neighbor lookups by cosine / L2 / inner-product distance.

pgvector is an optional dependency. Detect with mentat.has_pgvector(). The integration is a soft, side-table design: vectors live in per-attribute auxiliary tables — pg_mentat does not (yet) register :db.type/vector in the schema or accept vectors through mentat.t. That bigger schema-side integration is tracked in docs/INTEGRATIONS.md.

How the side-table integration works

Each attach_vector_attribute(:attr, dim) call creates:

CREATE TABLE mentat.attr_<entid>_vector(
    e BIGINT PRIMARY KEY,
    v vector(<dim>) NOT NULL
);

Vectors are keyed by entid only. The corresponding Datalog attribute must already be registered in the schema (any value type works — the attribute exists in mentat.schema for entid lookup, but the vector data lives separately).

Quick start

# Build pgvector from source (one-time).
git clone --depth 1 --branch v0.7.4 https://github.com/pgvector/pgvector
cd pgvector
make USE_PGXS=1 PG_CONFIG=/path/to/pg_config
make USE_PGXS=1 PG_CONFIG=/path/to/pg_config install

CREATE EXTENSION pg_mentat;
CREATE EXTENSION vector;

-- Define the attribute (any string-or-long type — the data lives in the
-- aux table).
SELECT mentat.t('[
  {:db/ident :doc/title :db/valueType :db.type/string :db/cardinality :db.cardinality/one}
  {:db/ident :doc/embedding :db/valueType :db.type/string :db/cardinality :db.cardinality/one}
]');

-- Insert documents (no vectors yet).
SELECT mentat.t('[
  {:db/id "a" :doc/title "How to install Postgres"}
  {:db/id "b" :doc/title "Datalog query patterns"}
  {:db/id "c" :doc/title "Cookies are good"}
]');

-- Attach the aux table for embeddings of dimension 3.
SELECT mentat.attach_vector_attribute(':doc/embedding', 3);

-- Populate vectors via the helper (use real entids in production).
DO $do$
DECLARE e_a BIGINT; e_b BIGINT; e_c BIGINT;
BEGIN
  SELECT e INTO e_a FROM mentat.datoms_text_new
    WHERE a = (SELECT entid FROM mentat.schema WHERE ident = ':doc/title')
      AND v = 'How to install Postgres';
  SELECT e INTO e_b FROM mentat.datoms_text_new
    WHERE a = (SELECT entid FROM mentat.schema WHERE ident = ':doc/title')
      AND v = 'Datalog query patterns';
  SELECT e INTO e_c FROM mentat.datoms_text_new
    WHERE a = (SELECT entid FROM mentat.schema WHERE ident = ':doc/title')
      AND v = 'Cookies are good';
  PERFORM mentat.set_vector(e_a, ':doc/embedding', '[0.9, 0.1, 0.0]');
  PERFORM mentat.set_vector(e_b, ':doc/embedding', '[0.0, 0.9, 0.1]');
  PERFORM mentat.set_vector(e_c, ':doc/embedding', '[0.0, 0.0, 1.0]');
END;
$do$;

-- Top-2 nearest by cosine distance, joined to the title attribute.
SELECT mentat.q('[
  :find ?title ?dist
  :where [(vector-near $ :doc/embedding "[1,0,0]" 2) [[?e ?dist]]]
         [?e :doc/title ?title]
  :order (asc ?dist)
]');
-- => [["How to install Postgres", 0.0061], ["Datalog query patterns", 1.0]]

The vector-near where-fn

[(vector-near $ <:attr> <"[v1,v2,...]"> <k> [<distance-op>]) [[?e ?dist]]]

Binding shape [[?e ?dist]] (relation):

• ?e — entid of each near neighbor.
• ?dist — distance (lower = closer for :cosine / :l2; lower = closer for :inner's negative inner product convention).

The compiled SQL uses pgvector's distance operators directly:

| :cosine | <=> | Cosine distance, in [0, 2]. | | :l2 | <-> | Euclidean / L2 distance. | | :inner | <#> | Negative inner product (lower = more similar). |

The K-limit is applied inside the subquery, so vector-near returns exactly K rows before joining to the rest of the where-clause graph. Subsequent patterns (e.g. [?e :doc/title ?title]) JOIN by entid — no cartesian-product workarounds required.

HNSW index

SELECT mentat.create_hnsw_vector_index(':doc/embedding', 'cosine');
-- => 'attr_<entid>_vector_hnsw_cosine'

Idempotent. dist_op must be 'cosine', 'l2', or 'inner'; the function chooses the right pgvector opclass (vector_cosine_ops, vector_l2_ops, vector_ip_ops).

The index is keyed on the aux table only — there's no partial-WHERE trick because each attribute already has its own table. Tune hnsw.m / hnsw.ef_construction via session GUCs in the standard pgvector way; pg_mentat doesn't wrap those.

Errors

Worked example: semantic document search

;; Application populates :doc/embedding via mentat.set_vector after
;; running each document through a sentence-transformer.

(d/q '[:find ?title ?author ?dist
       :where
         [(vector-near $ :doc/embedding ?query-embedding 10) [[?d ?dist]]]
         [?d :doc/title ?title]
         [?d :doc/author ?author-eid]
         [?author-eid :user/name ?author]
       :order (asc ?dist)]
     db
     query-embedding)  ;; passed in via :in

Plan:

1. vector-near returns top-10 doc entids by cosine distance, JOINed from the per-attribute aux table directly via the HNSW index.
2. Three EAV joins follow the entid back to title, author entid, and author name.
3. Result is 10 rows ordered by ascending distance.

What this does NOT (yet) give you

• :db.type/vector schema integration. Vectors don't transact via mentat.t. Use mentat.set_vector directly. A future session will add the schema-side integration; the aux-table representation this integration uses is forward-compatible with that design.
• Variable-length vector args to vector-near. The vector is a string literal in the EDN; passing a Datalog variable bound elsewhere is not supported (parameterized embedding values can be threaded via the :in clause once schema integration ships).
• IVFFlat indexes. Only HNSW is exposed today; vector_*_ops with IVFFlat are accessible through plain SQL CREATE INDEX.
• Quantization (BIT, halfvec, sparsevec). pgvector 0.7+ supports these; pg_mentat doesn't wrap them yet.

Cross-Database Datalog with postgres_fdw

postgres_fdw is a built-in PostgreSQL contrib extension (PG13+). It lets one PostgreSQL database run queries against tables on a different PostgreSQL server as if they were local. Combined with pg_mentat, this gives you cross-database Datalog: one query that joins datoms from your local store with datoms (or relational rows) on a remote server.

This is a cookbook page, not a new where-fn. Everything here is plain postgres_fdw SQL — pg_mentat just happens to slot in cleanly.

postgres_fdw ships with PostgreSQL. No build, no preload.

When to use this

It is not a sharding solution. Each remote query goes over the network with TCP overhead per round-trip. For N>3 remotes joined in one query, expect minutes-not-milliseconds latency. For a real sharded multi-store, see Citus integration in INTEGRATIONS.md (Tier 3, currently a stub).

Setup: one local + one remote pg_mentat

On the remote server (we'll call it tenant_a):

-- Standard pg_mentat install on the remote.
CREATE EXTENSION pg_mentat;

-- Some data.
SELECT mentat.t('[
  {:db/ident :issue/title :db/valueType :db.type/string :db/cardinality :db.cardinality/one}
  {:db/ident :issue/status :db/valueType :db.type/keyword :db/cardinality :db.cardinality/one}
]');
SELECT mentat.t('[
  {:db/id "i1" :issue/title "memory leak in cache"   :issue/status :status/open}
  {:db/id "i2" :issue/title "fix typo in docs"       :issue/status :status/closed}
]');

On the local (central) server:

-- Enable the FDW.
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Server pointer.
CREATE SERVER tenant_a_srv
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'tenant_a.example.com', port '5432', dbname 'mydb');

-- User mapping (read-only role recommended).
CREATE USER MAPPING FOR CURRENT_USER
  SERVER tenant_a_srv
  OPTIONS (user 'reporting', password 'secret');

-- Bring the remote mentat schema in as foreign tables.
CREATE SCHEMA tenant_a;
IMPORT FOREIGN SCHEMA mentat
  LIMIT TO (datoms_text_new, datoms_keyword_new, datoms_long_new,
            datoms_ref_new, datoms_double_new, datoms_boolean_new,
            datoms_instant_new, datoms_uuid_new, datoms_bytes_new,
            schema, idents)
  FROM SERVER tenant_a_srv INTO tenant_a;

Cross-store queries

Approach 1: raw SQL with the foreign tables

Datalog runs on the local store. To pull data from the remote store, write a SQL view that wraps the foreign datoms:

CREATE OR REPLACE VIEW tenant_a_open_issue_titles AS
SELECT
  d.e AS entid,
  d.v AS title
FROM tenant_a.datoms_text_new d
WHERE d.a = (SELECT entid FROM tenant_a.schema WHERE ident = ':issue/title')
  AND d.added = true
  AND EXISTS (
    SELECT 1 FROM tenant_a.datoms_keyword_new s
    WHERE s.e = d.e
      AND s.a = (SELECT entid FROM tenant_a.schema WHERE ident = ':issue/status')
      AND s.added = true
      AND s.v = ':status/open'
  );

SELECT * FROM tenant_a_open_issue_titles ORDER BY title;

This pushes filtering down to the remote server (postgres_fdw is good at this — verify with EXPLAIN). The local Datalog never touches the remote; the remote results materialize as ordinary rows.

Approach 2: union local + remote in one Datalog query

If the local store has its own :issue/title attribute and you want both sets in one Datalog answer, materialize the remote as a side table the local pg_mentat can see:

-- Materialized cache of remote data (refresh on a cron).
CREATE MATERIALIZED VIEW remote_open_issues AS
SELECT entid, title FROM tenant_a_open_issue_titles;

CREATE INDEX ON remote_open_issues(title);

-- Periodic refresh.
REFRESH MATERIALIZED VIEW CONCURRENTLY remote_open_issues;

Then, inside Datalog you can use raw SQL via (ground ...) collection:

(d/q '[:find ?title
       :where
         [(ground ["t-a-1" "t-a-2" "t-a-3"]) [?title ...]]   ;; ← values from the SQL side
         ;; ... mix with local datoms here ...
       ]
     db)

Or — preferable — issue a SQL UNION at the top:

SELECT title FROM remote_open_issues
UNION
SELECT (mentat.q('[:find ?title :where [?e :issue/title ?title]
                                       [?e :issue/status :status/open]]')
        ->'results') AS title;

Approach 3: Datalog :in clause + foreign data

The cleanest pattern. Run the Datalog query locally; pass remote data in via :in:

WITH remote AS (
  SELECT entid AS r_eid, title AS r_title
  FROM tenant_a_open_issue_titles
)
SELECT mentat.q(
  '[:find ?title ?local-status
    :in $ [[?title ...]]
    :where
      [?e :issue/title ?title]
      [?e :issue/status ?local-status]]',
  jsonb_build_object('?title', (SELECT jsonb_agg(r_title) FROM remote))
) AS result;

The [[?title ...]] collection binding lets the Datalog query restrict its search to titles that exist on the remote; the FDW push-down eliminates remote rows we don't need; and the join happens inside the local Datalog.

Multi-tenant pattern: many remotes, one query

-- Repeat IMPORT FOREIGN SCHEMA for tenant_b, tenant_c, ...
CREATE SCHEMA tenant_b;
IMPORT FOREIGN SCHEMA mentat LIMIT TO (...) FROM SERVER tenant_b_srv INTO tenant_b;

CREATE OR REPLACE VIEW all_open_issues AS
SELECT 'a' AS tenant, * FROM tenant_a_open_issue_titles
UNION ALL
SELECT 'b' AS tenant, * FROM tenant_b_open_issue_titles
UNION ALL
SELECT 'c' AS tenant, entid, title FROM (... tenant_c view ...) v;

Each tenant's portion runs in parallel (postgres_fdw uses async remote execution since PG14). Datalog on the central side joins the union to local data.

Performance notes

• Use FDW pushdown. Always start with EXPLAIN VERBOSE and look for Foreign Scan with Remote SQL. Joins, sorts, aggregates, and WHERE-clauses pushdown when types match exactly.

• Pin use_remote_estimate = true on the foreign server for any query the planner gets wrong:

  ALTER SERVER tenant_a_srv OPTIONS (SET use_remote_estimate 'true');
  

• Network is always the bottleneck. 1 ms of round-trip × N rows pulled is the floor. Pull aggregates, not rows, when you can.

• Foreign tables are not indexable from the local side. Do index work on the remote.

• Read-only by default. pg_mentat's transact path doesn't cross FDW boundaries; mentat.t only writes locally. If you need to write to a remote pg_mentat, send the EDN over via plain SQL (SELECT mentat.t(...) ON tenant_a_srv is not possible — you need a separate connection).

Errors

See also

• Joining mentat to legacy SQL tables — same FDW techniques applied to non-pg_mentat tables.
• INTEGRATIONS.md — Citus and pglogical entries for true sharding and CDC instead of pull-based federation.

Transactional Event Stream via PgQue

pg_mentat integrates with PgQue (NikolayS/PgQue, Apache 2.0) to emit one event per transaction into a durable Postgres-backed event stream. Every successful mentat.t (or any other commit that writes to mentat.transactions) produces a mentat.tx-typed PgQue event whose ev_data is the full datom payload of that tx.

PgQue is the modern, managed-Postgres-friendly revival of Skype's PgQ engine (~2007). It uses snapshot-based batching and TRUNCATE table rotation — zero dead-tuple bloat under sustained load — and ships as a single SQL file with no C extension, no shared_preload_libraries, no external daemon. Works on any PG14+, including RDS / Aurora / Cloud SQL / AlloyDB / Supabase / Neon.

PgQue is an optional dependency. Detect with mentat.has_pgque(). The integration installs nothing on its own — you opt in per queue with mentat.pgque_emit_tx('queue_name').

When to use this

It is not a replacement for logical replication. PgQue events contain the EAV-shaped tx payload, not WAL records. For physical / logical replication of the storage tables, use Postgres replication.

Quick start

# Install PgQue (one-time per database).
git clone https://github.com/NikolayS/PgQue
cd PgQue
psql -d mydb -f sql/pgque.sql

CREATE EXTENSION pg_mentat;

-- Enable per-tx emit on a queue (creates the queue if missing,
-- attaches a deferred constraint trigger to mentat.transactions).
SELECT mentat.pgque_emit_tx('mentat_events');

-- Register a consumer that will receive events.
SELECT mentat.pgque_register_consumer('mentat_events', 'reporting');

-- Now every mentat.t produces an event at COMMIT time.
SELECT mentat.t('[
  {:db/ident :p/name :db/valueType :db.type/string :db/cardinality :db.cardinality/one}
]');
SELECT mentat.t('[{:db/id "a" :p/name "Alice"}]');

-- Drive the queue (or use pg_cron / pg_timetable to do it for you).
SELECT pgque.force_next_tick('mentat_events');

-- Pull a batch.
DO $$
DECLARE bid BIGINT := pgque.next_batch('mentat_events', 'reporting');
BEGIN
  IF bid IS NOT NULL THEN
    -- Consumer code here.
    PERFORM pgque.finish_batch(bid);
  END IF;
END;
$$;

Event shape (ev_type = 'mentat.tx')

The ev_data field is a JSON envelope:

{
  "tx": 1000007,
  "tx_instant": "2026-05-14T10:15:25.7+00:00",
  "store_id": "0",
  "datom_count": 4,
  "datoms": [
    {"e": 1000007, "a": 50, "v": "2026-05-14 10:15:25.7+00", "vt": "instant", "tx": 1000007, "added": true},
    {"e": 10001,   "a": 10000, "v": "Alice",                  "vt": "string",  "tx": 1000007, "added": true}
  ]
}

Field reference:

Why a deferred constraint trigger

mentat.transactions rows are inserted early in each tx, by mentat.current_tx(), before the datoms for that tx are written to the typed tables. A normal AFTER INSERT FOR EACH ROW trigger would fire too early: the datom rows wouldn't exist yet.

The integration uses CREATE CONSTRAINT TRIGGER ... DEFERRABLE INITIALLY DEFERRED. PostgreSQL fires deferred constraint triggers at the end of the transaction, right before COMMIT, by which point all datom inserts for the tx are visible. The trigger function aggregates them across the 9 typed tables, builds the JSON envelope, and calls pgque.insert_event.

If pgque.insert_event fails (e.g. someone dropped the schema), the trigger swallows the exception and emits a NOTICE so the user's tx still commits. This is a deliberate trade-off: queue emit failures should not corrupt the transactional database.

Multiple queues

You can emit to as many queues as you like — each pgque_emit_tx call attaches its own deferred trigger:

SELECT mentat.pgque_emit_tx('audit_log');
SELECT mentat.pgque_emit_tx('search_index_updates');
SELECT mentat.pgque_emit_tx('webhooks');
-- Now every tx fires three triggers, one per queue.

The triggers fire in name order. Each one builds the same payload (no work-sharing today). For high-volume workloads, prefer one queue

• a fan-out consumer pattern instead of N queues — the trigger overhead is per-queue.

Disabling emit

SELECT mentat.pgque_disable_tx('audit_log');
-- => true if a trigger existed and was dropped, false otherwise

The PgQue queue itself is not dropped — it may still hold events that haven't been consumed. To drain and remove:

-- Drain manually first, or just drop:
SELECT pgque.drop_queue('audit_log', force => true);

Driving the ticker

PgQue's batching needs a ticker — something that periodically advances the snapshot boundary. Options, in increasing order of latency guarantee:

1. pg_cron (recommended for managed Postgres) — SELECT pgque.start(); schedules a 1-second pg_cron slot that re-ticks every 100 ms internally. ~50 ms median end-to-end.
2. pg_timetable — SELECT pgque.start_timetable(); for clusters running the external pg_timetable worker.
3. External cron / systemd timer — call SELECT pgque.ticker(); on whatever cadence suits you.
4. Manual ticking from your application — useful in tests: SELECT pgque.force_next_tick('audit_log');

Tune cadence with SELECT pgque.set_tick_period_ms(50); (20 ticks/sec). Allowed periods are exact divisors of 1000ms in [1, 1000].

Performance notes

• Emit cost: one INSERT INTO pgque.event_<qid> per pg_mentat tx, plus the JSON aggregation across the 9 typed tables for that tx's datoms. For most workloads this is < 1 ms.
• Trigger ordering: PostgreSQL fires constraint triggers in alphabetical name order. The per-queue trigger names are mentat_pgque_emit_<sanitized-queue-name>, so emits to multiple queues fire in queue-name order.
• Bloat: PgQue's TRUNCATE rotation means the event tables don't accumulate dead tuples regardless of throughput. The trigger on mentat.transactions does NOT cause additional bloat there either — it's an INSERT-only side effect.
• Concurrent commits: each tx runs the deferred trigger in its own commit phase. There's no global lock; emit throughput scales with commit throughput.

Errors

What this does NOT give you

• Retroactive events. Only transactions committed AFTER you call pgque_emit_tx produce events. Existing data is not backfilled.
• Schema changes as separate events. A schema-installing tx produces one combined event with the schema datoms; consumers must inspect vt / a to detect schema-affecting changes.
• Causal ordering across stores. Each store gets its own emit trigger with its own queue (or shares a queue but distinguishes via store_id in the payload). Cross-store causality is application-side.
• Exactly-once delivery. PgQue is at-least-once: a consumer that crashes between next_batch and finish_batch re-receives the same batch on next read. Make consumers idempotent.

See also

• PgQue README — full API, benchmarks, comparison with PGMQ / River / pg-boss / Que.
• PostgreSQL deferred constraint triggers — the timing mechanism this integration relies on.
• The Skype PgQ paper, PGCon 2009 — original architecture.

Model-Knowledge Search via pg_infer

pg_mentat integrates with pg_infer, an experimental PostgreSQL extension that exposes transformer model knowledge as SQL relations. With pg_infer installed and a model registered, pg_mentat queries can rank text by what the model "knows" — without running inference at query time and without precomputed embeddings.

pg_infer is experimental (alpha, PG18+, may break between releases). Treat this integration as the same kind of contract: expect breakage when pg_infer's SQL surface evolves.

It is an optional dependency. Detect with mentat.has_pg_infer(). The Datalog where-fns produce SQL that calls pg_infer's operators directly; without pg_infer installed, queries fail at execution with the standard PG "operator/function does not exist" error.

How it differs from pgvector / pg_trgm / rum

Use pg_infer when you need to find that "AutoML for Deep Networks" matches the query "neural architecture search" because the model learned that relationship — even though no keywords overlap and you haven't embedded anything.

Quick start

# Build pg_infer (requires Rust nightly + pgrx 0.17 + PG18+; see
# https://codeberg.org/gregburd/pg_infer for instructions).
git clone https://codeberg.org/gregburd/pg_infer
cd pg_infer
cargo pgrx install --release

CREATE EXTENSION pg_mentat;
CREATE EXTENSION pg_infer;

-- Register a model from a vindex artifact.
SELECT infer_create_model('qwen05b', '/data/qwen-0.5b.vindex');

-- (Optional) make it the default for queries that don't pass a model.
SET infer.default_model = 'qwen05b';

-- Define a string attribute.
SELECT mentat.t('[
  {:db/ident :paper/title :db/valueType :db.type/string :db/cardinality :db.cardinality/one}
]');

SELECT mentat.t('[
  {:db/id "p1" :paper/title "Efficient Neural Architecture Search"}
  {:db/id "p2" :paper/title "AutoML for Deep Networks"}
  {:db/id "p3" :paper/title "Cookies are good"}
]');

-- Build the partial pg_infer index keyed by attribute.
SELECT mentat.create_infer_index(':paper/title', 'qwen05b');

-- Top-2 nearest by model-knowledge distance.
SELECT mentat.q('[
  :find ?title ?dist
  :where [(infer-near $ :paper/title "neural architecture search" 2) [[?e ?title-shadow ?dist]]]
         [?e :paper/title ?title]
  :order (asc ?dist)
]');
-- => Both AutoML and ENAS papers, even though "AutoML" shares no
-- keywords with "neural architecture search".

Where-fns

[(infer-near $ <:attr> <"text"> <k> [<:model>]) [[?e ?dist]]]

Top-K nearest neighbors by model-knowledge distance, using pg_infer's <~> operator + ORDER BY ... LIMIT k for index-driven retrieval.

Binding [[?e ?dist]]:

• ?e \u2014 entid of each near neighbor.
• ?dist \u2014 model-knowledge distance (lower = more similar).

The infer-near subquery applies LIMIT inside, so exactly K rows are returned before joining to subsequent patterns. ?e is also exposed for downstream EAV joins via the FTS-join entity-binding fix landed alongside the pgvector integration.

[(infer-similar a b [<:model>]) ?score]

Scalar similarity between two text values via pg_infer's infer_similarity(text, text) function. Higher = more similar.

[(infer-similar ?title "France") ?s]
[(infer-similar "Paris" "France") ?s]

Today the optional model arg is accepted syntactically but ignored \u2014 pg_infer's infer_similarity is documented as 2-arg only, with model selected via the infer.default_model GUC. A future pg_infer release that adds a 3-arg form will be picked up here without an API change.

[(infer-implies a b [<:model>]) ?bool]

Test whether the model's knowledge supports a directional relationship from subject a to object b. Returns 0 or 1 (not boolean \u2014 pg_mentat's scalar-binding return path needs an integer-shaped value).

[(infer-implies "France" "Paris") ?ok]    ;; ?ok = 1 if implies holds
[(infer-implies ?title "AI") ?ok]

Set-returning verbs: walk / describe / predict

Three additional pg_infer verbs are exposed as relation-binding where-fns. Unlike (infer-near), they do not bind an entity variable, so they cannot JOIN to subsequent EAV patterns by ?e. To combine their output with entity-side data, materialize via (ground ...) or wrap in raw SQL with :in.

[(infer-walk "prompt" top [<:model>]) [[?layer ?feature ?score ?concept]]]

Trace gate activations across model layers for a prompt. Useful for debugging model behavior or building custom ranking pipelines on raw activations.

[(infer-walk "the capital of France is" 10) [[?layer ?feature ?score ?concept]]]

Maps to SELECT layer, feature, gate_score, concept FROM walk(prompt, top).

[(infer-describe "entity" [threshold] [<:model>]) [[?relation ?target ?score ?layer]]]

Return knowledge edges the model has about an entity. Each row is a (relation, target, score, layer) tuple representing a relationship the model encodes.

[(infer-describe "France") [[?relation ?target ?score ?layer]]]
;; => :capital -> Paris (42.7), :language -> French (38.1), ...

Maps to SELECT relation, target, gate_score, layer FROM describe(entity, threshold). The threshold argument is optional; pass NULL or omit for the GUC default.

[(infer-predict "prompt" top [<:model>]) [[?token ?probability ?rank]]]

Forward-pass next-token prediction. Returns the top tokens with probabilities and ranks. Requires pg_infer's inference feature built with --features inference.

[(infer-predict "The capital of France is" 5) [[?token ?prob ?rank]]]

Maps to SELECT token, probability, rank FROM infer(prompt, top).

Index helpers

SELECT mentat.create_infer_index(':paper/title', 'qwen05b');
-- => 'datoms_text_infer_<entid>_qwen05b'

Idempotent. Creates a partial index using the default infer_text_ops opclass:

CREATE INDEX datoms_text_infer_<entid>_<model>
    ON mentat.datoms_text_new
    USING infer (v) WITH (model = '<model>')
    WHERE a = <entid> AND added = true;

Drop with:

SELECT mentat.drop_infer_index(':paper/title', 'qwen05b');
-- => true if dropped, false otherwise

Combined-search pattern

pg_infer composes with pg_trgm, pgvector, and rum in a single Datalog query. The classic multi-signal ranking pattern:

:find ?title ?score
:in $ ?query
:where
  [(infer-near $ :paper/title ?query 100) [[?e ?title-shadow ?infer-d]]]
  [?e :paper/title ?title]
  [(similar-to $ :paper/title ?query 0.2) [[?e ?title-shadow2 ?trgm]]]
  [(rum-fulltext $ :paper/body ?query) [[?e ?body ?ts-rank]]]
  [(* (- 1 ?infer-d) 0.4) ?part1]      ;; via where-fn arithmetic
  [(* ?trgm 0.2) ?part2]
  [(* ?ts-rank 0.4) ?part3]
  [(+ ?part1 ?part2) ?p12]
  [(+ ?p12 ?part3) ?score]
:order (desc ?score)
:limit 20

Each signal contributes orthogonal information: pg_infer finds semantic relationships the other tools can't discover; pg_trgm catches typos; rum ranks by lexeme position. The final score is just a weighted sum of the three.

Errors

What this does NOT (yet) give you

• Per-query model override threading. The :model keyword arg is parsed but doesn't yet rewrite the SQL to inline the model. Set infer.default_model GUC at session/transaction scope.
• The walk() / describe() tabular outputs. Those return multi-column tables; pg_mentat where-fns are scalar / relation shaped today. To use them, drop into raw SQL alongside Datalog.
• Index-only (infer-implies). The implies operator @> is not in infer_text_ops's default opclass; queries using (infer-implies) are sequential scans regardless of index.
• CI happy-path tests. pg_infer is experimental and not in any managed-Postgres apt repo today. The pg_mentat test suite exercises every negative path (arity, arg type, missing extension, unknown attribute) on every CI run, but the e2e happy path tests skip unless pg_infer is installed in the test cluster.

See also

• pg_infer README for the underlying SQL surface, model registration, and vindex extraction.
• Vector Search via pgvector \u2014 the embedding-based cousin of infer-near.
• Trigram Similarity via pg_trgm and BM25-style Ranked Search via rum for the text-side complements.

Geospatial Search via PostGIS

pg_mentat integrates with PostGIS, the canonical geospatial extension for PostgreSQL: WKT/WKB geometry types, GEOS-backed spatial predicates, GiST spatial indexing, and SRID-aware coordinate transforms. With PostGIS attached, pg_mentat queries can filter and rank entities by spatial relationships — distance, containment, intersection — alongside the rest of Datalog's pattern matching.

PostGIS is an optional dependency. Detect with mentat.has_postgis(). The integration uses a side-table aux pattern (mirroring pgvector): pg_mentat does not yet add :db.type/geometry to the schema; geometry data lives in per-attribute auxiliary tables keyed by entid.

Side-table model

Each attach_geometry_attribute(:attr, srid, geom_type) creates:

CREATE TABLE mentat.attr_<entid>_geom(
    e BIGINT PRIMARY KEY,
    geom geometry(<geom_type>, <srid>) NOT NULL
);

geom_type defaults to 'GEOMETRY' (untyped, accepts any subtype); restrict to 'POINT', 'POLYGON', etc. when you want PostGIS to enforce subtype at insert time.

srid defaults to 4326 (WGS84). The SRID is read at query-compile time from geometry_columns so input WKT is automatically coerced to the column's SRID via ST_GeomFromText(<wkt>, <srid>).

Quick start

# Install PostGIS (Debian/Ubuntu example).
sudo apt-get install postgresql-16-postgis-3

CREATE EXTENSION pg_mentat;
CREATE EXTENSION postgis;

SELECT mentat.t('[
  {:db/ident :place/name :db/valueType :db.type/string :db/cardinality :db.cardinality/one}
  {:db/ident :place/loc  :db/valueType :db.type/string :db/cardinality :db.cardinality/one}
]');
SELECT mentat.t('[
  {:db/id "a" :place/name "Boston" :place/loc "side"}
  {:db/id "b" :place/name "NYC"    :place/loc "side"}
  {:db/id "c" :place/name "Origin" :place/loc "side"}
]');

-- Attach + populate.
SELECT mentat.attach_geometry_attribute(':place/loc', 4326, 'POINT');
SELECT mentat.create_gist_geometry_index(':place/loc');
DO $$
DECLARE eb BIGINT; en BIGINT; eo BIGINT;
BEGIN
  SELECT e INTO eb FROM mentat.datoms_text_new
    WHERE a = (SELECT entid FROM mentat.schema WHERE ident = ':place/name')
      AND v = 'Boston';
  SELECT e INTO en FROM mentat.datoms_text_new
    WHERE a = (SELECT entid FROM mentat.schema WHERE ident = ':place/name')
      AND v = 'NYC';
  SELECT e INTO eo FROM mentat.datoms_text_new
    WHERE a = (SELECT entid FROM mentat.schema WHERE ident = ':place/name')
      AND v = 'Origin';
  PERFORM mentat.set_geometry(eb, ':place/loc', 'POINT(-71.0589 42.3601)');
  PERFORM mentat.set_geometry(en, ':place/loc', 'POINT(-74.0060 40.7128)');
  PERFORM mentat.set_geometry(eo, ':place/loc', 'POINT(0 0)');
END;
$$;

-- 2 nearest cities to (-72, 41).
SELECT mentat.q('[:find ?name ?d
                  :where [(geom-near $ :place/loc "POINT(-72 41)" 2) [[?e ?d]]]
                         [?e :place/name ?name]
                  :order (asc ?d)]');
-- => [["Boston", 1.65...], ["NYC", 2.03...]]

Where-fns

[(geom-near $ :attr "WKT" k) [[?e ?dist]]]

Top-K nearest neighbors by ST_Distance, ordered by ascending distance. Uses PostGIS's <-> GiST-friendly distance operator inside the subquery for index-driven retrieval.

[(geom-within $ :attr "WKT" radius) [[?e ?dist]]]

Entities whose geometry is within radius of the input WKT, via ST_DWithin. The radius unit matches the column's SRID (degrees for 4326, meters for projected SRIDs). ?dist is ST_Distance(geom, wkt) for ordering.

[(geom-contains $ :attr "WKT") [[?e]]]

Entities whose geometry contains the input WKT, via ST_Contains. Useful for "which polygon does this point fall in?".

[(geom-intersects $ :attr "WKT") [[?e]]]

Entities whose geometry intersects the input WKT, via ST_Intersects. The most permissive predicate — any kind of overlap, including touching.

All four where-fns:

• Take a leading $ source-var for parser symmetry.
• Take an attribute keyword and a WKT string literal.
• Bind ?e to the entity id; geom-near and geom-within also bind ?dist (a float8).
• JOIN cleanly to subsequent EAV patterns by ?e thanks to the FtsJoin entity-binding fix shipped with pgvector.

Index

SELECT mentat.create_gist_geometry_index(':place/loc');
-- => 'attr_<entid>_geom_gist'

Plain GiST index on geom. PostGIS uses it automatically for ST_DWithin, ST_Distance (when the <-> operator drives the ORDER BY), ST_Contains, ST_Intersects, and other spatial-relationship operators. Without the index every spatial predicate becomes a sequential scan with full GEOS evaluation — fine for a few hundred rows, painful past ~10k.

SRID handling

The integration reads each column's SRID from geometry_columns at compile time and emits ST_GeomFromText(<wkt>, <srid>) so input WKT inherits the column's SRID automatically. Without this, mixed SRID (Point 0 vs Point 4326) raises Operation on mixed SRID geometries.

If your column is SRID 0 (intentionally projection-agnostic), the emit becomes ST_GeomFromText(<wkt>, 0) and PostGIS treats both sides as "unknown SRID". Spatial predicates still work, but ST_Distance returns Cartesian distance, not geodesic.

For accurate geodesic distance over WGS84, use SRID 4326 and either:

• Cast through geography outside this integration (e.g. ST_Distance(g1::geography, g2::geography)).
• Use a projected SRID that matches your data's region.

Errors

Worked example: "where are the customers near our new store?"

(d/q '[:find ?name ?email ?dist-km
       :where
         ;; New store at given coords.
         [(geom-within $ :customer/address ?store-wkt 10.0) [[?e ?dist]]]
         [?e :customer/name ?name]
         [?e :customer/email ?email]
         [(* ?dist 111.0) ?dist-km]   ;; deg -> km approximation at mid-latitudes
       :order (asc ?dist)
       :limit 50]
     db
     "POINT(-71.05 42.36)")

Plan:

1. geom-within returns (?e, ?dist) for each customer within 10° of the store, using the GiST index. With ~1M customers, this is typically < 50ms.
2. Two EAV joins follow ?e to name and email.
3. (* ?dist 111.0) converts decimal degrees to kilometers (rough, latitude-dependent — for production use a projected SRID instead).
4. Top 50 by ascending distance.

What this does NOT (yet) give you

• :db.type/geometry schema integration. Geometries don't transact via mentat.t. Use mentat.set_geometry directly. A future schema-side integration is planned; the aux-table representation is forward-compatible.
• Variable WKT in where-fns. The WKT must be a string literal in the EDN. Pass dynamic geometries through the :in clause (when schema integration ships).
• Geography type. Only geometry is wrapped today. geography is reachable via raw SQL alongside Datalog.
• Raster, topology, tiger geocoder. PostGIS has many sub-extensions this integration doesn't wrap. Use them directly via SQL.
• 3D / 4D coordinates. Z and M coordinates pass through as part of the geometry but the where-fns operate on 2D predicates only.

Property Graph Queries via PG19 SQL/PGQ

PostgreSQL 19 adds SQL/PGQ (ISO/IEC 9075-16, Property Graph Queries) — CREATE PROPERTY GRAPH DDL and GRAPH_TABLE SELECT clauses that let you query relational tables using graph pattern matching syntax.

pg_mentat provides helpers that map its narrow EAV storage onto SQL/PGQ-compatible vertex and edge tables, so a Datalog datom store can be queried with cypher-style graph patterns alongside Datalog.

PG19 is in development at the time of writing. The SQL/PGQ feature landed on master on 2026-03-16 (commit 2f094e7ac69) and will ship in PG19. The integration here is forward-looking: the DDL generator produces well-formed text on any PG version, but executing the result requires PG19+. Detect with mentat.has_pg19_graph().

Data-model fit

SQL/PGQ expects:

• Vertex tables: typed entity tables, one row per entity, with named columns becoming vertex properties.
• Edge tables: rows representing relationships, with explicit SOURCE / DESTINATION foreign-key references.

pg_mentat's narrow datom tables are EAV-shaped (e, a, v, tx, added) — there's no built-in entity-typed view. The integration materializes vertex and edge views from named attributes:

Each registered attribute becomes one view; users compose them into a property graph by name.

Quick start

CREATE EXTENSION pg_mentat;

-- Define schema with a ref-type attribute (the edge).
SELECT mentat.t('[
  {:db/ident :person/name     :db/valueType :db.type/string :db/cardinality :db.cardinality/one}
  {:db/ident :company/name    :db/valueType :db.type/string :db/cardinality :db.cardinality/one}
  {:db/ident :person/employer :db/valueType :db.type/ref    :db/cardinality :db.cardinality/one}
]');

-- Materialize vertex views (one per entity-attr).
SELECT mentat.create_vertex_view(':person/name');
-- => 'mentat.v__person_name'
SELECT mentat.create_vertex_view(':company/name');
-- => 'mentat.v__company_name'

-- Materialize an edge view (must be ref-typed).
SELECT mentat.create_edge_view(':person/employer');
-- => 'mentat.e__person_employer'

-- Generate the CREATE PROPERTY GRAPH DDL. Returns text — executing
-- it requires PG19+.
SELECT mentat.create_property_graph_ddl(
  'social',
  ARRAY[':person/name', ':company/name'],
  ARRAY[':person/employer']
);

Output (well-formed DDL):

CREATE PROPERTY GRAPH social
    VERTEX TABLES (
        mentat.v__person_name LABEL "person/name",
        mentat.v__company_name LABEL "company/name"
    )
    EDGE TABLES (
        mentat.e__person_employer
            SOURCE mentat.v__person_name
            DESTINATION mentat.v__person_name
            LABEL "person/employer"
    );

On PG19+, EXECUTE this DDL and then issue graph queries:

SELECT name FROM GRAPH_TABLE (social
  MATCH (p IS "person/name")-[IS "person/employer"]->(c IS "company/name")
  COLUMNS (c.label AS name));

Helpers

Caveats and design notes

1. Edge SOURCE/DESTINATION resolution. SQL/PGQ requires explicit SOURCE/DESTINATION references for edge tables. The DDL generator currently uses the first vertex attribute as both source and destination labels. For multi-vertex-type graphs (person -> company), you'll want to hand-edit the generated DDL or call create_property_graph_ddl per-edge with the right vertex-attr pair. A future revision will accept a per-edge (src-attr, dst-attr) mapping.

2. Edge IDs are synthetic. The edge view exposes `id = e * 100000

   • v` as a synthetic primary key. With realistic entity ids this won't collide, but for very large entity-id ranges you may want to redefine the view.

3. Datom type fan-out. Each vertex view materializes from a single typed datom table (string / long / ref / etc.) so a "person" with mixed-type attributes has one view per attribute. To get multiple property columns in one vertex view, materialize a custom view by hand (combining several datom tables on e) and reference that from the property graph.

4. Read-only. SQL/PGQ is a read-only graph view over base tables. Updates still go through mentat.t. The vertex and edge views automatically reflect new transactions because they are non-materialized views.

What this does NOT (yet) give you

• Native graph storage. SQL/PGQ is a graph view over relational tables. For native graph workloads, integrate Apache AGE or Neo4j.
• Pre-PG19 support. SQL/PGQ requires PG19+. On older versions, use Datalog patterns directly — [?p :person/employer ?c] [?c :company/name ?cn] expresses the same graph hop.
• Heterogeneous edge types. All edges in the generated DDL share the same source and destination labels. Multi-typed graphs (e.g. person -> company and person -> person) need hand-edited DDL.
• End-to-end CI tests. The pgrx test cluster is PG16; happy-path graph queries can't be exercised until pgrx supports PG19.

See also

• PostgreSQL devel docs: Querying Graphs
• ISO/IEC 9075-16:2023 (SQL/PGQ standard)

Time-Series Storage via TimescaleDB

TimescaleDB (Apache 2.0 OSS edition) converts regular PostgreSQL tables into "hypertables" — automatically partitioned by a time column with chunk-level operations for fast inserts, time-range queries, retention, and compression.

pg_mentat's transaction log (mentat.transactions) and the instant-typed datoms (mentat.datoms_instant_new) are natural fits: both have a meaningful time axis, and time-window queries are common on the audit / time-travel paths. Treat as a SOFT dependency.

When to use this

If the store is small (< 10M txs) and history is short (< 1 year), plain PostgreSQL handles it fine and TimescaleDB adds operational overhead without measurable benefit.

Helpers

Quick start

# Install TimescaleDB (Debian/Ubuntu example).
sudo apt-get install timescaledb-2-postgresql-16
# Add to postgresql.conf: shared_preload_libraries = 'timescaledb'
# Restart postgres.

CREATE EXTENSION pg_mentat;
CREATE EXTENSION timescaledb;

-- Convert the transaction log to a hypertable. Default chunk = 1 month.
SELECT mentat.timescale_attach_transactions();

-- Optionally attach the instant datom table too.
SELECT mentat.timescale_attach_instant_datoms();

-- Drop tx history older than 90 days. WARNING: time-travel queries
-- with :as-of will fail for txs older than this.
SELECT mentat.timescale_set_transaction_retention(INTERVAL '90 days');

Caveats

1. Retention is destructive. TimescaleDB's add_retention_policy physically drops chunks. Datoms inserted in those chunks are gone. pg_mentat's :as-of time-travel queries will fail for txs older than the retention window. Pin retention to your audit requirements before enabling.

2. Chunk size affects insert latency. Smaller chunks (1 day vs 1 month) mean more chunks, more catalog overhead per insert. For most pg_mentat workloads — tx counts in the millions per month — monthly chunks are a good default.

3. CLUSTER doesn't apply. Hypertable chunks are individually CLUSTER'able but not as a unit. The narrow datom tables remain plain (only mentat.transactions and datoms_instant_new become hypertables).

4. Compression is opt-in. TimescaleDB's columnar compression (alter_table ... SET (timescaledb.compress = true) plus add_compression_policy) is not wrapped here. Use it directly via raw SQL if cold-tier compression matters.

5. Distributed hypertables not supported here. Multi-node TimescaleDB is out of scope for this integration.

What this does NOT (yet) give you

• Continuous aggregates wrapped in Datalog. TimescaleDB's CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous) is the right tool for "datoms-per-day" rollups; use it directly via SQL.
• Compression policies. Wrap add_compression_policy directly.
• Retention helpers for non-time tables. Only the time-axis tables (transactions, datoms_instant_new) get hypertable treatment.

See also

• TimescaleDB Hypertable docs
• pg_partman integration — declarative partitioning without TimescaleDB; PostgreSQL-native.

Declarative Partitioning via pg_partman

pg_partman (PostgreSQL license) is the canonical declarative partition-management extension. It builds on PostgreSQL's native partitioning to automate creation, rotation, and retention of time- and range-based partitions.

pg_mentat's natural fit is partitioning mentat.transactions on tx_instant — pg_partman creates monthly/weekly partitions automatically, so old tx history can be dropped or archived without manual maintenance. Treat as a SOFT dependency.

pg_partman vs TimescaleDB

Both partition mentat.transactions on tx_instant. Differences:

pg_partman is the lighter choice for plain time-based retention. Use TimescaleDB when you need its analytic features on top.

Helpers

One-time conversion of mentat.transactions

mentat.partman_attach_transactions requires mentat.transactions to already be a partitioned table. The default pg_mentat install ships it as a plain table to keep installation simple. Converting is a one-time manual step:

-- 1. Lock the table to prevent concurrent writes.
LOCK TABLE mentat.transactions IN ACCESS EXCLUSIVE MODE;

-- 2. Rename + recreate as partitioned root.
ALTER TABLE mentat.transactions RENAME TO transactions_old;

CREATE TABLE mentat.transactions (
    tx         BIGINT PRIMARY KEY,
    tx_instant TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (tx_instant);

-- 3. Create one initial partition covering existing data.
--    pg_partman will manage future partitions.
CREATE TABLE mentat.transactions_default
    PARTITION OF mentat.transactions DEFAULT;

-- 4. Migrate rows.
INSERT INTO mentat.transactions SELECT * FROM mentat.transactions_old;
DROP TABLE mentat.transactions_old;

-- 5. Now register with pg_partman.
SELECT mentat.partman_attach_transactions('1 month', premake => 4);

After registration, pg_partman's maintenance functions handle everything else.

Quick start

# Install pg_partman (NO_BGW=1 skips the bgw build, so no preload required).
cd pg_partman
PG_CONFIG=/path/to/pg_config make NO_BGW=1
PG_CONFIG=/path/to/pg_config make NO_BGW=1 install

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

-- Convert mentat.transactions (one-time manual step above).

-- Register.
SELECT mentat.partman_attach_transactions('1 month');

-- Set retention to 90 days.
SELECT mentat.partman_set_transaction_retention('90 days');

-- Run maintenance now to materialize partitions; pg_cron schedules
-- this normally (see docs/src/pg_cron.md).
SELECT mentat.partman_run_maintenance();

Periodic maintenance

pg_partman needs run_maintenance called regularly — typically every few hours — to:

• Pre-create upcoming partitions (controlled by premake).
• Drop expired partitions (controlled by retention).
• Update analyze stats on each partition.

Schedule via pg_cron:

SELECT mentat.cron_schedule_partman_maintenance('0 3 * * *');
-- Daily at 03:00 UTC; idempotent.

Errors

What this does NOT (yet) give you

• Auto-conversion of mentat.transactions. The one-time partition conversion is manual; the helper refuses to do it for you to prevent silent data loss.
• Per-store partitioning in multi-tenant deployments. Add store_id to the partition key by hand-editing the partition layout.
• Datom table partitioning. Only the transaction log gets pg_partman treatment; datom tables are typically high-throughput with low time-axis selectivity, so per-tx partitioning is the better approach via TimescaleDB hypertables on datoms_instant_new.

See also

• pg_partman docs
• pg_cron integration for scheduling maintenance.
• TimescaleDB integration for the hypertable alternative.

Scheduled Maintenance via pg_cron

pg_cron is the canonical scheduler extension for PostgreSQL: cron-style schedules stored in cron.job, executed by a background worker. Use it to schedule pg_mentat maintenance: pg_partman partition rotation, narrow-datom-table VACUUM, materialized view refreshes, and the like.

pg_cron is an optional dependency. Detect with mentat.has_pg_cron(). Without it, the helpers raise a clear missing-extension error rather than silently no-op'ing.

Operational requirements

pg_cron requires:

1. shared_preload_libraries = 'pg_cron' (or 'pg_cron,pg_tre,...' if you have other preloads). Cluster restart.
2. cron.database_name = '<your-db>' in postgresql.conf — pg_cron runs jobs in exactly one database per cluster.
3. CREATE EXTENSION pg_cron; in that database.

Without those steps CREATE EXTENSION pg_cron fails. The pg_mentat helpers don't bypass this — they detect via mentat.has_pg_cron() and refuse to install jobs if pg_cron is missing.

Helpers

Quick start

-- After enabling pg_cron in postgresql.conf and restarting:
CREATE EXTENSION pg_cron;
CREATE EXTENSION pg_mentat;

-- Schedule daily partition maintenance.
SELECT mentat.cron_schedule_partman_maintenance();
-- => job id

-- Schedule nightly VACUUM ANALYZE on datom tables.
SELECT mentat.cron_schedule_vacuum_datoms();
-- => job id

-- Custom job: refresh a materialized view at 02:30 UTC daily.
SELECT mentat.cron_schedule(
    'refresh-search-cache',
    '30 2 * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY app.search_index;'
);

-- Cancel one.
SELECT mentat.cron_unschedule('refresh-search-cache');

Cron schedule syntax

pg_cron uses standard 5-field cron syntax (minute hour day month dow) in UTC. Examples:

pg_cron also supports a 6-field "second-precision" form (PG 1.6+); see the pg_cron docs.

Common pg_mentat schedules

-- Hourly partman maintenance for high-volume stores.
SELECT mentat.cron_schedule_partman_maintenance('0 * * * *');

-- Vacuum just the high-churn fulltext attribute hourly.
SELECT mentat.cron_schedule(
    'mentat-vacuum-text',
    '0 * * * *',
    'VACUUM (ANALYZE) mentat.datoms_text_new;'
);

-- Drop dead tuples from PgQue's tx-emit triggers monthly.
SELECT mentat.cron_schedule(
    'mentat-vacuum-pgque-events',
    '0 4 1 * *',
    'VACUUM FULL pgque.event_1;'
);

-- Refresh the FDW caches (materialized view from postgres-fdw cookbook).
SELECT mentat.cron_schedule(
    'mentat-refresh-fdw',
    '*/30 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY remote_open_issues;'
);

Inspecting jobs

SELECT jobid, jobname, schedule, command, active
FROM cron.job
WHERE jobname LIKE 'mentat-%';

-- Recent runs.
SELECT jobid, runid, start_time, end_time, status, return_message
FROM cron.job_run_details
ORDER BY start_time DESC LIMIT 20;

Errors

What this does NOT (yet) give you

• Per-job notifications. pg_cron writes results to cron.job_run_details; if you want alerts, set up a separate monitoring job that reads from there.
• Multi-database scheduling. pg_cron runs jobs in exactly one database (cron.database_name). For multi-tenant pg_mentat deployments where each store is its own database, see the cross-database pattern in the pg_cron docs.
• Sub-minute schedules in the standard 5-field form. Use the 6-field form (pg_cron 1.6+) for second-precision schedules.

See also

• pg_cron README
• pg_partman integration — schedules partition maintenance via mentat.cron_schedule_partman_maintenance.
• PgQue integration — uses pg_cron as the recommended ticker driver.

SQL Function Reference

All pg_mentat functions live in the mentat schema. After CREATE EXTENSION pg_mentat, they are accessible as mentat.function_name().

Function Naming Convention

pg_mentat provides two sets of function names:

Convenience aliases (recommended for everyday use in the default mentat schema):

SELECT mentat.t('[{:person/name "Alice"}]');
SELECT mentat.q('[:find ?e :where [?e :person/name "Alice"]]');
SELECT mentat.pull('[*]', 10001);

Full-name functions use a mentat_ prefix. These read naturally when installed into a custom schema:

-- If you install into a custom schema:
CREATE EXTENSION pg_mentat SCHEMA myapp;
SELECT myapp.mentat_transact('[{:person/name "Alice"}]');
SELECT myapp.mentat_query('[:find ?e :where [?e :person/name "Alice"]]');
SELECT myapp.mentat_pull('[*]', 10001);

The full-name functions exist because pgrx derives the SQL function name from the Rust function name. Since any schema can host the extension, the mentat_ prefix ensures the function names read sensibly regardless of the schema choice. The convenience aliases eliminate redundancy for the common default case.

Quick Reference

Transaction Functions

mentat.t(edn) / mentat_transact(edn)

Execute a transaction. Returns a JSON transaction report with tx_id, tx_instant, and tempids.

SELECT mentat.t('[
  {:db/id "tempid-1"
   :person/name "Alice"
   :person/age 30}
]');

The t alias transacts against the default store. Use the full function with a store argument for named stores:

SELECT mentat.mentat_transact_store('analytics', '[{:event/type "click"}]');

mentat_with(edn) — Speculative Transaction

Execute a transaction without persisting it. Returns the same report format, but writes nothing. Useful for validation or "what-if" analysis.

SELECT mentat.mentat_with('[
  {:person/name "Test" :person/age 99}
]');

Query Functions

mentat.q(query, inputs) / mentat_query(query, inputs)

Execute a Datalog query. Returns JSONB with columns and results.

-- Simple query
SELECT mentat.q('
  [:find ?name ?age
   :where [?e :person/name ?name]
          [?e :person/age ?age]
          [(> ?age 21)]]
');

-- With input bindings (positional, matching :in clause order)
SELECT mentat.q('
  [:find ?name
   :in $ ?min-age
   :where [?e :person/name ?name]
          [?e :person/age ?age]
          [(>= ?age ?min-age)]]
', '[25]');

The inputs parameter is a JSON value:

• Simple array for positional bindings: '[25]'
• Empty for no inputs: '{}' or '[]'

mentat.explain(query) / mentat_explain(query)

Show the generated SQL and PostgreSQL's EXPLAIN output without executing the query.

SELECT mentat.explain('[:find ?name :where [?e :person/name ?name]]');

mentat_query_sql(query) — Generated SQL

Return only the generated SQL string (no execution, no EXPLAIN).

SELECT mentat.mentat_query_sql('[:find ?name :where [?e :person/name ?name]]');

mentat_query_view(name, query) — Create SQL VIEW from Datalog

Create a PostgreSQL VIEW backed by a Datalog query:

SELECT mentat.mentat_query_view('people_over_30', '
  [:find ?name ?age ?email
   :where [?e :person/name ?name]
          [?e :person/age ?age]
          [?e :person/email ?email]
          [(> ?age 30)]]
');

-- Now use it like any SQL view
SELECT * FROM mentat.people_over_30 WHERE name LIKE 'A%';

Pull Functions

mentat.pull(pattern, eid) / mentat_pull(pattern, eid)

Pull attributes for a single entity. Returns a nested JSON document.

-- Pull everything
SELECT mentat.pull('[*]', 10001);

-- Pull specific attributes with nested refs
SELECT mentat.pull('[
  :person/name
  :person/age
  {:person/friends [:person/name :person/age]}
]', 10001);

-- Reverse lookup: who has this entity as a friend?
SELECT mentat.pull('[:person/name :person/_friends]', 10001);

-- With modifiers
SELECT mentat.pull('[
  :person/name
  {(:person/friends :limit 5 :as :top-friends) [:person/name]}
  {(:person/_friends :as :admirers) [:person/name]}
]', 10001);

mentat.pull_many(pattern, eids) / mentat_pull_many(pattern, eids)

Pull the same pattern for multiple entities. Returns a JSON array.

SELECT mentat.pull_many('[:person/name :person/age]', ARRAY[10001, 10002, 10003]);

mentat.entity(eid) / mentat_entity(eid)

Return all current attributes for an entity as a flat JSON map (equivalent to pull('[*]', eid)).

SELECT mentat.entity(10001);

Schema Functions

mentat.schema() / mentat_schema()

Return the full schema as JSON, keyed by attribute ident.

SELECT mentat.schema();

Store Management

mentat.create_store(name, description)

Create a new isolated store with its own schema, tables, and indexes.

SELECT mentat.create_store('analytics', 'Event tracking store');

mentat.drop_store(name)

Drop a store and all its data (irreversible).

mentat.list_stores()

List all stores with metadata.

mentat.rename_store(old_name, new_name)

Rename an existing store.

Time Travel Functions

mentat.log(store, from_tx, to_tx)

Return the transaction log for a range of transactions.

SELECT mentat.log('default', 1000001, 1000010);

mentat.diff(store, from_tx, to_tx)

Compute the diff between two points in time — what was added and retracted.

SELECT mentat.diff('default', 1000003, 1000007);

Time-travel via query parameters

Pass as_of_tx or since_tx to query functions:

-- Query the database as of transaction 1000005
SELECT mentat.q('
  [:find ?name :where [?e :person/name ?name]]
', '[]', 1000005, NULL);

Excision Functions

mentat_excise(store, entity_id, attribute)

Permanently remove datoms from the database, including all history. This is the only operation that truly deletes data (GDPR compliance).

-- Remove all data for an entity
SELECT mentat.mentat_excise('default', 10042, NULL);

-- Remove only a specific attribute
SELECT mentat.mentat_excise('default', 10042, ':person/email');

Subscription Functions

mentat.subscribe(store, name, query)

Subscribe to changes matching a Datalog query pattern. Uses PostgreSQL LISTEN/NOTIFY.

SELECT mentat.subscribe('default', 'new_people',
  '[:find ?e :where [?e :person/name]]');

-- In another session:
LISTEN mentat_subscription_new_people;

mentat.unsubscribe(store, name)

Remove a subscription.

Materialized View Functions

mentat.materialize(store, name, query)

Create a materialized view from a Datalog query for faster repeated access.

SELECT mentat.materialize('default', 'active_users',
  '[:find ?e ?name :where [?e :person/name ?name] [?e :person/active true]]');

mentat.refresh(store, name)

Refresh a materialized view with current data.

Statistics & Monitoring

mentat.stats() / mentat_query_stats()

Query execution statistics: call counts, timing, cache hit rates.

mentat.storage() / mentat_storage_stats()

Storage statistics: row counts, table sizes, index sizes.

mentat.cache_stats() / mentat_stmt_cache_stats()

Prepared statement cache statistics.

mentat.cache_clear() / mentat_stmt_cache_clear()

Clear the statement cache.

mentat_health_check()

Extension health check (returns JSON with status, version, store count).

mentat_slow_queries(threshold_ms)

Return recently logged slow queries exceeding the given threshold.

EDN Helper Functions

These operate on EDN-formatted text values and are installed in the public schema for convenience.

Bootstrap Functions

mentat.bootstrap_schema()

Re-run the bootstrap schema installation. Called automatically during CREATE EXTENSION but can be invoked to repair a corrupted schema.

Configuration (GUCs)

pg_mentat exposes its configuration through PostgreSQL's Grand Unified Configuration (GUC) system. All parameters use the mentat. prefix and can be set at the session, transaction, or system level.

Setting Parameters

-- Session level
SET mentat.query_timeout_ms = 60000;

-- Transaction level (reverts after transaction)
SET LOCAL mentat.max_result_rows = 500000;

-- System level (requires superuser, persists across restarts)
ALTER SYSTEM SET mentat.slow_query_threshold_ms = 200;
SELECT pg_reload_conf();

-- Per-query (via SET LOCAL in a transaction block)
BEGIN;
SET LOCAL mentat.enable_optimizer_hints = true;
SELECT mentat_query('...', '{}');
COMMIT;

Query Execution Parameters

mentat.query_timeout_ms

Maximum execution time for a single Datalog query in milliseconds. Queries exceeding this limit are cancelled. Set to 0 to disable (not recommended in production).

SET mentat.query_timeout_ms = 60000;  -- 60 seconds

mentat.max_result_rows

Maximum number of result rows returned by a single query. Prevents cartesian explosions from consuming all available memory. Set to 0 for unlimited (not recommended in production).

SET mentat.max_result_rows = 50000;

mentat.max_recursion_depth

Maximum depth for recursive rule evaluation (WITH RECURSIVE CTEs). Limits traversal depth to prevent infinite loops from cyclic data. Applied as the iteration limit on generated recursive CTEs.

SET mentat.max_recursion_depth = 50;

mentat.temp_file_limit

Maximum disk space for intermediate results during query execution (sorts, hash joins, materialization). Applied via SET LOCAL temp_file_limit. Prevents disk exhaustion from large queries.

SET mentat.temp_file_limit = '2GB';

Optimizer Parameters

mentat.enable_optimizer_hints

When enabled, pg_mentat applies optimizer hints during query execution:

• Sets work_mem to mentat.default_work_mem for complex queries (multiple joins, aggregates, CTEs)
• May disable sequential scan for queries that should use indexes

Enable this if you observe suboptimal query plans. Disable if it conflicts with your global PostgreSQL tuning.

SET mentat.enable_optimizer_hints = true;

mentat.default_work_mem

The work_mem value applied during Mentat query execution when mentat.enable_optimizer_hints is true. Only affects queries with multiple joins, aggregates, or CTEs.

SET mentat.default_work_mem = '128MB';

Explain Parameters

mentat.explain_format

Output format for mentat_explain() plans. Valid values: text, json, yaml, xml.

SET mentat.explain_format = 'json';
SELECT mentat_explain('[:find ?e :where [?e :person/name]]', '{}');

Monitoring Parameters

mentat.slow_query_threshold_ms

Queries exceeding this threshold (in milliseconds) are logged at WARNING level with their execution time and generated SQL. Set to 0 to disable slow query logging.

SET mentat.slow_query_threshold_ms = 200;

mentat.log_all_queries

When enabled, logs the generated SQL for every query (not just slow ones). Useful for debugging but verbose. Not recommended in production.

SET mentat.log_all_queries = true;

Recommended Production Configuration

-- postgresql.conf or ALTER SYSTEM

-- Prevent runaway queries
ALTER SYSTEM SET mentat.query_timeout_ms = 30000;
ALTER SYSTEM SET mentat.max_result_rows = 100000;
ALTER SYSTEM SET mentat.max_recursion_depth = 100;
ALTER SYSTEM SET mentat.temp_file_limit = '1GB';

-- Monitor performance
ALTER SYSTEM SET mentat.slow_query_threshold_ms = 100;
ALTER SYSTEM SET mentat.log_all_queries = false;

-- Let the optimizer help
ALTER SYSTEM SET mentat.enable_optimizer_hints = true;
ALTER SYSTEM SET mentat.default_work_mem = '64MB';

SELECT pg_reload_conf();

Recommended Development Configuration

-- More permissive for development/testing
SET mentat.query_timeout_ms = 0;          -- no timeout
SET mentat.max_result_rows = 0;           -- unlimited
SET mentat.max_recursion_depth = 1000;    -- deep graphs
SET mentat.slow_query_threshold_ms = 0;   -- log everything
SET mentat.log_all_queries = true;        -- see all SQL
SET mentat.explain_format = 'json';       -- structured plans

Viewing Current Settings

SHOW mentat.query_timeout_ms;
SHOW mentat.enable_optimizer_hints;

-- Or view all mentat settings
SELECT name, setting, short_desc
FROM pg_settings
WHERE name LIKE 'mentat.%'
ORDER BY name;

Multi-Tenancy with Per-Store Row-Level Security

pg_mentat ships with optional per-store Row-Level Security (RLS) for the nine narrow datom tables (mentat.datoms_<type>_new). When armed, every SELECT, INSERT, UPDATE, and DELETE against those tables is automatically filtered by store_id = mentat.current_store_id(), where mentat.current_store_id() reads the session GUC mentat.current_store_id. This gives multi-tenant Postgres deployments per-tenant isolation enforced by the database itself, not by application code.

This is one of the genuine differentiators pg_mentat has over Datomic: isolation lives in the storage layer, not at the application boundary.

The model

• The narrow datom tables are shared across all stores in a single Postgres database. Each row carries a store_id BIGINT column.
• mentat.stores maps store_name to store_id (BIGSERIAL). The default store is always store_id = 0.
• The session-level GUC mentat.current_store_id (a placeholder GUC, parsed as BIGINT by mentat.current_store_id()) tells RLS which store the connection is acting on behalf of. Unset, empty, or unparseable values fall back to 0 (the default store).
• A second GUC, mentat.enable_multi_tenant_rls (boolean, default off), records operator intent: it is the canonical user-visible switch that says "RLS is on for this deployment". Audit tooling and the docs treat the GUC as authoritative; the actual enforcement is per-table state (next section).

Opting in

RLS is off by default so that single-store deployments pay zero overhead. Two steps arm it:

1. Register the GUC for the deployment so audit tools can see operator intent:

   ALTER SYSTEM SET mentat.enable_multi_tenant_rls = on;
   SELECT pg_reload_conf();
   

   Or, for a single session: SET mentat.enable_multi_tenant_rls = on;.

2. Arm the per-table RLS state on every database where the extension is installed:

   SELECT mentat.enable_multi_tenant_rls(true);
   

   This calls ALTER TABLE ... ENABLE ROW LEVEL SECURITY on all nine narrow tables in one transaction and returns 9.

To disarm:

SELECT mentat.enable_multi_tenant_rls(false);  -- DISABLE ROW LEVEL SECURITY on all nine tables
ALTER SYSTEM SET mentat.enable_multi_tenant_rls = off;
SELECT pg_reload_conf();

The two-step opt-in is intentional: arming RLS briefly takes AccessExclusiveLock on each narrow table, and we do not want that to happen implicitly on a session GUC change.

Per-session usage

Once RLS is armed, every session that wants to read or write datoms must set its store id:

SET mentat.current_store_id = '7';      -- pretend to be store_id=7 for this session

A common pattern is to bind the GUC to a per-tenant role:

ALTER ROLE tenant_alice SET mentat.current_store_id = '1';
ALTER ROLE tenant_bob   SET mentat.current_store_id = '2';

A connection authenticating as tenant_alice then sees only store_id = 1 rows, automatically. The application never needs a WHERE store_id = ... clause; forgetting one used to leak data, with RLS it cannot.

Threat model

What this protects:

• Cross-tenant reads by regular (non-superuser, non-BYPASSRLS) roles. A query that omits a WHERE store_id = ... clause is silently filtered.
• Cross-tenant writes: the policies have a WITH CHECK predicate that rejects an INSERT or UPDATE whose store_id does not match the session value. A misconfigured ETL job that tags rows with the wrong tenant id fails closed instead of silently corrupting another tenant's data.

What this does not protect against:

• Superusers (rolsuper = t). PostgreSQL always bypasses RLS for superusers; SET row_security = on does not change this. Run application connections as a non-superuser role.
• BYPASSRLS roles. Same as superusers; never grant BYPASSRLS to an application role.
• The table owner. By default the role that ran CREATE EXTENSION pg_mentat owns the narrow tables and bypasses RLS. If the application connection role is the same as the owner, RLS is silently inert. Either run CREATE EXTENSION as a dedicated installer role and grant SELECT/INSERT/UPDATE/DELETE to per-tenant roles, or call ALTER TABLE mentat.datoms_<type>_new FORCE ROW LEVEL SECURITY to subject the owner as well.
• SECURITY DEFINER functions owned by a privileged role. SECURITY DEFINER functions execute as their owner, so an unwary function created by the superuser is a hole in the wall.
• Direct file-system access. Anyone who can read the cluster's data directory can read every store's datoms.
• Tenant-id forgery. Any role that can SET mentat.current_store_id can claim to be any tenant. The contract is that tenant id is assigned by trusted middleware (typically by authenticating to a per-tenant role and using ALTER ROLE ... SET mentat.current_store_id = '<id>'). A web tier that lets the client send the tenant id is its own bug, not one that pg_mentat will catch.

A worked example

-- One-time install and arming
CREATE EXTENSION pg_mentat;
SELECT mentat.enable_multi_tenant_rls(true);
ALTER SYSTEM SET mentat.enable_multi_tenant_rls = on;
SELECT pg_reload_conf();

-- Two tenants
INSERT INTO mentat.stores (store_name, schema_name)
VALUES ('alice', 'mentat'), ('bob', 'mentat');

-- One role per tenant, each pinned to its store_id via ALTER ROLE
CREATE ROLE tenant_alice LOGIN PASSWORD 'redacted' NOSUPERUSER NOBYPASSRLS;
CREATE ROLE tenant_bob   LOGIN PASSWORD 'redacted' NOSUPERUSER NOBYPASSRLS;
GRANT USAGE ON SCHEMA mentat TO tenant_alice, tenant_bob;
GRANT SELECT, INSERT, UPDATE, DELETE
   ON mentat.datoms_ref_new, mentat.datoms_long_new, mentat.datoms_text_new,
      mentat.datoms_double_new, mentat.datoms_instant_new,
      mentat.datoms_keyword_new, mentat.datoms_uuid_new,
      mentat.datoms_bytes_new, mentat.datoms_boolean_new
   TO tenant_alice, tenant_bob;

ALTER ROLE tenant_alice
    SET mentat.current_store_id = '1';   -- alice's store_id
ALTER ROLE tenant_bob
    SET mentat.current_store_id = '2';   -- bob's store_id

-- Now alice's session sees only alice's data, automatically:
\c - tenant_alice
SELECT count(*) FROM mentat.datoms_long_new;   -- only alice's rows
INSERT INTO mentat.datoms_long_new (store_id, e, a, v, tx, added)
VALUES (2, 1, 1, 1, 1, true);                  -- ERROR: WITH CHECK violation

Caveats

• The mentat.datoms compatibility view is currently hard-coded to store_id = 0. Code that still reads from the view sees only the default store regardless of the session's mentat.current_store_id. Migrate to the narrow tables (or to mentat_query / mentat_pull) for multi-store access.
• Schema metadata (mentat.schema, mentat.idents, mentat.partitions, mentat.transactions) is not yet under RLS. In the present model all stores share one schema; tenants can see one another's attribute definitions. If your tenants must not even share schema, run them in separate databases.
• mentat.enable_multi_tenant_rls(true) does not (and cannot) attach RLS retroactively to per-store schemas created by mentat_create_store(). Per-store schemas predate the narrow-table multi-tenancy model and are gradually being subsumed by it.

Datomic Compatibility

pg_mentat implements the core Datomic data model and query language as a PostgreSQL extension. This document reflects the actual implementation status of the current codebase.

Overview

pg_mentat provides Datomic-compatible schema, transactions, and Datalog queries via SQL functions. The companion mentatd server exposes an HTTP API compatible with Datomic client libraries.

Transaction Operations

Query Operations

Find Specifications

Where Clauses

Input Bindings

Query Functions

Predicates

Aggregates

Rules

Pull API

Schema

Value Types

Time-Travel

Excision

Subscriptions / tx-report-queue

mentatd HTTP Protocol

Known Incompatibilities

Migration from Datomic

Schema definitions transfer directly. Remove #db/id partition references:

;; Datomic
[{:db/id #db/id[:db.part/db]
  :db/ident :person/name
  :db/valueType :db.type/string
  :db/cardinality :db.cardinality/one
  :db.install/_attribute :db.part/db}]

;; pg_mentat (same minus partition boilerplate)
[{:db/ident :person/name
  :db/valueType :db.type/string
  :db/cardinality :db.cardinality/one}]

Queries work unchanged. Transactions work unchanged. Pull patterns work unchanged.

Operations: Throughput, Bloat, and the Live Projection

This page covers the operational concerns that matter when pg_mentat is the identity / knowledge-graph backbone of a production service: how to make mentat.t() ingest fast, how to keep the narrow datom tables from bloating, and how to read the "current value" of an attribute cheaply.

It is written against real production feedback (an 82 GB store used as a community-stats identity backbone). The version that introduced the accessors and autovacuum defaults described here is 1.4.0.

1. mentat.t() throughput

What costs time per call

Each mentat.t() call does, regardless of batch size:

1. Allocate a transaction id (nextval on the tx sequence) and insert the mentat.transactions row + the :db/txInstant datom.
2. Parse the EDN, resolve idents / tempids, validate constraints.
3. Per cardinality-one datom, look up the current value to decide assert / replace / skip.
4. Batch-insert the new datom rows (one INSERT per touched type table).

Step 3 used to run a 9-way UNION ALL probe per datom. As of 1.4.0 it is a single indexed lookup on the one narrow table matching the value's type — a measured ~1.8× speedup on a cardinality-one re-assertion workload (6.2 s → 3.4 s for 2000 calls in the project's microbenchmark).

The residual per-call floor (~1–2 ms in that benchmark; higher under production latency and replication) is tx allocation + the txInstant datom + savepoint setup. It is fixed per call, so the way to amortize it is fewer, larger transactions — not smaller batches.

Make backfills fast: one tx, many facts

Batch as many assertions as possible into a single mentat.t() call. The per-call overhead is paid once; the per-datom cost scales linearly and is cheap. A 250k-fact backfill batched at, say, 5,000 facts/tx is 50 calls — not 1,250.

-- Good: 5000 facts, ONE tx, ONE tx-allocation overhead.
SELECT mentat.t($edn$[
  {:db/id "c1" :contribution/key "..." :contribution/kind :commit ...}
  {:db/id "c2" ...}
  ... 4998 more ...
]$edn$);

The larger the batch, the closer you get to the per-datom floor. There is no fixed upper bound other than statement memory; batches of several thousand facts are routinely fine.

Idempotent re-assertion is already a no-op for the data

If your sync re-asserts a cardinality-one fact whose value already matches the current value, pg_mentat takes the Skip path: no new datom is written, no retraction is written, the datom table is not churned. You still pay the per-call tx overhead, so the same advice applies — batch the no-op re-assertions into few large transactions and the cost disappears into the per-call floor.

Tip. If your nightly mirror is mostly no-ops (idempotent by a :contribution/key-style natural key), the cheapest thing you can do is widen the batch. The Skip path means the table doesn't grow; the only cost left is the per-t() tx allocation, which batching amortizes.

2. Autovacuum and bloat

The default-scale-factor trap

PostgreSQL's default autovacuum_vacuum_scale_factor = 0.2 means a table is vacuumed only after 20% of its rows are dead. On a 50M-row narrow table that is 10M dead tuples of slack — autovacuum effectively never fires, and the table (especially its PK and EAVT index) bloats without bound. The instant-typed table is the worst case because monotonic attributes (:first-seen, :last-seen, :observed-at) are re-asserted on every sync, each generating a retraction + assertion.

What 1.4.0 ships

CREATE EXTENSION pg_mentat (and the 1.3.0→1.4.0 upgrade) now sets, on all nine datoms_*_new tables and on mentat.transactions:

autovacuum_vacuum_scale_factor  = 0
autovacuum_vacuum_threshold     = 50000
autovacuum_analyze_scale_factor = 0
autovacuum_analyze_threshold    = 50000

Scale-factor 0 + a fixed 50k-dead-tuple threshold means autovacuum fires on a constant amount of dead tuples regardless of table size. High-churn deployments can lower the threshold further per table:

ALTER TABLE mentat.datoms_instant_new
  SET (autovacuum_vacuum_threshold = 10000);

Reclaiming existing bloat

Storage params change future triggering; they do not shrink a table that is already bloated. To reclaim:

-- Online, no exclusive lock, needs pg_repack installed:
pg_repack -t mentat.datoms_instant_new -d yourdb

-- Or, during a maintenance window (takes an ACCESS EXCLUSIVE lock):
VACUUM FULL mentat.datoms_instant_new;

Schedule a periodic VACUUM via pg_cron:

SELECT mentat.cron_schedule_vacuum_datoms('0 4 * * *');

Monitoring before it bites

mentat.attribute_health() reports live datom counts and the dead-tuple % of each backing table:

SELECT * FROM mentat.attribute_health() ORDER BY dead_pct DESC;

   attr_ident    | value_type |    backing_table      | live_datoms | dead_pct
-----------------+------------+-----------------------+-------------+----------
 :person/seen    | instant    | mentat.datoms_instant_new | 124032  |     31.4
 :person/email   | string     | mentat.datoms_text_new    | 41200   |      2.1
 ...

Alert on dead_pct > 25 to catch bloat before it costs you query latency or disk.

Note on :last-seen-style attributes. Keeping full history of a value that changes every sync is inherently bloat-generating: each change is a retraction + an assertion. If you do not need the history of a monotonic timestamp, mark the attribute :db/noHistory true (see below) — noHistory attributes keep only the current value, so they generate no history trail and cannot bloat.

:db/noHistory — non-historical attributes

As of 1.5.0 the datom log is append-only: a retraction is a new immutable datom, never an in-place flip of the prior assertion. That makes history exact, but it also means an attribute whose value changes every sync (the :observed-at / :last-seen class) accumulates one assert + one retract datom per change — unbounded growth.

Mark such an attribute :db/noHistory true to opt out of history:

{:db/ident :host/last-seen
 :db/valueType :db.type/instant
 :db/cardinality :db.cardinality/one
 :db/noHistory true}

For a :db/noHistory attribute, each assertion physically replaces the prior value in the log (and the projection) instead of appending a retraction + assertion. The log holds exactly the current value:

-- After 10 updates to a noHistory :host/last-seen:
SELECT count(*) FROM mentat.datoms_instant_new
  WHERE e = :host AND a = mentat.attr_id(':host/last-seen');
-- => 1   (a normal attribute would have ~20 rows: 10 asserts + 10 retracts)

Semantics:

• Current-time queries are unchanged — [?h :host/last-seen ?t] returns the current value exactly as for a normal attribute.
• :as-of / history queries see only the current value, because no prior versions are retained. This is the deliberate trade: you give up time-travel on that attribute in exchange for zero bloat.
• Per-attribute, not global — a noHistory attribute and a full-history attribute on the same entity each behave correctly.
• This is Datomic-compatible: Datomic's :db/noHistory has the same "keep only the current value" meaning.

Use it for high-churn, history-irrelevant values (heartbeats, last-seen timestamps, observed counters). Do not use it for attributes whose history you audit or time-travel.

3. The live projection: mentat.current() and mentat.attr_id()

The problem with DISTINCT ON / LATERAL views

A view that resolves "the latest value of attribute A for entity E" typically looks like:

SELECT DISTINCT ON (e) e, v
FROM mentat.datoms_text_new
WHERE a = <attr> AND added
ORDER BY e, tx DESC

…with a LEFT JOIN LATERAL (... ORDER BY tx DESC LIMIT 1) per extra attribute. That recomputes the latest-per-(e, a) on every read and fans out one lateral per attribute — the dominant cost when refreshing a materialized view that joins many attributes.

mentat.current(e, a)

mentat.current() returns the current value of one attribute for one entity as text, with a single indexed lookup on the (store_id, e, a, tx DESC) WHERE added covering index. It dispatches on the attribute's declared value type so only one narrow table is touched.

-- By attribute keyword (resolves the entid for you):
SELECT mentat.current(12345, ':person/canonical-email');

-- Or by attribute entid, if you already have it:
SELECT mentat.current(12345, mentat.attr_id(':person/canonical-email'));

Use it in a view to replace the DISTINCT ON / LATERAL machinery:

CREATE VIEW community.persons AS
SELECT
    e AS person_id,
    mentat.current(e, ':person/canonical-email') AS email,
    mentat.current(e, ':person/name')            AS name,
    mentat.current(e, ':person/employer')        AS employer
FROM (
    -- the set of person entities (one row per entity)
    SELECT DISTINCT e FROM mentat.datoms_text_new
    WHERE a = mentat.attr_id(':person/canonical-email') AND added
) p;

Each mentat.current() call is an index lookup; the planner folds the STABLE function once per row. This is dramatically cheaper than a per-attribute LATERAL on a large fan-out.

A maintained current-state index

For the absolute hottest read paths, back mentat.current() with a covering index per attribute type (most projections read text/keyword):

-- Already shipped: the *_tx covering index supports the lookup:
--   (store_id, tx DESC) INCLUDE (e, a, v) WHERE added
-- For a per-attribute hot path, add a partial index:
CREATE INDEX idx_person_email_current
  ON mentat.datoms_text_new (e, tx DESC)
  WHERE a = <:person/canonical-email entid> AND added;

A fully maintained "current datoms" table (kept in sync on t()) is on the roadmap; today the covering-index + mentat.current() combination gives index-backed reads without it.

mentat.attr_id()

mentat.attr_id(':ns/name') resolves an attribute keyword to its entid for use in SQL and view definitions, so generated viewdefs read a = mentat.attr_id(':person/name') instead of an opaque a = 1308861. It is STABLE, so the planner evaluates it once.

4. Health dashboard

A minimal alerting query:

SELECT attr_ident, live_datoms, dead_pct
FROM mentat.attribute_health()
WHERE dead_pct > 25
ORDER BY dead_pct DESC;

What is NOT solved by auto-indexing

A common first instinct for "pg_mentat is slow" is missing indexes. It is worth stating plainly: the narrow datom tables already carry EAVT / AEVT / VAET / tx covering indexes plus an FTS GIN index. The costs that actually hurt in production are:

• per-t() transaction overhead — solved by batching, not indexing;
• history-resolution on reads — solved by mentat.current() + a per-attribute partial index, not by adding more general indexes;
• dead-tuple bloat — solved by autovacuum tuning + scheduled vacuum, not by indexing.

Adding indexes beyond the shipped set will not move these numbers and will slow t() further (every index is maintained on write).

5. Reading from a hot standby (read replica)

The Datalog read path (mentat.q, mentat.pull, mentat.entity, :as-of / :since time-travel, mentat.lookup_by_ident, and the mentat.has_<ext>() extension detectors) runs on a PostgreSQL hot standby (streaming-replication read replica). As of 1.5.3–1.5.4 these paths use read-only SPI and set their resource-limit GUCs via set_config_option rather than a SQL SET, so they never try to assign a transaction id during recovery.

What this means operationally:

• Serve read APIs from the replica. A query such as SELECT mentat.q('[:find ?e :where [?e :db/ident :db/ident]]') runs on the standby with no error.
• Writes (mentat.t, excision, entid allocation, schema changes) are primary-only by nature — they take a mutable transaction and cannot run on a standby. That is expected; route writes to the primary.

No configuration is required; it works whenever the extension is installed on both hosts (as it must be for replication).

6. Entity-id partitions and collision repair

pg_mentat allocates entity ids from three disjoint, bounded per-partition sequences:

Since 1.5.6 each sequence carries a MAXVALUE at its band ceiling, so an exhausted partition fails loud (nextval: reached maximum value of sequence) instead of silently issuing ids that collide with the next partition's space. db.part/tx consumes one id per mentat.t; its band (~1e12 ids) is effectively unbounded at any realistic write rate.

Pre-1.5.6 stores: overflow and collisions

Stores created before the bands were bounded ran their sequences unbounded to bigint-max. A long-lived, write-heavy store can therefore have overflowed the old [1e4,1e6) user band and [1e6,2e6) tx band into one another, producing entids used as BOTH a transaction and a user/schema entity — one integer, two logical entities, so mentat.entity(E) returns the union of both.

Check for this after upgrading:

SELECT mentat.entid_collision_count();          -- 0 == healthy
SELECT * FROM mentat.entid_collision_report();   -- one row per colliding entid

entid_collision_report() lists each colliding entid, how many non-tx datoms it carries, whether it is used as an attribute, and its incoming ref count.

Repairing collisions

mentat.repair_entid_collisions(dry_run BOOLEAN DEFAULT true, store BIGINT DEFAULT 0) renumbers the colliding non-transaction entities into fresh user-band ids — rewriting e, a, and incoming ref v across the nine log tables and nine current-projection tables, plus the schema/idents catalogs. The transaction keeps its id: a tx id is woven through every datom's tx column and anchors basis-t / :as-of monotonicity, so it must not move.

The repair is destructive. It defaults to a dry run. The safe procedure:

-- 1. See how many would be remapped, changing nothing:
SELECT mentat.repair_entid_collisions(true);

-- 2. Back up the database.

-- 3. In your own transaction, perform the repair:
BEGIN;
SELECT mentat.repair_entid_collisions(false);
SELECT mentat.entid_collision_count();   -- confirm 0 before COMMIT
COMMIT;

On a store with many collisions the repair rewrites every datom of each colliding entity, so run it in a maintenance window and expect it to scale with the number of affected datoms, not with total store size.

The 1.5.6 upgrade caveat (fixed in 1.5.7)

The 1.5.5→1.5.6 migration bounded the sequences with a fixed MAXVALUE, which aborts if a sequence has already run past that ceiling (RESTART value cannot be greater than MAXVALUE). Upgrade directly to 1.5.7 or later: it ships a direct 1.5.5→1.5.7 path that bounds each sequence to GREATEST(intended_ceiling, current_head) — never below the live head — so an overflowed store keeps working. Do not stop at 1.5.6 on a store whose sequences may have overflowed.

mentatd HTTP Server

mentatd is a standalone HTTP server that provides a Datomic client-compatible API on top of pg_mentat. It connects to PostgreSQL (with pg_mentat installed), translates HTTP requests into SQL function calls, and returns results in EDN or Transit+JSON format.

Architecture

Client (Datomic SDK / HTTP) --> mentatd (Axum) --> PostgreSQL (pg_mentat extension)

mentatd is built with:

• Axum -- async HTTP framework
• Tokio -- async runtime
• deadpool-postgres -- connection pooling
• tower-http -- CORS, tracing, timeouts
• Prometheus -- metrics collection

Running mentatd

From Source

cd mentatd
cargo run -- --config config.toml

With Docker Compose

cd docker
docker compose up -d

This starts PostgreSQL with pg_mentat, mentatd, Prometheus, and Grafana.

Environment Variables

mentatd reads configuration from a TOML file. The path defaults to config.toml or can be specified with --config.

Configuration

[server]
host = "0.0.0.0"
port = 8080
api_key = "your-secret-key"  # Optional; omit for no auth

[database]
host = "localhost"
port = 5432
dbname = "postgres"
user = "postgres"
password = "secret"
pool_size = 16

[logging]
level = "info"         # trace, debug, info, warn, error
format = "json"        # json or pretty

API Endpoints

Unified Endpoint

All operations can be dispatched through the root endpoint:

POST /
Content-Type: application/edn

The request body contains the operation type and parameters in EDN format.

RESTful Aliases

mentatd also exposes Datomic-compatible route aliases:

Public Endpoints

WebSocket

Request Format

Content Types

mentatd accepts:

• application/edn -- EDN format (default)
• application/transit+json -- Transit JSON format

And returns results in the same format as the request, or as specified by the Accept header.

Query Request

{:op :query
 :query "[:find ?name :where [?e :person/name ?name]]"
 :args {}
 :db-name "default"}

HTTP example:

curl -X POST http://localhost:8080/api/query \
  -H "Content-Type: application/edn" \
  -H "Authorization: Bearer your-secret-key" \
  -d '{:op :query
       :query "[:find ?name ?age :where [?e :person/name ?name] [?e :person/age ?age]]"
       :args {}
       :db-name "default"}'

Transact Request

{:op :transact
 :tx-data "[{:db/id \"t1\" :person/name \"Alice\" :person/age 30}]"
 :db-name "default"}

HTTP example:

curl -X POST http://localhost:8080/api/transact \
  -H "Content-Type: application/edn" \
  -H "Authorization: Bearer your-secret-key" \
  -d '{:op :transact
       :tx-data "[{:db/id \"t1\" :person/name \"Alice\"}]"
       :db-name "default"}'

Pull Request

{:op :pull
 :pattern "[:person/name :person/age]"
 :eid 10001
 :db-name "default"}

List Databases

{:op :list-dbs}

Create Database

{:op :create-db
 :db-name "analytics"}

Delete Database

{:op :delete-db
 :db-name "analytics"}

Authentication

When api_key is configured in the server settings, all API endpoints (except /health and /metrics) require an Authorization header:

Authorization: Bearer your-secret-key

Requests without a valid key receive a 401 Unauthorized response.

CORS

mentatd enables CORS by default via tower-http, allowing cross-origin requests from browser-based clients. The default configuration permits all origins. Restrict this in production by configuring allowed origins in the TOML file.

Streaming Queries

The /stream/query endpoint returns results as a stream of newline-delimited JSON objects, suitable for large result sets:

curl -X POST http://localhost:8080/stream/query \
  -H "Content-Type: application/edn" \
  -d '{:op :query
       :query "[:find ?e ?name :where [?e :person/name ?name]]"
       :db-name "default"}'

Results arrive incrementally rather than buffered into a single response.

Metrics

mentatd exposes Prometheus metrics at GET /metrics:

• mentatd_requests_total -- total HTTP requests by operation and status
• mentatd_request_duration_seconds -- request latency histogram
• mentatd_active_connections -- current PostgreSQL pool connections in use
• mentatd_pool_size -- total pool capacity

Grafana Dashboard

The Docker Compose setup includes a pre-configured Grafana dashboard for mentatd monitoring.

Connection Pooling

mentatd uses deadpool-postgres for connection pooling. The pool_size configuration determines the maximum number of concurrent PostgreSQL connections. Each HTTP request acquires a connection from the pool for the duration of the operation.

Recommended pool sizing: 2-4x the number of CPU cores, or match your expected concurrent request volume.

Production Deployment

Reverse Proxy

Place mentatd behind nginx or a similar reverse proxy for TLS termination:

server {
    listen 443 ssl;
    server_name mentat.example.com;

    ssl_certificate /etc/ssl/cert.pem;
    ssl_certificate_key /etc/ssl/key.pem;

    location / {
        proxy_pass http://127.0.0.1:8080;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
    }
}

Health Checks

Use /health for load balancer health checks. It verifies PostgreSQL connectivity and returns:

• 200 OK -- healthy
• 503 Service Unavailable -- PostgreSQL connection failed

Resource Limits

Configure connection pool size and PostgreSQL timeouts appropriately:

[database]
pool_size = 32  # Match expected concurrency

[server]
request_timeout_ms = 30000  # Match mentat.query_timeout_ms

Contributing

Development Environment

Prerequisites

• Rust 1.88+ (stable)
• PostgreSQL 16 development headers (or 13-18)
• LLVM 18 / Clang (for pgrx bindgen)
• cargo-pgrx 0.17.0

Nix (Recommended)

The project includes a Nix flake that provides the complete development environment:

nix develop

This gives you Rust 1.90, PostgreSQL 16, LLVM 18, cargo-pgrx, and all required build dependencies.

Manual Setup

# Install Rust
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh
rustup default stable

# Install cargo-pgrx
cargo install cargo-pgrx --version 0.17.0

# Initialize pgrx (downloads and builds PostgreSQL)
cargo pgrx init --pg16 $(which pg_config)

Building

# Debug build (fast compilation, assertions enabled)
cargo pgrx install --features pg16

# Release build (optimized, LTO)
cargo pgrx install --release --features pg16

# Build without installing
cargo build --features pg16

Testing

Unit Tests

The project uses pgrx's test infrastructure, which spins up a temporary PostgreSQL instance:

# Run all tests
cargo pgrx test pg16

# Run tests in a specific file
cargo pgrx test pg16 -- --test query_tests

# Run a single test
cargo pgrx test pg16 -- --test query_tests::test_basic_query

Running Interactively

Start a PostgreSQL instance with the extension pre-loaded:

cargo pgrx run pg16

This drops you into a psql session where you can interactively test:

CREATE EXTENSION pg_mentat;
SELECT mentat_transact('[{:db/ident :test/attr :db/valueType :db.type/string :db/cardinality :db.cardinality/one}]');

Project Structure

pg_mentat/
  Cargo.toml              # Workspace root
  edn/                    # EDN parser (PEG grammar)
    src/lib.rs            # Parsing rules for EDN, queries, transactions
  core-traits/            # Shared type definitions
  core/                   # Core data structures
  pg_mentat/              # PostgreSQL extension (main crate)
    src/
      lib.rs              # Extension entry point, _PG_init, SQL schema DDL
      functions/          # SQL-callable functions
        query.rs          # Datalog-to-SQL compiler
        transact.rs       # Transaction processor
        pull.rs           # Pull API implementation
        schema.rs         # Schema introspection
        time_travel.rs    # As-of, since, history
        excision.rs       # Entity excision
        store_management.rs  # Multi-store management
        subscriptions.rs  # LISTEN/NOTIFY subscriptions
        stats.rs          # Performance statistics
        edn_functions.rs  # EDN helper functions
        bootstrap.rs      # Schema bootstrap
      planner/
        hooks.rs          # GUC registration, optimizer hints
      cache.rs            # Schema cache (LRU, generation-based invalidation)
      monitoring.rs       # Slow query logging, metrics
    pg_mentat.control     # Extension metadata
  mentatd/                # HTTP server
    src/
      main.rs             # Entry point
      server.rs           # Axum routes, request handling
      websocket.rs        # WebSocket support
      stream.rs           # Streaming query responses
      db_cache.rs         # Database snapshot cache
      session.rs          # Client session management

Code Style

The project enforces strict Clippy lints:

• No panics -- unwrap_used = "deny", panic = "deny", unimplemented = "deny"
• No debug output -- dbg_macro = "deny", print_stdout = "deny"
• No TODOs -- todo = "deny"
• Pedantic warnings -- pedantic = "warn" with minimal relaxations

Run lints locally:

cargo clippy --features pg16 -- -D warnings

Format code:

cargo fmt --all

Architecture Decisions

Why Narrow Tables?

Each value type gets its own table so PostgreSQL stores values in their native format. This means:

• No type tags or BYTEA serialization overhead
• Index comparisons use native operators (integer comparison for longs, text collation for strings)
• The query planner can estimate selectivity accurately
• Smaller indexes (no wasted space on a universal value column)

Why SPI?

The query compiler generates SQL and executes it through PostgreSQL's SPI (Server Programming Interface) rather than implementing a custom executor. This means:

• Queries benefit from PostgreSQL's optimizer, statistics, and parallel execution
• No need to reimplement join algorithms, aggregation, or sorting
• Index usage decisions are delegated to the planner

Why pgrx?

pgrx provides safe Rust bindings to PostgreSQL's C API. It handles:

• Memory context management (palloc/pfree)
• Error handling (longjmp safety)
• SPI lifecycle
• GUC registration
• Test infrastructure

Submitting Changes

1. Fork the repository
2. Create a feature branch from claude (the main development branch)
3. Write tests for new functionality
4. Ensure all tests pass: cargo pgrx test pg16
5. Ensure Clippy is clean: cargo clippy --features pg16 -- -D warnings
6. Submit a pull request with a clear description

License

pg_mentat is licensed under Apache-2.0. By contributing, you agree that your contributions will be licensed under the same terms.