Building PostgreSQL Full-Text Search with GIN Indexes and Trigger-Based Search Vectors

introduction

As LinkedUp evolved, simple SQL LIKE queries became insufficient for searching posts and users. I needed ranked results, fast lookups, and scalable performance without introducing Elasticsearch prematurely. PostgreSQL’s built-in full-text search capabilities provided a powerful and lightweight solution.

problem statement

Using ILIKE or wildcard-based search does not scale well. It prevents efficient index usage, causes sequential scans on large tables, and provides no relevance ranking. For a social feed system, search must be fast, accurate, and ordered by meaningful relevance.

naive approach and limitations

• ILIKE '%query%' prevents index usage.
• No ranking mechanism for relevance.
• Poor performance under larger datasets.
• Difficult to prioritize titles over content.

design decision

{
  "core_feature": "PostgreSQL Full-Text Search",
  "index_type": "GIN (Generalized Inverted Index)",
  "update_strategy": "Trigger-maintained search_vector column",
  "ranking": "ts_rank for relevance scoring"
}

architecture overview

I introduced a dedicated search_vector column in the posts table. This column stores a weighted tsvector built from multiple text fields (e.g., title, content). A GIN index was created on this column to allow fast inverted index lookups. A database trigger automatically updates the search_vector whenever content changes.

implementation details

{
  "search_vector_column": "Added a generated or manually updated tsvector column combining title and content fields.",
  "weighting_strategy": "Applied higher weight to titles (A) and lower weight to content (B) to prioritize important fields.",
  "gin_index": "Created a GIN index on the search_vector column for fast lookup.",
  "trigger_logic": "A BEFORE INSERT OR UPDATE trigger recalculates the search_vector automatically."
}

example query pattern

Search queries use to_tsquery or plainto_tsquery combined with ts_rank(search_vector, query) to rank results by relevance and return ordered output.

performance considerations

• GIN indexes drastically reduce lookup time compared to sequential scans.
• Trigger-based maintenance avoids stale search vectors.
• Index size must be monitored as dataset grows.
• Query normalization improves ranking accuracy.

security and validation

• Sanitized user input before constructing tsquery.
• Avoided dynamic SQL injection risks.
• Limited maximum query length to prevent abuse.

tradeoffs

{
  "pros": [
    "No external search engine required.",
    "Native PostgreSQL integration.",
    "Strong relevance ranking.",
    "Simpler deployment architecture."
  ],
  "cons": [
    "Less advanced than Elasticsearch for complex linguistic features.",
    "Requires careful indexing strategy.",
    "Index size increases with content volume."
  ]
}

why not elasticsearch

• Operational overhead for early-stage system.
• Extra infrastructure complexity.
• PostgreSQL FTS sufficient for current scale.

lessons learned

• Built-in database features can solve complex problems efficiently.
• Indexes must align with query patterns.
• Search quality depends heavily on weighting strategy.
• Premature introduction of external services increases maintenance burden.

future improvements

• Add trigram index for fuzzy search support.
• Implement search result highlighting.
• Introduce caching layer for frequent queries.
• Add multi-language support if platform expands.

conclusion

By leveraging PostgreSQL’s full-text search with GIN indexing and trigger-maintained search vectors, I implemented a scalable and ranked search system without external dependencies. This approach balances performance, simplicity, and architectural cleanliness — aligning with LinkedUp’s evolving backend design.