Product search index job crash - postgress

Replace the search_index_item.description B-tree with an app-owned PostgreSQL full-text GIN index

Status

Proposal — no database index has been changed by this document.

This records the investigation into the failed search-index rebuild and proposes a durable PostgreSQL implementation. The recommended solution is deliberately more than DROP INDEX / CREATE INDEX: it makes the search document, the query strategy, the migration, and the Vendure-upgrade contract explicit application-owned code.

Executive summary

The manual search-index rebuild failed because PostgreSQL tried to insert a long, mostly Hebrew product description into a B-tree index on search_index_item.description. PostgreSQL B-tree index tuples have a 2704-byte limit; the value was truncated by Vendure to 2600 characters, but Hebrew UTF-8 characters commonly consume two bytes, so the resulting value was still 4482–4724 bytes.

The failing index is not useful for Vendure's PostgreSQL full-text query. Vendure's PostgresSearchStrategy searches a to_tsvector(...) document; a plain B-tree on the raw description text cannot serve that expression. The proper replacement is a GIN index on the same tsvector search document that the application queries.

The important compatibility caveat is that the B-tree currently originates in Vendure's SearchIndexItem entity metadata. A database-only change would work at runtime (synchronize is off), but Vendure migration generation would keep proposing the core B-tree on later upgrades. We must therefore version-control a small pnpm patch which removes that core metadata decorator, alongside the app-owned migration and search strategy. It will either reapply cleanly or fail visibly when Vendure is upgraded; it must never be an untracked edit in node_modules.

What failed

The dashboard's Rebuild search index action created job record 24168 in the update-search-index queue. It failed with:

index row size 2712 exceeds btree version 4 maximum 2704 for index
"IDX_9a5a6a556f75c4ac7bfdd03410"

PostgreSQL confirms that index is exactly:

CREATE INDEX "IDX_9a5a6a556f75c4ac7bfdd03410"
ON "search_index_item" USING btree ("description");

This explains why rebuilding the search index did not fix the collection-product display issue: the rebuild job itself aborted. It is also a separate concern from collection filter inheritance. The earlier inheritance migration set every collection.inheritFilters value to false; rebuilding the search index does not re-run the collection-filter membership job.

Ownership and origin

The index was created by the repository's initial Vendure schema migration, not by a custom product-search feature:

• src/migrations/1787321024686-initialize-db.ts creates search_index_item and creates the index at line 150.
• src/vendure-config.ts enables DefaultSearchPlugin.init({ bufferUpdates: false, indexStockStatus: true }).
• In the installed Vendure core, SearchIndexItem.description is annotated as a full-text index. On this PostgreSQL database, the generated schema is nevertheless a plain B-tree index.

So this is a Vendure core schema-metadata behaviour surfaced through our generated initial migration. It is not an index hand-authored for Shoplifter.

Why Vendure's guard did not prevent it

Vendure's installed search indexer contains the PostgreSQL-specific guard equivalent to:

return description.substring(0, 2600);

That constrains JavaScript UTF-16 characters, not PostgreSQL index bytes. It happens to be adequate for short ASCII-heavy descriptions, but is insufficient for long UTF-8 text. Hebrew was the decisive case here.

substring(..., 2600) therefore produced values whose observed sizes were safely over the database limit even though their character count was 2600.

Proof and exact offending source rows

We did not infer this solely from the job error.

1. A temporary-table probe attempted to create the same B-tree index for every currently persisted search_index_item.description; all existing rows passed. That is expected: an indexing transaction that fails does not persist its new failing row.

2. A second probe used the actual product-translation source and the same PostgreSQL transformation Vendure applies:

   sql复制

   substring(description FROM 1 FOR 2600)

   It inserted one candidate at a time into a temporary table and created an equivalent B-tree index. The following four rows independently reproduce the failure.

Any one of these rows is sufficient to fail index rebuild. The failed job log cannot reliably identify which row it encountered first, but the probe proves all four.

Why a raw GIN index is not the replacement

The replacement must be a GIN index over a tsvector, not a GIN index on raw description text.

Vendure's PostgreSQL strategy builds a search document from SKU, product name, variant name, and description using to_tsvector(...), then matches it using to_tsquery(...). An index must match the query expression to be useful. A raw B-tree on description does not; nor would a generic GIN (description) be the correct implementation for this query shape.

pg_trgm plus gin_trgm_ops is a valid alternative only if we deliberately move to substring/ILIKE search. It is not the correct index for the current full-text strategy. The proposed tsvector GIN index uses built-in PostgreSQL functionality and does not require installing the pg_trgm extension, which keeps it compatible with managed PostgreSQL/RDS permissions.

Not sure what the proper solution is

I’m not yet certain of the correct Vendure-core fix because this sits between three layers:

Vendure marks the search fields as @Index({ fulltext: true }), but on PostgreSQL TypeORM generates ordinary B-tree indexes. The fulltext option is effectively MySQL-specific, and TypeORM currently has no metadata option for PostgreSQL USING GIN.

Vendure’s PostgreSQL search strategy queries a computed to_tsvector(...) document, so a correct GIN index must match that exact expression/configuration. That raises design questions for core: whether to add a stored tsvector column, use a functional GIN index, or change the strategy; and how to ship the PostgreSQL-specific DDL while retaining Vendure’s multi-database support.

So the immediate failure is clear—the B-tree index on UTF-8 descriptions—but I’m not confident a simple decorator change is the right upstream fix. It may require coordinated changes to entity metadata, PostgreSQL migrations, and PostgresSearchStrategy.