--- title: "PostgreSQL and MySQL search providers" description: "How the built-in SQL providers store, rank, highlight, migrate, and validate the same portable search contract while reusing the application's database." canonical: "https://bylinecms.app/es/docs/search/postgres-mysql-search" locale: "es" collection: "docs" updated: "2026-08-24T06:57:58.397Z" --- # PostgreSQL and MySQL search providers How the built-in SQL providers store, rank, highlight, migrate, and validate the same portable search contract while reusing the application's database. Companions: - [Configure search](/docs/search/configure-search) — registration and collection configuration are shared across both providers. - [Indexing and reindexing](/docs/search/search-indexing) — both providers consume the same published-document lifecycle. - [Provider contract](/docs/search/search-provider-contract) — the shared interface and capability requirements. - [Portable multilingual analysis](/docs/search/multilingual-search-analysis) — both providers index the same logical terms and analyzer fingerprint. `@byline/search-postgres` and `@byline/search-mysql` provide the same application-facing search behavior over different database full-text primitives. Both reuse the host database pool and keep their derived index in the same database as the application. ## Shared behavior Both providers: - store one row per `(collectionPath, documentId, locale)`; - accept the same `SearchDocument`; - use `@byline/search-analysis`; - support all, any, minimum-should-match, and phrase matching; - preserve identifiers and Han-gram fallback; - map field boosts into A through D weight classes; - rank with deterministic tie-breaking; - return portable highlighted body snippets; - scope by collection or zone, locale, and status; - store facets and filters for future queries; - store one analyzer fingerprint metadata row per collection; - reject incompatible fingerprints; and - own an independent migration stream. Their capability reports are currently equivalent: ```typescript { facets: false, typoTolerance: false, semantic: false, bm25: false, weighting: true, highlights: true, fullText: { nativeAnalysis: false, portableAnalysis: true, allTerms: true, anyTerms: true, minimumShouldMatch: true, phrase: true, }, } ``` ## Implementation comparison | Concern | PostgreSQL | MySQL | | --- | --- | --- | | Package | `@byline/search-postgres` | `@byline/search-mysql` | | Driver peer | `pg` | `mysql2` | | Full-text storage | one weighted `tsvector` | one matching stream plus four weighted `FULLTEXT` streams | | Full-text index | GIN on `search_vector` | five InnoDB `FULLTEXT` indexes | | Zone storage | `text[]` with GIN | JSON | | Facet and filter storage | `jsonb` | JSON | | Ranking | `ts_rank` | weighted sum of `MATCH ... AGAINST` | | Weight realization | `tsvector` A through D positions | score multipliers 8, 4, 2, 1 | | Migration behavior | transactional per file | DDL auto-commit with advisory lock | | Whole-index clear | `TRUNCATE` | `DELETE` | ## PostgreSQL provider Register the provider with the pool returned by `pgAdapter`: ```typescript import { migrate, postgresSearch } from '@byline/search-postgres' await migrate(db.pool) const search = postgresSearch({ pool: db.pool, defaultLocale: 'en', }) ``` The `byline_search_documents` table stores: - original body text for highlights; - a weighted portable `tsvector`; - collection, document, locale, status, and zone scope; - title, path, and update time; - analyzer fingerprint; and - `jsonb` facet and filter projections. Portable terms are encoded before being rendered into the `tsvector`. Exact and identifier tokens keep their source field's weight. Derived tokens lose one weight class. Han grams use class D. The provider translates portable concepts into `to_tsquery('simple', ...)` expressions. `ts_rank` produces the score. Results order by score descending, update time descending, then collection path, document id, and locale so offset pagination is deterministic on ties. PostgreSQL `ts_rank` is not exposed as a BM25 guarantee, so `capabilities.bm25` remains false. ## MySQL provider Register the provider with the promise pool returned by `mysqlAdapter`: ```typescript import { migrate, mysqlSearch } from '@byline/search-mysql' await migrate(db.pool) const search = mysqlSearch({ pool: db.pool, defaultLocale: 'en', }) ``` MySQL's full-text parser has server-dependent stopword and minimum-token behavior. The provider avoids making those settings part of Byline's contract by encoding every logical token into parser-safe ASCII before MySQL sees it. The physical row contains: - `search_text` for matching semantics; - `search_a` through `search_d` for weighted scoring; - original body text for highlights; - scope and display metadata; - analyzer fingerprint; and - JSON facet and filter projections. The provider keeps separate source, expansion-kind, and Han-gram streams so phrase matching follows the analyzer's logical positions. Query phrase variants mirror those physical stream kinds. It does not generate impossible mixed-kind Cartesian products. The score is: ```text 8 * MATCH(search_a) + 4 * MATCH(search_b) + 2 * MATCH(search_c) + 1 * MATCH(search_d) ``` MySQL's native relevance value may include term-frequency and inverse-document-frequency behavior, but the provider does not claim a stable BM25 contract. ## Schema ownership Each provider ships: - `migrations/0001_init.sql` as the readable source of truth; - embedded migration data for bundled server runtimes; - `migrate(pool)` for explicit application; and - `autoMigrate` as a development convenience. The migration ledger is `byline_search_migrations`. Analyzer metadata lives in `byline_search_index_metadata`. Indexed documents live in `byline_search_documents`. These names are provider-owned and independent of the storage adapter's Drizzle migrations. The search index can be dropped without deleting CMS documents. ### PostgreSQL migration guarantees The PostgreSQL migrator creates the ledger, reads applied versions, and runs each pending file inside a transaction. It records the version only after the SQL succeeds. ### MySQL migration guarantees MySQL DDL auto-commits, so its migrator cannot provide the same per-file transaction. It: 1. acquires the `byline-search-mysql-migrations` server lock; 2. applies idempotent semicolon-delimited statements; 3. records the version only after all statements succeed; and 4. releases the lock before returning. Migration files must not contain stored routines that require internal delimiter changes. ## Analyzer metadata An upsert first establishes or reads the collection metadata row under a lock. A provider instance with another fingerprint rejects the write rather than mixing incompatible tokens. Before a query, the provider checks metadata scoped to the requested collection or zone. The check reads the compact collection metadata table and does not scan the document projection. After changing analyzer behavior: ```typescript await client.collection('docs').reindex() ``` The client method calls the provider clear operation first, then repopulates published content. ## Choosing a provider Use the provider that matches the database selected by the CLI; search does not require choosing or operating a second engine. PostgreSQL is the default; `byline init --database mysql` selects MySQL 8.0.14 or later. Example installations receive the matching database and search packages, connection environment variable, server adapter, and `migrate(pool)` call. Minimal installations omit search for either database. Both providers pass the same conformance suite and offer the same portable matching floor. A dedicated engine becomes useful when the product requires capabilities the SQL providers do not claim, such as mature typo tolerance, spelling suggestions, complex facet aggregation, semantic retrieval, or engine-specific ranking. That engine should still implement `SearchProvider` so authorization and hydration remain in the Byline pipeline.