postgres-elasticsearch-fdw
pg-tsquery
Our great sponsors
| postgres-elasticsearch-fdw | pg-tsquery | |
|---|---|---|
| 3 | 1 | |
| 106 | 56 | |
| - | - | |
| 4.2 | 4.3 | |
| 29 days ago | 3 months ago | |
| Python | JavaScript | |
| MIT License | - |
Stars - the number of stars that a project has on GitHub. Growth - month over month growth in stars.
Activity is a relative number indicating how actively a project is being developed. Recent commits have higher weight than older ones.
For example, an activity of 9.0 indicates that a project is amongst the top 10% of the most actively developed projects that we are tracking.
postgres-elasticsearch-fdw
- Full-text search engine with PostgreSQL (part 2): Postgres vs. Elasticsearch
-
Postgres Full Text Search vs. the Rest
My experience with Postgres FTS (did a comparison with Elastic a couple years back), is that filtering works fine and is speedy enough, but ranking crumbles when the resulting set is large.
If you have a large-ish data set with lots of similar data (4M addresses and location names was the test case), Postgres FTS just doesn't perform.
There is no index that helps scoring results. You would have to install an extension like RUM index (https://github.com/postgrespro/rum) to improve this, which may or may not be an option (often not if you use managed databases).
If you want a best of both worlds, one could investigate this extensions (again, often not an option for managed databases): https://github.com/matthewfranglen/postgres-elasticsearch-fd...
Either way, writing something that indexes your postgres database into elastic/opensearch is a one time investment that usually pays off in the long run.
-
Lesser Known PostgreSQL Features
I used a foreign data wrapper to query elasticsearch indexes from within postgres.[0]
It pushed alot of complexity down away from higher-level app developers not familiar with ES patterns.
[0]: https://github.com/matthewfranglen/postgres-elasticsearch-fd...
pg-tsquery
-
Postgres Full Text Search vs. the Rest
You could have a bit of logic on top of PG to better handle the search query, for example using https://github.com/caub/pg-tsquery
What are some alternatives?
rum - RUM access method - inverted index with additional information in posting lists
tbls - tbls is a CI-Friendly tool for document a database, written in Go.
pg-ulid - ULID Functions for PostgreSQL
pgvector - Open-source vector similarity search for Postgres
postgres-elasticsearch-fd
js-id - ID generation for JavaScript & TypeScript Applications
quickwit - Cloud-native search engine for observability. An open-source alternative to Datadog, Elasticsearch, Loki, and Tempo.
MeiliSearch - A lightning-fast search API that fits effortlessly into your apps, websites, and workflow
zombodb - Making Postgres and Elasticsearch work together like it's 2023
spec - The canonical spec for ulid
tantivy - Tantivy is a full-text search engine library inspired by Apache Lucene and written in Rust