---
title: "Search backends"
description: "External search projection"
canonical_url: "https://docs.superdurable.io/zh-Hans/reference/server/search/"
source: "docs/reference/server/search.mdx"
locale: "zh-Hans"
---

# Search backends

<a id="search-projection"></a>

## External 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.

<a id="search-projection-behavior"></a>

### 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.

<a id="search-projection-initial-load"></a>

### 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](https://docs.superdurable.io/zh-Hans/reference/server/search.md#search-projection-read-your-writes).

<a id="search-projection-read-your-writes"></a>

### 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.

<a id="search-projection-configuration"></a>

### 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**.              |

<a id="search-projection-monitoring"></a>

### 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                                        |

```text
# 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.

```yaml
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](https://docs.superdurable.io/zh-Hans/reference/server/search.md#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.