{"schemaVersion":"1.0","type":"Article","types":["Article"],"slug":"how-we-built-fast-search-for-zyvop-without-elasticsearch-or-algolia-6p4im","url":"https://zyvop.com/how-we-built-fast-search-for-zyvop-without-elasticsearch-or-algolia-6p4im","title":"How We Built Fast Search for ZyVOP Without Elasticsearch or Algolia","subtitle":"A case study in using PostgreSQL full-text search, trigram indexes, and a small cache to make developer content easy to find.","tldr":"ZyVOP searches posts, authors, tags, and series with PostgreSQL and Redis. Here is how stored search vectors, trigram indexes, query expansion, and candidate-first ranking keep search responsive without Elasticsearch or Algolia.","keywords":["postgres","search","performance","ZyVOP","Case Studies","Building ZyVOP in Public"],"entities":["Sanju Singh","postgres","search","performance","ZyVOP","Case Studies","Building ZyVOP in Public"],"keyTakeaways":["Search is one of those features that looks simple until people start using it.","A developer might remember a tool by its name, search for a topic mentioned deep in an article, misspell a framework, or look for a writer instead of a post.","We wanted ZyVOP search to handle those everyday queries across posts, series, and authors."],"headings":["The problem with searching article text on every request","One query, several ways to find a post","Search should return useful results, not just matching text","Candidate recall still needs a golden test set","Keeping the interface quick","Why PostgreSQL was enough for us","What “fast” means here"],"outboundLinks":[],"contentText":"Search is one of those features that looks simple until people start using it. A developer might remember a tool by its name, search for a topic mentioned deep in an article, misspell a framework, or look for a writer instead of a post. We wanted ZyVOP search to handle those everyday queries across posts, series, and authors. We also wanted to keep publishing and search on the same source of truth. That led us to a practical question: could PostgreSQL do the job without adding Elasticsearch or Algolia? For our current workload, yes. The search experience is responsive, and the system is built from capabilities we already operate: PostgreSQL for search and Redis for short-lived result caching. There is no separate search index to synchronize with the database. The problem with searching article text on every request Our posts can contain long technical articles. The earlier query path rebuilt a text vector from post content while searching. That meant repeatedly processing large text values, including content PostgreSQL may store out of line in TOAST. The current search service calls out that rebuilding a vector from a full TOASTed article can take seconds. The query should not have to parse every article from scratch each time someone types a term. We moved that work to writes: PostgreSQL now maintains a stored search vector for each post, and a GIN index makes that vector searchable. ALTER TABLE \"posts\" ADD COLUMN \"search_vector\" tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('english', COALESCE(\"title\", '')), 'A') || setweight(to_tsvector('english', COALESCE(\"excerpt\", '')), 'B') || setweight(to_tsvector('english', COALESCE(\"content\", '')), 'C') ) STORED; CREATE INDEX idx_posts_search_vector_published ON \"posts\" USING GIN (\"search_vector\") WHERE \"status\" = 'PUBLISHED';The title, excerpt, and body are all searchable, and the vector stores a higher weight for the title than the excerpt or body. PostgreSQL updates the generated value when a post changes instead of rebuilding it for each search. The partial index focuses on published posts, which are the records public search can return. The weights are present in the vector; the current result score is calculated separately, as described below. One query, several ways to find a post Full-text search is good at finding words in an article body. It is not the whole search experience. A search for a slightly misspelled title, a tag, an author, or a category needs other match paths. We gather candidate post IDs from a few sources and combine them: Full-text matches use the stored vector to search titles, excerpts, and article content. Title matches use pg_trgm similarity for near matches and case-insensitive partial matching for prefixes and substrings. Metadata matches look through tags, categories, and author names or usernames. Tags, category names, and author names also use trigram similarity; usernames support case-insensitive partial matches. Technical synonyms expand common variants such as nextjs / next.js, nodejs / node.js, and postgres / postgresql. The query helper also sanitizes search tokens, turns them into prefix terms, and expands known synonyms before creating PostgreSQL text queries. That lets the application support common technical variants without passing raw user input into to_tsquery. The key performance choice is to separate finding candidates from scoring them. PostgreSQL can use its indexes to reduce the result set first. We then calculate ranking signals only for those matching posts, rather than running expensive ranking work across the entire corpus. Search should return useful results, not just matching text After gathering candidates, ZyVOP assigns a score using a few understandable signals: A title containing the query string gets a large boost. Trigram similarity in the title raises likely typo and near-title matches. A matching tag, category, or author adds a smaller relevance bonus. Views contribute a modest popularity signal. This means a post with the query in its title can rise above one that only matches in its body, while a tag or author search can still surface the right work. Once the top IDs are selected, the service loads their author, category, and tag relations and restores the score order for the response. The current ranking is intentionally simple. Full-text search determines whether body content is a candidate, but the score does not add a separate body-relevance rank. A post that matches only in its body can therefore be ordered mainly by title similarity, metadata bonuses, and views. That is a clear area to improve if search behavior shows that content-only matches need finer ordering. Candidate recall still needs a golden test set A ranking score cannot recover a post that candidate selection has already dropped. The current automated checks verify that public post search uses the stored vector and excludes posts that are not eligible for discovery. They do not yet measure recall against a stable set of queries with known expected posts. The next quality check should use a fixed, representative fixture and label the eligible posts expected for each query. It should include exact titles, body-only terms, tags and authors, uncommon technical terms, known synonyms, and realistic misspellings. We should inspect the candidate IDs before scoring and report candidate recall separately from ranking quality. A ranking metric such as MRR or NDCG is useful only after we know relevant posts survived candidate selection. Until that evaluation exists, we can describe the matching paths implemented in code, but we should not claim measured recall or broad typo coverage. Keeping the interface quick The search modal waits until the user has paused typing for 300 milliseconds, then requests posts, series, and authors together. This avoids sending a request for every keystroke while keeping results feeling immediate. For post search, the cache key includes the lowercased query and the requested page. ZyVOP’s cache service checks an in-process LRU cache and Redis, and keeps search results for up to 60 seconds. Repeated searches can therefore avoid rerunning the database query during that window. Search still works from PostgreSQL on a cache miss; Redis holds results, not a second search index. The service also limits page sizes and only returns published posts that pass the public discovery rules, including distribution, no-index, length, and review-status checks. Drafts and posts that are not eligible for public discovery do not leak into search results. There is also a fallback path. If the hybrid SQL query fails, the service logs the error and runs a simpler query-builder search across title, excerpt, author name, and tag name, ordered by publication date. This keeps a basic search path available, though it does not preserve the hybrid query’s body matching, fuzzy matching, category matching, or relevance score. Why PostgreSQL was enough for us Choosing PostgreSQL kept the search path close to the data it searches. A post edit updates its stored vector as part of the same database write. Search does not depend on an asynchronous export job catching up or on an external search service staying in sync. PostgreSQL still maintains its own GIN indexes, and those indexes add storage and write costs. That does not mean a dedicated search product is never the right choice. Elasticsearch or Algolia may be a better fit when a product needs advanced language analyzers, complex faceting, extensive typo controls, semantic retrieval, or search scale and traffic that PostgreSQL cannot meet comfortably. Indexes also have a cost: they take disk space and add work when records change. For ZyVOP’s current needs, PostgreSQL gives us the features we use—full-text search, typo-tolerant matching on short fields, filters, and relevance signals—without adding another system to deploy and maintain. What “fast” means here This case study describes the design behind ZyVOP’s responsive search, not a benchmark claim. We are not publishing a millisecond number because a useful figure needs a reproducible test against a defined corpus, database configuration, cache state, and traffic pattern. A number without those details would be easy to misread. The practical lesson is simpler: before adding a dedicated search cluster, make sure the database is not doing avoidable work. Store derived search data, index the fields that need fuzzy matching, narrow the candidate set before ranking, and cache repeated queries. For our workload, that has been enough to make search feel fast while keeping the architecture straightforward. If our data or search requirements change, we can benchmark the current path and make that decision with evidence. Until then, PostgreSQL remains both our application database and our search engine.","contentHash":"sha256:27c3da2271b0a20fb573263223265e2d2def1f9eb8ea649bebc7e08713b8561e","authorName":"Sanju Singh","authorUrl":"https://zyvop.com/author/sanjay687","authorSameAs":[],"category":"Case Studies","tags":["postgres","search","performance","ZyVOP"],"audience":"Software engineers and developers building applications with Case Studies","tone":"Practical and evidence-based engineering guidance","readingTimeMinutes":6,"wordCount":1376,"faqs":null,"primaryTopic":"Case Studies","publishedAt":"2026-11-03T04:36:56.582Z","updatedAt":"2026-11-03T09:25:47.094Z","canonicalUrl":"https://zyvop.com/how-we-built-fast-search-for-zyvop-without-elasticsearch-or-algolia-6p4im"}