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.

A transaction locks each cardinality-one value and :db/unique value it writes, so concurrent writers can’t break those rules (see “Transaction isolation” in crates/pg/pg_mentat/sql/README.md). Past max_locks_per_transaction such values (64 by default) it locks those attributes whole instead, so a big backfill makes other writers of them wait until it commits.

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 (the values entities have now, from the current-state projection) and the dead-tuple % of each backing log 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 primary-key lookup on the attribute’s mentat.current_<type> projection table, so a replaced or retracted value is never returned. For a cardinality-many attribute it returns the value asserted last. It dispatches on the attribute’s declared value type so only one 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.current_text
    WHERE store_id = 0 AND a = mentat.attr_id(':person/canonical-email')
) 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

Function Returns
mentat.attr_id(':ns/n') The attribute’s entid (BIGINT), or NULL.
mentat.current(e, a) Current value of attribute for entity, as TEXT.
mentat.attribute_health() Per-attribute live datom count + backing-table dead %.
mentat.storage() Per-table size + row estimates (pre-existing).
mentat.stats() Query-execution statistics (pre-existing).

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. The shipped set is EAVT / AEVT / VAET / tx covering indexes on the history tables, AEV + AVET (1.10.0) on the current-state projection, and an FTS GIN index – so equality lookups, ref traversal and current-state value ranges are already index probes. The costs that actually hurt in production are:

  • per-t() transaction overhead – solved by batching, not indexing;
  • history-resolution on reads – solved by the current-state projection (current-time queries read it directly);
  • dead-tuple bloat – solved by autovacuum tuning + scheduled vacuum, not by indexing.

The one gap the automatic index manager (section 7) fills is value ranges on one attribute in temporal queries. Beyond that, extra indexes 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:

partition band source sequence
db.part/db [0, 1e6) partition_db_seq (schema / bootstrap)
db.part/user [1000001, 1e12) partition_user_seq (data entities)
db.part/tx [1e12, 2e12) partition_tx_seq (transactions)

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. A transaction id counts only if partition_user_seq or partition_db_seq could have issued it (it is at most one of their last_values): a transaction’s own annotations ("datomic.tx", 1.19.0) are datoms on its id and are not collisions. On a store whose user sequence overflowed past a transaction id, the data can’t tell an annotation from a collision, and both are reported; check the rows before repairing.

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.

Idents moved by 1.18.0 and earlier (#415)

Before 1.19.0, re-asserting an existing ident in a map without :db/id ([{:db/ident :color/red :db/doc "red"}], or an idempotent schema install that lists enum idents) pointed the store’s idents row at a new, empty entity. Values written afterwards went to that entity, values written before stayed on the ident’s own entity, so an enum’s data split in two ([?c :db/ident :color/red] [?e :c/color ?c] found only the older ones), and a redefined transaction function stopped being one. 1.19.0 no longer does this, but it does not change existing data on upgrade. The 1.19.0 upgrade WARNs if the default store has such idents; check every store:

SELECT * FROM mentat.moved_idents('default');   -- no rows == healthy

Each row is one ident: owner is the entity with its :db/ident datom (for a bootstrap ident such as :db/doc, its fixed entid: re-asserting {:db/ident :db/doc :db/doc "..."} moved it too), moved_to the entity the idents row names, n_datoms / n_refs what was written to moved_to since, and blocker why the repair can’t merge the two (NULL if it can).

mentat.repair_moved_idents(store TEXT DEFAULT 'default', dry_run BOOLEAN DEFAULT true) points each ident with no blocker back at its owner, and moves what was written to moved_to onto the owner in the log: datoms whose entity is moved_to, and ref values that point at it. Transaction ids stay as they are. A datom written through both entities becomes one datom with one history, and the latest row wins as for any datom: a ref asserted to the owner and later retracted through moved_to is retracted. Rows that only repeat the state the datom already had are dropped, which changes no as-of answer: re-loading {:c/name "car" :c/color :color/red} after the move retracted the ref to the owner and asserted one to moved_to in one transaction, and after the repair car’s color is the owner, asserted when it was first written. The repair then recomputes the current state of the merged datoms from the log, so current queries and as-of the latest transaction agree, and checks it. An ident whose repair fails that check, or fails at all, is rolled back on its own and reported as failed: <error>.

It skips (and reports) an ident when both entities have a different value of one cardinality-one attribute, when moved_to was used as an attribute, or when a :db.unique ref attribute has the owner as its value on one entity and moved_to on another (both would hold one unique value); those need a decision about which value to keep. Fix them by hand (retract one value), then run the repair again. Values through each entity that overlapped only in the past are not a blocker (history can’t be changed by hand); as of that time the merged entity shows both.

Each row’s notes (NULL if none, one line each) says what the merge changes besides moving moved_to’s datoms onto the owner, in a dry run as in a real run:

  • past overlap: from tx T1 until tx T2, :color/red has two values of :c/label: "a" and "b": a cardinality-one attribute had one value through each entity at once. Queries as of those transactions show both (a pull shows one of them).
  • past overlap: from tx T1 until tx T2, entities E1 and E2 both hold :c/uv :color/red: the same for a :db.unique ref attribute.
  • [E :c/tags :color/red] is live now and retracted after the repair (a later retraction wins): the value was asserted to the owner, then asserted and retracted through moved_to; the latest row wins.

A real run must run under READ COMMITTED (it refuses REPEATABLE READ and SERIALIZABLE: its snapshot would predate the writers it waits for, and a ref one of them wrote to moved_to would stay there with nothing listing it). It blocks every transaction on the store (t() / edn_t, mentat_excise, rename_store) from when it starts until the transaction it runs in ends, and first waits for the ones in flight, including prepared transactions (check pg_prepared_xacts first). A writer that arrives meanwhile waits before it resolves any ident, then reloads the idents and writes to the owner, not to moved_to, under READ COMMITTED, the default. A REPEATABLE READ or SERIALIZABLE writer keeps the idents of its first snapshot, even when t() is its only statement (default_transaction_isolation), and its ref to moved_to is then invisible to moved_idents, so stop those writers first. Reads of the store, and other stores, don’t wait. Until COMMIT a real run holds:

  • the store’s write key, an advisory lock (pg_locks: locktype = 'advisory', classid = 1299084336 (0x4D6E7430, “Mnt0”), objsubid = 2), exclusive. Every t() call on the store takes it shared first, so writers of the store wait;
  • the store’s row in mentat.cache_generation (it bumps the generation, so every backend reloads its idents), which a schema-changing transaction of the store also locks;
  • row locks on every log and current row it rewrote or deleted, and ROW EXCLUSIVE on the store’s tables, which blocks no reader and no writer of other rows.

The repair and the dry run scan the store’s ref log a few times per ident. On 200,000 ref datoms with 3 moved idents (91,666 refs to their moved_to): moved_idents 0.19 s, dry run 1.3 s, real run 17 s, a second run 2 ms. Writers of the store wait for all of it, so on a large store run it in a maintenance window.

SELECT * FROM mentat.repair_moved_idents('default');           -- dry run
-- back up, then (writers to the store wait until COMMIT):
BEGIN;
SELECT * FROM mentat.repair_moved_idents('default', false);
SELECT * FROM mentat.moved_idents('default');   -- only skipped ones left
COMMIT;

An ident re-asserted more than once moved each time; only the last move is visible, so refs written to an earlier moved_to are not found or repaired.

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.

7. Automatic index management

mentat_tune_indexes(dry_run BOOLEAN DEFAULT true) adds indexes where query evidence says they help and drops the ones it added once they stop being used. It is small on purpose: one kind of index, two rules, one registry.

SELECT * FROM mentat_tune_indexes();        -- what would change (default: dry run)
SELECT * FROM mentat_tune_indexes(false);   -- do it
SELECT * FROM mentat.managed_indexes;       -- what it owns, and why

Each row returned is (action, index_name, table_name, reason) with action one of create, drop, forget (a managed index someone dropped by hand), or skip (the skip rule below; an amortized run that hits a lock timeout is LOGged as skipped). Every create / drop is also LOGged with its reason. dry_run changes no index and no registry row.

Evidence. Every compiled query with a range predicate (<, <=, >, >=) on the value of a constant-attribute pattern that reads a history table (as-of / since / history queries) counts one hit for (store, attribute, table). Hits are per backend and flushed to mentat.index_evidence every mentat.auto_index_every_n_tx edn_t calls and by mentat_tune_indexes() itself.

Create rule. An attribute with at least mentat.auto_index_min_queries hits and at least mentat.auto_index_min_rows rows in its history table gets

CREATE INDEX mentat_auto_datoms_<type>_new_a<entid>
  ON mentat.datoms_<type>_new (store_id, v, e, tx) WHERE a = <entid> AND added;

Why only this: current-time queries read the projection, whose AVET index (store_id, a, v, e) already turns a range on one attribute into an index range scan (a 1% range of a 1.3M-value attribute at 10M datoms: 1.5 ms; a per-attribute partial index measured 1.1 ms – not worth an index per attribute). The history tables' VAET index leads with v, not a, so for a wide range the planner scans all of that attribute’s values through AEVT with a Filter. The index scan itself gets ~5x cheaper (4.9 ms vs 23.6 ms for a 25% range of 200k values), but an as-of query also resolves supersession with an anti-join, so end to end the gain is modest: that query went 162 -> 141 ms through edn_q (-13%), and a narrow (0.5%) range 2.8 -> 2.6 ms. Expect a real but small win on large, hot temporal range queries – not an order of magnitude.

Skip rule. An attribute that is at least half of its history table (from pg_stats on a) is reported as skip: the VAET index is then already effectively per-attribute (measured: :issue/priority at 100% of datoms_long_new, 10M datoms: 655 -> 606 ms for a 62 MB index). The predicate carries only literals (store_id is a bound parameter in the generated SQL, so it is a key column), and the generated SQL pushes the attribute down as a literal a = <entid>, so the planner can match it. Caveat: edn_q caches prepared statements, and after five runs PostgreSQL may switch to a generic plan, which assumes v >= $n matches a third of the rows and can prefer AEVT’s e order for the as-of anti-join. If the index is then never scanned, the vestigial rule removes it.

Vestigial rule. A managed index whose idx_scan has not grown since it was last seen in use, for at least mentat.auto_index_idle_window (default 7 days), while its table took writes, is dropped. Hysteresis: nothing is dropped within one window of its creation, and any scan restarts the clock. A read-only table never loses its indexes (an idle index there costs nothing).

Only its own. The drop rule reads only mentat.managed_indexes, whose CHECK pins names to mentat_auto_*: primary keys, the shipped indexes, M1’s AVET indexes and user indexes are never candidates.

Modes (mentat.auto_index, see configuration): schema (default) collects evidence and acts only when you call mentat_tune_indexes; adaptive also runs it every mentat.auto_index_every_n_tx transactions of a backend, with mentat.auto_index_lock_timeout (default 100 ms) on the CREATE INDEX lock: if it would wait longer the run is skipped and LOGged (mentat auto_index: tuning skipped (55P03 ...)) and retried at the next tick (the evidence is kept), and the triggering edn_t succeeds regardless. Note that the adaptive build is a plain CREATE INDEX inside the caller’s transaction: it blocks writes to that table for the build (seconds per million rows). On a large store, prefer schema mode and run mentat_tune_indexes(false) in a maintenance window, or create the reported index yourself CONCURRENTLY under the reported name and insert its registry row.

8. Scaling notes: large results and big stores

Measured on a 32-vCPU box, PostgreSQL 16.15, current-time queries (benchmarks/scale, 1M and 10M datoms; see benchmarks/results/pg-autoindex-* for the full tables).

  • Point lookups and ref traversal are flat in store size. A value-bound pattern ([?e :user/email ?x]) is an AVET index probe on the current-state projection: ~0.19 ms at 1M and at 10M datoms.
  • Aggregates scale with the attribute, not the store. (count ?x) whose tuple is a key of the join (a single cardinality-one pattern, or a chain of them) runs as COUNT(*) with parallel workers (10M datoms, 1.3M values: ~55 ms). When the tuple is not a key (a cardinality-many join, a non-unique value) it is de-duplicated first with SELECT DISTINCT + HashAggregate – correct, but proportional to the join’s size.
  • Results are bounded by mentat.max_result_rows (100k). edn_q builds its answer as one JSONB value in memory (PostgreSQL caps a value at 1 GB). For large results use edn_q_rows, which returns one row per result and can be consumed by COPY (SELECT ...) TO STDOUT or a client-side cursor, together with SET LOCAL mentat.max_result_rows = 0 – or paginate with {"limit": N, "offset": M} inputs (offsets are O(offset); prefer a range predicate on a key for deep pages).
  • Spills. Large DISTINCT / sort steps spill to disk under work_mem; mentat.temp_file_limit (1 GB) bounds that per query and the error names it. Raising work_mem for the session (or mentat.default_work_mem with optimizer hints on) keeps them in memory.
  • Temporal range queries (as-of / since / history with </> on a value) are the one access path the shipped indexes do not cover per attribute; the automatic index manager (section 7) adds those indexes from observed use.

9. Upgrading to 1.19.0

ALTER EXTENSION pg_mentat UPDATE TO '1.19.0' builds indexes, and it holds the locks they take until it commits:

  • Each pg_fts index mentat.create_fts_index made (mentat_fts_<entid>) is dropped from mentat.datoms_text_new and built again on mentat.current_text. That is an ACCESS EXCLUSIVE lock on mentat.datoms_text_new and a SHARE lock on mentat.current_text.
  • The full-text GIN index idx_current_text_fts is built on mentat.current_text and on each named store’s current_text: a SHARE lock on each.
  • In each named store, the trigram indexes create_store made with pg_trgm move from datoms_text_new / datoms_keyword_new to current_text / current_keyword: an ACCESS EXCLUSIVE lock on the log table and a SHARE lock on the projection.

Two more steps take ACCESS EXCLUSIVE locks, held until the upgrade commits:

  • Each named store’s views (facts, text_values, …) are replaced, so a query on one of them waits from the moment its store is done until commit. Its functions (find_text, entity_value, …) and its tables are not locked.
  • The last step gives the nine mentat.current_<type> tables their Row-Level Security policy. Coming from 1.18 it creates the policies, which locks those tables: from there to commit, which is short, every read and write of the default store waits. A table already in the wanted state is left alone, so running the step again takes no lock.

While it runs, every write of a string or keyword value to an affected store waits (on the default store, so does every read of mentat.datoms_text_new); other writes, and reads of the tables, go on. Measured on an assert-enabled build: a pg_fts index over 100,000 values took 26 s to rebuild, the trigram move on 383,000 values 0.9 s. On a big store, plan a maintenance window, or set lock_timeout in the session: the upgrade then fails and changes nothing instead of waiting.

The upgrade also deletes what mentat_excise left in the current-state tables on 1.5.0 to 1.18.0, for the entities mentat.excision_log lists. It runs as the role that runs ALTER EXTENSION, usually a superuser, in tables a store’s owner owns, so each of its statements runs as the tables' owner, in a security-restricted operation with a pinned search_path, the way VACUUM and REFRESH MATERIALIZED VIEW run as a table’s owner: a trigger or index function the owner put on those tables runs with the owner’s privileges. As a second check, a store is skipped, with a WARNING, if its schema and tables have more than one owner, or if any of its tables is a partition or in an inheritance tree, has a DELETE trigger, a rule, a foreign key pointing at it, a column type of its own, or an index, CHECK constraint or statistics object that uses a non-superuser’s function, operator or type. For a skipped store, run mentat_excise again on the ids mentat.excision_log lists after the upgrade.