Postgres Backend (v2)¶
Popoto keeps one model API with two native storage backends behind it (#759). Redis keeps today's hashes, index sets and Lua. Postgres stores each model in a typed table with native indexes, and it is where new capabilities land.
New to it? Start with Use Postgres, which goes from an empty database to a working memory loop. This page is the reference; server requirements (PostgreSQL 18+, UTF8, pgvector) are listed under Known limitations.
Postgres support ships in popoto 1.10.0. It is opt-in: a model uses Postgres only when you select it (below), and every model that does not keeps running on Redis exactly as before. Redis behaviour is unchanged unless noted.
On Postgres you get the same model API: records, queries, Q objects,
ordering, counting and atomic increments for every field type listed under
Supported fields, including
indexed, unique, tag, relationship, collection, date/time, bytes and geo
fields. The agent-memory layer runs there too: decay ranking, confidence
(partitioned too), read tracking, the write filter, ObservationProtocol and
composite_score, BM25 keyword search, pgvector embeddings, exact membership
filters, fusion and recall(), ContextAssembler over all of them,
ValidityField with SupersessionProtocol, CoOccurrenceField with its
graph expansion, the recipes and mixins on top, CyclicDecayField,
TDValueField and the prediction ledger. So do the operational pieces:
record expiry (Meta.ttl), transaction() and popoto.batch(), async
methods, event streams and pub/sub, and index maintenance and transfer.
The one field Postgres does not store is DataFrameField. Popoto refuses a
model that uses it when you declare it, so it never fails halfway through.
That refusal and the other places where Postgres behaves differently from
Redis are collected under Known limitations.
Selecting the backend¶
A backend is chosen per model, or once for the whole process:
import popoto
class Note(popoto.Model):
owner = popoto.KeyField()
slug = popoto.KeyField()
hits = popoto.IntField(default=0)
score = popoto.SortedField(type=float, partition_by="owner")
class Meta:
backend = "postgres" # this model only
export POPOTO_BACKEND=postgres # process default
export POPOTO_POSTGRES_URL=postgresql://db.internal:5432/agents # the DSN
export POPOTO_POSTGRES_SCHEMA=popoto # optional; default "popoto"
export POPOTO_POSTGRES_MAINTENANCE_URL=postgresql://db.internal:5432/agents # optional; see below
Which variable each component reads:
| Component | Reads | Never reads |
|---|---|---|
the library (popoto) |
POPOTO_BACKEND, POPOTO_POSTGRES_URL, POPOTO_POSTGRES_SCHEMA, POPOTO_SCHEMA_AUTO, and optionally POPOTO_POSTGRES_MAINTENANCE_URL (maintenance DSN), POPOTO_POSTGRES_GRANT_MAIN_ROLE (two roles) and POPOTO_POSTGRES_LISTEN_URL |
POSTGRES_URL, DATABASE_URL |
| the pytest conformance harness | POSTGRES_URL, for its throwaway popoto_test_<hex> schema (see Testing) |
— |
Popoto never picks Postgres up from a generic variable. Selecting Postgres
without POPOTO_POSTGRES_URL, or without the postgres extra installed,
raises BackendUnavailableError naming what is missing. You can also pass
the DSN directly:
popoto.backends.set_backend(PostgresBackend(dsn=..., schema=...)), plus
maintenance_dsn=... for the optional
maintenance DSN and
grant_main_role=True to opt in to per-object
grants.
The instance rule. That instance serves every model without
Meta.backend and every model with Meta.backend = "postgres": since #816
a set_backend instance is the backend its own name resolves to, so a
Postgres-pinned model, resolve_stream_backend(backend="postgres") and a
publish joining a Postgres transaction all use it, and POPOTO_POSTGRES_URL
is not consulted for them. A model pinned to a
different name still resolves by name, and the rule does not apply to
Redis: Meta.backend = "redis" always resolves to the stock Redis backend,
even under a Redis-named set_backend instance, exactly as before.
Laziness. import popoto never imports psycopg. Defining a
Meta.backend = "postgres" model never touches the network either: class
creation only checks the model's fields against a static capability table.
The connection, the version check, the DDL and the schema check all run inside
the model's first query or save. bind() itself only compiles the table
spec, so an outage at that first call is charged to the call that hit it: a
first save against an unreachable server counts as a dropped write, and a
first query does not.
Meta.backend is not inherited. Like every popoto Meta option, it is
read from the class's own Meta: a subclass that declares no Meta of its
own takes the process default, not its parent's backend. Repeat
backend = "postgres" on each concrete model that needs it.
Known limitations¶
This section collects what Postgres does not do, or does differently, in 1.10.0. Each item links to the section with the details.
Server requirements. Popoto checks these on a model's first query or
save, not at import or class creation, and raises BackendCapabilityError
when one fails:
- PostgreSQL 18 or newer (
server_version_num >= 180000). server_encodingUTF8. Ties are ordered bytewise, as on Redis.- pgvector, for
EmbeddingFieldonly. A model with anEmbeddingFieldneeds thevectorextension installed in its database, in a schema on the connection'ssearch_path(the default,public, is). Popoto never runsCREATE EXTENSION; an operator with that privilege does. Models without anEmbeddingFielddo not need pgvector. PostGIS is not used: geo fields run on plain columns (see Geo).
Refused when you declare the model. With Meta.backend = "postgres",
class creation raises BackendCapabilityError (with the process default,
the model's first use does) for:
DataFrameField. Store the frame's JSON in aDictFieldor aBytesFieldinstead.- An
IndexedFieldorUniqueFieldwhose type is notint,float,str,bool,Decimal,datetime,dateortime(a collection type, for example), and aSortedFieldwhose type is not numeric or a date/time. - A custom field that overrides a storage hook, and any field kind the backend does not know.
Refused at the call. These raise BackendCapabilityError on a Postgres
model, before anything is written:
save(migrate_key=True)that changes a key field. Create the new record and delete the old one. For the same reason,MemoryLifecyclewith aKeyFieldtier is refused when you build it; declare the tier as anIndexedField._ttl/_expire_aton a model withoutMeta.ttl(Record expiry).order_byon a collection field (divergence (xvii) below).- A
GeoFieldradius filter used to scope a ranking or a search (recall,top_by_decay, and the like). It scopesfilter()andcount(). rebuild_indexes()while atransaction()orpopoto.batch()is open in the same thread or task (Index maintenance).- A write that would wait on a record lock that an enclosing transaction in
the same thread or task holds, which could never be granted
(
popoto.batch()). - One
popoto.batch()that writes to both Redis and Postgres, or to two Postgres backends. raw_update()naming a field that has no stored column.auditable_extractionwhen the model, the decision log and the journal would land in different stores. Under a Redis default, bind the default withset_backend("postgres")orPOPOTO_BACKEND=postgres, or remove the one pin; under a Postgres default, remove the journal entry model'sMeta.backendpin. A Redis default with a Postgres-pinned journal is refused too.
Redis-only APIs. These inspect Redis structures and raise
BackendCapabilityError on a Postgres model: load_raw_hash,
Query.keys(clean=…) / Query.keys(catchall=…),
DecayingSortedField.rank_decayed(zset_key, …) and
CyclicDecayField.rank_decayed(zset_key, …) (use Model.query.top_by_decay(),
which works on both), and ConfidenceField.get_confidence_filtered /
ConfidenceField.migrate_to_partitioned.
Postgres-only APIs. Model.query.recall() and
Model.query.top_by_relevance() raise BackendCapabilityError on Redis. On
Redis, use keyword_search / semantic_search with fuse(), and
top_by_decay().
Not stored on Postgres. The WriteFilterMixin priority tier is a no-op:
the gate still runs, but no priority is kept, so
composite_score({"priority": …}) raises BackendCapabilityError. The
confirmed access log (a list of read timestamps) is not kept either; the
access counters are.
Results that differ from Redis. Redis behaviour is unchanged in 1.10.0, so the two backends disagree in places. They fall into a few groups, each row pinned by a test on both backends (see Documented divergences):
- Queries where Redis's answer is wrong. Composed
Qobjects that drop an unindexed leaf, ignored lookups and bounds, unimplementedKeyFieldlookups, and similar. Postgres returns the correct rows. The Redis bugs are tracked in #771 (open); until it is fixed, code that relied on the Redis result will see different rows on Postgres. - Typed comparison. Postgres compares numbers, decimals and instants by value where Redis compares key strings.
- Atomicity. A failed statement rolls back the whole
transaction()or batch, where RedisMULTI/EXECapplies the other commands. - Ordering and edge values. With no ordering requested, Postgres returns
rows in bytewise
_pkorder; NaN,\x00, out-of-range integers and awaretimevalues are refused or normalised rather than stored as Redis stores them. - Exact structures.
ExistenceFilterandFrequencySketchare exact on Postgres (no false positives, no over-counting).
Not carried by the Redis to Postgres migration. The tombstone archive
($TOMB:<Model>:data and :index, the deleted-record payloads
MemoryLifecycle.restore() reads) has no carry path, so the tool stops the run
before writing anything (exit code 3, #829); clear the archive first or accept
losing the restore window. See the
migration guide.
Harness session state does not migrate. The harness integration runs on Postgres with no Redis commands (see its On Postgres section). Its between-turn bookkeeping (pending captures, injected-key sets, counters) lives in its own tables, and the Redis to Postgres migration does not copy it: after a switch, those counters start at zero and the first turn of each session may inject a memory the Redis side had already injected.
Supported fields (M1, M1.1, M2a, M2b, M3, M4, M5)¶
| popoto field | Column | Index |
|---|---|---|
KeyField / UniqueKeyField / AutoKeyField (type=T) |
T (default text) |
all key fields under one UNIQUE; a B-tree for each non-leading key field; UniqueKeyField also gets its own UNIQUE |
SortedField(type=T, partition_by=…) / SortedKeyField |
T: bigint, double precision, numeric, date, time or timestamptz |
B-tree (partition cols…, f, _pk COLLATE "C") |
IntField / FloatField / DecimalField / BooleanField |
bigint / double precision / numeric / boolean |
— |
StringField |
text |
— |
DatetimeField |
timestamptz plus <f>__utcoff integer (offset in seconds; NULL = naive) |
— |
Field(type=int/float/str/bool/Decimal/datetime) |
as above | — |
IndexedField(type=T) / UniqueField(type=T) |
T (a scalar type) |
B-tree on the column; UNIQUE for a unique field |
TagField |
text[], normalised (sorted, unique, str(tag); untagged is {}) |
GIN: __contains and __all are @>, __any is && |
Relationship(model=M) |
text holding the target's _pk |
B-tree (no foreign key: Redis enforces none, and references may be circular) |
ListField / DictField / SetField / TupleField, Field(type=list/dict/set/tuple) |
jsonb |
— |
ListField(max_length=N) |
jsonb, each element type-tagged as push() writes it on Redis |
— |
BytesField / DateField / TimeField |
bytea / date / time |
— |
Meta.indexes |
— | a composite B-tree per entry; UNIQUE when is_unique |
DecayingSortedField(partition_by=…, base_score_field=…) |
double precision: the decay clock, in epoch seconds |
B-tree (partition cols…, f, _pk COLLATE "C") |
ConfidenceField (partition_by= too) |
double precision for the attribute, plus the state <f>__conf (double precision), <f>__n, <f>__corr, <f>__contra (bigint) |
— |
BM25Field(source=…) |
no column of its own: postings <table>__<f>__post (scope, term, _pk, tf) and lengths <table>__<f>__dl (_pk, scope, len) |
postings PRIMARY KEY (scope, term, _pk) plus a B-tree on _pk; lengths (scope) INCLUDE (len) |
EmbeddingField(source=…) |
<f> bigint (the dimension count Redis stores), <f>__vec vector(d), <f>__model text, <f>__hash text, plus the narrow vector table <table>__<f>__vec (_pk, scope, v vector(d)), v stored PLAIN |
HNSW vector_cosine_ops on <f>__vec; a partial B-tree on rows with no vector, and a B-tree on <f>__model (the backfill's probe); (scope) on the narrow table |
ExistenceFilter / FrequencySketch |
no column: <table>__<f>__tok (token, _pk) / <table>__<f>__cnt (token, count) |
PRIMARY KEY (token, _pk) / PRIMARY KEY (token) |
ContentField |
text holding the content itself (no $CF: reference, no file) |
— |
ValidityField |
double precision for the declared value, plus <f>__valid_from, <f>__invalid_at, <f>__ingested_at (double precision; 'Infinity' = open) and <f>__supersedes, <f>__superseded_by (text); companion <table>__<f>__open (digest, member) |
B-tree on <f>__valid_from and on <f>__invalid_at; the companion's member references _pk ON DELETE CASCADE |
CoOccurrenceField(symmetric=…, max_edges=…) |
no column: the edge table <table>__<f>__edge (src, dst, weight) |
PRIMARY KEY (src, dst) |
CyclicDecayField(cycles=…, pressure_rate=…) |
double precision: the decay clock, as for DecayingSortedField; the cycles as four parallel double precision[] columns <f>__cycle_period, <f>__cycle_amp, <f>__cycle_phase, <f>__cycle_base (the #698 declared baseline, a NULL element = unknown); the pressure as <f>__pressure_rate and <f>__pressure_at (last_resolved). NULL = no companion-hash entry |
B-tree (partition cols…, f, _pk COLLATE "C") |
TDValueField |
numeric, as DecimalField |
— |
GeoField |
jsonb for the coordinates as given, plus <f>__geohash bigint (the score GEOADD stores; NULL = not in the geo index) and <f>__geolon / <f>__geolat (double precision, the position decoded from that score) |
partial B-tree on <f>__geohash (no PostGIS, no GiST) |
PredictionLedgerMixin adds no column: its ledger is two engine tables
(see Long-tail fields). DataFrameField is refused
at declaration with its reason: a pandas DataFrame is not stored on Postgres
in 1.10.0 (the field needs the optional dataframe extra, which no CI job
installs, so a column for it would ship untested); store the frame's JSON in a
DictField or a BytesField.
Every table also has:
_pk text PRIMARY KEY, which holds the sameClassName:key:…string that Redis uses as the hash key.Model.pkandredis_keyare therefore identical on both backends._created_atand_updated_at(timestamptz), which the engine maintains. They are Postgres-only._migrated_from jsonband_estimated_fields text[], the import contract for the one-off Redis-to-Postgres copy (#756). An importer records where a row came from and which values it inferred. Every native write (save,atomic_increment,push, and the embedding backfill) sets_migrated_fromtoNULL, so a re-load can guard withWHERE _migrated_from IS NOT NULLand never overwrite a row popoto has written since. The only engine write to_estimated_fieldsis the backfill's: it removes the embedding field's name, because the vector it writes is derived natively rather than imported.
DatetimeField round-trips the way it does on Redis (#521). An aware value
comes back with the same UTC offset as a fixed-offset timezone. A naive
value comes back naive. A naive value is stored and compared as UTC, which is
the same instant SortedField scores it as on Redis.
Collections are JSON, never msgpack. A collection field comes back
exactly as it comes back from Redis: the field's own type is restored at the
top level (a TupleField is a tuple, a SetField a set), nested values
behave as msgpack does (a nested tuple comes back a list; a tagged
Decimal/date/tuple element of a capped list comes back typed), and a
value msgpack cannot pack (a nested set or Decimal) raises TypeError on
both backends. What JSON lacks and msgpack has is tagged so it survives:
bytes, a non-finite float, and a dict with non-str keys.
Relationships stay lazy. The column holds the related record's key
string. filter()/all() return it as that string; get()/get_many()
resolve it to the related instance, exactly as the Redis read paths do.
Relationship.sample_related_keys is an id-only SELECT … WHERE f = $key
ORDER BY random() LIMIT n, with SRANDMEMBER's count contract (negative
counts repeat).
Uniqueness. A UniqueField, UniqueKeyField or unique Meta.indexes
tuple is checked in pre_save by a read through the backend, with the same
ModelException text as Redis and before any write. The UNIQUE index is the
authority behind it: a write that races past the read gets the same text from
the index (SQLSTATE 23505, mapped to the same ModelException).
When the save is handed a unit of work that already holds a connection (a
transaction(), or a popoto.batch() with a Postgres write in it), the read
runs on that unit's connection (#776). It sees the unit's own writes: a
holder the unit deleted no longer conflicts, and a value the unit already
claimed is refused at the call, before anything is sent, leaving the unit
healthy. A concurrent unit's uncommitted claim is invisible to any read
under READ COMMITTED, so there the index decides: the later save waits on
the earlier unit's index entry and, once that commits, raises the
ModelException. Of N concurrent units claiming one value, exactly one
commits and the others raise ModelException
(tests/postgres/test_postgres_unique_race.py, sync threads and async tasks,
transaction() and popoto.batch()). Before #776 the read always took a
second pooled connection: with PG_POOL_MAX_SIZE units open it could never
be served, and every contender failed with BackendBusyError.
Two units that claim the same values in opposite orders can deadlock on
those index entries: Postgres breaks it and one unit raises
BackendRetryableError, as for any cross-record deadlock (see "One lock
order for every writer" under Search). Measured with 4 threads × 40 transactions, each claiming two
values shared by every thread, half in each order (PostgreSQL 18.6,
localhost): 80-90 of 160 were deadlock victims at pool size 4 or 16, against
95 on main at pool size 16 (at pool size 4, main failed 136 with
BackendBusyError first) -- no new deadlock. Claim shared values in one
order (sorted) to avoid them. No advisory lock is taken on the value: a
unit's later statement cannot be ordered before its earlier ones, so a value
lock would deadlock in the same shapes and remove none.
Memory state. A ConfidenceField keeps two things, as it does on
Redis: the model attribute (the hash value there, its own column here) and
the evidence state (the companion hash entry there, the four __ columns
here). State that is NULL is the seed Redis writes on save with HSETNX
(initial_confidence and three zeros), so a save never writes it and a
re-save never resets it. A model with AccessTrackerMixin gains
_access_count, _last_accessed, _staged_reads and _staged_at; staged
reads count only while _staged_at is within _staged_ttl_seconds, which is
what the Redis list's refreshed EXPIRE means. WriteFilterMixin's gate runs
above the seam as before; its priority tier is not stored.
RecallProposal keeps its pending proposals in one engine table per schema,
popoto_recall_proposal, created on first use like popoto_schema. No state
column is indexed: each index would make every update_confidence or
confirm_access a non-HOT update, and nothing filters or orders by one alone.
A partitioned ConfidenceField needs nothing more: its state is the
record's own columns, so its partition is the row's partition columns, and a
partition change that keeps the key keeps the state.
PredictionLedgerMixin keeps its ledger in two engine tables (see
Long-tail fields). EventStreamMixin writes its
stream to the backend's events tables
(Event streams and pub/sub), in the
save's own transaction.
Meta.ttl adds an engine column, _expires_at; see
Record expiry.
GeoField is stored without PostGIS; see Geo.
An IndexedField on a collection type is refused, as is a custom field that
overrides a storage hook: on_save, on_delete, filter_query,
format_value_pre_save or pre_save_validate. A subclass of a built-in field
that overrides none of the five is accepted and stored as its base field. A
model that uses one of them raises
BackendCapabilityError when you declare it with Meta.backend =
"postgres", or on first use when it takes the process default.
What the schema looks like¶
The model above compiles to:
CREATE TABLE "popoto"."note" (
"_pk" text PRIMARY KEY,
"hits" bigint, "owner" text, "score" double precision, "slug" text,
"_created_at" timestamptz NOT NULL DEFAULT now(),
"_updated_at" timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX "note__keys__idx" ON "popoto"."note" ("owner", "slug");
CREATE INDEX "note__slug__idx" ON "popoto"."note" ("slug");
CREATE INDEX "note__sort__score__idx" ON "popoto"."note" ("owner", "score", "_pk" COLLATE "C");
Migrations. Popoto records each table's fingerprint in
popoto.popoto_schema, along with the DDL, the popoto version that wrote it
and a schema-format version. The first use in each process creates a missing
table or applies an additive change (a new nullable column, a new index)
under pg_advisory_xact_lock: the schema's lock first, then the table's.
Every first use takes the schema lock before CREATE SCHEMA, a model table's
and an engine table's alike, so any number of processes can start on a schema
that does not exist yet without racing each other into pg_namespace
(test_engine_table_first_use_is_race_free_on_a_fresh_schema). No manual
step is needed. Anything else raises
SchemaDriftError with the diff instead of writing: a dropped or retyped
column, a key change, a schema written by a newer popoto, or a table popoto
did not create. Set POPOTO_SCHEMA_AUTO=0 to turn off even the automatic
create and additive changes.
Search (M2b)¶
A model's search fields keep their state in the same database as its
records, written by the record's own INSERT … ON CONFLICT statement as
data-modifying CTEs: one round trip, one transaction. A delete cascades to
the postings and the token rows. Nothing touches Redis or the filesystem.
class Memory(popoto.Model):
memory_id = popoto.AutoKeyField()
project_key = popoto.Field(type=str)
relevance = popoto.SortedField(type=float, partition_by="project_key")
content = popoto.StringField(default="")
lexical = BM25Field(source="content")
embedding = EmbeddingField(source="content") # provider: 1536 dimensions
bloom = ExistenceFilter(fingerprint_fn=lambda m: m.content)
seen = FrequencySketch(fingerprint_fn=lambda m: m.content)
class Meta:
backend = "postgres"
compiles to (beside the record table's own columns and indexes):
"embedding__vec" vector(1536), "embedding__model" text, "embedding__hash" text
CREATE INDEX "memory__hnsw__embedding__idx" ON "popoto"."memory"
USING hnsw ("embedding__vec" vector_cosine_ops);
CREATE TABLE "popoto"."memory__lexical__post" (
"scope" text NOT NULL, "term" text NOT NULL,
"_pk" text NOT NULL REFERENCES "popoto"."memory" ("_pk") ON DELETE CASCADE,
"tf" integer NOT NULL, PRIMARY KEY ("scope", "term", "_pk"));
CREATE TABLE "popoto"."memory__lexical__dl" (
"_pk" text PRIMARY KEY REFERENCES "popoto"."memory" ("_pk") ON DELETE CASCADE,
"scope" text NOT NULL, "len" integer NOT NULL);
CREATE TABLE "popoto"."memory__bloom__tok" (
"token" text NOT NULL,
"_pk" text NOT NULL REFERENCES "popoto"."memory" ("_pk") ON DELETE CASCADE,
PRIMARY KEY ("token", "_pk"));
CREATE TABLE "popoto"."memory__seen__cnt" (
"token" text PRIMARY KEY, "count" bigint NOT NULL);
CREATE TABLE "popoto"."memory__embedding__vec" (
"_pk" text PRIMARY KEY REFERENCES "popoto"."memory" ("_pk") ON DELETE CASCADE,
"scope" text NOT NULL, "v" vector(1536) NOT NULL);
ALTER TABLE "popoto"."memory__embedding__vec" ALTER COLUMN "v" SET STORAGE PLAIN;
tests/postgres/test_postgres_search.py::test_compiled_search_ddl_is_pinned
pins the full statement list.
The scope. A model's scope is the partition_by of its
DecayingSortedField (Valor's project_key), else of its first partitioned
SortedField. With neither, every record is in scope ''. A NULL scope
column is scope '' too, so it is never dropped. The scope leads the
postings key. A save that changes a scope column, including
save(update_fields=["project_key"]), moves the record's postings and its
narrow vector row in the same statement.
The postings, document length and narrow vector row always carry the scope of
the row that is stored after the save. A scope column the save writes (every
column on a full save, the listed ones with update_fields) takes the
instance's value; one it does not write keeps the stored value, read from the
record row inside the save's own statement, after the record lock. So
save(update_fields=["text"]) on an instance whose project_key was changed
but never saved, or was changed by another writer after this instance was
loaded, re-indexes the text in the scope the row actually holds. A scope part
whose type has no exact SQL spelling of str(value) (anything but str,
int and bool) is taken from the record's existing side rows, which already
hold the stored scope. Pinned: tests/postgres/test_postgres_search.py::test_a_partial_save_keeps_the_side_tables_in_the_stored_scope
and ::test_a_partial_save_takes_each_unwritten_scope_column_from_the_store.
Concurrent saves of one record. The data-modifying CTEs of a save share
one snapshot, so under READ COMMITTED a second save of the same record
whose statement started before the first one committed could not see the
rows the first one wrote, and would leave them behind. A save therefore
takes the record's advisory lock first, as its own statement in the same
message:
SELECT pg_advisory_xact_lock(hashtextextended('popoto:rec:<table>:<_pk>', 0)).
The rewrite then takes its snapshot after any earlier writer of that record
has committed, which also covers two racing first inserts. The backfill
takes the same locks, sorted by _pk. Pinned by
test_concurrent_saves_of_one_record_leave_no_stale_rows, which interleaves
two saves behind a held row lock in 8 runs, and by its control, which shows
the same interleaving goes stale without the lock.
One lock order for every writer. Every statement that writes a record
row takes that record's advisory lock first, not only a save: delete,
atomic_increment, a capped-list push, touch, update_confidence, the
access tracker's writes, on_context_used's FOR UPDATE and an
ExistenceFilter row. The order is any (model, field) lock (a
ValidityField's), then the record-key locks sorted by _pk, then the row locks in _pk
order. A record's key lock is always the first lock taken on it, so a
transaction that runs update_confidence(x, pipeline=tx) and then
x.save(pipeline=tx) queues a concurrent x.save() behind it instead of
deadlocking with it. Transactions that each take several records in
different orders can still deadlock; that surfaces as
BackendRetryableError. Pinned by
test_a_confidence_update_then_save_cannot_deadlock_a_save and its control.
A record delete on a model with a symmetric CoOccurrenceField writes
rows in its partners' edge sets too (the reverse edges), so it takes the
partners' record-key locks with the deleted keys', in one sorted sequence,
before it touches a row. The lock statement runs twice: the first takes the
locks in order with the partners its snapshot saw, the second picks up a
partner linked while the first waited (none, normally; once the deleted keys
are held no new partner can appear, since a link takes both keys' locks).
Pinned by tests/postgres/test_postgres_graph.py::test_a_delete_cannot_deadlock_a_transaction_holding_a_partner_set
and its control, where a transaction holding a partner's set and then linking
into the deleted record deadlocks a delete that skips the partners' locks.
BM25¶
The tokens come from popoto's own tokenizer, the one Redis uses; Postgres
text search is never involved. BM25Field.search computes BM25_SEARCH_LUA's
formula operation for operation, in double precision:
idf = ln((N - df + 0.5) / (df + 0.5) + 1) and
tf · (k1 + 1) / (tf + k1 · (1 - b + b · dl / avgdl)), summed over the query
terms in query order, with k1 = 1.2 and b = 0.75. N, avgdl and df
are corpus-wide, as on Redis. Results come back in the same order (ties
by key, bytewise) and with Lua's %.14g score representation. The
statistics are counted live from the document-length table, so there is no
running avgdl to drift and recompute_stats has nothing to do.
get_idf, filter_selective_tokens and Query.keyword_search follow.
BM25Field.search(allowed_keys=) keeps only the best
max(limit, SCOPED_SEARCH_FETCH_CAP) records overall, which is the window
Redis's widening loop reaches before it stops.
Embeddings¶
EmbeddingField needs the pgvector extension. popoto never creates or
drops an extension, so run CREATE EXTENSION vector in the database (as a
role allowed to). Its schema must be on the connection's search_path; the
default, public, is. A model with an EmbeddingField raises
BackendCapabilityError naming the fix on its first use when either is
missing. The pgvector/pgvector:pg18 image ships the extension.
The dimension d is read from the field's provider at the model's first
use. Configure the provider (popoto.configure) before then. With no
provider the column is an unconstrained vector with no HNSW index, and
every search is exact.
A save embeds the source text exactly as on_save does on Redis: a provider
failure raises RuntimeError and nothing is written. The vector is stored
as float32, the precision of Redis's .npy files. Reads that hydrate records
never fetch it. A save that names the source in update_fields re-embeds,
as it re-indexes BM25.
The narrow vector table. Each vector is also kept in
<table>__<f>__vec (_pk, scope, v), written in the save statement and
cascaded on delete. In the record table a 1536-d vector (6 kB) is TOASTed,
so an exact scan reads two heaps and de-TOASTs every row in scope. The
narrow table stores v inline (STORAGE PLAIN, up to
1,900 dimensions, so the row fits an 8 kB page), and a scope's vectors are
one index range on scope plus their heap pages. The exact path reads it;
the HNSW index stays on the record table's column. The cost is a second
copy of every vector, about 6 kB a row at 1536-d.
The vector arm (_get_vector_scores, behind ContextAssembler's hybrid
path) returns cosine similarity 1 - (v <=> q), positive only, as Redis's
numpy path does. While at most Defaults.PG_VECTOR_EXACT_MAX (5,000) rows in
scope hold a vector, it scans them exactly, ordering by (v <=> q) + 0,
which no index can serve. Above that it uses the HNSW index with
SET LOCAL hnsw.ef_search = Defaults.PG_HNSW_EF_SEARCH and
hnsw.iterative_scan = relaxed_order. When HNSW returns fewer rows than
min(limit, rows with a vector), the recall guard re-runs the arm
exactly (on the narrow table), so a pathological query costs latency rather
than coming back empty.
EmbeddingField.load_embeddings reads the column (normalized as before, in
_pk order). garbage_collect and sweep_stale_tempfiles return 0 and
touch no file: the vector goes with its row.
Backfill. After a save whose own embedding succeeded, the engine embeds
up to Defaults.PG_BACKFILL_BATCH (4) rows of the same scope that have no
vector, or whose vector another model made. It runs after the commit,
outside any transaction(), and stops at
Defaults.PG_BACKFILL_BUDGET_SECONDS (1 s) of wall clock. A provider call
that outlives the budget is abandoned, so a slow provider cannot hold
save() past it. A row is written only while its source still hashes to the
text that was embedded and the row is still in the scope, so a concurrent
edit is never given a stale vector. It writes the narrow row in the same
statement, under the same record locks a save takes. Errors are logged and
swallowed. The busy flag is process-wide: while one backfill's provider
call is still out (abandoned at the budget but not yet returned), every
other model's backfill skips.
Membership¶
ExistenceFilter and FrequencySketch are exact on Postgres (a
documented strictness): might_exist has no false positives, and
get_frequency returns the true count. The token table holds each live
record's tokens and forgets a record when it is deleted. The count table
counts saves and never decrements, as the sketch never does.
fill_ratio reports the fill a bloom of the field's parameters would have
after the distinct tokens stored, 1 - e^(-k·n/m).
Fusion and recall()¶
Query.fuse is unchanged Python RRF. On Postgres the builder's filters
(including plain fields) scope the fused set through one SELECT, and the
survivors hydrate through one load.
Query.recall() is new and Postgres-only ([PG-only]; on a Redis-bound
model it raises BackendCapabilityError):
hits = Memory.query.recall(
"kubernetes upgrade",
scope="valor", # the partition_by value
filters={"agent_id": "a1"}, # KeyField / IndexedField equality
tags=["ops"], tags_mode="any", # the TagField: && ("any") or @> ("all")
weights={"bm25": 1.0, "vector": 0.4},
limit=10,
)
for memory, score in hits: ...
The BM25 and vector arms rank max(limit, Defaults.PG_RECALL_ARM_DEPTH)
deep and are fused by weighted RRF, Σ w / (60 + rank). A first, index-only
statement counts the scope's rows with a vector, which picks the vector
path. On the exact path one SQL statement then runs both arms and the
fusion, and returns the fused records' rows. On the HNSW path the vector
arm runs as its own statement, with the recall guard, first. That arm needs
statement-wide planner settings to keep the planner on the HNSW index, and
in a combined statement those settings would push the BM25 arm onto a full
index scan. Its ranking then joins the fused statement as a ranked list.
A record that leaves the scope between the HNSW statement and the fused one
is filtered out again in the fused statement, like every ranked list that
joins it.
Every scoping argument is optional, and they compose. BM25's statistics are
per scope by default (bm25_stats="scope"): on a shared database, one
agent's corpus must not skew another's IDF. bm25_stats="corpus" uses the
corpus-wide statistics BM25Field.search uses.
A model with one DecayingSortedField adds a third arm, weights["decay"]
(default 1.0): top_by_decay's ranking (DECAY_SCORE_LUA over the decay
columns: the field's clock, its base_score_field, and <f>__conf
modulation when the field resolves a ConfidenceField), computed in the
fused statement itself over the same scope and filters. It adds no round
trip.
Query.top_by_relevance(scope=None, limit=10, *, as_of=None) ([PG-only], #758 D8)
returns [(instance, score)] ranked by decay × confidence in SQL, the score
top_by_decay ranks by. It replaces reading a decay sorted set with a raw
ZREVRANGE. scope is the decay field's partition_by value; None ranks
every partition together. On a Redis-bound model it raises
BackendCapabilityError; use top_by_decay there.
Both read through the validity gate. On a model with a
ValidityField, top_by_relevance and recall leave out a record closed at
or before as_of and one that starts after it, as top_by_decay,
composite_score and the assembler do. as_of is epoch seconds and defaults
to now; Query.recall(..., as_of=) and top_by_relevance(..., as_of=) take
it, so as_of in the past returns the records that were valid then. In
recall the gate is ANDed onto the domain of every arm (BM25, vector,
decay), so a superseded record cannot enter through the lexical arm either.
Defaults.VALIDITY_GATING_ENABLED = False turns it off, and a model without a
ValidityField is unaffected.
composite_score(similarity_boost=…) works on Postgres too. The
mapping is one more arm, weight 1.0 and last, as its temporary set is in the
Redis ZUNIONSTORE. semantic_search(indexes=…) uses it, and on a model
with a ValidityField the validity mask applies to it as to every arm
(pinned two-leg by tests/test_semantic_search.py::TestSemanticSearchWithIndexes). The order is the
same on both backends; with three or more summed arms a score can differ by
up to 2 ulp, because ZUNIONSTORE adds the smallest input set first while
Postgres adds the arms in order. co_occurrence_boost= is an arm too:
weight 1.0, after the indexes and before similarity_boost, the position its
temporary set takes in the ZUNIONSTORE.
ContextAssembler (M2c)¶
ContextAssembler runs on a Postgres-bound model with no option of its own,
and returns the same ranked records as on Redis. It issues no Redis
command: tests/postgres/test_postgres_assembler.py patches the Redis
connection layer to record and refuse every command, then runs a Valor-shaped
model through assemble() in every mode with scopes, tags, a budget, the
gate, assess_quality and emit_trace, plus assess() and
on_context_used(), and asserts the record is empty.
What each stage runs on Postgres:
| Stage | Postgres |
|---|---|
| ExistenceFilter short-circuit | might_exist on the exact token table |
| Hybrid / lexical pull | the same body as on Redis: BM25Field.search (keyword_search with corpus-wide statistics), the vector arm (vector_search), and fuse with _fusion_weights. The BM25 window is narrowed by the scope's indexed filters from one id-only SELECT, as filter_for_keys_set narrows it on Redis |
Zero-signal fallback, composite pull, assess probe |
composite_score: rank_composite, one SELECT |
| Tag scoping | one id-only SELECT with && (any) / @> (all) |
Score proxy (assess_quality, emit_trace, assess) |
the partition's rank_decayed for a decay field, with the model's validity gate at now (a record closed now or not yet started scores None and counts as stale, as on Redis, which runs DECAY_SCORE_LUA with the gate; pinned two-leg by test_the_score_proxy_gates_on_validity_at_now), the column for a plain sorted field |
| Post-effects | one transaction: the rows of the selected and the suppressed records locked in _pk order behind their record-key locks, one staged-read UPDATE, one confidence UPDATE for every suppressed candidate |
Why the hybrid path does not call recall(). recall() ranks each arm
inside the scope; the assembler ranks the vector arm across the whole model
and the BM25 arm inside the SCOPED_SEARCH_FETCH_CAP window, and fuse then
keeps the in-scope records. RRF sums those ranks, so the same keys in the same
order on both backends needs the same arms. The BM25 statistics are
corpus-wide on that path, never recall()'s per-scope default (architect
decision 3).
The parity evidence. The retrieval-quality fixture (200 records, 20
queries, retrieval_mode="auto") returns the same keys in the same order,
the same formatted output and the same token count on both legs, through the
lexical path, and through the hybrid path partitioned by agent_id with each
record's text suffixed (memory N) (test_postgres_assembler.py): with the
fixture's texts as written, several records share a text and so a vector, and
the hybrid path's parity then holds only up to the vector-arm tie row below. scripts/probe_assembler_parity.py builds
random corpora on both legs and compares assemble() (keys, formatted output
and token count, metadata, trace, quality, and the post-effects' confidence and
staged-read state), on_context_used() and assess(); its classes are in
the probe's docstring, and test_postgres_assembler.py runs a 12-shape slice
of it in CI.
REDIS_URL=redis://localhost:6379/13 \
POPOTO_POSTGRES_URL=postgresql://localhost:5432/postgres \
python scripts/probe_assembler_parity.py --seeds 1 2 3 --shapes 100
The differences the probe classifies are rows elsewhere on this page, seen
through the assembler: the exact ExistenceFilter (a cue whose only record was
deleted or rewritten short-circuits on Postgres, not on Redis), a reload after
touch() (after an acted outcome the formatted relevance of that record
differs, and a token budget can then admit a different record), and the vector
arm's tie order (records with the same text have the same vector, which Redis
returns in file-listing order and Postgres by key).
An outage raises BackendUnavailableError from assemble(), as a Redis
outage raises ConnectionError; catch popoto.backends.OUTAGE_ERRORS, which
holds both (popoto.recipes.context_assembler.OUTAGE_ERRORS is the same
object since #816). That is the retrieval
path's rule, not every helper's: the quality helpers behind
assess_quality and assess() (score spread, feeling-of-knowing,
staleness) catch every exception and degrade, on both backends (unchanged
from Redis), so assess() during a rank_decayed outage returns a degraded
quality rather than raising.
Post-effects fail differently. On Postgres the post-effects are one
transaction (above), so they are all-or-nothing: a statement that fails drops
the staged reads and every suppression signal of the call, with one warning.
On Redis a TypeError or ValueError is swallowed per candidate and the
pipeline carries on with the rest. And Postgres stages reads only for a model
with AccessTrackerMixin (the _at_member check), where Redis calls each
record's on_read: a user-defined on_read on a model without the mixin runs
on Redis but not on Postgres. Neither is reachable with the shipped models.
Co-occurrence graph (M4)¶
CoOccurrenceField keeps, on Redis, one sorted set per source key; on
Postgres each field has one edge table, a row per directed edge:
CREATE TABLE "popoto"."memory__associations__edge" (
"src" text NOT NULL, "dst" text NOT NULL,
"weight" double precision NOT NULL, PRIMARY KEY ("src", "dst"));
A symmetric field writes both directions, as the Lua writes both sets, so the
two weights of a pair can differ exactly as they can on Redis (a prune or a
weaken_all touches one side). There is no foreign key: Redis links any two
key strings, records or not. Deleting a record removes its own edges and,
for a symmetric field, the reverse edge in every set it linked to, as CTEs of
the record's DELETE, behind those partners' record-key locks as well as its own (see "One lock order for every writer"). Every tie-break is COLLATE "C", the sorted set's
member order. Pinned: tests/postgres/test_postgres_graph.py::test_the_edge_table_ddl_is_pinned.
Writes. Each one is a statement behind the record-key advisory locks of
the sets it writes (src, and dst when symmetric), sorted: the backend's
one lock order. That lock is what makes a link's count-then-prune atomic, as
the script is: no committed state of a set ever holds more than max_edges
edges (test_concurrent_links_to_one_set_never_exceed_max_edges, and its
deterministic interleaving with a control that overflows without the lock).
| Call | Lua | Postgres |
|---|---|---|
link |
LINK_WITH_PRUNE_LUA per direction: an existing edge keeps its weight; a new one is added and, past max_edges, the lowest count - max_edges by (weight, member) are removed |
the same, in one statement: the prune is decided over the set as it would be after the insert, so a new edge that ranks lowest is never inserted |
strengthen |
min(old + delta, cap), a missing edge as 0, no prune |
INSERT … ON CONFLICT DO UPDATE with the same CASE |
unlink |
ZREM |
DELETE |
weaken_all |
every edge times factor; below 0.001, removed |
one statement: DELETE the pruned, UPDATE the rest |
The arithmetic is the Lua's, in double precision, and the replies are the
Redis ones: link returns the script's number reply, which Redis sends as an
integer (the weight truncated toward zero, so link(…, 0.5) returns 0.0 on
both); strengthen returns tostring(new) (%.14g). Where Postgres would
raise on a float overflow or underflow and C saturates, the statement guards
the one product that can (an underflow in weaken_all, whose edge is removed
either way), and an input past 1e300, infinite or NaN takes an exact path:
the same steps in Python floats, which are C doubles, in one transaction.
propagate is PROPAGATE_BFS_LUA. The Lua runs a FIFO queue with a
visited map; an entry expands through the node's top max_edges neighbours
by (weight, member) descending unless an earlier entry for the node was at
least as heavy, and a neighbour is reached with w * decay * min(edge, cap),
kept when that is at least the threshold. With a positive threshold every
weight is positive and the step is monotone, so a skipped entry is dominated
by an earlier, no-deeper one: each result is the heaviest walk of at most
depth hops whose weights stay at or above the threshold. That is one
WITH RECURSIVE statement, a layer per iteration:
WITH RECURSIVE
"_seed" AS (SELECT DISTINCT u AS pk FROM unnest($seeds::text[]) AS u),
"_walk" (pk, w, d) AS (
SELECT pk, 1::float8, 0 FROM "_seed"
UNION ALL
SELECT pk, w, d FROM ( -- each node's heaviest arrival
SELECT pk, w, d, row_number() OVER (PARTITION BY pk ORDER BY w DESC) AS rk
FROM (SELECT n.dst AS pk, safe_mul(k.w * $decay, least(n.weight, $cap)) AS w,
k.d + 1 AS d
FROM "_walk" AS k
CROSS JOIN LATERAL ( -- the top max_edges neighbours
SELECT dst, weight FROM <edges> WHERE src = k.pk
ORDER BY weight DESC, dst COLLATE "C" DESC LIMIT $max_edges) AS n
WHERE k.d < $depth) AS h
WHERE w >= $threshold AND w <> 'NaN') AS r
WHERE rk = 1)
SELECT pk, max(w) FROM "_walk"
WHERE d >= 1 AND pk NOT IN (SELECT pk FROM "_seed")
GROUP BY pk ORDER BY max(w) DESC, pk COLLATE "C"
safe_mul is the saturating product of the decay SQL.
That statement only runs while it expands at most
Defaults.PG_GRAPH_RECURSIVE_MAX_LAYERS (2) layers, i.e. ceil(depth) <= 2
(the default depth=2). A recursive CTE sees only the previous layer, so it
cannot apply the visited map: it re-expands every reached node on every layer
until depth runs out or the weights drop under the threshold. Up to two
layers that is exactly the pruned work (layer one expands the seeds, layer two
every first arrival); past that it grows with depth × fan-out where the Lua
stops. The #781 review measured a 400-node clique at depth=50, decay=0.99 at
24.9 s against Redis's 0.10 s, and at larger depths (or a decay near 1) the
statement ran into statement_timeout, which the outage contract reports as
BackendUnavailableError. A deeper call therefore runs one statement per
layer instead: each frontier node's top max_edges neighbours, the same
product and threshold, each node's heaviest arrival (GROUP BY), and
between layers the Lua's visited rule -- a node goes on only when it arrived
strictly heavier than at any earlier layer. A pruned arrival is dominated by
the earlier one, so the answer is the recursive statement's; the work is at
most the Lua's, and the loop stops after the last layer that improved a node
whatever depth is (the contraction guard keeps decay * cap < 1, so an
improving walk is a simple path: at most one layer per node reached). A
statement timeout is never what bounds a graph read. Measured on the review's
graphs (macOS arm64, PostgreSQL 18.6, Redis 8.10, load average 4.2-4.9,
median of 5 runs; 7 for the probe shapes):
| graph, call | Redis | Postgres |
|---|---|---|
400-node clique, depth=50, decay=0.99, threshold=0.01 |
77 ms | 300 ms |
same, depth=1000, threshold=1e-6 |
86 ms | 295 ms |
same, depth=1e6, decay=0.999 |
81 ms | 290 ms |
same, depth=1e6, decay=0.5, threshold=1e-280 |
84 ms | 288 ms |
same, depth=inf, decay=0.99 |
82 ms | 289 ms |
the review probe's five ≤12-node shapes at depth=1e9, decay=0.999999 |
0.21-0.32 ms | 1.15-1.55 ms |
50k edges, fan-out 10, depth=2 / 3 / 5, threshold=0.01 |
2.7 / 11 / 35 ms | 1.8 / 10 / 61 ms |
The deep calls run their layers on one pooled connection in one read
transaction, so a layer costs one round trip. Every answer is identical to Redis's. Pinned:
test_a_dense_clique_at_any_depth_answers_inside_the_statement_timeout
(a 40-node clique at depth=1e9 with the timeout lowered to 3 s; the health
record sees no failure), test_a_deep_propagate_prunes_as_the_visited_map_does
and test_the_three_bfs_paths_agree.
Outside that domain -- a threshold at or below 0 (or under 1e-290), a negative or
infinite decay_per_hop -- the step is not monotone and the visited map's
order decides the answer, so the backend replays the Lua's queue exactly in
Python, over the same neighbour lists fetched one layer per statement.
Scores come back through %.14g, as the script's tostring sends them.
get_linked is ZREVRANGEBYSCORE +inf <min> LIMIT 0 <limit>: one indexed
read of the source's rows, the stored weights unclamped (graph_expand at
depth 1), with ( for an exclusive minimum, limit=0 empty and a negative
limit everything.
recipes/graph_traversal.traverse runs unchanged on top: propagate, the
relationship walk (sample_related_keys), and the confidence and
decay modulation. ContextAssembler's graph arm and
composite_score(co_occurrence_boost=) work on a Postgres model as on Redis.
The seeded probe. scripts/probe_graph_parity.py replays the same random
sequence of writes on both legs -- weights from typical to -inf, ties and the
cap; deltas from 1e-12 past the cap; factors 0, tiny and 1; record
deletes and import_state (NaN weights included) -- comparing every return,
then each key's full edge set, get_linked (limit=None and a NaN bound
included), propagate (depths up to 1e9 and inf, decays up to 0.999999)
on all three Postgres paths, export_state, composite_score with the boost
and traverse(). Its classes are in its docstring; the documented ones are the
co-occurrence rows of the divergence table.
tests/postgres/test_postgres_graph.py runs a 25-shape slice in CI.
REDIS_URL=redis://localhost:6379/13 \
POPOTO_POSTGRES_URL=postgresql://localhost:5432/postgres \
python scripts/probe_graph_parity.py --seeds 1 2 3 --shapes 200
Recipes, mixins and the queue (M4)¶
The recipes reach storage through field and model methods (#630), so a
Postgres-bound model runs them unchanged. The few methods that keep state
outside a model's own hash on Redis are field_call adapters on Postgres
(popoto/backends/postgres/recipes.py), several of them backed by engine
tables created on first use, like popoto_recall_proposal:
| Method | Redis | Postgres |
|---|---|---|
Model.idle_seconds |
OBJECT IDLETIME: whole seconds since the key was last read or written |
whole seconds since the later of the row's last write (_updated_at) and, with AccessTrackerMixin, its last confirmed read (_last_accessed); None with no row, or an expired one |
SortedFieldMixin.count / members / score |
ZCARD / ZRANGE / ZSCORE on the partition's sorted set |
the partition's rows, in (value, _pk COLLATE "C") order, with ZRANGE's inclusive and negative ranks; score converts the stored value as the sorted set scores it |
counters.increment / read (model=) |
INCRBY / GET on a string |
popoto_counter (key, value): INSERT … ON CONFLICT DO UPDATE … RETURNING |
TombstoneStore (MemoryLifecycle's archive) |
$TOMB:{Model}:data hash + :index sorted set |
popoto_tombstone (model, member, entry, ts) |
TombstonePriorStore (the negative prior) |
$TOMBPRIOR:{Model}:* |
popoto_tombstone_prior (model, digest, burials, ts) + popoto_tombstone_stats |
NeverRecordMixin's audit log |
$NR:{Model}:counts hash + :drops capped list |
popoto_never_record_count (model, reason, count) + popoto_never_record_log (model, seq, entry), capped at NR_TOMBSTONE_LOG_MAX |
| The question queue's token bucket and propose lock | $QuestionBucket:{agent} (with a TTL) and SET NX PX |
popoto_question_bucket (agent, last_turn, expires_at) and popoto_lease (key, token, expires_at): an expired row reads as absent |
DefaultMemory's eviction counts and pages its partition through the sorted
reads above and records the eviction in popoto_counter, so a
Postgres-bound DefaultMemory issues no Redis command, and MemoryService
reads that counter back with its own (below).
The harness integration (#814). MemoryService -- the popoto-memory
hook, the MCP server and doctor -- follows DefaultMemory's backend. On
Postgres its session state is the _harness adapter's tables and its
counters are popoto_counter rows under the same key strings the Redis
counters use, so a Postgres-bound hook process binds no Redis connection and
sends Redis no command:
| State | Redis | Postgres |
|---|---|---|
| Read-to-write handoff | $popoto_memory:pending:{agent}:{session} list: RPUSH/LTRIM/EXPIRE, claimed by LRANGE + LREM, else LPOP |
popoto_harness_pending (agent, session, seq, turn, keys, expires_at): one message under the session's advisory lock; the claim is one DELETE … RETURNING |
| Injection suppression | $popoto_memory:injected:{agent}:{session} set: SADD/EXPIRE, SMEMBERS |
popoto_harness_injected (agent, session, member, expires_at) |
| Last-success timestamps | $popoto_memory:last:{agent}:{op} strings |
popoto_harness_event (agent, name, stamp) |
| Counters | $popoto_memory:counter:{agent}:{op} strings, INCR, read by SCAN |
popoto_counter rows, read by key prefix (_counter scan) |
Each session's rows share one expiry, refreshed on every write as EXPIRE
refreshes the Redis key, and every read filters expired rows; a write also
deletes up to 20 expired rows of any session in passing, so abandoned
sessions do not accumulate. PostgresBackend.describe() is the one
read-only round trip doctor makes for the server facts (version and
support, schema, pgvector, the DSN summary). Pinned by
tests/test_integrations_backends.py (both legs) and
tests/postgres/test_postgres_harness.py, which runs the real hook and
doctor subprocesses with Redis unreachable and a connect guard that
records any Redis dial.
MemoryLifecycle.tombstone() archives the record as restore() will decode
it: on Redis the raw hash bytes, on Postgres the stored row encoded the way a
Redis save would write it, so decode_popoto_model_hashmap restores it
unchanged.
The question queue. Delivery is _DELIVER_LUA as one statement behind
the agent's bucket advisory lock, so two deliveries for one agent serialize
and the budget is exact under any concurrency (the 8-thread hammer passes on
both legs):
SELECT pg_advisory_xact_lock(hashtextextended('popoto:qq:<schema>:<agent>', 0));
WITH b AS (SELECT last_turn FROM popoto_question_bucket
WHERE agent = $agent AND expires_at > <now>),
pick AS (SELECT t._pk, coalesce(t.ask_count, 0) + 1 AS n, k.ord
FROM unnest($candidates::text[]) WITH ORDINALITY AS k(pk, ord)
JOIN question_candidate AS t ON t._pk = k.pk
WHERE NOT EXISTS (SELECT 1 FROM b WHERE $turn < b.last_turn + $K)
AND t.status = ANY($deliverable)
AND (t.cooldown_until IS NULL OR NOT ($turn < t.cooldown_until))
ORDER BY k.ord LIMIT 1
FOR UPDATE OF t SKIP LOCKED),
upd AS (UPDATE question_candidate AS t
SET status = 'delivered', ask_count = pick.n, delivered_turn = $turn
FROM pick WHERE t._pk = pick._pk RETURNING pick.ord, pick.n),
bk AS (INSERT INTO popoto_question_bucket (agent, last_turn, expires_at)
SELECT $agent, $turn, <now> + $ttl FROM upd
ON CONFLICT (agent) DO UPDATE
SET last_turn = EXCLUDED.last_turn, expires_at = EXCLUDED.expires_at)
SELECT ord, n FROM upd;
The bucket is written only when a candidate was claimed, as the script
writes it, so a refused or empty delivery never spends the budget. The
statement waits on nothing but the bucket lock, which no record writer takes,
so it cannot join a deadlock cycle. The claim (_CLAIM_LUA) is an
UPDATE … WHERE status = ANY($allowed) AND <guard> IS NOT DISTINCT FROM … RETURNING
behind the candidate's record lock. Pinned by
tests/postgres/test_postgres_question_queue.py (a deterministic
interleaving of two deliveries in one turn, and its control without the
bucket lock, which delivers twice).
NeverRecordMixin refuses on Postgres exactly where it refuses on
Redis, in Model.save() before the backend is asked to write: a blocked save
returns False, no row is written, and the content-free audit entry goes to
the backend's tables. tests/test_never_record_firewall.py runs on both
legs; on the Postgres leg its "no trace anywhere" sweep covers every table of
the schema and Redis.
AppendOnlyMixin checks for the record through the backend. Inside a
Postgres transaction() the check runs on the transaction's connection, so
two saves of one key in one transaction refuse the second (on Redis the
"intra-pipeline" shape stays open, as its module docstring records).
ProvenanceJournal runs its pre-flight unchanged and then appends --
and, for a closing kind, closes the target's interval -- in one
transaction (SupersessionProtocol.save_and_invalidate): its own, or
the caller's when the backend's unit of work is passed as pipeline=. The
close's outcome is known at the call, so AnnotationResult.target_closed is
the truth (on Redis a caller pipeline reports None, "unknown until you
execute", with a close_index to read after EXEC), and a close that fails
rolls the annotation back with it (on Redis the queued annotation is kept:
the ProvenanceJournal row of the
divergence table). Any other pipeline= object, a Redis pipeline included, is
refused with ValueError before anything is written.
AppendOnlyMixin.hard_delete removes the row (its open-claim pointer
cascades) and clears the <f>__supersedes / <f>__superseded_by columns of
the records that named it, the value side of the Redis chain hashes. The
reconciler's statement-vector cache is popoto_embedding_cache (model,
member, vector), beside the entries, and erase_entry drops it there.
JournalEntry composes EventStreamMixin: on Postgres its mutation log is
the backend's events table, appended in the journal write's own
transaction (a caller's transaction() or popoto.batch() included), and the reconciler's StreamConsumer trigger
(reconciliation_consumer, which names JournalEntry as its model) reads it
there. A rolled-back write leaves no entry, and a Postgres-bound journal and
its reconciler send Redis no command at all. Pinned by
tests/postgres/test_postgres_journal.py and
tests/postgres/test_postgres_events.py::test_a_postgres_journal_and_its_reconciler_send_redis_nothing.
Memory telemetry runs on Postgres, on top of record expiry:
AssemblyEvent declares Meta.ttl, so its events expire there as on Redis,
and report_outcomes() and TelemetryAnalyzer read them back. Pinned by
tests/test_memory_telemetry.py, whose every test runs on both legs.
Decision log (#811). SubconsciousMemory(auditable_extraction=...)
constructs and runs on a Postgres-bound process with no Redis command at all.
DecisionLog follows DecisionRecord's backend (the process default, as the
journal does): the per-candidate row is a decision_record row under a unique
key, the guarded terminal write (refuse a terminal write over an assembled
accept) is one statement run under the row's advisory lock, the assembly
claim is a popoto_lease row (INSERT ... ON CONFLICT ... WHERE expires_at <=
now, so a lapsed claim is takeable and a release is token-checked), and
turn_summary is a GROUP BY over the turn's rows computed on read. Every
method has the same semantics on both backends: rows, return values, the
guard, list_pending and the claim. turn_summary means the same thing on
both (a rollup of the rows' current terminal states, zero counts absent,
pending never counted, an empty or NULL reason counted as reason:), and
both backends return exactly that. Redis keeps it in a hash that every
state-changing write updates atomically with the row, the pending write
included: a pending write over a row that is already terminal takes that
row's counts back out (#822). A hash written by a version before #811 or #822
can disagree with its rows; DecisionLog.rebuild_turn_summary repairs it.
Construction refuses only the split trail, whenever auditable_extraction is
set: a log that would land in Redis while the memory model is on Postgres, or
a journal entry model in a different store from the log, in either direction
(a Redis default with a Postgres-pinned journal is refused too). The error
names which side is on which backend and gives the fix for that direction
(#822). Under a Redis default it says to bind the default with
set_backend("postgres") or POPOTO_BACKEND=postgres, or to remove the one
pin. Under a Postgres default, where re-binding would change nothing, it says
to remove the journal entry model's Meta.backend pin. A memory model pinned
to Redis under a Postgres default keeps its log in Postgres, alongside the
journal. Data placement changes with the binding: a process that moves to
Postgres starts with an empty decision log, as it does for the journal.
Migrate existing rows with the
Redis-to-Postgres migration tool, naming
both popoto.extraction.decision_log:DecisionRecord and
popoto.recipes.provenance_journal:JournalEntry, so the rows' entry_ids
travel with their journal. The summary hash and claim keys stay behind:
Postgres derives the summary, and a claim is a lease. The decision log never
joins a caller's transaction() or popoto.batch(). Each of its statements
takes its own pooled connection and autocommits, as the Lua scripts do on
Redis. While four or more transaction() blocks are open at once
(Defaults.PG_POOL_MAX_SIZE is 4), every pooled connection is held, so each
of the log's statements waits Defaults.PG_CONNECT_TIMEOUT_SECONDS and then
raises BackendBusyError (a busy pool is the
backend's general limit, not the log's). Keep units short, or keep fewer
concurrent units than the pool size. Pinned by tests/postgres/test_postgres_decision_log.py
(including a whole extraction under a Redis connection layer that refuses
every command) and by the conformance classes in
tests/test_auditable_extraction.py and tests/test_reference_resolution.py.
The seeded probe, scripts/probe_queue_parity.py, replays random queue
sessions on both legs (proposals with dedup, use, delivery with and without
cues, answers, expiry, prune, regressed turns) and compares every return,
the stored candidates, the targets' confidence and the bucket;
test_postgres_question_queue.py runs a 20-shape slice in CI.
REDIS_URL=redis://localhost:6379/13 \
POPOTO_POSTGRES_URL=postgresql://localhost:5432/postgres \
python scripts/probe_queue_parity.py --seeds 1 2 3 --shapes 200
Event streams and pub/sub (M5)¶
EventStreamMixin, StreamConsumer, Publisher and Subscriber run on
Postgres with no Redis command at all. The stream lives where the model that
writes it lives: a Redis stream for a Redis-bound model, the backend's events
tables for a Postgres-bound one.
The entry is part of the write. A save (create, update, partial update)
or delete of a Postgres-bound EventStreamMixin model appends its mutation
entry in the same transaction as the record: the record and its entry
commit together or not at all. On Redis the XADD rides the save's
MULTI/EXEC, which applies queued commands without rollback, so this is
strictly stronger. Without a caller transaction the save takes one of its own
(the write, then the append); inside a caller's transaction() or a
popoto.batch() the entry is appended just before that transaction's
COMMIT (uow.defer_stream_append), so a batch that is reset() appends
nothing. ConfidenceField.update_confidence and CoOccurrenceField.strengthen
log their events to the same stream, as on Redis, and a custom
_xadd_event(..., pipeline=batch) joins the batch the same way.
Stream locks are taken in one global order. Each append locks its
stream's popoto_stream row until the transaction ends (that lock is what
makes ids commit in id order, below). A unit's appends therefore run at
COMMIT, after every record lock the unit takes, sorted by stream key
(stably, so one stream's entries keep their order); a write that appends in
its own transaction (a multi-record delete) sorts the same way. Two
transactions that write two EventStreamMixin models, or two partitions of
one, in opposite orders then queue on the first stream rather than
deadlocking. Measured with 4 threads × 40 transaction()s, each saving one
record of two stream models in random order: 82-86 of 160 failed with
40P01 deadlock detected on popoto_stream before the sort, 0 after; the
partitioned variant went from 35/160 to 0 (pool size 16, so that the pool
did not run out first). Pinned by
test_postgres_events.py::test_opposite_order_transactions_never_deadlock_on_stream_locks
and ::test_a_units_stream_appends_run_in_stream_key_order. The cost is the owned
transaction: a save of an EventStreamMixin model measured p50 0.83-0.99 ms
against 0.32-0.38 ms for a plain model (3 runs × 400 saves, PostgreSQL 18.6
on localhost, Apple M1 Max, load 4.7-4.9).
Tables (one set per schema, created on first use):
| Table | Holds |
|---|---|
popoto_stream (stream, last_ms, last_seq, length, entries_added, max_del_ms, max_del_seq, created_at) |
one row per stream key: its last generated id and counters. The row is the key: an empty stream keeps it, as Redis keeps an empty stream key. Its row lock is the append lock |
popoto_stream_entry (stream, ms, seq, fields bytea[], created_at) |
one row per entry; fields is the flat k1, v1, k2, v2 array, in order, of the bytes XADD would send |
popoto_stream_group, popoto_stream_consumer, popoto_stream_pending |
each consumer group's cursor and logical read counter, its consumers, and its pending entries list (owner, last delivery time, delivery count) |
popoto_stream_attempt |
StreamConsumer's handler-attempt counter (the _hattempts: hash on Redis) |
popoto_pubsub_listener |
live Subscriber registrations, for publish's count |
Ids are Redis's <ms>-<seq>: the server's millisecond clock, with seq
restarting at 0 each new millisecond, strictly increasing per stream (a clock
that steps back keeps the last ms and bumps seq; the last id survives
deleting the entry that holds it). Because the stream row stays locked until
the appending transaction ends, ids commit in id order: every snapshot
sees an id prefix of the stream, so a group cursor never skips an entry that
commits later. Explicit ids (XADD key 5-1 …, 5-*, 7) are validated with
Redis's rules and error text.
Reading. Model.stream_range(), Model.stream_revrange() and
Model.stream_len() read a model's stream on either backend, and
popoto.streams.stream_client(model) returns the client that holds it: the
Redis client on Redis, the backend's stream store on Postgres, with the same
redis-py methods and replies (xadd, xrange, xrevrange, xlen, xdel,
xtrim, xgroup_*, xreadgroup, xack, xpending, xpending_range,
xclaim, xautoclaim, xinfo_groups, xinfo_consumers, delete,
exists). Errors keep Redis's message text (BUSYGROUP …, NOGROUP …;
XINFO CONSUMERS answers no such key for a missing stream and NOGROUP No
such consumer group 'g' for key name 'k' for a missing group, as Redis does)
as StreamCommandError, and redis-py's client-side checks as
StreamDataError.
StreamConsumer finds its backend from backend= or model=, else from
the EventStreamMixin models that write its stream_key (and their dead:
streams), else the process default; reconciliation_consumer names
JournalEntry. On Postgres:
XREADGROUP >advances the group's cursor under the group row's lock, so two consumers never receive one entry; the entries join the pending list in the same transaction. At-least-once delivery, retries, reclaim and dead-lettering are the Redis consumer's own logic, unchanged.XCLAIM/XAUTOCLAIMtake pending rowsFOR UPDATE SKIP LOCKED: a row a concurrent claimer holds is passed over rather than waited on or claimed twice.XAUTOCLAIMscans as Redis does (count × 10entries looked at), and reports entries deleted from the stream in its third list.- Blocking reads (
block_ms) wait onLISTEN: each append sendspg_notifyon the schema's events channel inside its transaction, so the notification arrives when the entry is visible.LISTENneeds a session, so the wait rides the process's one sharedLISTENsession (below), never a pooled connection -- PgBouncer in transaction mode would hand a pooled connection to another client between statements (Topology). Its DSN isPOPOTO_POSTGRES_LISTEN_URLwhen set (point it past a transaction-mode pooler at the server), else the maintenance DSN when one is configured, else the backend's own. The waiter is attached before the read it backs up, so an append that commits in between is not missed. A wait is capped at one second (STREAM_WAIT_POLL_SECONDS), after which the read runs again anyway -- the fallback poll that bounds a lost notification. Cancelling the task awaiting a read wakes its worker thread at once, and the thread returns without reading again: a cancel that lands while the thread is waiting claims nothing. One that lands while a read is already running in the thread cannot stop it -- that read commits, and the entries it claimed stay pending under the consumer, their reply discarded with the cancelled task. They are not lost:XPENDINGlists them andXCLAIM/XAUTOCLAIM(or the consumer's own0-id re-read) recovers them, as for any consumer that died holding entries.consumer.close()(or the end ofrun()) detaches the consumer's waiter.
Pub/sub. Publisher.publish is one pg_notify and Subscriber polls a
PostgresPubSub, a redis-py-shaped PubSub (subscribe, psubscribe,
unsubscribe, punsubscribe, get_message, listen, close, the same
message dicts) on the process's shared LISTEN session. A publisher uses its
model's backend, backend=, or the process default; a subscriber backend=
or the default.
- One notification channel per schema carries every popoto channel: the
payload names the channel, so channel names are not bound by Postgres's
63-byte identifier limit. Pattern subscriptions are matched
client-side, with Redis's glob rules translated to a regular expression
and matched byte-wise, as Redis's
stringmatchlenmatches:?and[^…]consume one byte of a non-ASCII channel, a range compares bytes as Cchar(signed on the x86-64 and Apple-silicon builds, so bytes 0x80-0xFF sort below 0x00), and[x-]is a range ending at](popoto.backends.postgres.pubsub.glob_match). Every subscriber of the schema receives every message and keeps what its subscriptions match. NOTIFYis transactional: a message published with the backend's unit of work, or apopoto.batch(), aspipeline=is delivered when that transaction commits (the batch'sexecute()), and never if it rolls back (reset()); a PostgresPublishernever queues a RedisPUBLISHon a batch. A batch anasync_*call opened (whose connection belongs to the event loop) takes the same publish: theNOTIFYis sent from inside the transactionawait pipe.async_execute()commits, as are its stream appends (pinned bytests/postgres/test_postgres_async.py::test_an_async_opened_batch_carries_its_events_and_notifies_to_commit). Postgres folds identical notifications sent in one transaction into one, so each payload carries an 8-character per-publish nonce, stripped before delivery:dup, dup, other, duppublished in one transaction arrives as all four, in order, as a RedisMULTIdelivers them (pinned on both legs bytests/test_pubsub.py::test_identical_messages_in_one_unit_are_each_delivered).- Payload limit. A
NOTIFYpayload must be shorter than 8000 bytes, and the message travels base64-encoded beside the channel name and the nonce, so about 5.9 KB of message fits. A larger one is refused withPubSubPayloadTooLarge(aPublisherException) before anything is sent, not split: a split message could be half-delivered to a subscriber that joins between its parts. Publish a key and keep the body in a record. publishreturns the number of live subscriptions reached, counted frompopoto_pubsub_listenerrows whose session pid is live (pg_stat_activity); a subscriber deletes its rows onclose(), and rows of a session that died stop counting and are swept by the next subscriber to connect. Every subscriber in a process registers under the shared session's pid, each with its own token, so the count is per subscription as before. TheLISTENURL must reach the same server.
One LISTEN session per process (#799). LISTEN belongs to a session
and cannot be pooled, so every subscriber and blocking stream read in a
process shares one dedicated session per DSN
(popoto.backends.postgres.listen), outside the pool and outside any
transaction-mode pooler. A hub thread owns it: it LISTENs the union of the
channels its subscribers want (the schema's pub/sub channel, its events
channel, for every schema on that DSN), hands each notification to every
subscriber of that channel in arrival order, UNLISTENs a channel when
its last subscriber leaves, and closes the session when none is left.
Measured with POPOTO_POSTGRES_LISTEN_URL tagged by an application_name:
50 Subscribers plus 4 blocking XREADGROUPs in one process held 54
LISTEN sessions before, and hold 1 now; 50 async stream readers
(backend.async_streams(), what StreamConsumer drives) hold 1, and so do
10 subscribers with 5 sync and 5 async blocking readers together
(tests/postgres/test_postgres_listen.py). In the central-database
topology a process therefore costs one LISTEN session on top of
Defaults.PG_POOL_MAX_SIZE pooled ones, however many consumers and
subscribers it runs. The session reports application_name=popoto_listen
in pg_stat_activity unless its DSN names one. Each publish still reads
pg_stat_activity for its count, so its cost grows with the server's
session count.
- A subscriber is live when
subscribereturns. The first subscriber of a channel is live from itsLISTEN; a later one from a barrier -- apg_notifythe hub sends itself on that channel. Postgres delivers notifications in commit order, so a message published before thesubscribecall is never handed to the new subscriber (as a RedisSUBSCRIBEnever receives an earlierPUBLISH), and one published after it returns always is. Nonce de-duplication, the payload limit and glob matching are the subscriber's own, unchanged by the sharing. - Reconnects. When the session drops (
pg_terminate_backend, a server restart, a network fault) the hub reconnects at once, then with backoff (0.2 s doubling to 5 s), re-LISTENs every channel and re-registers every subscription under the new pid on its own session, before any subscriber polls. A pub/sub message published while the session is down is lost --NOTIFYkeeps nothing for a session that is not listening. That is the same gap, in kind, as a Redis subscriber's: messages published while its connection is down are not delivered.publish's count for such a message is usually 0, because the registrations name a dead pid, but not always:pg_stat_activitylags a killed backend by a moment, so a message published just after the drop can still count its subscribers (in a review run, 2 of 34 lost messages were counted). Read the count as advisory -- the subscriptions registered and alive as far as the server knew -- never as a delivery receipt. Order is kept across the gap and nothing is delivered twice. Stream reads lose nothing: an entry is a row, and the reconnect wakes every blocked reader to read again (well before the fallback poll). Re-registration works from a snapshot of each subscriber's subscriptions, never waiting on a subscriber's own lock, so one subscriber in the middle of a slow registration write cannot stall the reconnect for the others (#803). Only the follow-up cleanup -- a snapshot row for a channel unsubscribed meanwhile -- needs that lock, because the channel may have been subscribed again since and its fresh row is indistinguishable from the stale one. The hub takes the lock without waiting; when it is busy, the subscriber holding it does the cleanup as it lets go. That cleanup also deletes the subscriber's rows under any pid but the current one: a registration write that read the old pid can land after the reconnect's own delete, and the old backend may still be listed inpg_stat_activity(a partitioned peer stays until the server's keepalive gives up -- about 2 h by default), where its row would count the subscriber twice. A write that raises skips the cleanup until the subscriber's next poll. Pinned bytest_postgres_listen.py::test_a_terminated_listen_session_reconnects_and_relistens,::test_a_reconnect_mid_stream_keeps_order_and_loses_only_the_gap,::test_a_blocking_stream_read_loses_nothing_across_a_reconnect,::test_a_slow_subscriber_write_does_not_stall_the_reconnect,::test_a_channel_resubscribed_during_the_reconnect_keeps_its_registration,::test_a_busy_subscriber_sweeps_what_the_reconnect_left,::test_a_subscribe_that_read_the_old_pid_leaves_no_row_under_itand::test_a_subscriber_stuck_through_twenty_reconnects_ends_with_one_row_each. - Undecodable payloads (#803). A notification is decoded with the
session's encoding. On a
SQL_ASCIIdatabase (or session) that is theasciicodec, so any non-ASCIINOTIFY-- even valid UTF-8 -- used to raise on the hub's thread and stop every subscriber's delivery. Such a payload is now decoded as UTF-8 with U+FFFD for invalid sequences, logged once per hub at WARNING, and delivered as text like any other; popoto's own payloads are ASCII and never take this path, and pub/sub drops a foreign payload anyway. Pinned by::test_the_hub_survives_a_payload_its_session_cannot_decode. - Dead connections (#803). A session whose peer vanished without a FIN
or RST -- a NAT or load-balancer entry that silently expired -- looks
idle forever to a plain wait, so the hub checks. The session sets TCP
keepalives (
keepalives,keepalives_idle,keepalives_interval,keepalives_countfromDefaults.PG_LISTEN_KEEPALIVES*: probe after 30 s idle, every 10 s, give up after 3) unless its DSN names them, and the hub's wait is bounded: afterDefaults.PG_LISTEN_LIVENESS_INTERVAL_SECONDS(30 s) without traffic it sendsSELECT 1and, if no reply arrives withinDefaults.PG_LISTEN_LIVENESS_TIMEOUT_SECONDS(10 s), drops the session and reconnects as above. A silent drop is therefore noticed within about 40 s, on any platform, whether or not the OS honours the keepalive parameters. Pinned by::test_a_silently_dead_session_is_detected_and_replaced, which freezes the session's traffic in a TCP proxy. - Fork. The session belongs to the process that opened it: a forked
child opens its own on first use and never touches (or closes) the
parent's, and a subscriber the child inherited re-attaches there with a
fresh registration token. An inherited subscriber that the child closes
or unsubscribes before it ever polls there forgets its subscriptions
locally and leaves the parent's
popoto_pubsub_listenerrows alone, so the parent'spublishcount is unchanged (#803;::test_a_forked_child_never_drops_the_parents_registrations). - Back-pressure. A subscriber that stops reading keeps at most 100 000
unread messages (
Defaults.PG_LISTEN_QUEUE_MAX_MESSAGES) and at most 32 MiB of unread payload (Defaults.PG_LISTEN_QUEUE_MAX_BYTES, Redis's default pub/subclient-output-buffer-limithard cap); past either, the oldest are dropped -- the analogue of Redis disconnecting a pub/sub client past that limit. The count cap alone allowed about 800 MB of near-limit payloads per stalled subscriber. Drops are logged at WARNING (the first, then every 10 000th) and counted:pubsub.droppedandpubsub.dropped_bytesper subscriber (queued_bytesis what it holds unread now, in UTF-8 bytes, so a foreign non-ASCIINOTIFYon the channel counts at its real size) andhub.droppedfor every subscriber on the shared session (popoto.backends.postgres.listen.hub_for(dsn)). Both caps are read when the subscriber is made. A stream reader only keeps which streams were notified, so it costs nothing while it is not waiting.
Divergences (stream and pub/sub; Redis behaviour unchanged):
| Behaviour | Redis | Postgres |
|---|---|---|
MAXLEN ~ N (the mixin's _stream_max_length, and XTRIM ~) |
trims whole radix-tree nodes, so keeps at least N, often more |
keeps exactly N, the newest. The probe's documented approx_trim class; pinned by test_postgres_events.py::test_maxlen_trims_exactly_a_documented_divergence |
| An append that fails at the server after an immediate save | the XADD failure is logged and the record stays |
the save raises and nothing is kept (one transaction). An entry that cannot be built (an empty _stream_name) is logged and skipped on both, or raises with a pipeline= on both. Pinned: test_a_failing_append_fails_the_save_and_keeps_no_record |
| Error types | redis.exceptions.ResponseError / DataError |
StreamCommandError / StreamDataError, same text (the Postgres backend never imports redis) |
XADD … MINID/LIMIT, XTRIM … LIMIT, XCLAIM … FORCE, XREADGROUP … CLAIM |
supported | StreamDataError |
| Stream keys, groups, consumers, channels | binary-safe | UTF-8 text without NUL |
A pending row another transaction holds during XCLAIM/XAUTOCLAIM |
single-threaded: never contended | skipped (SKIP LOCKED) |
| A message published inside a transaction | MULTI/EXEC sends it at EXEC |
delivered at COMMIT, never after a rollback |
| A pub/sub message whose payload encodes to 8000 bytes or more | delivered | PubSubPayloadTooLarge |
| Pattern subscriptions | matched by the server | matched by each subscriber (byte-wise, the same result); every subscriber of the schema receives every message |
Stream id parts in (2**63 - 1, 2**64 - 1] |
valid ids | refused by every stream command with StreamIdOutOfRange (a StreamCommandError), since each part is a bigint; no clock reaches them. Past 2**64 - 1 both refuse with Invalid stream ID. XCLAIM with such an id raises the refusal, where Redis returns []. Pinned: test_ids_past_bigint_are_refused_a_documented_divergence |
| A message matching several pattern subscriptions | one pmessage per pattern, in the order of the server's pattern table (unspecified) |
the message (if the channel is subscribed), then one pmessage per pattern in subscription order. The payloads are the same as a multiset. Pinned: test_delivery_and_confirmation_order_a_documented_divergence |
unsubscribe() / punsubscribe() with no arguments |
one confirmation per subscription in the server's table order, count falling to 0 | confirmations in subscription order, count falling to 0 (same pin) |
| A confirmation while nothing is subscribed (an unsubscribe-all of nothing, or one reaching 0) | redis-py's get_message does not read while unsubscribed, so it surfaces only after the client subscribes again |
returned by the next get_message at once (same pin) |
The seeded probe, scripts/probe_events_parity.py, replays random sessions
on both legs -- appends with auto and explicit ids, ranges with every bound
form, deletes, exact trims, group create/destroy, reads (new and history,
NOACK), acks, pending summaries and ranges, claims and autoclaims, XINFO
GROUPS (including entries-read and lag), key deletes, a model's saves,
partial saves, deletes and rolled-back writes, and StreamConsumer batches
with a failing handler (retry, reclaim, dead-letter) -- renames each leg's
ids e1, e2, … in creation order, and compares every reply and error.
test_postgres_events.py runs a 20-shape slice in CI.
REDIS_URL=redis://localhost:6379/13 \
POPOTO_POSTGRES_URL=postgresql://localhost:5432/postgres \
python scripts/probe_events_parity.py --seeds 1 2 3 4 5 6 7 8 --shapes 100
Long-tail fields (M5)¶
Three Redis features that each owned a Lua script are stored natively
(popoto/backends/postgres/longtail.py). Every number a script computes is
computed in SQL operation for operation, in double precision and in the
script's order of evaluation; cos and power are the platform libm on
both servers, as for the decay expression. Numbers a script replies went
through Lua's tostring (%.14g) and numbers it stores through
cmsgpack, which packs an integral number as an integer; Postgres applies the
same rules on the way out. The seeded probe compares both legs bit for bit:
REDIS_URL=redis://localhost:6379/7 \
POPOTO_POSTGRES_URL=postgresql://localhost:5432/postgres \
python scripts/probe_longtail_parity.py --seeds 1 2 3 --shapes 500
CyclicDecayField. The clock is the field's column; the cycles and the
pressure are columns beside it (the table above). Why parallel arrays and not
a single jsonb column: an amplitude is a double the score
multiplies, it can be NaN or ±inf (a declared float("inf") passes the
field's validation), and a jsonb number is numeric, which holds neither
and adds a text round trip to every row a ranking scans.
CYCLIC_DECAY_LUAis an expression the rankings share --top_by_decay(rank_decayed),composite_score's arm for the field (and so the assembler's push path), and the assembler's score proxy:
(SELECT y.dc + y.p FROM (SELECT z.d + z.c AS dc, z.p FROM (SELECT
<DECAY_SCORE_LUA's expression> AS d,
(SELECT 0 + coalesce(sum(u.a * cos(6.283185307179586 * (<now> - coalesce(u.h, 0)) / u.p)
ORDER BY u.o), 0)
FROM unnest(t.f__cycle_period, t.f__cycle_amp, t.f__cycle_phase)
WITH ORDINALITY AS u(p, a, h, o)
WHERE u.p > 0 AND u.p <> 'NaN') AS c,
CASE WHEN t.f__pressure_rate > 0 AND t.f__pressure_rate <> 'NaN'
THEN t.f__pressure_rate
* greatest((<now> - coalesce(t.f__pressure_at, <now>)) / 86400, 0)
ELSE 0 END AS p
OFFSET 0) AS z OFFSET 0) AS y)
shown in its plain form. A row whose cycles or pressure lie outside the box
where no step can leave the double range takes a clamped form instead:
each step through the decay SQL's saturating helpers (plus a saturating divide, and
cos(±inf) = NaN where Postgres would raise "input is out of range"),
and the terms folded left to right by a recursive CTE with a saturating
add. The script's unsplit base * pow equals DECAY_SCORE_LUA's
sign * |base| * pow once + cyclic (at least 0) has turned a -0
into +0, so the decay half is the decay expression unchanged. The script has
no validity gate (its KEYS are all taken), so a cyclic ranking ignores
the gate on Postgres too (TestCyclicDecayGatingGap, and the "Known
limitations" of validity and supersession).
- CYCLES_MERGE_LUA is part of the save's upsert: a new row takes the
declared cycles (baseline = the declared amplitude) and last_resolved =
now; on conflict the period, phase and baseline columns take the
declaration and the amplitudes merge in one sub-select --
f__cycle_amp = (SELECT array_agg(CASE WHEN s.o IS NULL THEN d.a
WHEN s.b IS NULL OR (s.b = d.a AND s.b <> 'NaN') THEN s.a
ELSE d.a END ORDER BY d.o)
FROM (SELECT x.p, x.a, x.o, row_number() OVER (PARTITION BY float8send(x.p) ORDER BY x.o) AS r
FROM unnest(<declared periods>, <declared amps>) WITH ORDINALITY AS x(p, a, o)) d
LEFT JOIN (SELECT y.a, y.b, y.o, float8send(y.p) AS k,
row_number() OVER (PARTITION BY float8send(y.p) ORDER BY y.o) AS r
FROM unnest(t.f__cycle_period, t.f__cycle_amp, t.f__cycle_base)
WITH ORDINALITY AS y(p, a, b, o)) s
ON s.k = float8send(d.p) AND s.r = d.r)
-- the script's period-keyed FIFO pairing and #698's three-way rule, with
the period's bits as its %.17g match key. The pressure rate is
refreshed and last_resolved kept (now for a new entry); no declared
cycles, or a rate <= 0, clears that half. The upsert returns the
previous cycles (RETURNING old.…), from which the save logs #698's reset
line, word for word as on Redis. No second statement: the merge is atomic
with the row it belongs to.
- CYCLES_ADJUST_LUA (strengthen_cycle / weaken_cycle) is one UPDATE …
SET f__cycle_amp = coalesce((SELECT array_agg(<clamp(a * factor)> ORDER BY o) …), '{}')
WHERE f__cycle_period IS NOT NULL RETURNING … behind the record's key lock; a
NaN amplitude passes every comparison untouched, as in Lua. The
coalesce is load-bearing: array_agg over no rows is NULL, and an
empty cycles entry must stay empty, as the script's re-pack of an empty
array does (test_adjusting_an_empty_cycles_entry_keeps_it_empty).
resolve_pressure is one UPDATE of the two pressure columns.
- The factor is read as the script reads it, with Lua's tonumber: C
strtod with only trailing space after it, so "0x10" is 16 and
"0x1p-1" is 0.5 on both legs, while "1_0" and non-ASCII digits are not
numbers (lua_tonumber, checked case by case against the server's own in
test_lua_tonumber_matches_the_servers). A factor that reads as nil
(None, "abc", True) fails the script's first multiplication, so
Redis writes nothing and raises ResponseError; Postgres raises
ValueError with the same text, before any write -- never a NaN
amplitude. As on Redis, it is reached only when there is a cycle to
multiply: no entry still returns [], and an empty entry stays empty.
TDValueField. TD_UPDATE_LUA is one statement behind the record's key
lock: the row locked FOR UPDATE, td = target - q and q' = q + alpha * td
(with target = reward + gamma * max_future_q evaluated first, as the
script does), and q' stored as the numeric of the script's
tostring(q') -- to_char(q', '9.9999999999999EEEE') is C's %.13e, the
same 14 significant digits as %.14g, with its trailing zeros trimmed. The
reply is tostring(td). The constants enter through a MATERIALIZED CTE, so
the planner cannot fold a subnormal one into a plan-time "underflow".
The stored numeric is read as the script's tonumber of its text: a
Decimal past the double range is ±inf and one below half the smallest
subnormal (2**-1075) is ±0, at strtod's exact rounding boundaries,
where a plain ::float8 cast raises "value out of range"
(test_td_update_reads_an_out_of_range_value_as_tonumber_does).
recipes/policy_cache.py runs unchanged on a Postgres-bound PolicyEntry.
PredictionLedgerMixin. The $PL:{Class}:meta:{pk} entry is a row of
popoto_prediction_ledger (model, member, entry jsonb, lua_packed) and the
$PL:{Class}:errors:{part} sorted set is popoto_prediction_error (model,
part, member, error), engine tables created on first use like the recipes' ones.
RESOLVE_PREDICTION_LUA is one statement:
WITH r AS (UPDATE popoto_prediction_ledger
SET entry = entry || <{resolved, prediction_error, resolution_mode, resolved_at}>,
lua_packed = true
WHERE model = $m AND member = $k AND jsonb_typeof(entry) = 'object'
AND (entry->'resolved' IS NULL OR entry->'resolved' IN ('null', 'false'))
RETURNING 1)
INSERT INTO popoto_prediction_error (model, part, member, error)
SELECT $m, $part, $k, abs($error) FROM r
ON CONFLICT (model, part, member) DO UPDATE SET error = EXCLUDED.error
RETURNING 1;
-- Lua truthiness for resolved, and ZADD as an upsert. The script
re-packs the whole entry with cmsgpack, so lua_packed makes a read apply
the same transformation: an integral number becomes an int, an empty map a
list, a nil field is dropped, an array with a hole a map, a table 16 deep
nil. A prediction holding bytes cannot be resolved -- Redis's cmsgpack
reads no msgpack bin ("Bad data format"), so the script's pcall fails --
and that refusal is reproduced. get_highest_errors is ZREVRANGE's order
(error descending, ties by member bytes descending) and rank arithmetic
(limit <= 0 counts from the end). Like the Redis keys, the ledger is not
removed when the record is deleted. The entry is jsonb with this module's
tags for what JSON lacks (bytes, a non-finite or exponent-form float, a
-0.0, a non-str key), never msgpack.
ObservationProtocol applies the whole effects matrix on Postgres now,
in the order the Redis functions apply it and inside the batch's one
transaction: strengthen_cycle and resolve_pressure on acted,
weaken_cycle on dismissed and contradicted, auto_resolve on every
outcome but deferred (with its confidence feedback on the batch's
connection, so it never waits on the batch's own row lock), and the
pressure auto-discharge on contradicted, reading the confidence the batch
just updated. Every ledger and cycle write takes the record-key lock the
batch already holds, so the one lock order is kept; a rolled-back
batch resolves nothing.
Expiry and popoto.batch(). On a Meta.ttl model every long-tail
statement that addresses the record row -- the ledger's EXISTS guard, the
cycle adjustment, the pressure write, the cycle export and import, and the
TD update -- carries #783's live-row filter, so an expired record is no
record to it. A popoto.batch() handed to strengthen_cycle,
weaken_cycle, resolve_pressure, td_update or the ledger's writers
joins the batch's transaction, as a save does: nothing lands before
execute(), a reset() writes nothing, and the record locks it takes are
the batch's (test_long_tail_writes_join_a_batch).
Index maintenance and transfer (M5)¶
check_indexes(), clean_indexes(), rebuild_indexes() (and their
async_ twins, which run them on the async backend: every statement,
REINDEX included, is an AsyncConnection on the running loop),
raw_update() and popoto.transfer all work on a Postgres-bound model. The
code is backends/postgres/maintain.py.
What drifts¶
On Redis every index is a separate key written by a second command, so it
can point at a hash that is gone. On Postgres the class set, key-field sets,
sorted sets, geo set and composite-index hashes are the table and its
B-trees, maintained by the server inside the row's own transaction. Their
counts keep their Redis keys in the check_indexes() dict and are always
0. What can drift is the state popoto keeps in companion tables:
| Side table | Orphans (side rows naming no record) | Missing (live record, no side rows) | Stale (live record, wrong side rows) |
|---|---|---|---|
BM25 postings and lengths (__post, __dl) |
counted per row | tokens but no postings and no length | postings or length that are not the tokenization of the source column, or a scope other than the record's |
Narrow vector rows (__vec) |
counted per row | the record has a vector but no narrow row | a narrow row whose vector or scope differs from the record's, or a narrow row for a record with no vector |
ExistenceFilter tokens (__tok) |
counted per row | a fingerprint token of the record's current values is absent (extra tokens are membership_add's, never drift) |
— |
Validity open-claim pointers (__open) |
counted per row | — (carried state) | — |
CoOccurrenceField edges (__edge) |
never an orphan: see below | — | — |
The first four companions name a record through REFERENCES … ON DELETE
CASCADE, so popoto's own deletes never leave an orphan there. Only a load
or restore run with triggers disabled (session_replication_role = replica,
pg_restore --disable-triggers, the shape a #756 copy may take) can.
Edges are different, and they match Redis: an edge has no foreign key,
because Redis links any two key strings, records or not. An edge whose
endpoint has no record is kept, by clean_indexes() and
rebuild_indexes() and so by check_indexes()' total. That covers a key
that was never saved, a record the TTL reaper removed, and a record deleted
outside popoto (a raw SQL DELETE, or a Redis DEL of the hash). Only
popoto's own delete() removes a record's edges (and, in a symmetric field,
the reverse edges). check_indexes() reports the edges whose endpoint is
missing apart from the drift, as
side_tables["graph_edges"]["dangling"], which total leaves out, so a
Meta.ttl model with edges reports total == 0 after a reap, as on Redis.
dangling counts a missing src in every field and a missing dst in a
symmetric field, both only for keys in the model's own key space
(<Model>:); an asymmetric edge to a deleted record is not counted. It is
information for a person to act on; remove such an edge with unlink. Pinned
on both legs: test_backend_parity_ttl.py::test_edges_to_never_saved_endpoints_survive_maintenance
and ::test_a_ttl_reaped_records_edges_survive_maintenance; on Postgres,
test_postgres_maintain.py::test_dangling_edges_are_reported_apart_and_kept.
Missing and stale rows come from direct SQL, or from raw_update() of a
source column, which, like the Redis method, runs no hooks.
Invalid indexes. A REINDEX ... CONCURRENTLY that fails (a lock
timeout or a cancel) leaves INVALID *_ccnew indexes behind. Every write
still maintains them, including an extra HNSW index, no read uses them, and
a later REINDEX does not remove them. check_indexes() counts them as
invalid_indexes. clean_indexes() and rebuild_indexes() drop them with
DROP INDEX CONCURRENTLY, skipping any table that has an index build in
progress.
Not checked, matching Redis:
FrequencySketchcounts, which are never decremented, as the sketch never is.- The shared engine tables keyed by
(model, member)with no foreign key:popoto_tombstone,popoto_embedding_cache,popoto_recall_proposal,popoto_prediction_ledgerandpopoto_prediction_error. Each outlives its record on Redis too, and Redis does not check them either. - State held in the record row's own columns: confidence, access tracking, validity intervals and cycles.
- The validity pointers' content, which is carried state. Only their orphans count.
EventStreamMixin's events (the backend's events tables, not the model's
table) are not checked, cleaned or rebuilt here.
partial_writes keeps its Redis meaning on a model whose key is a single
AutoKeyField: a live row whose auto-key column is NULL or ''.
result = Memory.check_indexes()
# {'class_set': 0, 'partial_writes': 0, 'key_fields': {...}, 'sorted_fields': {...},
# 'geo_fields': {}, 'composite_indexes': {},
# 'side_tables': {'lexical': {'orphans': 0, 'missing': 0, 'stale': 1}, ...},
# 'invalid_indexes': 0, 'legacy_hash': [], 'stale_bloom_staging': [],
# 'total': 1}
legacy_hash and stale_bloom_staging are always [] here. They describe
Redis bloom filters (v1-hash filters, and staging keys left by a dead v2
conversion), and Postgres keeps an exact token table instead of a bit array.
rebuild_indexes(bloom_hash_version=2) is accepted and has no further effect.
total sums partial_writes, invalid_indexes and every side_tables
count.
What each operation does¶
check_indexes()is read-only and takes no locks. It counts orphans with one anti-join per side table, then pages over the live records inbatch_sizekeyset pages (_pk COLLATE "C"). For each page it derives the side rows a save would write, using the same tokenizer, scope and fingerprint as the save path, and compares them with what is stored.clean_indexes()deletes orphan side rows (never edges), partial-write rows and invalid indexes, and returns how many. Each page of orphans is deleted behind its keys' record locks and re-checked in the statement, so a record saved under that key in the meantime keeps its rows. Inside an opentransaction()or batch it leaves invalid indexes alone, because the drop would wait on that transaction. Missing and stale rows are left forrebuild_indexes(), which needs the record's values, as on Redis.rebuild_indexes()first drops the invalid indexes left by an earlier failed run. It then pages over the live records. Each page is one transaction that takes the page's record-key locks first, in_pkbyte order (the backend's one lock order), then reads the rows and rewrites only the drifted records' side rows. It then removes orphans and runsREINDEX TABLE CONCURRENTLYplusANALYZEon the table and every companion.REINDEXrepairs a corrupt B-tree, the only way a Postgres index drifts.CONCURRENTLYkeeps writes flowing while it runs. The return value is the Redis one: anintof records indexed, with.diverged_keyslisting rows whose key columns no longer produce their stored_pk(direct SQL on a key column). Those rows are skipped, as Redis skips a hash whose derived key differs from its own. The rebuild never calls the embedding provider: the vector is carried state, and the backfill owns its freshness.
How a rebuild's REINDEX is bounded¶
REINDEX ... CONCURRENTLY waits for every transaction that holds a lock on
the table to finish. That includes an idle-in-transaction session that wrote
the table, such as another agent's open transaction on the shared server,
and it includes the caller's own open transaction. Three rules keep that
wait from hanging the call or being mistaken for an outage:
- Inside a transaction it is refused. Calling
rebuild_indexes()while atransaction()or apopoto.batch()holding Postgres writes is open in the same thread or task (or in a parent task awaiting it) raisesBackendCapabilityErrorat once. Without the refusal theREINDEXwould wait on that transaction forever. Run it after the batch executes. - Every wait is bounded.
REINDEXandANALYZErun on a dedicated autocommit connection that is opened for the call and closed afterwards. It never takes a pool slot, so its session settings cannot leak to other callers. That connection setslock_timeout = Defaults.PG_MAINTAIN_LOCK_TIMEOUT_MS(10 s) andstatement_timeout = Defaults.PG_MAINTAIN_STATEMENT_TIMEOUT_MS(30 min). The statement timeout cancels any single statement that runs longer, so a legitimateREINDEXof a very large table (an HNSW index especially) is cancelled at 30 min too, and a retry fails the same way: raise that default for such a table. Behind PgBouncer in transaction mode those sessionSETs do not stick: aSETlands on whichever server connection ran it, solock_timeout,statement_timeoutandclient_connection_check_intervalcan leak into other clients' transactions, and theREINDEXitself may run on a server connection that never got them, and wait without a bound again. Run maintenance on a direct or session-mode connection: setPOPOTO_POSTGRES_MAINTENANCE_URL(ormaintenance_dsn=), and this connection, and the oneclean_indexes()drops leftover indexes on, opens through it while every other query keeps the pooled DSN (see A maintenance DSN). - Stopping early is not an outage. A lock timeout (
55P03), a statement timeout or cancel (57014), a deadlock or any SQL error raisesMaintenanceIncompleteError. It is aBackendRetryableError, because rerunning the rebuild is safe. The backend'shealthis untouched and no dropped write is counted. The error reports what completed:.completed("side_rows","orphans", then"reindex <table>"for each table that finished),.failed_step,.indexed,.diverged_keys, and.invalid_indexes. Every side-row repair before the failure has committed. It tries once at once to drop the*_ccnewindexes the failure left, with a shortPG_MAINTAIN_CLEANUP_LOCK_TIMEOUT_MS(1 s). The session that blocked theREINDEXusually blocks that drop too, so any it could not drop are listed and left for the nextcheck,cleanorrebuild. A lost connection or a server shutdown is still an outage (BackendUnavailableError).
from popoto.backends import MaintenanceIncompleteError
try:
Memory.rebuild_indexes()
except MaintenanceIncompleteError as exc:
log.warning("rebuild stopped at %s after %s", exc.failed_step, exc.completed)
# rerun later; check_indexes()['invalid_indexes'] shows any leftovers
raw_update(keys, **values) is an UPDATE of those columns on the rows
that exist, with no hooks, no auto_now and no side-table work.
Every operation honours the TTL read filter. An expired row is neither checked nor rebuilt, and its side rows belong to the reaper, so they are never counted as orphans.
Transfer¶
export_records / import_records and the popoto-transfer CLI use the
same JSON Lines format on both backends. The format carries each record's
field values plus each field's semantic state. It never carries Redis
structures or SQL, so it reads the same whichever backend wrote it. Three
transfer paths now go through the backend on a Postgres model:
- import's existence check, which uses
existsinstead of a RedisEXISTS; - a filtered export, which runs one id-only
selectcompiled from the same filter; - the carried state of
ConfidenceField(the four state columns),EmbeddingField(the vector column, exported as the same float32.npybytes the Redis backend keeps on disk) andAccessTrackerMixin(access_count,last_accessed).
CyclicDecayField, PredictionLedgerMixin, ValidityField and
CoOccurrenceField already went through the backend. Rebuilt fields (BM25,
ExistenceFilter, the indexes) are rebuilt by the import's save, as on
Redis. preserve_keys=False mints keys and remaps Relationship values the
same way on both backends.
popoto-transfer's database-0 refusal applies only to a transfer that uses
Redis: a Postgres-bound model transfers without --allow-db0 even when Redis
is unset or on database 0, and the CLI refuses any Redis command such a run
would issue instead of letting it reach database 0
(Refusing database 0).
Cross-backend. An export taken from a Redis-bound model imports into the
same model bound to Postgres. A re-export then matches the Redis export, and
to_dict() matches for every record. This is pinned for a plain model
(key, indexed, tag, relationship, collection, decimal, datetime and sorted
fields), Valor's memory slice (decay, confidence, BM25, embedding,
existence, access tracking) and the carried long tail (validity and
supersession, co-occurrence edges, cycles and pressure, the prediction
ledger) in
tests/postgres/test_postgres_transfer.py::test_a_redis_export_imports_into_postgres_and_reads_identically.
That is most of what #756's one-off copy needs. The gaps are listed in
the next section.
Cross-backend migration notes (for #756)¶
Everything a Redis → Postgres copy loses or changes when it goes through
popoto.transfer, and what to do about it:
| What | What happens | What #756 must do |
|---|---|---|
ContentField |
A Redis export carries the $CF:<sha>:<Model>/<key>.txt file reference, not the text. A Postgres import resolves it through the content store, so it works only if the same POPOTO_CONTENT_PATH is reachable. A Postgres re-export carries the inline text, so the re-export is not byte-identical to the Redis export. |
Copy the content files out of band, or make them reachable, before importing. Compare content by value, not by the export bytes. |
| Per-record TTL | Not carried on either backend. The format has no TTL. An imported record expires Meta.ttl after its import, whatever was left of a per-instance _ttl (3570 s left becomes 60 s on a Meta.ttl = 60 model). An expired record is not exported at all. |
Re-apply any per-record TTL after import, or accept Meta.ttl. Pinned: test_a_per_record_ttl_is_not_carried_on_either_backend. |
FrequencySketch |
Not carried on either backend. It is rebuilt from the imported saves, so it counts each imported record once, and counts that came from records deleted before the export are lost. | Accept the reset. The sketch is approximate by design. |
AccessTrackerMixin's confirmed access log |
Postgres keeps no access log (see the divergences below). access_count and last_accessed cross. |
Nothing, unless the log is needed. |
| A cycles entry's declared-baseline slot | It is deployment-local, and import_state drops it on both backends (#698). |
Re-declare baselines on the destination. |
GeoField |
Nothing geo-specific is carried: the import re-saves the coordinates and the save rebuilds the geo columns (roundtrip_policy = "rebuild"). |
Nothing. |
DataFrameField |
Refused on Postgres. | Keep those models on Redis. |
EventStreamMixin |
The record crosses; the event stream does not (the format carries records, not the stream or its consumer groups). | Out of scope for the copy. |
CoOccurrenceField |
An import truncates each record's edge set to the destination field's max_edges, on both backends. Edges beyond it are dropped. |
Raise max_edges on the destination before the import if the source's sets are larger. |
WriteFilterMixin priority tier |
Not stored on Postgres: the gate still decides whether a record is saved, but no priority set is kept, and composite_score({"priority": …}) raises BackendCapabilityError. |
Priority sets do not carry over. Re-derive priority from the records if you need it. |
Memory telemetry (AssemblyEvent, outcomes included) |
Runs on Postgres; pinned on both legs by tests/test_memory_telemetry.py. A copy re-saves each event, so its Meta.ttl expiry restarts at the import. |
Name the model to copy it (--model popoto.recipes.memory_telemetry:AssemblyEvent); otherwise its keys are out of scope and Postgres starts empty. |
The one-off copy itself is
Redis to Postgres Migration: it serves the
snapshot from a private redis-server, handles each row of this table,
writes _migrated_from/_estimated_fields, and merges several machines'
stores into one schema.
Geo (M5)¶
A GeoField needs no extension: plain columns reproduce what Redis does
with a geo set, from its own C (geohash.c, geohash_helper.c, geo.c at
8.10.2), in popoto/backends/postgres/geo.py. The radius filters
(<f>=(lat, lon) or <f>_latitude/<f>_longitude, <f>_member,
<f>_radius, <f>_radius_unit, <f>_with_distances) behave as on Redis,
inside Q objects too, through filter(), count() and their async_
twins.
What is stored. Redis keeps a member's position as a 52-bit geohash, the
zset score, and measures from the cell's center, never from the coordinates
you saved. So beside the coordinates as given (jsonb, which is what a read
returns on both backends), a save writes:
<f>__geohash: the scoreGEOADDstores. It isgeohashEncodeWGS84at step 26, converted to adoubleas the zset holds it. That conversion matters at the edges. A point on the latitude limit (±85.05112878) sets bit 52, and one on longitude 180 sets bit 53, where thedoublerounds to a neighbouring cell. Redis then stores that neighbour, and so does Postgres.<f>__geolon/<f>__geolat: the position decoded from the score, i.e. whatGEOPOSreturns.
A value with a falsy latitude or longitude (None, 0) is not indexed
(NULL score), as GeoField.on_save ZREMs it. A delete removes the row,
and with it the point. A partial save writes these columns only when it
writes the field.
How a search runs. The nine geohash boxes Redis scans are computed in
Python: its step estimate, its "decrease the step" check, and its pruning of
useless neighbours. One statement fetches the rows whose score falls in those
[min, max) ranges. That is the bounding-box prefilter, served by the
partial B-tree; on a Meta.ttl model it fetches live rows only, and it is
narrowed by the query's other top-level filters. A search by member reads
the member's position whether or not its record has expired, as Redis's
geo set still holds an expired member (see the divergences below). Each
candidate's distance is then geohashGetDistance, Redis's haversine on
its 6372797.560856 m earth radius with its equal-longitude shortcut. A point is in when
distance <= radius * conversion. The matched keys scope the query's own
statement (_pk = ANY(…)), so limit, order_by, values= and the
other filters apply in SQL. With with_distances, each row carries
_geo_distance as WITHDIST replies it (the meters divided by the unit's
factor, printed with four decimals, ties to even), plus
_geo_distance_unit. Rows sort by that distance after any order_by term
and before the key tie-break, which is how the Redis path re-sorts its
hydrated objects. This is QueryPlan.compute (ComputedCol("_geo_distance",
…)).
Arithmetic: plain IEEE doubles, never fused. A Postgres deployment has
no Redis to mirror, so the port never depends on how a compiler built
Redis: each a*b + c in Redis's C is evaluated as two roundings, as CPython
evaluates it. The geohash encode and decode use no libm, so the stored
score and decoded position are the same on every platform and equal to an
x86-64 Redis's (GEOPOS) to the bit. A Redis built by clang on arm64 (Homebrew 8.10.2, for
one) contracts two expressions into fused multiply-adds: the decode's
min + (i / 2**step) * scale and the haversine's
u*u + cos(lat1)*cos(lat2)*v*v. Against that build, a decoded position
(GEOPOS) or a distance is one fused rounding away from this one in places.
For a position that is at most an ulp of the decode's product (about
1.4e-14 degrees), which is thousands of ulps of a latitude near zero, where
the decode's subtraction cancels. For a distance it is usually an ulp or
two, but near the antipode asin's slope amplifies one rounding of the
haversine's a: the seeded probe saw up to about 0.19 m on a
2×10^7 m distance. The consequence that reaches a query is at the radius:
a member whose distance is that close to the radius can be in on one build
and out on the other, and a WITHDIST value can differ in its fourth
decimal when the gap straddles a rounding boundary.
The distance also calls sin, cos and asin, from the host Python's
libm, as Redis calls its own. Mainstream libms (glibc, musl, Apple's) are
accurate to under an ulp but do not agree on the last bit, so a distance
can differ by those ulps (propagated through the formula; again up to tens
of centimetres near the antipode) between the port and a Redis linked
against another libm, and between ports on different hosts. CI shows it:
redis:7-alpine links musl and the runner's Python glibc, and with
positions identical, a handful of radius tests in the seeded probe come out
on opposite sides of a radius that close. sqrt is IEEE, correctly
rounded everywhere.
The exact test runs in Python rather than SQL so the save's decode and the
search's test share one implementation.
Quirks reproduced, not repaired. A point whose score is 2^52 or more
(the latitude limit, longitude 180) lies past every search box, so no radius
search finds it; it is stored all the same. A search by member of a field
with nothing indexed replies empty without looking at the member or the
radius, as GEORADIUSBYMEMBER on a missing key does. An inf radius is
accepted and covers the whole indexed set. redis-py refuses a bool or a
Decimal center or radius before anything is sent, so its message comes
before any server-side check.
PostGIS (a geography column and a GiST index) could replace the box
scan later as an optimisation. It would not change the arithmetic above, and
it is not a dependency.
The seeded probe compares both legs against the running Redis, including
the stored score and decoded position against ZSCORE/GEOPOS, and each
leg's exact distance read off its own radius test (in at d, out one ulp
below). Every comparison is exact, except five classes that count apart:
four for arithmetic, and nan_distance_arm64 (the NaN reply below,
against a Redis that is not x86-64).
fma_position_ulp (a position or distance one fused rounding away) and
fma_boundary (a search or count differing only by members the contraction
moves across the radius) exist only against a contracting build, and are
not a tolerance: a mismatch joins one only when a model of the same C with
the two contractions reproduces the running Redis to the bit. libm_ulp
and libm_boundary exist only against a Redis on another libm: a distance
(never a position) within the envelope the formula gives when each of its
five libm results is off by at most one ulp, or a search differing only by
members whose envelope straddles the radius.
Which of these classes a run allows is measured, not assumed. Before the
first seed the probe stores a fixed battery of 2,000 points on the running
Redis and reads back each position (GEOPOS) and the exact distance Redis
measures to a center. The fma_* classes are on only if some position is
the fused decode's and not the plain one's. Positions call no libm, so they
alone decide it. The libm_* classes are on only if some distance is
neither model's on the probe's own libm, or if Redis reports another OS in
INFO server. Against a Redis that shares the probe's arithmetic both are
off, so any search or count that differs is undocumented. On arm64 macOS
with Homebrew Redis the report reads contracting (fma_* on), this host's
libm (libm_* off), with 0 of 2,000 distances unexplained. On CI
(redis:7-alpine against the runner's glibc Python) every position is
plain and 129 distances are unexplained, so the result is fma_* off and
libm_* on. --libm and --fma force either pair on or off.
A search or count that differs is accepted member by member, never by the
size of the gap. Each member the two legs disagree on must be in the class's
boundary set, and the Postgres leg must answer it the way the probe's own
plain model does, computed from the stored score with the probe's constants
rather than the port's. A count only says how many rows matched, so when the
counts differ the probe reads each leg's rows for the same parameters to
find which members are involved. In the #789 review's planted bugs (seeds
1–3 × 300, arm64), the search and count stage now flags the earth radius
wrong in its 9th digit with 565 filter and 474 count mismatches, where
libm_boundary used to absorb 718 and leave 208 and 39. It flags >=
instead of > with 294 and 303, where 423 used to be absorbed and 87 and 87
were left.
REDIS_URL=redis://localhost:6379/7 \
POPOTO_POSTGRES_URL=postgresql://localhost:5432/postgres \
python scripts/probe_geo_parity.py --seeds 1 2 3 --shapes 500
Divergences (each counted apart by the probe):
| Redis | Postgres | |
|---|---|---|
Saving a point GEOADD refuses (lat past ±85.05112878, lon past ±180) |
the hash is written, then GEOADD fails: the record exists, unindexed, and the save raises ResponseError |
refused before writing: ValueError with the same text (invalid longitude,latitude pair …) |
| A search the server refuses (bad center, bad or negative radius, a member that is not indexed) | ResponseError |
QueryException, same text |
A record that has expired (Meta.ttl) |
its geo-set member stays (no read purges it): filter() drops it at hydration, but count() keeps counting it |
invisible to every search and count() at once |
A search by an expired member (Meta.ttl) |
GEORADIUSBYMEMBER still decodes the stale member and searches around its last position: filter() returns the live rows near it ([] when none are), count() counts the stale members too. A member that was never saved, while only stale members are left, raises ResponseError: could not decode requested zset member |
the same rows, searched around the expired row's last position, while the row is still in the table. count() counts live rows only; the never-saved member raises QueryException with the same text. Once the reaper has deleted the row (after a later write to the model), the member is missing and the search raises that QueryException, as Redis does after clean_indexes or a delete |
A distance that computes to NaN (asin of a rounding-inflated argument above 1; near-antipodal points under a huge or inf radius, not reached in 10 million centers at or within two ulps of a stored point's antipode on arm64) |
the member is in (NaN > radius is false). WITHDIST prints llrint(NaN * 10000): -922337203685477.5808 on x86-64 (LLONG_MIN), which sorts it first; 0.0000 on arm64 |
as x86-64, CI's reference: -922337203685477.5808 (geo.NAN_DISTANCE_REPLY). Against arm64 Redis the probe counts the row as nan_distance_arm64 |
A geo leaf scoping a ranking or a search (rank_decayed(where=…), recall) |
the leaf's key set | BackendCapabilityError: a geo filter scopes filter() and count() only, for now |
| A Redis built with floating-point contraction (clang, arm64) | the decode and the haversine round a*b + c once: positions and distances one fused rounding away (up to ~0.19 m near the antipode), so a member that close to the radius can flip |
plain IEEE arithmetic: positions equal to x86-64 Redis's to the bit, on every platform |
A Redis linked against another libm (musl in redis:7-alpine, against glibc or Apple's) |
sin/cos/asin from its libm |
from the host Python's: a distance can differ in its last bits, so a member that close to the radius can flip |
Two leaves asking for distances in one query (two geo fields, or a geo leaf
in each branch of a Q) merge their distances in predicate order, and the
last leaf's unit wins. On Redis the merge order follows its own evaluation
order, which this does not promise to match.
Topology and the outage contract¶
The deployment model is one central Postgres for every agent and machine.
Each process opens one psycopg_pool.ConnectionPool per DSN, and opens it
again after a fork. Its max_size is Defaults.PG_POOL_MAX_SIZE (4).
Popoto holds no session state. Each operation is a single message,
SET LOCAL statement_timeout = …; <statement>, which runs as one implicit
transaction. That makes it atomic and costs one round trip. It is also safe
behind PgBouncer in transaction mode, which you should put in front once
you have more than a few dozen clients. Advisory locks are only ever
pg_advisory_xact_lock.
One connection per unit of work (#776). A transaction() (or a
popoto.batch() holding Postgres writes) holds one pooled connection, and
every statement popoto issues for that unit -- its writes, and the reads a
write makes first (pre_save's uniqueness read, ValidityField's stored
valid_from, the observation and provenance-journal pre-flights, the
neighbour-link sweep of AppendOnlyMixin.hard_delete(..., pipeline=uow), the
never-record tombstone of a save refused inside the unit) -- runs on it, and
commits or rolls back with it.
First use takes no pooled connection either. A model's first use in a
process (and the first use of an engine side table: event streams, recipe
tables, popoto_recall_proposal) checks the catalog, and when that happens
inside a unit the check runs on the unit's own connection: to_regclass
plus the model's popoto_schema record, which is all a table that already
exists and is current -- the production case -- ever needs, so there is no
DDL and no second connection. Only when the table is missing or needs an
additive migration does DDL run, and DDL must commit on its own, so it runs
on a dedicated connection opened outside the pool for that one call and
closed after it, never on a pool slot the open units may all be holding.
Before this, first use inside a unit took a second pooled connection: with
the pool full of open units (4 async units on a fresh model at the default
pool size of 4) every one of them failed with BackendBusyError. Outside a
unit, first use runs on a pooled connection as before.
That DDL must never wait on the unit that triggered it: an ALTER TABLE on
the dedicated connection waits for every lock on the table, including the one
the open unit took when it read or wrote it, and the unit cannot commit while
its own task waits. So before any DDL, popoto checks pg_locks for the open
units' backends on that table (and its validity pointer tables); if they hold
any lock and the table needs a change, the operation raises
SchemaDriftError at once, naming the lock. The shape that reaches it is a
model that gained a field after the unit already used its table. (Before
826, an auto-key model whose table a query bound before its first instance¶
reached it too, when that instance added _auto_key; the field is now
registered when the class is created, so the first bind already has it.) Let the schema change
happen outside the unit -- use the model once before opening the
transaction -- or commit the unit first. A lock held by another session
cannot be refused up front, so every DDL statement popoto issues -- on a
pooled or a dedicated connection, for model tables, validity pointer tables
and the engine side tables alike, sync and async -- runs under SET LOCAL
lock_timeout = Defaults.PG_DDL_LOCK_TIMEOUT_MS (5 s) and SET LOCAL
statement_timeout = Defaults.PG_DDL_STATEMENT_TIMEOUT_MS (5 min) (SET
LOCAL, so a pooled connection is never left modified). The 5 s limit bounds
only the DDL statements themselves. The wait for the schema's DDL advisory
lock, and the "is this table current?" catalog read after it, run under the
much longer Defaults.PG_DDL_SCHEMA_LOCK_TIMEOUT_MS (5 min, a backstop), so
during a rolling deploy a new worker whose models are already current waits
for another worker's long migration and then proceeds, rather than failing
at 5 s. The wait cannot deadlock: every holder of those locks is itself
bounded by the 5 s DDL limit. Without it, a thread
with no unit open could wait on another thread's open unit while holding
the first-use lock, and that thread's next first use would then wait on the
lock: a deadlock the server cannot see. A wait past either timeout raises
BackendRetryableError (contention, not an outage: health is untouched),
the backend's first-use lock is released for every other thread, and nothing
is memoised, so the next use runs the check again. Three notes. A
SchemaDriftError raised from that up-front refusal leaves the unit usable:
no statement ran on its connection, so you can catch it inside the
transaction(), keep saving and commit. A lock a unit holds only on a
companion table (BM25, edges) is not visible to the up-front check; that case
ends in the lock timeout as BackendRetryableError, never in a hang. And a
single DDL statement that legitimately runs longer than
PG_DDL_STATEMENT_TIMEOUT_MS (a b-tree build on a populated column of very
roughly 100M rows) is cancelled, and every retry would rebuild it and be
cancelled again: raise Defaults.PG_DDL_STATEMENT_TIMEOUT_MS in the process
that first uses such a table. What you issue
yourself outside the unit while it is open -- a query inside with
transaction(), a save without pipeline=, a nested batch -- still takes a
connection of its own and sees committed state, as a Redis read inside a
batch sees nothing queued. popoto.backends.postgres.STRICT_UNIT_CONNECTION
is a debug guard (off by default; popoto's own test suite turns it on): with
it set, a pooled checkout while a unit is open in the same task or thread
raises SecondConnectionError unless the block is inside
backend.second_connection_ok(). No popoto code path declares that scope:
it is for a caller who reads or writes outside the unit on purpose.
Stale connections. A server restart, a failover or an idle reaper kills
the pool's backends while the server stays up. The pool checks each connection
when it hands it out (ConnectionPool.check_connection, one empty-query round
trip), so a dead one is replaced rather than used. If a connection dies while a
statement is running, the statement is retried once on a fresh connection only
when it cannot have committed: always for a read, and for a write only when the
server reported an error for it (such as AdminShutdown), because the server
rolls that statement's transaction back. A write whose reply was lost may have
committed, so it is not retried. It raises BackendUnavailableError and counts
as a dropped write. The count means "no confirmed reply", not "did not land":
a write that committed but lost its reply (for example the connection dying
while the server waits on a synchronous standby) is counted as dropped even
though the row is there. Check the row before replaying such a write. Statements inside a transaction() are never retried.
When Postgres is unreachable, or a connect or statement timeout fires
(Defaults.PG_CONNECT_TIMEOUT_SECONDS, Defaults.PG_STATEMENT_TIMEOUT_MS),
the call raises BackendUnavailableError. To catch an outage on either
backend, catch popoto.backends.OUTAGE_ERRORS (the redis-py
ConnectionError/TimeoutError pair plus BackendUnavailableError), not
popoto.redis_db.OUTAGE_ERRORS, which is the Redis pair only. It is the
tuple SubconsciousMemory, ContextAssembler, the harness integration's
one-attempt breaker and popoto-transfer all test against (#816), so a
Postgres outage re-raises from the recipes instead of reading as "no
memories". BackendRetryableError is not in it. The backend's health record
tracks the outage:
backend = popoto.backends.get_backend(Note)
backend.health.as_dict()
# {'ok': False, 'last_ok_at': ..., 'consecutive_failures': 3,
# 'dropped_writes': 2, 'last_error': 'PoolTimeout: ...'}
The outage is logged at ERROR once per Defaults.PG_OUTAGE_LOG_WINDOW_SECONDS
(60 s), however many calls fail within that window. The
BackendUnavailableError each failed call raises does not log a second time,
so a host application's logs carry one outage line per window. After a successful call
the record resets, and a recovery is logged at WARNING.
A busy pool is not an outage. When every connection the pool may hold
(Defaults.PG_POOL_MAX_SIZE) is checked out by other callers -- more
concurrent async with transaction() blocks or async_* calls on one event
loop than that, or more threads -- a caller waits up to
Defaults.PG_CONNECT_TIMEOUT_SECONDS for one to come back, and then raises
popoto.backends.BackendBusyError, a subclass of
BackendRetryableError. Every connection is in use, so this is contention:
the health record is not touched and no dropped write is counted; the call
sent nothing, so running it again is safe:
from popoto.backends import BackendBusyError
try:
await note.async_save()
except BackendBusyError:
... # back off and retry, or bound your concurrency below PG_POOL_MAX_SIZE
A connection that cannot be opened is still BackendUnavailableError and
an outage. The sync psycopg_pool reports both cases as one PoolTimeout,
so on the sync path popoto calls a timeout busy only when every connection
of the pool is checked out to a caller at that moment. The async pool opens
connections inline, so it can tell more: a wait that times out is busy when
every slot is checked out, and otherwise it is decided by what the server
did. Some slot is then held by a caller still validating an idle
connection (the checkout check's round trip) or connecting, and the
waiter waits -- at most one connect timeout more -- for that round trip's
outcome: one that reaches the server makes the wait contention
(BackendBusyError), a connect that fails (refused, timed out at the
socket) makes it an outage, and none answering within the connect timeout
is an outage too. So a slow but reachable server under contention -- a
loaded CI runner, where a round trip can straddle a 1 s checkout timeout --
is busy and leaves health untouched, while during a partition (an
unresponsive server, a black-holed port) callers sit inside the connect
holding slots, the waits queued behind them end as outages, health flips and
each write is counted dropped. Classifying by slot state alone counted the
first case as an outage, which is what turned main red after #784. The
"every slot checked out" rule still says nothing about the server's state,
only the pool's -- with every connection checked out and the server then
going down, a new caller is still told the pool is busy, and the holders
report the outage when their statements fail. Pinned by
tests/postgres/test_postgres_async.py::test_a_busy_async_pool_is_not_an_outage,
test_a_partitioned_async_pool_is_an_outage_not_busy,
test_slow_connects_to_a_reachable_server_are_contention_not_an_outage,
test_a_wait_behind_a_slow_checkout_check_is_contention_not_an_outage and
test_a_busy_sync_pool_is_not_an_outage.
A maintenance DSN (for PgBouncer transaction mode)¶
Ordinary queries are safe behind PgBouncer in transaction mode, because they hold no session state. Three things need a real session, and a transaction-mode pooler cannot give them one:
REINDEX ... CONCURRENTLY/DROP INDEX CONCURRENTLY(rebuild_indexes(),clean_indexes()). They cannot run in a transaction block, so theirlock_timeout/statement_timeoutare sessionSETs. Behind the pooler those settings leak to other clients and may not reach theREINDEXat all (see How a rebuild'sREINDEXis bounded).- First-use DDL. It runs on a dedicated connection with session timeouts when a unit of work is open. It also often needs a role that the application's role is not, such as the schema owner.
- The shared
LISTENsession (blocking stream reads,Subscriber). ALISTENbelongs to a session.
Point one optional second DSN at the same database, directly or through a session-mode pool:
export POPOTO_POSTGRES_URL=postgresql://app@pgbouncer:6432/agents # transaction mode
export POPOTO_POSTGRES_MAINTENANCE_URL=postgresql://owner@db.internal:5432/agents # direct
or PostgresBackend(dsn=..., schema=..., maintenance_dsn=...). When it is set:
| Work | Connects through |
|---|---|
saves, loads, queries, transaction(), check_indexes(), the TTL reaper |
the pool, on POPOTO_POSTGRES_URL |
rebuild_indexes()'s REINDEX/ANALYZE, clean_indexes()'s DROP INDEX CONCURRENTLY |
a dedicated connection on the maintenance DSN |
| first-use DDL (model tables, validity pointer tables, engine side tables), inside a unit or not | a dedicated connection on the maintenance DSN |
the shared LISTEN session |
POPOTO_POSTGRES_LISTEN_URL if set, else the maintenance DSN |
Unset, or equal to the main DSN, everything runs exactly as it did before the
setting existed. "Equal" compares the parsed DSNs, without their passwords, so
host=h dbname=db equals dbname=db host=h and postgresql://h/db. The dedicated connections are opened per call and closed
after it. First-use DDL with a maintenance DSN therefore always costs one
connect, paid once per model per process. Without one, DDL outside a unit
reuses a pool slot. LISTEN falls back to the maintenance DSN because a
LISTEN needs exactly the session that DSN exists to provide, and it is one
connection per process (#799). POPOTO_POSTGRES_LISTEN_URL still wins when
you want LISTEN on a third DSN, such as a role with fewer privileges than
the one that runs DDL.
It must reach the same database. On its first use (lazily, like
everything else here, so constructing the backend touches no network) popoto
connects through both DSNs and compares current_database(), the database
OID, pg_postmaster_start_time() and server_version_num, plus
pg_control_system().system_identifier where both roles may read it. Server
addresses are not compared, because a pooler or a different interface reports
a different address for the same server. If any of these facts differ,
popoto raises popoto.backends.postgres.MaintenanceDsnMismatchError (a
ValueError) before it runs a single statement through the maintenance DSN.
So DDL and REINDEX never land on the wrong database. The error names hosts,
ports, users and database names, never a password. It is a configuration
error, not an outage: health is untouched, and the check runs again on the
next use. A passing check is remembered for the backend's lifetime. Threads
that make their first use at the same moment run the check once between them.
If the maintenance DSN is unreachable (refused, timed out, or its
credentials rejected), only the work that needs it fails. First-use DDL,
rebuild_indexes() and LISTEN raise
popoto.backends.postgres.MaintenanceConnectionError (a ConnectionError,
not a BackendUnavailableError). The message names the maintenance host and
database, never a password. The main DSN may be healthy, so health stays
ok and no write is counted as dropped. Once a model's table is set up in
this process, its saves and queries never touch the maintenance DSN. A model's
first use in a process does, even when its table already exists. That first
use raises the error and writes nothing.
Two roles: an application role and an owner role¶
The example above runs the main DSN as app and the maintenance DSN as
owner. When the two DSNs run as different roles, the tables first-use DDL
creates belong to owner, and app cannot use them until it is granted
access. Popoto grants nothing by default. On the first save, app fails
loudly with popoto.backends.postgres.MainRolePermissionError (a
PermissionError). It wraps a table, schema or sequence privilege refused on
popoto's own schema; any other permission denied (a function such as
pg_read_file, say) reaches you as the driver's own error. The message
carries the server's own permission denied text and links to this section. It is not an outage, so health is
untouched. The roles need only this:
CREATE ROLE owner LOGIN PASSWORD '...';
CREATE ROLE app LOGIN PASSWORD '...';
GRANT CREATE ON DATABASE agents TO owner; -- creates the popoto schema on first use
The default: grant it yourself. Once a first use has created the schema
(the failing first save does), run this as owner, or as a superuser:
GRANT USAGE ON SCHEMA popoto TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA popoto TO app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA popoto TO app;
-- the registry: app reads it to check a table is current; only owner writes it
REVOKE INSERT, UPDATE, DELETE ON popoto.popoto_schema FROM app;
-- tables a later first use creates (a new model, a new field's side table)
ALTER DEFAULT PRIVILEGES FOR ROLE owner IN SCHEMA popoto
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;
ALTER DEFAULT PRIVILEGES FOR ROLE owner IN SCHEMA popoto
GRANT USAGE, SELECT ON SEQUENCES TO app;
app never gets CREATE on the schema, TRUNCATE, or ownership. Read the
scope of these statements before you run them:
- Give popoto a schema of its own.
ON ALL TABLES IN SCHEMAreaches every table in the schema, not only popoto's, and the default privileges reach every tableownercreates there later, by hand included. In a schema shared with other tables (POPOTO_POSTGRES_SCHEMA=public, say) they handappall of them. There, grant per table instead (GRANT ... ON TABLE popoto.<table> TO app). - Do not run them as a superuser for a superuser
owner. Run as a superuser,ON ALL TABLES IN SCHEMAalso grants on tables other roles own. Keep theFOR ROLE ownerin the default privileges: without it they apply to whichever role runs the statement. - Behind PgBouncer,
appis the server role. When the pooler maps clients to a server role ([databases] ... user=), that mapped role is the one that needs the grants, and other services using the same mapping get them too.
To create the schema up front instead, make owner its owner
(CREATE SCHEMA popoto AUTHORIZATION owner) and run the USAGE grant and
the two default privileges before the first deploy. Then run the REVOKE on
popoto_schema once the first use has created it.
Opt in: grant_main_role=True. PostgresBackend(..., grant_main_role=True)
or POPOTO_POSTGRES_GRANT_MAIN_ROLE=1 (read by the environment-configured
backend, next to the other variables) makes popoto grant app access to what
its own DDL creates, and nothing else. The identity check learns each DSN's
current_user (through a pooler, the server role it maps to). When they
differ, each first-use DDL transaction on the maintenance DSN grants, in that
same transaction and only on the objects that transaction created:
| Created by that transaction | Granted to app |
|---|---|
| a popoto table (a model's, a side table, a validity pointer table, an engine table) | SELECT, INSERT, UPDATE, DELETE on that table |
| its sequences | USAGE, SELECT on each |
the popoto_schema registry |
SELECT only |
| the schema itself | USAGE on it |
"Created by that transaction" is checked in the catalog: the object's
pg_class (or pg_namespace) row was written by this transaction and was not
there once the transaction held the DDL's advisory locks. A table only altered
(ADD COLUMN) is not created, and an object another session made, at any
moment, never qualifies: that includes a table an older process (flag off)
created while this one waited for the lock, which this one then only
migrates. So popoto
never grants on a table you create by hand, on another role's table, or on a
schema it did not create. It runs no ALTER DEFAULT PRIVILEGES and no
ON ALL TABLES. Once the tables exist, a process's first use creates nothing
and grants nothing. A REVOKE you run stays revoked across restarts, and the
next save fails with MainRolePermissionError instead of quietly regaining
access. If the schema already existed when popoto first ran (you made it, or
an earlier deploy without the flag did), grant app USAGE on it yourself.
Tables created before the flag was turned on are not granted: use the
statements above.
The Redis-to-Postgres migration tool follows the same setting. It reads
MigrationConfig(grant_main_role=...), falling back to
POPOTO_POSTGRES_GRANT_MAIN_ROLE, and grants nothing otherwise.
If you set POPOTO_SCHEMA_AUTO=0 and run the DDL yourself, popoto runs no
DDL and so grants nothing, whatever the flag says. Run the grants above.
The reverse case is an existing deployment whose tables were created by
app, before the maintenance DSN was set. There REINDEX and additive
ALTER TABLE need the maintenance role to own the tables. The MAINTAIN
privilege (PostgreSQL 17 added it; popoto needs 18 anyway) is enough for
REINDEX. Move the
ownership once, as a superuser (or a role with the privileges of both):
-- every object app owns in this database, not only popoto's
REASSIGN OWNED BY app TO owner;
-- or, per table: ALTER TABLE popoto.<table> OWNER TO owner;
Then run the grants above, so that app keeps its access.
tests/postgres/test_postgres_maintenance_dsn.py runs these setups with real
roles. By default the first save fails with the docs link, and the SQL above
(the test checks its statements against this page) fixes it. With the flag, the test runs
a first save, queries, a model first used later inside a unit,
rebuild_indexes() and LISTEN/NOTIFY, directly and through
transaction-mode PgBouncer. It also checks that a hand-made table after the
first save is not granted, that a superuser owner does not grant on another
role's table, that a REVOKE survives a new process, and that
injection-shaped role names are quoted.
POPOTO_POSTGRES_MAINTENANCE_URL is read only next to POPOTO_POSTGRES_URL,
by the environment-configured backend. A PostgresBackend(dsn=...) you
construct takes maintenance_dsn= explicitly. The Redis-to-Postgres
migration tool takes postgres_maintenance_dsn=. It also reads the variable
when its target DSN comes from the environment. It holds its run lock (a
session advisory lock) and runs its own table DDL on one direct connection
to that DSN for the whole run. Behind a transaction-mode pooler, the tool
needs the maintenance DSN for that lock (see the migration
guide). tests/postgres/test_postgres_maintenance_dsn.py pins the
routing. The test points both DSNs at one server under two application_names,
and an event trigger records which one each DDL and REINDEX came from. It
also has an optional test against a real transaction-mode PgBouncer, which
runs when POPOTO_TEST_PGBOUNCER_URL is set.
Transactions¶
backend = popoto.backends.get_backend(Note)
with backend.transaction() as uow: # one READ COMMITTED transaction
Note(owner="a", slug="1").save(pipeline=uow)
Note(owner="a", slug="2").save(pipeline=uow)
# both committed, or (on an exception) neither
bulk_create, bulk_update and bulk_delete on a Postgres model each run
inside one transaction(). A unique conflict anywhere in the batch rolls back
the whole batch. Deadlock and serialization failures are retried
automatically, up to Defaults.PG_TRANSACTION_RETRIES times, for single
statements, which then raise popoto.backends.BackendRetryableError. Inside a
transaction() -- including at its commit -- the whole unit rolls back and
BackendRetryableError is raised at once, chained from the driver error;
popoto does not retry a block it does not own:
from popoto.backends import BackendRetryableError
for attempt in range(3):
try:
with backend.transaction() as uow:
...
break
except BackendRetryableError:
continue # rolled back by a concurrent transaction; safe to rerun
After-commit hook. uow.after_commit(fn) registers a no-argument
callable to run once this transaction has committed. Callbacks run in
registration order after COMMIT returns. A rolled-back transaction drops
them, and so does a failure at COMMIT. A callback that raises is logged at
WARNING; the remaining callbacks still run, and the exception is not raised,
because the write has already committed. This hook is how a Postgres-bound
save runs the WriteFilterMixin priority tag (a no-op off Redis). Without a
caller transaction, the save has already committed by the time it runs, so
it runs immediately. Pinned by
test_after_commit_callbacks_run_only_after_commit and
tests/postgres/test_postgres_recipes.py::test_post_save_redis_side_effects_wait_for_commit.
Before-commit hook. uow.before_commit(fn) runs fn inside the
transaction, just before its COMMIT, in registration order; an exception
from it rolls the whole unit back and propagates. A save's or delete's
EventStreamMixin entry is queued beside them with
uow.defer_stream_append(stream, fn) inside a caller's transaction() or a
popoto.batch(); those run after the before_commit callbacks, sorted by
stream key, so the stream row locks are the last locks the transaction takes
(after every record-key and row lock, the one lock order), always in
the same order, and the entry commits or rolls back with the record. Pinned by
tests/postgres/test_postgres_journal.py::test_the_mutation_stream_is_written_only_after_commit.
Popoto maps psycopg's DeadlockDetected and SerializationFailure to
BackendRetryableError; neither reaches the caller raw. A statement whose completion is
unknown (SQLSTATE 40003) is not retryable -- it may have committed -- and is
reported as BackendUnavailableError.
popoto.batch() (M5)¶
The same code opens a batch on either backend:
pipe = popoto.batch()
Note(owner="a", slug="1").save(pipeline=pipe) # returns pipe, as on Redis
Note(owner="a", slug="2").save(pipeline=pipe)
pipe.execute() # both, or neither
batch() still returns a real redis.client.Pipeline (a subclass, so every
isinstance gate and every Redis byte is what it was). The first write from
a Postgres-bound model that is handed the batch opens one transaction() on
that model's backend, and that write and every later one runs inside it.
execute() commits it and returns []. reset(), leaving a with block,
or dropping the batch without execute() rolls it back, as an unexecuted
Redis pipeline sends nothing. Saves, deletes, increments, touch, the
confidence, read-tracking and ObservationProtocol writes, the
CoOccurrenceField edge writes (link, strengthen, unlink,
weaken_all), the AppendOnlyMixin guard and ProvenanceJournal's
append/supersede/retract all join it. Until execute(), no other
connection sees the writes, as with queued commands. A graph write handed
the batch runs in the batch's transaction, so it reuses the lock the batch
already holds on a record it saved; a second save of an append-only key in
one batch sees the first and is refused, as in a transaction().
Stream entries and side effects follow the batch. A save's
EventStreamMixin entry, a custom _xadd_event and a Publisher's message
join the batch's transaction (Event streams and pub/sub):
the entries are appended just before execute() commits, one per save, in
save order, and the message is delivered at that COMMIT; a batch that
reset(), a with block or a failed execute() rolled back appends and
delivers nothing. WriteFilterMixin's priority tag (a no-op off Redis) is
registered on the after-commit hook (Transactions). On
Redis the same effects queue on the pipeline, so both backends apply them
exactly when the writes land, and a Postgres batch sends Redis nothing.
Pinned on both legs by tests/test_batch.py and tests/test_pubsub.py.
Atomic, where Redis is not. If a statement fails inside the batch's
transaction (a save that loses a unique value to a concurrent unit at the
index, say), the call that issued it raises as usual, the transaction is
aborted, and execute() then raises popoto.backends.BackendError and
writes nothing. A Redis MULTI/EXEC applies the other queued commands. A
save that is refused before it sends anything -- pre_save finding a
unique value already held by a committed record or claimed earlier in the
same batch (#776: the read runs in the batch's transaction), a field
validation error -- raises at the call and leaves the batch healthy, so if
you catch it, execute() commits the rest, as on Redis. batch(transaction=False) is still one
transaction on Postgres. A deadlock inside the batch raises
BackendRetryableError, as inside any transaction().
A record lock this thread or task already holds. Until it commits, a
batch holds the record-key lock of each record it wrote. A write from the
same thread that would wait for one of those locks outside the batch -- a
second, nested batch() or transaction() that writes the same record, or
a plain save() of it -- could never be granted, because the only thread
that can release it is the one waiting. Popoto refuses that write with
BackendCapabilityError before sending it, instead of hanging until
PG_STATEMENT_TIMEOUT_MS; the batch that holds the record is unharmed.
Write through that batch, or execute() it first. Another thread's write
simply waits for the commit. Nested batches that write different records
work as on Redis.
Under asyncio the unit is the task, not the thread: every task's Postgres I/O runs on the event-loop thread (Async). Three rules decide whether a task's write that would wait on another unit's record lock waits or is refused:
- Its own transaction or batch: refused at once, as for a thread.
- An ancestor's: refused at once. An ancestor is a task that had a
transaction()or batch open on this backend when it created this task (asyncio.gather,create_task,TaskGroup), directly or through intermediate tasks. The parent typically awaits the child inside its block, so it cannot commit until the child finishes and the wait would hang untilPG_STATEMENT_TIMEOUT_MS. The error says so: passpipeline=uow(the parent's unit or batch) to the child to join the parent's transaction, or write the record after it ends. This applies even to a child the parent never awaits (fire-and-forget): popoto cannot tell the two shapes apart, so the child is refused rather than allowed to wait. - Anyone else's -- a sibling task, a task created before the
transaction opened, an unrelated task, another thread: an ordinary wait.
That unit can commit while this one waits, so it is never refused. Two
children of one parent, each in its own
transaction(), that write overlapping records serialize on the locks; neither is refused.
The ancestry is carried in a context variable that the parent's task sets
when an async_* call leaves it with a unit open, and that a task inherits
when it is created. So a child created before the parent opened its
transaction does not count as a descendant. Neither does the reverse case:
a child that opened a batch and returned, with the parent then writing
the same record outside that batch. Both still wait until
PG_STATEMENT_TIMEOUT_MS.
The check covers record-key locks only (the locks save, delete,
increment and the other record writes take first). A nested write that
would wait on any other lock an outer unit holds -- the (model, field)
lock a ValidityField write takes, a UNIQUE value the outer unit wrote
and has not committed -- is not detected, and waits until
PG_STATEMENT_TIMEOUT_MS.
One batch, one backend. A batch holding a Postgres transaction refuses a
Redis command, and a batch with Redis commands queued refuses a Postgres
write, with BackendCapabilityError, before anything is sent: a Redis
MULTI/EXEC and a Postgres transaction cannot commit atomically together,
and a batch that only looked atomic would be worse than a refusal. A batch is
also bound to the one Postgres backend it opened on. Use one batch per
backend. SupersessionProtocol's mutators refuse a batch on a Postgres
model, as they refuse any Redis pipeline; pass backend.transaction()'s
unit of work there. (ProvenanceJournal, which calls them, accepts a batch:
it hands them the batch's unit of work.)
Record expiry (M5)¶
class Session(popoto.Model):
token = popoto.KeyField()
user = popoto.StringField()
class Meta:
backend = "postgres"
ttl = 3600 # every save sets the record to expire in an hour
s = Session(token="t", user="u")
s._ttl = 60 # or an instance override, as on Redis
s.save()
A Meta.ttl model's table has one more engine column, _expires_at double
precision (epoch seconds; NULL never expires), with a partial B-tree on
the rows that set it. A model without Meta.ttl gets neither, and none of
the SQL below: its tables and plans are exactly what they were.
Writes. A save sets _expires_at from the instance, as the Redis save
issues EXPIRE/EXPIREAT: now + _ttl (the server's clock), else
int(_expire_at.timestamp()), else it leaves the stored value alone, as
HSET leaves a key's TTL alone. A partial (update_fields) save sets it
too. A zero or negative _ttl, or an _expire_at in the past, expires the
record at once. A save over an expired key that the reaper has not yet
deleted drops the expired row first, so the save writes a fresh record (new
side rows, the confidence seed) and returns the new-record count, as HSET
on a key Redis has expired creates a new hash.
Reads. Every read of the model sees a row only while _expires_at is
NULL or later than now: get, get_many, filter, all, count,
keys, exists, values=, load_fields, top_by_decay,
composite_score, top_by_relevance, BM25Field.search,
keyword_search, semantic_search, load_embeddings, recall,
might_exist, the BM25 corpus statistics, confidence and read-tracking
state, the validity reads and chain, idle_seconds, the AppendOnlyMixin
guard, and ContextAssembler through all of them. An expired row is gone to every reader the instant it expires, deleted
or not. Now is the server's statement_timestamp(), one clock for every
agent on the central database. A _ttl is added to that clock, so the app
machine's clock never moves it; only an _expire_at, an absolute instant
from the app, is exposed to skew between the app and the database server,
exactly as EXPIREAT is judged by the Redis server's clock. (Decay and the
validity as_of stay on the app's clock, as on Redis.) tests/postgres/test_postgres_ttl.py
exercises each of those reads against one expired record. A write addressed
to one record (atomic_increment, a capped push, touch,
update_confidence) treats an expired row as missing.
The reaper. No cron, no CLI, no manual job (#755 Q3). After a record
write on a Meta.ttl model commits (a save, delete, increment or capped
push, or a transaction()/batch() that wrote one), the backend deletes up
to Defaults.PG_REAPER_BATCH (20) expired rows of that table in a
transaction of its own; their postings, vectors, membership and validity
rows cascade. It runs at most once per Defaults.PG_REAPER_INTERVAL_SECONDS
(1 s) per table and process, except that a run which found a full batch
leaves the next write due at once, so a backlog drains a batch per write. It
takes the backend's lock order without ever waiting: each candidate's
record-key lock is a try-lock (a record a writer holds is skipped), rows are
locked SKIP LOCKED in _pk order and re-checked (a save that refreshed the
TTL keeps its row), and lock_timeout is
Defaults.PG_REAPER_LOCK_TIMEOUT_MS (50 ms), below deadlock_timeout, so
the reaper gives up before a caller could be picked as a deadlock victim. A
busy pool, a lock it would wait on, or an outage skips the run; it never
fails the write that triggered it and does not touch the health record.
Reads never reap, by decision. A read stays a single read-only
statement: it takes no row locks, works on a read-only role or a replica,
and its plan does not change with the backlog's size. Correctness does not
need the reaper, since every read filters expired rows out. The cost is that
a workload that stops writing to a Meta.ttl table stops draining it: the
expired rows stay until the next write, and the reads that filter them with
an anti-join (the BM25 corpus statistics, the membership reads) pay for each
one. The backlog is therefore bounded by write traffic: at most the rows
that expired since the table's last write, and each later write drains a
batch of 20 at once. A table that is read for long stretches without writes
and holds many short-lived rows can be drained by any write to it, such as
saving and deleting one record.
Cost, measured as the p50 of 300 saves (Apple M1 Max, PostgreSQL 18.6 on
localhost, load 5-7, three runs each): a model without Meta.ttl
0.33-0.35 ms; a Meta.ttl model with the reaper idle 0.37-0.39 ms; the
same with the reaper forced on every write and nothing due 0.66-0.79 ms
(what the 1 s interval saves); a save that reaps a full batch of 20 rows
1.07-1.09 ms (100 rows: 2.2-3.5 ms, which is why the batch is 20).
Reading a remaining TTL. backend.ttl_remaining(spec, ids) answers what
redis.ttl(key) answers: -2 for no live record, -1 for no expiry, else
the seconds left, rounded as Redis rounds its milliseconds.
The seeded probe. scripts/probe_ttl_parity.py runs random sequences of
saves (every kind of TTL above, full and partial), deletes and batches on
both backends, compares every outcome and every read (get, exists,
filter, a range filter, count, the remaining TTL), sleeps past the short
expiries and compares again, then writes to the expired records. On the
Postgres side alone it also freezes the clock at, just before and just after
each expiry instant and checks visibility against an exact model of the
sequence. Its documented classes are the TTL rows in the divergence table
below; tests/postgres/test_postgres_ttl.py runs a 40-shape slice.
REDIS_URL=redis://localhost:6379/10 \
POPOTO_POSTGRES_URL=postgresql://localhost:5432/postgres \
python scripts/probe_ttl_parity.py --seeds 1 2 3 --shapes 120
Async (M5)¶
On a Postgres-bound model every async_* method -- async_save,
async_create, async_delete, async_load, async_get_or_create,
async_update_or_create, the bulk twins, async_delete_all, and
Query.async_get/async_get_many/async_filter/async_all/async_count/
async_keys -- runs on the model's async backend, with its I/O on
psycopg.AsyncConnection on the running event loop, not in a worker
thread. A Redis-bound model's async_* methods are
unchanged, command for command (scripts/trace_async_redis_wire.py).
from popoto.backends.postgres.aio import get_async_backend
note = await Note.async_create(owner="a", slug="1")
rows = await Note.query.async_filter(owner="a")
backend = get_async_backend(Note) # Postgres-bound models only
async with backend.transaction() as uow: # one READ COMMITTED transaction
await Note(owner="a", slug="2").async_save(pipeline=uow)
await Note(owner="a", slug="3").async_save(pipeline=uow)
# both committed, or (on an exception) neither
One implementation. The async backend does not reimplement the sync one.
It runs the sync backend's own code in a greenlet on the loop thread (the
technique SQLAlchemy's asyncio extension uses) and swaps the one thing that
does I/O: inside that greenlet, every statement the sync code issues is
awaited on an AsyncConnection from the loop's pool. So the SQL, the lock
order, the deadlock retry and BackendRetryableError, the #769 no-blind-retry
rule, the BackendUnavailableError outage record (shared: get_backend(M).health
counts async failures and dropped writes too), the savepoint a guarded save
takes inside a caller's transaction, and the first-use version, encoding and
DDL checks are the sync backend's, by construction. greenlet comes with the
postgres extra; without it, the async_* methods fall back to the worker
thread and log one warning. A unit of work from the async transaction() is
for the async_* methods: handing it to a sync save(pipeline=uow) raises
BridgeMisuseError.
popoto.batch() from async code. An async_* write handed a batch
joins it as a sync write does (popoto.batch()), but
the batch's transaction is then opened on the loop's async pool, so commit
it from the coroutine:
pipe = popoto.batch()
await Note(owner="a", slug="1").async_save(pipeline=pipe)
await Note(owner="a", slug="2").async_save(pipeline=pipe)
await pipe.async_execute() # COMMIT on the loop, stream entries included
pipe.execute() on such a batch raises BridgeMisuseError naming
async_execute(); await pipe.async_reset() rolls it back. A plain
reset() or leaving a with block schedules the rollback on the running
loop as a task. If that rollback fails (its connection broke), the failure
is logged once at WARNING; the server rolls back a transaction whose
connection is gone. After-commit hooks wait for the commit exactly as in a
sync batch. async_execute() on any other batch (Redis commands, or a
transaction a sync call opened) runs execute() in a worker thread.
Pinned by test_postgres_async.py::test_an_async_save_joins_a_batch_on_the_loop.
A batch is driven by the side that opened it. If an async_* write
opened the batch, a sync write to it raises BridgeMisuseError. If a sync
write opened it, an async_* write raises BridgeMisuseError too: the
batch's connection is a blocking one, and driving it from the loop would
stall every other task for each statement. Both are refused before anything
is sent, and the batch stays usable from the side that opened it. A batch
that outlives its loop (asyncio.run() returned before async_execute())
is rolled back as the loop shuts down, and its connection is closed at the
socket by the next pool lookup or close_pools(). A later reset() of it
is a no-op.
Concurrent transactions are per task. Two tasks' async with
transaction() blocks that write the same records wait on each other's
record locks like two threads do; the refusal of a write that waits on a
lock its own open transaction holds (popoto.batch())
is per task. More concurrent transactions than PG_POOL_MAX_SIZE queue for
a connection, and one that waits longer than PG_CONNECT_TIMEOUT_SECONDS
raises BackendBusyError, not an outage (Topology).
What still leaves the loop. An embedding provider is a sync API: its call,
and the backfill's wait on it, run in a worker thread so they never block the
loop. A Postgres model's stream entries are rows written in the save's own
transaction, so they make no Redis call. async_check_indexes,
async_clean_indexes and async_rebuild_indexes run on the async backend on
Postgres, and in a worker thread on Redis
(Index maintenance).
Model hooks run on the event-loop thread. The bridge runs the whole sync
method, so a model's overrides of save(), pre_save() and delete(), and
its fields' pre_save_validate/on_save/on_delete hooks, run inside it, on
the loop thread, with the loop running, not in a worker thread. Two
consequences:
- A hook that calls
asyncio.run()(orloop.run_until_complete()) now raisesRuntimeError: asyncio.run() cannot be called from a running event loop.asyncio.get_running_loop()succeeds inside a hook, so a hook that branches on it sees a loop. - A hook that blocks (
time.sleep,requests, a sync client of another service) blocks the whole loop for that long, not one worker thread.
Keep hooks to in-memory work on the instance. Do blocking or async work
outside the save: await it before or after async_save(), or hand it to
asyncio.to_thread() / a task from the calling coroutine. A hook that must
stay blocking can be reached through the sync save() in
asyncio.to_thread(instance.save).
First use of a model from concurrent tasks. The backend's first-use table
check holds a threading.RLock, which separates threads, not tasks: every
bridge greenlet runs on the loop thread, so for them the lock is re-entrant
and concurrent first uses all pass it. What serialises them is the server:
ensure_table takes a transaction-scoped advisory lock
(pg_advisory_xact_lock on popoto:ddl:<schema>), so one task creates or
checks the table and the others wait, then find it made. Tasks that each hold
an open transaction() never wait on the pool for it: the catalog check runs
on each task's own connection, and any DDL on a dedicated connection outside
the pool (see Topology).
Event loops. Pools are per (DSN, process, event loop), lazy, each at most
Defaults.PG_POOL_MAX_SIZE connections, validated on checkout like the sync
pool's. A model used from two loops -- asyncio.run() twice, pytest-asyncio's
function-scoped loops -- gets a pool per loop. A loop's pool closes with the
loop: loop.shutdown_asyncgens(), which asyncio.run, asyncio.Runner and
pytest-asyncio call, finalizes it. A loop closed without that call is swept,
at the socket, by the next pool lookup from any loop, so pg_stat_activity
does not grow with the number of loops a process has used
(tests/postgres/test_postgres_async.py). A task cancelled at any point --
task.cancel(), an asyncio.wait_for timeout -- including while its
connection's checkout check is in flight, closes that connection rather than
leaving it open and unowned, so the server's session count for a pool stays
at or below PG_POOL_MAX_SIZE however often callers cancel. The pool is popoto's own, not
psycopg_pool.AsyncConnectionPool: that pool's maintenance workers catch
CancelledError, so one still open when its loop shuts down hangs
asyncio.run()'s task cancellation whenever a worker is mid-task.
Ranking and memory state (M2a)¶
Each operation is one statement. top_by_decay is rank_decayed (below)
followed by one load of the ranked rows, in rank order; a model with
AccessTrackerMixin then stages the reads in one more UPDATE.
rank_decayed is DECAY_SCORE_LUA as a SELECT, operation for
operation in double precision, so the platform's pow behind power()
gives the Lua's bits:
SELECT t."_pk",
((CASE WHEN b < 0 THEN -1 ELSE 1 END) * abs(b)
* power(greatest(($now - t.f) / 86400, 0.01), -$rate))
-- with confidence modulation:
* power(greatest(greatest(($now - t.f) / 86400, 0.01), 1),
-($rate * power(2, $s2 * ($c0 - greatest(0, least(1, coalesce(t.c__conf, $c0)))))
- $rate)) AS "_score"
FROM popoto.<model> AS t
WHERE t.f IS NOT NULL AND <partition filters>
ORDER BY ("_score" = 'NaN'), "_score" DESC, "_pk" COLLATE "C"
LIMIT $n
b is the base-score column (1.0 when there is none, or it is NULL, a
string or a boolean, as the script's HGET + cmsgpack rule gives; a
numeric converts through the same strtod the script's tonumber uses),
$s2 is twice Defaults.DECAY_CONFIDENCE_MODULATION_STRENGTH, and $c0 the
confidence field's initial_confidence. Only the partition filters scope the
scan, as on Redis, where they pick the sorted set.
Postgres raises value out of range where C's pow and * overflow to
inf or underflow to 0 (power(0.01, -155), 1e-300 * 1e-300; the #631
POC's boundary rows). Rows whose inputs sit in a box where no step can leave
the double range -- every realistic one -- take the expression above; any
other row, and every row when the rate or strength is extreme, takes a
correlated subquery that computes the same steps with each power, * and
- clamped, so the result is inf or 0, with its sign, where the Lua's is.
Inside a band of 0.003 in log space at each end of the double range (a
result within 0.25% of DBL_MAX, or between 2.47e-324 and 2.48e-324) the
clamp saturates where the exact value is still finite; that is the only place
the two legs can differ. Scores come back rounded through %.14g, which is
what the Lua's tostring replies with.
update_confidence is CAPPED_BAYESIAN_UPDATE_LUA as one UPDATE:
UPDATE popoto.<model> SET
c__conf = greatest(0, least(1, coalesce(c__conf, $c0)
+ ($signal - coalesce(c__conf, $c0)) / (least(coalesce(c__n, 0) + 1, $cap) + 1))),
c__n = coalesce(c__n, 0) + 1,
c__corr = coalesce(c__corr, 0) + <1 if $signal >= 0.5>,
c__contra = coalesce(c__contra, 0) + <1 if $signal < 0.5>
WHERE "_pk" = $pk
RETURNING c__conf, c__n, c__corr, c__contra
The arithmetic is the script's, so the stored confidence is bit-identical to
the Redis one; the returned value is rounded through %.14g as the script's
reply is. No row means the record does not exist: the field layer raises the
same TypeError as on Redis (with a pipeline, the update is skipped, as the
queued script skips it).
composite_score is one SELECT over the model's rows: each index is an
arm scoring the records its Redis sorted set would hold (the decay arm the
partition's clocks, the confidence arm every record, the access arm every
record read at least once), a record is ranked when any arm holds it, and the
aggregate runs over the arms that do -- SUM in the indexes' order (a NaN
step, inf + -inf, becomes 0 as ZUNIONSTORE makes it), MAX/MIN
ignoring the rest. Ties come back in descending key order, as ZREVRANGE
gives; min_score applies before temperature, and post_filter after it,
as on Redis. Two differences stay below the 1e-9 tolerance: Redis's decay arm
holds the script's %.14g-rounded scores where Postgres uses the full
double, and Redis adds three or more arms in its own set order rather than
the indexes', which can move a sum by an ulp.
The seeded probe. scripts/probe_memory_parity.py builds the same random
corpus on both backends -- ages from fresh to centuries, the future, shared
timestamps and pathological clocks; base scores of every type the script reads,
from 5e-324 to 1e305; signal sequences; partitions; staged and confirmed
reads -- and compares rank_decayed, top_by_decay, confidence,
read tracking and composite_score between the legs. Its classes are in its
docstring; the two documented NaN rows are the NaN decay-score rows of the
divergence table;
tests/postgres/test_memory_probe.py runs a 40-shape slice of it in CI.
REDIS_URL=redis://localhost:6379/10 \
POPOTO_POSTGRES_URL=postgresql://localhost:5432/postgres \
python scripts/probe_memory_parity.py --seeds 1 2 3 --shapes 200
ObservationProtocol.on_context_used applies all five outcomes for a
batch in one transaction: the batch's rows are locked FOR UPDATE in _pk
order first (the one lock order), then each instance's effects run in
that order -- touch for acted, staged reads confirmed (acted, used) or
discarded (the rest), the confidence signal (acted, contradicted), and its
proposal resolved. A deadlock or serialization failure retries the whole
batch up to Defaults.PG_TRANSACTION_RETRIES times and then raises
BackendRetryableError; inside a transaction() you pass as pipeline it
raises that error at once, for you to retry.
Documented divergences¶
Redis behaviour does not change to match Postgres in 1.10.0.
Where a test pins one of these, it pins both behaviours explicitly or carries a
redis_only(reason=...) mark.
Query results¶
Each row is pinned on both conformance legs: rows (i)–(xi) by a test in
tests/test_backend_parity_edges.py, rows (xii)–(xx) in
tests/test_backend_parity_fields.py. In rows (i)–(iv), (vi), (vii), (xii),
(xiv), (xv) and (xviii)–(xx) Redis's result is a bug in its query layer,
tracked in #771. The examples use code/group (KeyField), rank
(SortedField), note/hits (unindexed) and at (an unindexed
DatetimeField); rows (xii)–(xx) use IndexedFields named by type (s str,
i int, f float, dec Decimal, dte date), a DateField day, a
ListField lst, a capped ListField(max_length=3) cap, a
TupleField pair and a Relationship author/owner.
| Example | Redis (unchanged) | Postgres | Correct | |
|---|---|---|---|---|
(i) An unindexed field inside a composed Q, or a Q beside a kwarg |
filter(Q(hits=5) & Q(group="g1")) |
the plain leaf is dropped: every g1 row |
rows with hits = 5 in g1 |
Postgres |
| (i′) The same, negated | filter(~Q(note="x")) |
always empty: the leaf returns every key and its equality filter runs after the set algebra | NOT, with NULL counted as "not equal" |
Postgres |
(ii) values= with an unindexed-field filter outside the projection |
filter(group="g1", note="x", values=("code",)) |
[]: the filter runs on dicts that lack note |
the matching rows | Postgres |
(iii) Two lookups bounding the same side of one SortedField |
filter(rank__gt=10, rank__gte=1) |
only the last lookup for that side applies: every row | both apply (AND): [] |
Postgres |
(iv) values= + order_by over a column holding None |
filter(rank__gte=0, values=("code", "hits"), order_by="hits") |
TypeError: '<' not supported… |
rows, NULL sorted as the type's zero (as instances sort on both) |
Postgres |
(v) A bad SortedField range bound |
filter(rank__gte=None), filter(rank__lte="abc"), filter(rank__lte="nan") |
None: ResponseError: min or max is not a float. A string the server's strtod refuses ("nan", "1_0", "3 ", "abc", a non-ASCII digit) raises the same ResponseError (on a Decimal field Python's own ValueError, which converts first) |
a refused string raises QueryException("min or max is not a float"): the same text, a different class. None returns []. The strings Redis accepts parse identically ("+3", "3.", "1e1", "inf"; Redis also takes a leading space, hex and "" as 0, which Valkey refuses, and Postgres follows Redis). The ZRANGEBYSCORE exclusive prefix ("(3") is not supported: it raises. |
Redis (failing loudly is the better answer) |
(vi) KeyField __contains |
filter(code__contains="0") |
matches nothing (the lookup is accepted but not implemented) | LIKE '%0%' |
Postgres |
(vii) KeyField __isnull=False |
filter(group__isnull=False) |
only some non-null records match, depending on the key's position and value (a second key matches none; values such as "10", or containing _ or %, are missed) |
IS NOT NULL |
Postgres |
(viii) Equality on a DatetimeField with a naive value against a stored aware one |
filter(at=datetime(2024, 1, 5, 12)) |
compared in Python: naive never equals aware | compared as instants, naive taken as UTC (the rule sorted fields use, #519) | Postgres |
(ix) Equality on KeyField(type=Decimal) |
filter(code=Decimal("1.50")) against a stored 1.5; filter(code=2) against 2.0 |
key strings are compared, so both match nothing | numeric values are compared, so both match |
Postgres |
(x) Saving Decimal("1.5") and then Decimal("1.50") as KeyField(type=Decimal) |
create(code=Decimal("1.50")) after 1.5 |
two records ("1.5" and "1.50") |
unique violation: the values are equal | Postgres |
(xi) KeyField(type=float) -0.0 against a stored 0.0 |
filter(code=-0.0) |
[]: "-0.0" != "0.0" |
[0.0] |
Postgres |
| (xii) Chained relationship lookup | filter(author__country="uk") |
AttributeError: filter_query gets bytes keys back and calls .db_key on them |
resolves the related model's query and matches its keys | Postgres |
| (xiii) Equality on a collection field with another collection type | filter(pair=[1, "x"]) on a TupleField holding (1, "x") |
Python equality after hydration: a tuple never equals a list, so [] |
compares the stored JSON documents, so the list matches (a SetField compares as a set) |
Postgres is the more useful; neither is wrong by contract |
(xiv) IndexedField numeric equality across value forms |
filter(i=1.0) on 1; filter(f=1) or f="1" on 1.0; filter(dec=Decimal("1.5")) on 1.50; filter(i=True) on 1; i__in=[1, 2.0] |
compares the filter value's key string ("1.0" ≠ "1"), so []. Save coerces i=1.0 to 1, so a record saved with i=1.0 cannot be found by filter(i=1.0) |
compares numbers, as Python equality does (1 == 1.0, Decimal("1.5") == Decimal("1.50")) |
Postgres |
(xv) __startswith/__endswith on a row holding None |
filter(s__startswith="No"), s__endswith="ne", s__startswith="", f__startswith="N" |
the None set is named …:None, so the glob matches rows holding None |
LIKE never matches NULL |
Postgres |
(xvi) Pattern lookup on a float in 1e15 <= abs(x) < 1e16, or a Decimal Python writes in exponent form |
filter(f__startswith="1000") on 1e15 |
matches the key string 1000000000000000.0 |
no match: Postgres renders 1e+15 (and numeric never uses exponent form) |
Redis (the key string is the lookup's contract; outside this band Postgres renders it exactly) |
(xvii) order_by on a collection field |
filter(…, order_by="lst") |
sorts as Python does ([] < [1, 2] < [2]); TypeError for mixed or dict elements |
BackendCapabilityError: jsonb orders by length first ([2] < [1, 2]), so Postgres refuses rather than return a different order. Sort in Python. |
Redis, where it does not raise |
(xviii) order_by on a date column holding None (IndexedField(type=date), DateField) |
filter(…, order_by="day") |
TypeError: function missing required argument 'year': None sorts as the type's zero, date() |
NULL first (last for -day) |
Postgres |
(xix) order_by on a Relationship |
filter(…, order_by="owner") |
AttributeError: … '_meta' |
ordered by the stored key string, NULL first |
Postgres |
(xx) A capped ListField on a lazy read |
filter(…), all(); filter(…, values=("name", "cap")) |
cap is None (only get loads the separate list key); values= omits cap and logs quarantined field 'cap' per row |
the stored list, as get returns on both |
Postgres |
Numeric key fields otherwise follow Redis's key-string rules on both backends
(KeyField(type=int) matches 1 and "1" but not 1.0; an integer beyond
bigint matches nothing), and a SortedKeyField matches by score (2.0,
"01" and " 1" all find 2 and 1). Redis's SortedKeyField __in
ignores the filter and returns every record (#771); Postgres filters, by
score, with mixed numeric types allowed (pinned:
test_sorted_int_key_in_with_mixed_types). SortedKeyField equality with
True, False or None raises ResponseError: min or max is not a float on
Redis; Postgres matches the score 1/0, and None matches nothing (pinned:
test_sorted_key_equality_with_bool_or_none_is_a_documented_divergence).
A lone unindexed-field Q (filter(Q(hits=5))), one lower plus one upper
bound on a sorted field, and values= that projects the filtered field all
agree on both backends.
Field-type cases that agree on both backends, and are pinned as such: a string
filter on an IndexedField of type bool, date or datetime that is the
stored value's key string (b="True", dte="2026-01-01",
dtm="2026-01-01T12:00:00.000000Z"; "true" matches neither); an aware
time filter on a TimeField, which matches nothing; __startswith /
__endswith on a non-text IndexedField, matched against the key string
(1.0, True, 2026-01-01, 1.50, …T12:00:00.000000Z) except row (xvi);
and __in with mixed numeric types (i__in=[1, Decimal("2")]), each element
cast to the column's type.
Records and other behaviour¶
| Behaviour | Redis (unchanged) | Postgres |
|---|---|---|
save(migrate_key=True) that changes a key field |
rewrites the key and its index entries | raises BackendCapabilityError. Create the new record and delete the old one. |
| Unit-of-work / bulk failure | MULTI/EXEC applies the other queued commands |
the whole transaction rolls back |
\x00 in a text value |
stored | ValueError naming the field |
atomic_increment past 2**63 on an IntField |
Lua goes to float | NumericValueOutOfRange |
A Redis pipeline= handed to a Postgres model |
queued | the write runs immediately, and the pipeline comes back untouched for the caller to execute. A popoto.batch() is the exception: the write joins the batch's Postgres transaction (see popoto.batch()) |
atomic_increment of an IntField by a non-integral delta (hits=5, +1.5) |
stores the float sum 6.5 and returns it truncated, 6 |
stores and returns the sum cast to bigint, rounded half away from zero: 7. Neither is well defined; pass an int delta. |
Order of results with no order_by, no Meta.order_by and no sorted-field filter |
set order (arbitrary) | _pk in bytewise (COLLATE "C") order |
An invalid order_by= / values= on a query that matches nothing |
returns [] before validating |
raises the same QueryException either way |
save(update_fields=…) on a record that does not exist yet |
writes a partial hash that stays out of the class set, so queries do not see it | inserts the row (unlisted columns NULL), so queries see it |
UniqueKeyField / UniqueField / unique Meta.indexes conflict |
checked by a read in pre_save before the write |
the same read, through the backend -- on the save's unit of work when it holds one, so it sees that unit's own writes (#776) -- plus a UNIQUE index inside the write as the authority for a concurrent unit's uncommitted claim; same ModelException text either way (tests/postgres/test_postgres_fields.py, test_postgres_unique_race.py) |
| Two saves in one batch that claim one unique value | both queue; for a UniqueField or unique Meta.indexes, EXEC applies the first and reports the second's conflict. For a UniqueKeyField beside another key field (name = UniqueKeyField(), agent = KeyField()), both records are saved: two records hold one unique value, which an unbatched second save refuses with ModelException (pre-existing; #771) |
the second is refused at its save() call (the read sees the first) and writes nothing; the batch stays healthy and execute() commits the first, as EXEC applies it on Redis -- the error is raised at the call instead of at execute(). Pinned: tests/test_backend_parity_ttl.py::test_a_failed_batch_is_a_documented_divergence, tests/test_batch.py::test_a_second_claim_in_one_batch_is_refused_and_the_rest_commits |
A batch or transaction() that deletes a unique value's holder and then saves a new record claiming the value |
pre_save's read sees the committed holder (the queued DELETE has not run), so the second save raises ModelException and the batch never reaches EXEC: the holder stays |
the read runs on the unit's connection and sees the delete, so the claim succeeds and the unit commits both (#776). Pinned: tests/postgres/test_postgres_unique_race.py::test_a_unit_sees_its_own_delete_of_the_holder |
The never-record tombstone of a save refused inside a batch or transaction(), when the batch is then discarded (reset()) or the transaction rolls back |
the tombstone goes straight to the client, outside the queued commands: it survives the reset() |
it is written on the unit's connection and rolls back with the unit, like everything else the unit wrote; it commits when the unit commits (#776). A database error from that write (say, the audit table dropped) raises BackendError, never raw psycopg. Pinned: tests/postgres/test_postgres_unit_connection.py::test_a_refused_save_writes_its_tombstone_in_the_transaction, test_a_refused_save_writes_its_tombstone_in_the_batch, test_a_database_error_from_the_tombstone_is_a_popoto_error |
| A model that needs a schema change (an added field) inside a unit that already read or wrote its table | no schema: the save queues | SchemaDriftError at once, naming the lock the unit holds; the DDL would wait on the unit forever. Use the model once outside the unit first. Pinned: tests/postgres/test_postgres_unit_connection.py::test_a_field_added_after_the_unit_used_its_table_is_refused_at_once, and, for the auto-key shape #826 closed, test_an_auto_key_table_bound_before_its_first_instance_needs_no_ddl |
An aware time in a TimeField / SortedField(type=time) |
stored with its offset (isoformat()) |
ValueError naming the field: a time column holds wall-clock time only. Use a DatetimeField when the offset matters. Pinned: tests/postgres/test_postgres_fields.py::test_an_aware_time_is_refused |
push() on a capped ListField whose record was deleted |
LPUSH recreates an orphan list key |
raises ModelException (UPDATE finds no row). After a successful push() the in-memory list is the stored list, not a local prepend. Pinned: test_push_on_a_record_that_no_longer_exists_raises |
load_raw_hash, Query.keys(catchall=/clean=) |
Redis debug and inspection APIs | raise BackendCapabilityError |
Model.idle_seconds |
OBJECT IDLETIME: any read or write resets it (an HGETALL included) |
whole seconds since the row's last write or, with AccessTrackerMixin, its last confirmed read; an unconfirmed read does not reset it. Pinned: tests/postgres/test_postgres_recipes.py::test_idle_seconds_counts_a_confirmed_read |
MemoryLifecycle with a KeyField tier |
promotion is a key migration (save(migrate_key=True)) |
refused when you build it: MemoryLifecycle(...) raises BackendCapabilityError naming IndexedField, because every promotion would be a key migration, which the Postgres backend refuses. Declare the tier as an IndexedField on Postgres; promotion is then a plain save. Pinned: test_a_key_tier_lifecycle_is_refused_at_construction_on_postgres, test_lifecycle_promotes_a_non_key_tier, and tests/test_memory_lifecycle.py, whose Postgres leg runs every test against IndexedField-tier twins of its models |
| A question-queue delivery when another transaction holds a candidate's row | the script runs after the other write and sees it | FOR UPDATE SKIP LOCKED: that candidate is passed over for the next, as one another worker claimed would be. Pinned: test_postgres_question_queue.py::test_a_candidate_another_writer_holds_is_skipped |
| A proposal that duplicates two or more open candidates | folds into the first in QuestionCandidate.query.filter(agent_id=…)'s order: set order |
the first in _pk order (the "order of results" row above, seen through dedup). Pinned on both legs by tests/test_question_queue.py::TestPropose::test_a_proposal_duplicating_two_candidates_folds_into_the_first, and counted as the queue probe's dedup_order class |
DefaultMemory's eviction counter |
a Redis string MemoryService.status() reads |
a popoto_counter row, which MemoryService.status() reads on Postgres: a Postgres-bound DefaultMemory needs no Redis |
ProvenanceJournal with a caller pipeline= |
a Redis pipeline: the annotation and close are queued, target_closed is None and close_index names the close in execute()'s results |
the backend's unit of work only (anything else raises ValueError): the annotation and close run inside it, target_closed is known at the call, close_index is None. Pinned: test_postgres_journal.py::test_a_caller_unit_of_work_carries_the_annotation_and_the_close |
AppendOnlyMixin: two saves of one key in one unit of work |
both pass the guard (the documented intra-pipeline shape) | the second is refused (the guard reads inside the transaction). Pinned: test_postgres_recipes.py::test_append_only_sees_its_own_transaction |
async_get/async_filter/async_count/… |
native redis.asyncio (reads); a worker thread (writes) |
the async backend: the sync call's Postgres I/O on psycopg.AsyncConnection, on the running loop, no thread (Async) |
async_load(db_key=<str>) (the type its signature names) |
raises AttributeError: 'str' object has no attribute 'redis_key': the native path reads db_key.redis_key (a pre-existing Redis-path bug). The sync load(db_key=<str>) works |
runs the sync load: the record, or None when there is none. Pinned on both legs: tests/test_async_parity.py::test_async_load_with_a_string_db_key_is_a_documented_divergence |
async_all on an AccessTrackerMixin model |
stages a read per record (its native path hydrates through _async_get_many_objects), although all() is non-tracking by design |
does not stage, as all() does not |
ExistenceFilter.might_exist |
a bloom filter: false positives are possible, and a deleted record stays "seen" | exact: no false positives, and a deleted record is forgotten. Pinned on both legs: test_existence_filter.py::TestMembershipExactness |
FrequencySketch.get_frequency |
a count-min sketch: never under, may be over | the exact count of saves (never decremented, like the sketch). Pinned: same class |
ExistenceFilter.fill_ratio |
the fraction of set bits | an estimate, 1 - e^(-k·n/m) for the n distinct tokens stored |
save(update_fields=[…]) naming a BM25Field's or EmbeddingField's source, or only a scope column |
only the listed fields' hooks run, so the BM25 index keeps the old text or scope, and the vector (and its hash) the old text | re-indexes and re-embeds on the source; moves the postings and the narrow vector row on a scope change. Pinned: test_update_fields_naming_the_source_reindexes, test_update_fields_naming_the_source_re_embeds |
save(update_fields=[…]) naming a partitioned sorted field (DecayingSortedField, SortedField(partition_by=…)) but not its partition column, after an unsaved change to that column (#774 review) |
the field's hook reads the partition from the instance, so the member moves to the instance's partition sorted set while the hash keeps the old value: filter(agent="B").top_by_decay() finds a record whose agent is "A" (#771) |
the partition is the stored column, so the record stays where the row says; the BM25 postings, document length and narrow vector row follow the stored row too. Pinned: test_backend_parity_memory.py::test_a_partial_save_with_an_unsaved_partition_is_a_documented_divergence |
EmbeddingField storage |
a .npy file per record plus _index.json, and an in-process matrix cache |
the vector(d) column. No file, no cache, and garbage_collect / sweep_stale_tempfiles return 0. The tests that assert files are redis_only |
| Vector-arm ties and precision | equal similarities come back in directory-listing order; numpy float32 dot products | ties by key, bytewise; pgvector's <=>. Both are float32 accumulations in a different order, so similarities differ by an amount that grows with the dimension. Measured maximum absolute difference over 10,000 vector-query pairs per dimension (clustered Gaussian vectors, PostgreSQL 18.6, pgvector 0.8.7): 2.5e-7 at 2-d, 2.3e-7 at 8-d, 5.3e-7 at 64-d, 8.9e-7 at 256-d, 1.2e-6 at 768-d, 1.4e-6 at 1024-d, 1.7e-6 at 1536-d (the #774 review measured 1.56e-6) and 2.3e-6 at 3072-d. So 1e-6 holds only up to about 256 dimensions; above that, near-ties can order differently |
ContentField |
a $CF: reference in the hash, with the content in a file store |
the content itself in a text column |
BM25Field.recompute_stats |
corrects the running avgdl's floating drift |
a no-op: N and avgdl are counted live |
The $BM25: / $EF: / $FS: keys |
the index, readable through the raw client | not used: postings, length and token tables. The tests that read the keys are redis_only |
A reload after touch() |
touch moves only the sorted-set score, so the hash, and a reload, keep the save-time value |
the clock is the field's column, so a reload sees the touched time. Pinned: test_a_reload_after_touch_is_a_documented_divergence |
DecayingSortedField.rank_decayed(zset_key, …) (TD-40) |
ranks that sorted set | raises BackendCapabilityError naming top_by_decay, the backend-neutral call |
composite_score({"priority": …}) |
ranks by the WriteFilter priority set | raises BackendCapabilityError: the priority tier is a no-op on Postgres |
composite_score(similarity_boost=…, co_occurrence_boost=…) |
injects each boost as an arm | an arm on both. A key with no record cannot take a top-K slot on Postgres, where on Redis it takes one and is then dropped at hydration, so Redis can return fewer records (never in another order). Pinned: test_backend_parity_memory.py::test_composite_co_occurrence_boost_is_an_arm_on_both_backends; the graph probe's composite_orphan_slot class |
A NaN edge weight (link(…, initial_weight=nan) on a new edge, a NaN strengthen or weaken_all result) |
ZADD refuses it: ResponseError: value is not a valid float. A symmetric write whose second script fails keeps the first script's write |
ValueError with the same text, and the whole write rolls back. Pinned: test_postgres_graph.py::test_a_nan_weight_raises_value_error |
A NaN weight in CoOccurrenceField.import_state (one that survives the max_edges truncation) |
DELETE runs, then ZADD refuses the NaN: ResponseError: value is not a valid float, and the record's edge set is left empty |
refused before any write: ValueError with the same text, and the edge set is unchanged. No NaN edge is stored on either (stored, Postgres would rank it above every weight and least(NaN, cap) is the cap). Pinned on both legs: test_co_occurrence_field.py::TestImportStateAndErrorParity::test_a_nan_import_is_refused_a_documented_divergence; the graph probe's nan_import_keeps_set class |
CoOccurrenceField.get_linked(…, limit=None) |
redis-py refuses it client-side: DataError: ``start`` and ``num`` must both be specified (before any NaN-bound check) |
ValueError with the same text, in the same order. Pinned on both legs: test_get_linked_limit_none_a_documented_divergence; the graph probe's limit_none_error_type class |
A NaN min_weight in CoOccurrenceField.get_linked |
ResponseError: min or max is not a float |
ValueError with the same text. Pinned on both legs: test_get_linked_nan_min_weight_a_documented_divergence; the probe's nan_error_type class |
The order of propagate()'s dict, and so of equal weights in graph_traversal.traverse() |
Lua table iteration order | weight descending, then key bytewise. The dict and its weights are equal on both; traverse() sorts by weight, so only ties can be listed in another order (the graph probe's traverse_tie_order class) |
link()'s reply for a weight at or past 2**63 in magnitude |
the server's C (long long) cast of the Lua number: -inf and anything under -2**63 reply -2**63 on arm64 and x86-64; above 2**63 (only with a cap past it) arm64 saturates to 2**63 - 1, x86-64 replies -2**63 |
the arm64 values |
| Where a NaN decay score ranks | NaN (0 * inf: a -inf clock with above-prior confidence) makes the script's comparator inconsistent (x > nan is always false), so table.sort places it arbitrarily and can misorder real scores around it |
real scores sorted, NaN last; every member's score is the same on both. Pinned: test_where_a_nan_score_ranks_is_a_documented_divergence |
A NaN decay score in composite_score |
rank_decayed replies nan (0 * inf: a -inf clock with above-prior confidence) and the composite's ZADD refuses it: ResponseError: value is not a valid float |
that arm scores 0 for the record, the value ZUNIONSTORE gives a NaN product. Pinned: test_a_nan_decay_score_in_composite_is_a_documented_divergence |
| The confirmed access log | a capped list of read timestamps ($AT:…:access_log) |
not kept: access_count and last_accessed are. export_state carries the counters only, and a Redis export's access_log is dropped on import. The tests that read the log are redis_only |
raw_update() |
HSET on each key, creating a partial hash for a key with no record; every index stays stale until rebuild_indexes() |
an UPDATE of the rows that exist (a missing key is not created; the return value counts updated rows); unknown names and fields with no column (BM25Field…) raise BackendCapabilityError. B-trees follow the row at once; only companion tables (BM25 postings, narrow vectors, existence tokens) stay stale until rebuild_indexes(). Pinned: tests/postgres/test_postgres_maintain.py |
check_indexes() |
counts orphan index entries of five kinds | those five are always 0 (they are the table's own B-trees); the dict adds side_tables (the companion-table drift) and invalid_indexes, plus side_tables["graph_edges"]["dangling"] (informational, not in total: Redis keeps such edges) (see Index maintenance) |
rebuild_indexes() |
deletes every index key and re-runs each field's on_save, so a FrequencySketch counts each record again and an EmbeddingField may call the provider; runs inside a batch |
rewrites only drifted companion rows, never counts a sketch twice and never calls the provider; then REINDEX TABLE CONCURRENTLY and ANALYZE, bounded by PG_MAINTAIN_* timeouts. Refused (BackendCapabilityError) inside an open transaction()/batch; a REINDEX that stops early raises MaintenanceIncompleteError, not an outage |
update_confidence(…, pipeline=uow) with a Postgres transaction() |
(a Redis pipeline queues the update and returns None) |
the update runs inside the transaction, so its value is returned and the attribute synced |
CyclicDecayField.rank_decayed(zset_key, …) |
ranks that sorted set with CYCLIC_DECAY_LUA |
raises BackendCapabilityError naming top_by_decay, as DecayingSortedField.rank_decayed does |
A cycles entry written raw (import_state) |
Python msgpack keeps the importer's float for an integral period or baseline until the next save or adjustment re-packs it through cmsgpack |
cmsgpack's int at once. Values identical (probe class cyclic_merge_raw_types) |
| A non-numeric cycle slot (a string period, a malformed entry) | storable; the merge falls back to the declaration with a warning, and the ranking may raise | unrepresentable: import_state refuses a non-numeric slot with ValueError |
strengthen_cycle / weaken_cycle / resolve_pressure / td_update on a record that no longer exists, or has expired |
resolve_pressure and td_update write orphan companion or hash entries (HSET) |
nothing is written; td_update replies what the script replies from Q = 0 (on an expired record that is the same reply: Redis's HGET of the expired key is nil) |
A non-numeric strengthen_cycle / weaken_cycle factor (None, "abc", True) |
the script's multiplication fails: ResponseError: user_script:15: attempt to perform arithmetic on local 'factor' (a nil value) …, nothing written |
ValueError with the same text, before anything is written. Pinned on both legs: test_backend_parity_longtail.py::test_a_non_numeric_factor_is_refused_before_writing |
td_update on a stored Decimal('-0') |
tonumber("-0") is -0.0, so with a target of -0.0 the TD error is -0.0 - -0.0 = 0.0 |
a numeric has no -0: the value is stored as 0, and the reply is -0.0 - 0.0 = -0.0. The stored values compare equal. Pinned on both legs: test_backend_parity_longtail.py::test_td_update_from_a_negative_zero_is_a_documented_divergence |
A NaN CyclicDecayField score |
the script's comparator is inconsistent around it (x > nan is false): its sort may misplace real scores, or raise "invalid order function for sorting" |
NaN ranks last and every other score keeps its place (the decay ranking's rule; probe class cyclic_rank_nan) |
td_update(…, pipeline=uow) with a Postgres transaction() |
(a Redis pipeline queues the script and returns None) |
the update runs inside the transaction, so the TD error is returned, as update_confidence does |
A NaN td_update value |
stored as tostring(nan), "nan" or "-nan" by platform |
numeric NaN, unsigned |
| The key order of a resolved ledger entry | cmsgpack's Lua-table iteration order | the entry's own order. Dicts compare equal |
A NaN prediction error (inf against a number, inf - inf) |
the script marks the entry resolved, then its ZADD refuses the score (ResponseError: value is not a valid float), and Redis does not roll the HSET back: the entry reads resolved with a NaN error and no error-set member |
refused before anything is written: ValueError with the same text, and the entry stays unresolved. Pinned on both legs: test_backend_parity_longtail.py::test_a_nan_prediction_error_is_a_documented_divergence; the probe's ledger_nan_error class |
An integer in a ledger entry that rounds to 2**63 or more as a double |
cmsgpack's conversion is undefined behaviour in C: the re-packed value differs by platform (-2**63, -1) |
the double |
execute_supersede(mode="open") naming a member with no record |
ZADD NX indexes the member anyway |
writes nothing: the interval is the record's row. Only a direct execute_supersede call can ask for it. Pinned: tests/postgres/test_postgres_validity.py::test_mode_open_on_a_member_with_no_record_writes_nothing |
| An open-claim pointer naming a record that does not exist | storable (a manual SET, or a partial import_state); supersede reads it as "no incumbent" |
unrepresentable: the pointer table's foreign key refuses it, and deleting a record cascades to its pointers. import_state for a record that is not stored raises ValidityMemberAbsentError (a ValidityError, so a ValueError) chained from the driver's ForeignKeyViolation; Redis's import_state never raises there. Pinned: test_a_pointer_cannot_name_a_record_that_does_not_exist |
save_and_supersede / save_and_invalidate whose close fails |
MULTI/EXEC keeps the successor's save, and the typed error's text carries redis-py's Command # N (...) of pipeline caused error: prefix |
the whole unit rolls back, so the successor is not saved either; same exception type, and the text is the bare reply line |
A NaN valid_from on save |
the script's ZADD refuses it: ResponseError: … value is not a valid float from the pipelined EVALSHA, after MULTI/EXEC has written the record's hash, so the record exists with no interval |
refused before anything is written: ModelException("value is not a valid float") -- the same text, popoto's save error (as row (v) of the query table). Stored, a NaN start would sort above every float and hide the record from every gate. Pinned: tests/test_validity_parity.py::TestNanInstants::test_a_nan_valid_from_on_save_is_refused |
A NaN valid_from in a direct execute_supersede(mode="open") call |
the script's first ZADD refuses it: ResponseError: value is not a valid float script: …, nothing written. Every other NaN instant in supersede / invalidate / execute_supersede is refused on both legs alike -- ValueError("value is not a valid float (<instant> is NaN)") before the first write (#778, which on Redis used to close and chain the incumbent first); this one is left to the script because it is the save path's own refusal (the row above) |
ValueError with the same text, nothing written. Pinned: tests/test_validity_parity.py::TestNanInstants::test_a_nan_valid_from_in_mode_open_is_a_documented_divergence; the shared refusal by ::test_a_nan_at_is_refused_and_writes_nothing and ::test_a_nan_instant_in_execute_supersede_writes_nothing |
A NaN as_of / validity__as_of |
the range reads (filter, resolve_*_keys, the composite mask) raise ResponseError: min or max is not a float; the decay ranking's gate excludes nothing |
QueryException with the same text (as row (v) of the query table); the decay ranking excludes nothing |
SupersessionProtocol.supersede/invalidate with the backend's transaction() as pipeline |
(a Redis pipeline queues the script; the closed key is unknown until execute()) |
runs inside the transaction: the closed key is returned and a typed error raised at the call. A Redis pipeline is refused with ValueError by supersede, invalidate and save_and_* alike: it cannot carry a Postgres write. Pinned: test_a_redis_pipeline_is_refused_by_supersede_and_invalidate |
_ttl / _expire_at on a model without Meta.ttl |
EXPIRE/EXPIREAT on the hash |
BackendCapabilityError before anything is written: only a Meta.ttl model has _expires_at and the read filter, so a model that never expires keeps plans that never check. Declare Meta.ttl; an instance can opt out with _ttl = None. Pinned on both legs: test_backend_parity_ttl.py::test_an_instance_ttl_without_meta_ttl_is_a_documented_divergence, and tests/postgres/test_postgres_ttl.py::test_an_instance_ttl_needs_meta_ttl |
A _ttl that is not a whole number (1.5) |
MULTI/EXEC writes the hash, then EXPIRE fails: ResponseError: value is not an integer or out of range, and the record stays with its old TTL, or none |
ModelException with the same text, before anything is written. Pinned: test_backend_parity_ttl.py::test_a_fractional_ttl_is_a_documented_divergence |
count() / keys() after a record expires |
count the class or index set, which keeps the expired member until a hydrating read (get, filter, all) purges it or clean_indexes runs |
count live rows only. Probe class count_orphans. Pinned: test_backend_parity_ttl.py::test_count_after_expiry_is_a_documented_divergence |
| Rankings, search and membership after a record expires | the sorted sets, BM25 postings, vector file and bloom keep the member: top_by_decay(n=…) can give a slot to it and return fewer than n after hydration drops it, BM25's N/avgdl/df count it, and might_exist stays True |
the record is in none of them from the instant it expires: n live records, statistics over live documents, might_exist False once no live record holds the token. Pinned: test_backend_parity_ttl.py::test_ranking_after_expiry_is_a_documented_divergence, and each read in tests/postgres/test_postgres_ttl.py::test_every_public_read_misses_an_expired_record |
State keyed by an expired record: its confidence entry, validity intervals and open-claim pointers, staged reads, CyclicDecayField cycles and pressure |
kept in their own keys until something cleans them; ConfidenceField.update_confidence refuses (its script checks the hash); strengthen_cycle / weaken_cycle still adjust the cycles entry, export_state returns it, and a save over the key merges with it |
gone with the row: reads see the seed / no interval / no pointer, and chain stops at it as at a hard delete; an adjustment finds no entry ([]), export_state returns None, and a save starts from the declaration. update_confidence refuses on both. Pinned on both legs: test_backend_parity_ttl.py::test_state_keyed_by_an_expired_record_is_a_documented_divergence and test_backend_parity_longtail.py::test_cycle_state_of_an_expired_record_is_a_documented_divergence; the validity half in tests/postgres/test_postgres_ttl.py::test_validity_reads_miss_an_expired_record. The prediction ledger is not in this row: its EXISTS guard misses an expired record on both legs (record/resolve raise TypeError, auto_resolve returns None), and its entries outlive the record on both, as they outlive a delete (test_the_ledger_treats_an_expired_record_as_absent) |
| A save over an expired key | HSET creates a new hash, but the expired record's companion state (confidence entry, BM25 postings, interval) is still there for the new one to inherit |
the expired row and its side rows are deleted first: a fresh record, confidence at the seed. Pinned on both legs: test_backend_parity_ttl.py::test_a_save_over_an_expired_key_is_a_documented_divergence; the postings in tests/postgres/test_postgres_ttl.py::test_a_save_over_an_expired_key_writes_a_fresh_record |
atomic_increment through an instance whose record expired (or was deleted) under it |
the script HSETs the field onto a fresh key: a one-field hash with no TTL, outside the class set |
ModelException (… no longer exists), as for any missing row. Probe class increment_after_expiry. Pinned: test_backend_parity_ttl.py::test_increment_after_expiry_is_a_documented_divergence |
| The instant of expiry | a key is expired once the server's millisecond clock is past its expiry, so it is still there at that exact millisecond | a row is expired once _expires_at <= now (microseconds), so ttl=0 is gone even under a frozen clock. Observable only with the frozen test clock: two real clocks never land on one instant |
Removing Meta.ttl from a model whose table has _expires_at |
the next save simply stops issuing EXPIRE; the key keeps the TTL it had |
SchemaDriftError (a column the model no longer declares): drop the column by hand, or keep Meta.ttl and set _ttl = None per instance. Pinned: test_backend_parity_ttl.py::test_removing_meta_ttl_is_a_documented_divergence |
A failed statement inside popoto.batch() |
MULTI/EXEC applies the other queued commands |
the whole batch rolls back; execute() raises BackendError. Pinned: test_backend_parity_ttl.py::test_a_failed_batch_is_a_documented_divergence |
A popoto.batch() used for both Redis and Postgres writes |
one store: a raw command and a model save share one MULTI/EXEC |
refused with BackendCapabilityError before the second backend's first command, in either order. Pinned on both legs: test_backend_parity_ttl.py::test_a_batch_of_raw_commands_and_model_writes_is_a_documented_divergence; the reverse order in tests/postgres/test_postgres_ttl.py::test_a_batch_refuses_to_mix_backends |
A write that waits on a record an open batch or transaction() on the same thread holds: a nested batch writing the same record, or a plain save of a record saved in an open batch |
a queued command holds no lock: both apply, in execution order | BackendCapabilityError before anything is sent (it could never be granted); the holding batch is unharmed. Another thread waits for the commit. Pinned: tests/postgres/test_postgres_ttl.py::test_nested_batches_writing_one_record_are_refused_at_once, ::test_a_write_outside_the_batch_to_a_record_in_it_is_refused |
Validity and supersession (M3)¶
A ValidityField keeps the six Redis keys' state on the record's row and in
one companion table:
| Redis key | Postgres |
|---|---|
…:valid_from / …:invalid_at / …:ingested_at sorted sets |
<f>__valid_from / <f>__invalid_at / <f>__ingested_at, double precision (NULL = absent from that index; 'Infinity' = open) |
…:chain:fwd / …:chain:rev hashes |
<f>__superseded_by / <f>__supersedes on the record |
…:open:{digest} strings |
<table>__<f>__open (digest PRIMARY KEY, member), member referencing _pk ON DELETE CASCADE |
Why not tstzrange. An alternative design is one tstzrange column. A
timestamptz keeps microseconds, so the Redis score 1700000000.1234567
comes back 1700000000.123457, two scores one ulp apart become one instant,
and the gate's invalid_at <= as_of flips for a close one ulp after as_of
(pinned: tests/postgres/test_postgres_validity.py::test_timestamptz_would_not_hold_the_redis_score,
tests/test_validity_parity.py::TestExclusionRule::test_the_as_of_bound_is_bit_exact).
The second reason is a close at the record's own start, which the script
allows (its check is close < start): tstzrange(t, t) is the canonical
empty range and keeps neither bound, so the record's start and its recorded
close are both lost (and lower > upper raises). A range can tell "no
invalid_at recorded" from "invalid_at is +inf" (upper_inf('[t,)') is
true, upper_inf('[t,infinity)') false), so that is not a reason; the columns
spell it NULL vs 'Infinity'. So the interval follows the decay clock's representation:
double precision epoch seconds, bit-identical to the score.
The exclusion rule every gate applies -- top_by_decay's ranking,
composite_score's mask, ValidityField.resolve_excluded_keys, the
assembler -- is invalid_at <= as_of OR valid_from > as_of, with an absent end
never excluding, as one WHERE term on the row. filter(validity__as_of=t)
and resolve_valid_keys are the whitelist, valid_from <= t AND invalid_at >
t, both ends present; __current=False is the members of either index that
are not valid now. The #631 POC's 17-row table (tests/test_validity_parity.py)
holds on both backends.
supersede is SUPERSEDE_LUA phase for phase, in one transaction:
pg_advisory_xact_lock(hashtext('popoto:validity:<schema>.<table>.<f>'))-- one lock per model and field, the Redis single thread;- resolve the incumbent: the named one, else the identity's pointer;
- the record-key locks of the successor and the incumbent, in
_pkbyte order, thenSELECT … ORDER BY _pk COLLATE "C" FOR UPDATEon both rows; - validate, raising the typed error built from the script's reply line (and
so with the same text) and writing nothing: a successor that does not
exist, an asserted incumbent that does not exist (a pointer-resolved one
is "no incumbent"), a close before the incumbent's start, an asserted
valid_fromthat disagrees with the stored one; - mutate: close the incumbent if it is open (idempotent), both chain links,
open the successor
NX(an absent end filled, a closed record never reopened), repoint the pointer.
| Reply line | Exception (a ValueError) |
|---|---|
POPOTO_VALIDITY_MEMBER_ABSENT successor <key> |
ValidityMemberAbsentError |
POPOTO_VALIDITY_MEMBER_ABSENT incumbent <key> |
ValidityMemberAbsentError |
POPOTO_VALIDITY_CLOSE_BEFORE_START |
ValidityCloseBeforeStartError |
POPOTO_VALIDITY_VALID_FROM_CONFLICT <stored> <requested> (numbers as Lua's %.14g) |
ValidityValidFromConflictError |
A save opens the interval inside its upsert, the script's mode 'open', and a
declared valid_from that disagrees with the stored start fails the upsert's
guard, so nothing is written (behind the same pre_save_validate check as on
Redis). chain is one WITH RECURSIVE with the Redis walk's stop rules: a
missing link, a record already visited, a link naming a record with no
valid_from.
Lock order. Validity writers follow the backend's one lock order (see
"One lock order for every writer" above): the (model, field) lock, then the
record-key locks sorted by _pk, then the row locks. supersede takes them
in that order; save_and_supersede / save_and_invalidate take all of the
supersede's locks before the save (otherwise the save would hold the
successor's key and row while a concurrent supersede naming that record holds
the field lock and waits for them -- pinned:
test_save_and_supersede_of_an_existing_record_takes_the_supersede_lock_order,
which deadlocks every round with the pre-lock removed); import_state takes
the field lock as a pointer writer; and ObservationProtocol.on_context_used
takes the field lock before locking its batch, with a contradicted record's
successor locked as part of the batch. So two supersedes never interleave --
including the crossing chains that deadlocked the #631 POC (d1 → X
superseded by Y while d2 → Y is superseded by X; TestCrossingChains
forces the overlap ten times). A plain save does not take the field lock: it
meets a supersede on the record's key lock, and its upsert re-reads the row
it waited on. The residual is a caller's own transaction() that locks a
record and then supersedes while another supersede waits for that record:
Postgres detects the cycle and aborts one side. Usually the other, earlier
waiter is the victim and its owned transaction retries, so the caller
completes; when the caller is the victim it gets BackendRetryableError
(pinned: test_a_cross_operation_deadlock_is_a_retryable_error).
The seeded probe. scripts/probe_validity_parity.py runs the same random
sequences of saves (declaring and re-declaring starts), supersedes,
invalidations, direct execute_supersede calls in every mode, save_and_*
and deletes on both backends, comparing every return value and exception
(type and text), the intervals, links, pointers and chains, and every gated
read at the interval ends, ±inf, 1e308 and NaN. Its documented classes are
the validity rows (execute_supersede, open-claim pointers, save_and_*,
NaN valid_from and as_of, SupersessionProtocol) of the
divergence table; tests/postgres/test_validity_probe.py runs a
25-shape slice in CI.
REDIS_URL=redis://localhost:6379/10 \
POPOTO_POSTGRES_URL=postgresql://localhost:5432/postgres \
python scripts/probe_validity_parity.py --seeds 1 2 3 --shapes 200
Performance (M1, M2a and M3 exit criteria)¶
scripts/bench_backend_seam.py measures the public API on both backends. It
seeds 2,000 records, runs ANALYZE, and then makes three runs of 300
iterations per operation, with the two backends interleaved within each run.
The targets are Model.save() p50 at most 2x Redis and filter +
hydration p50 at most 1x Redis; for rank_decayed with a base score and
confidence modulation over all 2,000 records (top 10): Postgres p50 at most
1x Redis, DECAY_SCORE_LUA against one SELECT; top_by_decay with
hydration is measured beside it; and for the same ranking with a validity gate
(a tenth of the records superseded, a twentieth not yet started): Postgres
p50 at most 1x Redis. The PRs that introduced each target
record the measured numbers and the environment they were taken on.
REDIS_URL=redis://localhost:6379/14 \
POPOTO_POSTGRES_URL=postgresql://localhost:5432/postgres \
python scripts/bench_backend_seam.py
recall() (M2b exit criterion)¶
scripts/bench_recall.py seeds a 20,000-row, 1536-dimension corpus through
Model.save(): five projects (60 / 20 / 10 / 5 / 5 %), documents of 40-80
tokens from a 30,000-term Zipf vocabulary, and clustered synthetic vectors.
It runs VACUUM ANALYZE, then makes three runs of 200
recall(q, scope=p, limit=10) calls at a 5% and a 60% scope, and times each
arm alone. The targets are at most 15 ms p95 at 5% scope and at most 60 ms
p95 at 60% scope. The 5% scope (1,000 vectors) takes the exact path, over
the narrow vector table; before that table, reading 1,000 TOASTed 6 kB
vectors from the record table was most of the time, and the 5% p95 sat
at 13-25 ms. The 60% scope (12,000 vectors) takes HNSW. Each run prints the
load average.
Measured for #774 (Apple M1 Max, PostgreSQL 18.6 with shared_buffers
128 MB, pgvector 0.8.7, one 20k corpus, six invocations of three runs each;
the machine was shared, load average 4.7-7.5 throughout):
| Scope | Path | p50 (typical) | p95 per run | Runs within the bar |
|---|---|---|---|---|
| 5% | exact, narrow table | 5.6-5.9 ms (11.5-13.4 ms in the slow windows) | 6.5-10.8 ms in 13 runs; 16.4-20.2 ms in 5 | 13 of 18 (bar 15 ms) |
| 60% | HNSW | 9.9-17.8 ms | 16.8-46.3 ms | 18 of 18 (bar 60 ms) |
The five slow 5% runs fell in windows where the whole machine slowed: the same index-only count statement went from 0.4 ms to 1.0 ms p50 and every arm doubled together. Run alone in one window, the exact arm on the record table (TOAST) took p50 7.9 ms, p95 9.1 ms, and on the narrow table p50 2.9 ms, p95 3.6 ms.