Search backends
View as MarkdownExternal search projection
With search.backend: paradedb or search.backend: postgres, SearchFlows reads a SQL copy of the committed search documents. This copy is the search projection.
How the projection works
- Each committed change to a run's search document appends an entry to a durable outbox in the same local commit. A run purge appends one too.
- The outbox is part of the replicated state. Snapshots and log replay restore it after a restart or a takeover.
- The node that holds the lease reads the outbox in commit order. It writes each changed run's current document to SQL. Then it removes the written entries with a logged acknowledgement.
- Several changes to one run between two batches become one row write.
- The projection is asynchronous. When the database is slow or unavailable, commits continue and the outbox grows on the local disk. Search results lag, but no change is lost and nothing is rebuilt. After the database recovers, the projection continues from the oldest entry.
- Each partition's SQL progress row records the writer's lease epoch. A former lease holder with a lower epoch cannot write any more rows.
- An outbox entry takes about 60 bytes. Size the local store volume for the longest database outage you want to ride out.
Initial load
At startup, the node checks each partition's SQL progress against its committed state. It loads the partition when the progress cannot continue from the outbox:
| Reason in the log | When it happens |
|---|---|
| no_progress | A new database or schema, a switch from builtin search, or a load that a crash interrupted |
| no_outbox_progress | An older server release, which had no outbox, wrote the progress |
| enabled_after_progress | The cluster switched from SQL search to builtin search and back to the same schema |
| acked_after_progress | The database was restored to a point before entries that the cluster already acknowledged |
| ahead_of_committed_state | A defensive check: the progress is ahead of committed state. After an object storage restore, use a new schema instead. |
A load deletes the partition's rows and its progress in one transaction. Then it copies every current search document of the partition from one consistent view. Its last transaction records the new progress, so a crash during a load leads to another load. Later changes come from the outbox. Loads happen only at startup, never because the database is slow.
The log shows loading the partition into the SQL visibility projection with the reason for each load. dexzd_search_initial_loads_total counts loads, and dexzd_search_initial_load_pending_partitions reaches 0 when every partition is loaded. SearchFlows waits for a partition that is loading, as described in Read-your-writes.
Read-your-writes
SearchFlows waits until the projection holds every outbox entry committed before the request. After a restart or a takeover, it also waits for entries already in the local store and for loading partitions. The wait lasts at most search.readYourWritesWait. When it expires, SearchFlows returns UNAVAILABLE. It never returns results that miss such a write. Retry the call with backoff.
When the projection stops after an error it cannot retry, such as invalid committed state, SearchFlows returns INTERNAL until the server restarts. When the schema records a newer node epoch than this node's, the projection stops and SearchFlows returns UNAVAILABLE. A newer lease holder causes this, and so does a restored object storage prefix with a reused schema.
Configuration
| Field | Default | Meaning |
|---|---|---|
| search.readYourWritesWait | 2s | Longest SearchFlows wait for the projection |
| search.outboxBatchSize | 1000 | Most outbox entries or loaded documents in one SQL transaction |
| search.outboxPollInterval | 100ms | How often an idle projection looks for entries. New commits wake it at once. |
| search.outboxAckInterval | 1s | Least time between two acknowledgements of one partition. Each acknowledgement is a logged command, so a node with 1,024 busy partitions logs up to 1,024 per second. |
| search.outboxMaxAckEntries | 10000 | Most outbox entries that one acknowledgement deletes. A longer backlog is acknowledged in several commands. Must be at least search.outboxBatchSize. |
Monitoring
| Metric | Meaning |
|---|---|
| dexzd_search_outbox_oldest_entry_age_seconds | Age of the oldest outbox entry that SQL does not hold yet, 0 when none. This is the projection lag. |
| dexzd_search_outbox_entries | Entries that SQL does not hold yet, summed over partitions, as of each partition's last scan |
| dexzd_search_outbox_max_partition_entries | The largest of those per-partition counts |
| dexzd_search_outbox_batches_total with outcome | SQL transactions of the projection. Failed transactions are retried. |
| dexzd_search_outbox_projected_entries_total | Outbox entries that committed SQL transactions covered |
| dexzd_search_outbox_projected_runs_total | Rows written or deleted after changes of one run were combined |
| dexzd_search_outbox_ack_proposals_total with outcome | Acknowledgement commands. Failed proposals are retried. |
| dexzd_search_initial_load_pending_partitions | Partitions whose initial load has not finished |
| dexzd_search_initial_load_documents_total | Documents that initial loads wrote |
| dexzd_search_initial_loads_total | Partition loads started; the log gives each one's reason |
| dexzd_search_projection_mode_skipped_partitions_total | Stopped partitions that kept their previous projection mode at startup; each one also logs an error |
| dexzd_search_skipped_documents_total | Committed documents that SQL cannot store; search omits them |
# Search results lag committed writes by more than 1 minute
dexzd_search_outbox_oldest_entry_age_seconds > 60
# Every projection transaction failed for 10 minutes
increase(dexzd_search_outbox_batches_total{outcome="error"}[10m]) > 0
and increase(dexzd_search_outbox_batches_total{outcome="ok"}[10m]) == 0
# Every acknowledgement failed for 10 minutes, so the outbox only grows
increase(dexzd_search_outbox_ack_proposals_total{outcome="error"}[10m]) > 0
and increase(dexzd_search_outbox_ack_proposals_total{outcome="ok"}[10m]) == 0
# An initial load is still running; alert when this lasts 30 minutes
dexzd_search_initial_load_pending_partitions > 0
PostgreSQL search
Choose search.backend: postgres to run indexed search on PostgreSQL 15 or newer without extensions. It does not require pg_search, btree_gin, or any other extension. Choose ParadeDB when you need more indexed Attributes than the PostgreSQL slot limits allow.
search:
backend: postgres
postgresConnectionString: "postgres://USER:PASSWORD@HOST:5432/DATABASE"
postgresConnectionPoolSize: 10
postgresSchema: public
readYourWritesWait: 2s
The server connects to postgresConnectionString using the PostgreSQL protocol. It neither binds nor advertises that address. Leave the field empty to read DEX_ZD_POSTGRES_CONNECTION_STRING. The connection supports the same TLS options as paradeDbConnectionString, and serialized configuration redacts it. The default pool size is 10, and the default schema is public.
Each tenant has 10 Keyword slots and 3 slots each for Int, Datetime, Double, Bool, KeywordArray, and Text. Registrations take slots in registration order. System fields, including DexWorkQueuePermissions, do not consume slots. The registry is shared by the applications on the server. Registering another Attribute after a type reaches its limit returns FAILED_PRECONDITION. Startup also fails if the existing registry exceeds these limits. The registry has no delete operation. A full registry requires a new server.
An indexed Keyword value or KeywordArray element may contain at most 1024 UTF-8 bytes. A larger new write returns INVALID_ARGUMENT. A rejected Worker Step result causes that Step to fail. A previously committed document with an oversized value whose stored type matches its registered Keyword or KeywordArray type is omitted from search and recorded as a skipped document. Its Flow state remains committed. Unregistered keys do not trigger this skip.
A newly registered Attribute uses the existing JSONB query path until its slot backfill finishes. Results stay correct during that window, but queries can be slower. Backfill commits bounded batches and resumes after a restart. It does not pause projection of other partitions. Unregistered index keys also use JSONB. IS NULL, IS NOT NULL, and negations use stored key presence, including when the stored type differs from the registered type. Int and Double values remain distinct. A scalar string stored under KeywordArray matches as one element. One mismatch has a stricter result: positive Keyword predicates do not match a stored KeywordArray. The document remains searchable, and negations retain the builtin evaluator result. Negative predicates, such as != and NOT IN, often cannot use an index. Explicit ORDER BY StartTime and ORDER BY CloseTime sort all matching rows. Their cost grows with the number of matching rows.
Text matches all query terms. It does not rank results by BM25 relevance. Both external search backends use the same search projection and readYourWritesWait.
A schema records its search backend. Starting with a different backend fails. To switch between PostgreSQL and ParadeDB, select a new database or schema. The server loads it from committed state automatically. Existing ParadeDB version-three schemas gain the dialect marker without clearing data or changing their version. Older releases that accept schema version 3 can still use them. PostgreSQL uses schema version 4; older releases reject it.