Persister

In short

When the ingest API accepts a transaction, it doesn’t write it to the database straight away. It puts it on Kafka, which is fast and durable, and answers your system. The persister (role persist) then reads those transactions from Kafka and files them into PostgreSQL in large batches.

Think of Kafka as the in-tray and the persister as the clerk who files everything into the cabinet, a stack at a time, and ticks off each stack only once it is safely in the drawer.

ingest API ──▶ Kafka: sp.txn.ingested ──▶ persister ──▶ PostgreSQL
                                              │
                                              └──▶ sp.txn.ingested.dlq  (records it can't read)

What it writes

For each batch, in a single database transaction, the persister:

1. Finds or creates customers and accounts named in the transactions. If one doesn’t exist yet, it creates a placeholder that your first real customer or account record replaces. See Transactions that arrive before their customer.
2. Finds or creates counterparties, de-duplicated by a fingerprint of their details, so the same recipient seen 500 times is one record. Their identifiers are stored encrypted, with a keyed fingerprint that matches the fingerprint of an account you monitor with the same number. So a payment to one of your own customers can be linked to that customer without decrypting anything.
3. Copies the transactions into PostgreSQL with COPY (the fastest bulk-load method) into a staging table, then moves them into transactions, skipping any that are already there.

Why replays are safe

The persister tells Kafka it has finished with a batch only after the database transaction has committed. If the process crashes, is restarted, or Kafka moves partitions to another persister in between, the batch is simply read again.

Reading again creates no duplicates: a transaction that is already stored is skipped (each has a fixed ID, assigned once by the ingest API), and customers, accounts and counterparties are matched on their natural keys. This “at least once, but idempotent” design means you can restart persisters at any time.

How it divides the work

Each persist process runs SP_PERSIST_WORKERS writers (default 4). All writers in an installation join the same Kafka consumer group (SP_PERSIST_GROUP, default speculapro-persist), and Kafka hands each partition of txn.ingested to exactly one writer. Transactions for one account always share a partition, so they are always written in order.

The total number of writers across all processes should not exceed the partition count (64 by default). See Running roles.

A new consumer group starts at the beginning

The first time the group exists, the persister starts from the oldest message still on the topic, so nothing accepted before the persister was first started is missed. The same happens if you rename SP_PERSIST_GROUP, which makes it re-read everything retained (safely, but slowly).

Starting up

Before it reads anything, the persister checks that its database user can write for every workspace: a superuser, or a user with BYPASSRLS such as speculapro_system. If not, the process stops at once with

error: persist/stream/worker roles: database role cannot bypass row-level security (set SP_DB_SYSTEM_URL to the speculapro_system role)

This is deliberate. Without the check, a persister connected as speculapro_app would be blocked by row-level security on every batch, while the ingest API kept accepting data that never reached the database. Set SP_DB_SYSTEM_URL and start it again.

Tuning

Setting	Default	When to change it
SP_PERSIST_WORKERS	4	Raise it (or add persist processes) when consumer lag keeps growing during busy periods
SP_PERSIST_BATCH_MAX	5000	The most records written in one batch. Larger batches mean fewer, bigger database transactions; the default suits most installations
SP_DB_SYSTEM_URL	SP_DB_URL	Must point at speculapro_system (or another user with BYPASSRLS) in production; otherwise the process refuses to start. See Database roles

The persister collects whatever is waiting, up to SP_PERSIST_BATCH_MAX, and waits at most a quarter of a second for more, so data reaches PostgreSQL within a second or so under light load.

When a batch keeps failing

If PostgreSQL refuses a batch (it is down, out of disk, a permission is missing), the persister logs

level=ERROR msg="persist: batch failed, retrying" rows=… err=… backoff=…

and tries the same batch again, waiting a little longer each time, up to 30 seconds between attempts. It does not skip ahead, so nothing is lost and order is kept, but that writer makes no progress until the problem is fixed. Meanwhile the ingest API keeps accepting data, which waits safely on Kafka.

A persister connected as the wrong database user never gets this far: it refuses to start instead.

err mentions	Likely cause	Fix
permission denied	A table grant is missing for speculapro_system, usually because migrations ran as a user other than speculapro_owner	See Always run migrations as speculapro_owner
connection refused, timeout	PostgreSQL unreachable	Fix the database or network; the persister catches up on its own
no space left on device	Database disk full	Free or add space; the persister catches up on its own
no partition of relation "transactions" found	No partition for the transaction’s month and no default partition	Make sure a worker is running; create the partition by hand if needed. See Partitions

Kafka retention is your safety margin

While the persister is stuck, transactions wait on Kafka. If they wait longer than the topic’s retention period, Kafka deletes them and they are lost. Alert on repeated batch failed messages and on growing lag. See Backups: Kafka.

Dead letters

A record the persister can’t read at all (not valid JSON, an unknown event version, or missing its ID or workspace) is moved to the dead-letter topic <prefix>txn.ingested.dlq (by default sp.txn.ingested.dlq), and the persister carries on with the rest. You’ll see:

level=WARN msg="persist: sent undecodable records to dead-letter topic" count=…

Each dead letter keeps its original key and value, plus a header source-offset saying exactly where it came from (topic/partition/offset).

Records written by SpeculaPro’s own ingest API are always readable, so dead letters should not happen in normal operation. They mean something else wrote to the topic (another system or a manual test), or a newer version of SpeculaPro wrote events an older persister doesn’t understand (for example during a botched rollback).

How to handle dead letters

1. Look at them. In Redpanda Console, open sp.txn.ingested.dlq, or from a terminal:

   rpk topic consume sp.txn.ingested.dlq -n 10

2. Find the source from the record’s content and the source-offset header.

3. Fix the cause, for example by stopping whatever else writes to sp.txn.ingested, or by finishing the upgrade so all persisters run the same version.

4. Resend the transactions through the ingest API, not by copying records back onto the topic. Thanks to idempotency, anything that was in fact stored is reported as a duplicate.

Never write to sp.txn.ingested yourself

Only the ingest API should write to this topic. It encrypts identifiers, converts currencies and records idempotency keys before publishing. A record written by hand skips all of that.

Related

• Running roles
• Kafka / Redpanda
• Monitoring
• Sending data