Full-Text Search on Plain Postgres for a Read-Later App

GYevhen Viktorov··Updated ·5 min read

Textivore's full-text search runs on plain Postgres: one weighted tsvector column, one trigger, one GIN index. No Elasticsearch, no Meilisearch, no sync pipeline. This post walks through the actual schema and queries, including the parts I'd build differently today.

Last reviewed: July 28, 2026.

Why not a search engine

Search over a saved-article library is the feature Textivore sells hardest, so a dedicated search engine was the obvious first thought. I decided against it for a boring reason: every article already lives in Postgres, and a second datastore means a sync pipeline plus a brand new way for search results to disagree with the database.

The workload also doesn't need it. A read-later library is thousands of rows per user, not millions, and every query is scoped to one user_id. Postgres handles that shape comfortably, and you keep transactional consistency for free: an article is searchable the moment the insert commits.

One column, weighted

Everything searchable about an article gets folded into a single tsvector column with weights, so a match in the title outranks the same match buried in paragraph twelve:

NEW.search_vector :=
    setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(extract_tags_text(NEW.tags), '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.author, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(NEW.excerpt, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(extract_content_blocks_text(NEW.content_blocks), '')), 'C') ||
    setweight(to_tsvector('english', COALESCE(NEW.domain, '')), 'D');

Title and tags get weight A, author and excerpt B, body text C, and the source domain D. Later, ts_rank_cd turns those weights into ranking, so a search for "docker" surfaces the article titled "Docker networking" above the fifty articles that merely mention it.

The interesting part is extract_content_blocks_text. Article bodies are stored as a JSONB array of typed blocks (paragraphs, headings, lists, quotes, code), and this plpgsql function walks the array, keeps the searchable block types, and strips HTML tags with a regex, all inside Postgres. The alternative was extracting plain text in Go and storing it in a second column, which means one more thing to keep in sync. Doing it in the database keeps the tsvector derivable from the row itself.

A trigger rebuilds the vector whenever any source field changes, and only then:

CREATE TRIGGER trigger_update_article_search_vector
    BEFORE INSERT OR UPDATE OF title, excerpt, author, tags, content_blocks, domain
    ON articles
    FOR EACH ROW
    EXECUTE FUNCTION update_article_search_vector();

The OF column, ... clause matters. Marking an article as read is an update too, and without the column list every read/unread toggle would re-extract and re-stem the entire article body for nothing.

A GIN index on the column makes lookups fast, and the backfill migration updated existing rows in batches of 1,000 to avoid holding long locks on a live table.

The query side, where it gets less textbook

The search endpoint doesn't just pass your query to to_tsquery. Raw user input crashes it ("docker &" is a syntax error), so a preprocessing step keeps terms of three or more characters, escapes them, and turns each into a prefix match:

"postgres full text" becomes "postgres:* & full:* & text:*"

Prefix matching is what makes search-as-you-type behave: "kuber" finds the Kubernetes articles before you finish the word.

Then there's a fallback that pure full-text can't cover. When you import a thousand bookmarks from Pocket, many arrive as metadata only: a title, a URL, some tags, and no body text. Those rows have thin tsvectors. So the search condition is an OR group: match the tsvector, or match the title, URL, or a tag by substring. Imported articles stay findable even though their content was never fetched, and exact URL fragments ("that article from lwn.net") work as search terms.

Ranking applies when the full-text branch has something to say, and everything falls back to recency:

ORDER BY rank DESC, created_at DESC

Tag filters (any-of or all-of, using && and @> on a text array), date ranges, and read/archived state compose into the same WHERE clause, and archived articles are excluded unless you ask for them.

The wrinkle I fixed

For the first few months there was a mismatch in the setup above: the stored vectors used the english configuration, which stems words ("searching" is indexed as "search"), while queries ran through simple with prefix wildcards, which doesn't stem. Typing the exact word or a prefix of it worked, which covers most real queries. Typing an inflected form longer than the stored stem, "searching" against a body that only produced the token "search", missed in the full-text branch; the substring fallback caught it in titles, URLs, and tags, but not in body text.

The fix was one constant: queries now go through to_tsquery('english', ...), the same configuration the trigger uses, so "searching:" is stemmed to "search:" before it meets the index. The same change added a short body snippet to each result with ts_headline, so you can see why an article matched. I'm keeping this section because "we run our search on Postgres" posts tend to skip the part where a config mismatch sat unnoticed in production.

Would I make the same call again

Yes, for this product. A per-user library measured in the thousands of articles doesn't need a search cluster, and shipping search as one migration plus one query builder meant it existed weeks earlier than it otherwise would have. If Textivore ever needs typo tolerance or cross-language stemming, that's when a dedicated engine earns its operational cost.

The search described above is exactly what runs on textivore.com, and it is the reason I keep arguing that a reading library beats browser bookmarks. If you're a developer picking a read-later app, full-text search should be near the top of your list.

Related Articles