Full text search over Postgres: Elasticsearch vs. alternatives

Original Article ↗ Hacker News Discussion ↗

Engineers weigh the trade-offs between using PostgreSQL’s built-in full-text search and dedicated engines like Elasticsearch, OpenSearch, Meilisearch, Typesense, Solr, and newer Postgres-based tools such as ParadeDB. Key themes include relevance quality (e.g., BM25 scoring), support for facets and analytics-style aggregations, multi-tenant behavior, and the operational reliability and complexity of running Elasticsearch at scale. Many see Postgres FTS as adequate for simple or smaller workloads, but favor specialized search systems or Postgres extensions when ranking quality, scalability, or advanced search features become critical.

Postgres FTS vs. Elasticsearch

  • Several commenters say Postgres FTS is fine for simple or low-scale use (blogs, small apps), but becomes painful at larger scales, especially for facets and relevance.
  • Postgres lacks BM25 and global term statistics in core, limiting ranking quality; extensions like RUM and ParadeDB/pg_search aim to close this gap.
  • Some argue you eventually end up re‑implementing an ETL‑like pipeline and specialized indexing inside Postgres, without the full feature set of a dedicated search engine.

Elasticsearch Reliability & Operations

  • Mixed experiences: some report “random” failures, GC issues, and operational headaches, others say Elasticsearch is reliable if sized and configured correctly.
  • Common pitfalls: dynamic mapping creating huge schemas, underprovisioned clusters, poor shard/index design, and weak backup practices.
  • Replication, corruption handling, and leader election reliability are debated; some feel it’s improved since ~7.x, others remain wary.
  • Serverless Elasticsearch/OpenSearch is seen as promising but with tradeoffs (S3-backed, eventual visibility of new data, cold starts).

Faceted Search & Analytics

  • Facets/aggregations over large result sets are cited as a major reason to prefer Elasticsearch/Solr/OpenSearch.
  • Postgres can do facets but it’s complex and slow at scale; extensions like pgfaceting and ParadeDB’s aggregations aim to help.
  • Facets + FTS + analytics in one query is described as a key “stickiness” factor for Elastic-style systems.

BM25, Ranking, and Multi‑Tenancy

  • BM25 support is viewed as a big differentiator; SQLite FTS5 and Elasticsearch have it; core Postgres does not.
  • Multi‑tenant BM25 is tricky: corpus‑wide stats can leak information or skew relevance between tenants. Suggested mitigations include per-tenant indexes, partial/partitioned indexes, or per-DB/collection isolation.
  • Shard-level stats can affect scoring; there are workarounds (e.g., special query modes, 1‑shard indices).

Semantic/Vector Search and Hybrid Approaches

  • Consensus that vector/semantic search complements, not replaces, keyword/BM25 search.
  • Hybrid approaches (BM25 + vectors, reciprocal rank fusion, query rewriting) are recommended, especially when domain-specific terms or IDs are important.
  • Keeping two separate datastores (vector + keyword engine) in sync is seen as operationally fragile; some advocate unified systems that combine both.

Alternatives: Meilisearch, Typesense, Solr, Others

  • Meilisearch and Typesense praised for simplicity, developer experience, and ease of self-hosting, though feature sets are narrower than Elastic.
  • Solr is positioned as offering most of Elasticsearch’s Lucene-based power without Elastic’s ecosystem baggage but with less mindshare and more manual scaling.
  • Other options mentioned: Manticore, ArangoDB (for FTS + graphs), Manticore’s MySQL-friendly protocol, and newer hybrid engines (e.g., Tantivy-based, Infinity).

ParadeDB / pg_search Notes

  • ParadeDB’s pg_search is described as bringing BM25 and “Elastic-like” FTS into Postgres, distributed as an extension or a separate, replicated Postgres instance.
  • Earlier versions were weakly consistent; current BM25 indexes are said to be strongly consistent, with minor transactional overhead.
  • Many users reportedly deploy ParadeDB as a separate “search Postgres” fed via logical replication from primary OLTP Postgres to decouple workloads.