Implementing Full Text Search

The FTS Crossroads: Elasticsearch vs. PostgreSQL

You're building search. You have LIKE '%...%' and users hate it. You look at Elasticsearch and PostgreSQL. ES feels heavy, PG feels limited. You spend weeks fighting analyzers and mappings. We built this skill so you don't have to. You need a definitive path for implementing full text search without the trial-and-error.

Every engineer who has shipped a search feature knows the pain. You start with a simple query, and suddenly you're drowning in configuration. Do you use a standard analyzer or a custom one? Do you need tsvector columns or just rely on the database's built-in functions? The gap between "it works on my machine" and "it handles 100k queries per second without latency spikes" is massive.

Most teams try to wing it. They copy-paste a schema from Stack Overflow, tweak a few settings, and hope for the best. This is how you end up with slow queries, high CPU usage, and a search bar that returns irrelevant results. You need a production-grade foundation. You need templates that handle the edge cases—highlighting, ranking, fuzzy matching, and prefix searches—before you write a single line of application code.

If you're looking for a broader guide, check out our Search Implementation Pack for a deeper dive into faceted search and relevance tuning. But for the core implementation, you need the right tools in your hand immediately.

The Hidden Costs of Search Debt

Search debt accumulates fast. Every day you spend debugging tsvector triggers or ES index mappings is billable hours wasted. If you get relevance wrong, users bounce. If you scale without proper indexing, infra costs explode.

We've seen teams pay for clusters they don't need because they didn't tune their analyzers properly ^[3]. Or they get stuck maintaining a secondary DB for search when their primary DB could do it ^[4]. The cost isn't just engineering time; it's lost revenue. A search bar that doesn't work is a feature that doesn't exist.

Consider the complexity of handling text blobs. If you're storing large text fields, you need to know how to index them effectively. Best practices for storing and searching text in XML documents highlight the nuances of text processing ^[6]. Without the right setup, your search becomes a bottleneck, not a feature.

And if you're considering alternatives like managed services, you might look at Implementing Search With Algolia to compare trade-offs. But even with managed services, you need to understand the underlying mechanics to debug performance issues when they arise.

When Search Becomes a Bottleneck

Imagine a logistics platform with millions of records. They start with Elasticsearch. It works, but then they need to join search results with transactional data. The latency spikes. They look at migrating off ElasticSearch as sole primary database for a relational business domain ^[4].

Or picture a team dealing with large text blobs in XML documents ^[6]. They need phonetic matching and fuzzy search but the results aren't effective ^[5]. This skill resolves these tensions by providing production-grade templates for both paths.

We've seen teams struggle with the "Zoo" of error formats and query types. A 2024 GitHub Engineering blog post describes how inconsistent error handling can derail a release ^[3]. Similarly, inconsistent search implementation can derail a product launch. You need a unified approach.

Consider a fintech with 200 endpoints. They need search across user profiles, transactions, and documents. They try to use PostgreSQL's built-in FTS. They set up tsvector columns, but the triggers fail to update correctly. Data becomes stale. Users see outdated results. The team spends weeks fixing the triggers instead of building features.

This is where the skill kicks in. It provides pg-search-schema.sql with triggers that keep tsvector in sync automatically. It provides es-index-config.json with mappings that handle custom analyzers out of the box. You get a working search implementation in hours, not weeks.

Production-Ready Search in One Install

Once installed, you have a scaffolded project. You get es-index-config.json that handles custom analyzers out of the box. You get pg-search-schema.sql with triggers that keep tsvector in sync. You run validate-es-mapping.sh and it passes.

The skill includes:

• skill.md — Orchestrator skill that defines architecture decisions for FTS (Elasticsearch vs PostgreSQL), query routing strategies, indexing patterns, and explicitly references all templates, references, scripts, validators, and examples for guided implementation.
• templates/es-index-config.json — Production-grade Elasticsearch 8.19 index mapping and settings template. Includes custom analyzers, text/keyword subfields, multi_match-ready structures, and semantic embedding field configuration based on ES Query DSL docs.
• templates/pg-search-schema.sql — Production-grade PostgreSQL schema template. Defines tsvector columns, to_tsvector generation expressions, GIN indexes, and a PL/pgSQL trigger for automatic tsvector updates, grounded in PG textsearch functions.
• templates/es-query-dsl.json — Production-grade Elasticsearch query DSL template. Features a bool query combining match, multi_match, term filters, and highlight configuration. Directly implements parameter structures from ES Query DSL reference.
• templates/pg-search-queries.sql — Production-grade PostgreSQL search query template. Uses @@ operator, to_tsquery, ts_rank for relevance scoring, prefix matching (:*), and pagination. Grounded in PG tsvector/tsquery documentation.
• references/es-query-dsl-reference.md — Canonical reference extracted from Elasticsearch 8.19 docs. Covers full-text queries (match, match_phrase, multi_match, query_string), term-level queries, bool composition, and semantic search parameters with exact JSON schemas and field definitions.
• references/pg-textsearch-reference.md — Canonical reference extracted from PostgreSQL docs. Covers tsvector @@ tsquery operator semantics, to_tsvector/to_tsquery normalization, array_to_tsvector, prefix matching syntax, and ranking functions with exact SQL signatures.
• scripts/scaffold-search.sh — Executable bash script that scaffolds a FTS project structure, copies templates into a target directory, sets up virtual environments, and runs a dry-run validation of the generated mapping and schema files.
• validators/validate-es-mapping.sh — Validator script that parses templates/es-index-config.json using python's json module, verifies required keys (mappings, properties, analyzer definitions), and exits with code 1 on structural or syntax failures.
• examples/worked-example-es.md — Step-by-step worked example for implementing FTS in Elasticsearch. Covers index creation, document indexing, query execution, highlighting, and semantic search integration using the provided templates and DSL reference.
• examples/worked-example-pg.md — Step-by-step worked example for implementing FTS in PostgreSQL. Covers schema setup, trigger configuration, tsvector/tsquery usage, ranking, prefix search, and performance tuning using the provided SQL templates and reference.

With this skill, you can implement search in hours. You get validated mappings, tested queries, and clear examples. You can also check out our Vector Search Pack if you need embeddings later. Or explore Building Semantic Search Engine for advanced use cases.

Ship Search Without the Guesswork

Stop wasting time on search implementation. Upgrade to Pro to install this skill and ship search that works. We built this so you don't have to. Get production-grade templates, validators, and examples. Install today.