https://github.com/paradedb/paradedb/blob/dev/pg_bm25/README.md#
{"payload":{"allShortcutsEnabled":false,"fileTree":{"pg_bm25":{"items":[{"name":"sql","path":"pg_bm25/sql","contentType":"directory"},{"name":"src","path":"pg_bm25/src","contentType":"directory"},{"name":"test","path":"pg_bm25/test","contentType":"directory"},{"name":".gitignore","path":"pg_bm25/.gitignore","contentType":"file"},{"name":"Cargo.lock","path":"pg_bm25/Cargo.lock","contentType":"file"},{"name":"Cargo.toml","path":"pg_bm25/Cargo.toml","contentType":"file"},{"name":"README.md","path":"pg_bm25/README.md","contentType":"file"},{"name":"pg_bm25.control","path":"pg_bm25/pg_bm25.control","contentType":"file"}],"totalCount":8},"":{"items":[{"name":".cargo","path":".cargo","contentType":"directory"},{"name":".github","path":".github","contentType":"directory"},{"name":"benchmarks","path":"benchmarks","contentType":"directory"},{"name":"docker","path":"docker","contentType":"directory"},{"name":"docs","path":"docs","contentType":"directory"},{"name":"pg_bm25","path":"pg_bm25","contentType":"directory"},{"name":"pg_search","path":"pg_search","contentType":"directory"},{"name":"scripts","path":"scripts","contentType":"directory"},{"name":".gitignore","path":".gitignore","contentType":"file"},{"name":".hadolint.yaml","path":".hadolint.yaml","contentType":"file"},{"name":".markdownlint.yaml","path":".markdownlint.yaml","contentType":"file"},{"name":".pre-commit-config.yaml","path":".pre-commit-config.yaml","contentType":"file"},{"name":".prettierignore","path":".prettierignore","contentType":"file"},{"name":"CODE_OF_CONDUCT.md","path":"CODE_OF_CONDUCT.md","contentType":"file"},{"name":"CONTRIBUTING.md","path":"CONTRIBUTING.md","contentType":"file"},{"name":"Cargo.lock","path":"Cargo.lock","contentType":"file"},{"name":"Cargo.toml","path":"Cargo.toml","contentType":"file"},{"name":"LICENSE","path":"LICENSE","contentType":"file"},{"name":"README.md","path":"README.md","contentType":"file"},{"name":"SECURITY.md","path":"SECURITY.md","contentType":"file"},{"name":"THANKYOU.md","path":"THANKYOU.md","contentType":"file"}],"totalCount":21}},"fileTreeProcessingTime":5.458953,"foldersToFetch":[],"reducedMotionEnabled":null,"repo":{"id":660776503,"defaultBranch":"dev","name":"paradedb","ownerLogin":"paradedb","currentUserCanPush":false,"isFork":false,"isEmpty":false,"createdAt":"2023-06-30T20:21:47.000Z","ownerAvatar":"https://avatars.githubusercontent.com/u/124464601?v=4","public":true,"private":false,"isOrgOwned":true},"symbolsExpanded":false,"treeExpanded":true,"refInfo":{"name":"dev","listCacheKey":"v0:1696784857.0","canEdit":false,"refType":"branch","currentOid":"bb4f2890942b85be3e9736bb3e8f17dcf659c0a1"},"path":"pg_bm25/README.md","currentUser":null,"blob":{"rawLines":null,"stylingDirectives":null,"csv":null,"csvError":null,"dependabotInfo":{"showConfigurationBanner":false,"configFilePath":null,"networkDependabotPath":"/paradedb/paradedb/network/updates","dismissConfigurationNoticePath":"/settings/dismiss-notice/dependabot_configuration_notice","configurationNoticeDismissed":null,"repoAlertsPath":"/paradedb/paradedb/security/dependabot","repoSecurityAndAnalysisPath":"/paradedb/paradedb/settings/security_analysis","repoOwnerIsOrg":true,"currentUserCanAdminRepo":false},"displayName":"README.md","displayUrl":"https://github.com/paradedb/paradedb/blob/dev/pg_bm25/README.md?raw=true","headerInfo":{"blobSize":"6.7 KB","deleteInfo":{"deleteTooltip":"You must be signed in to make or propose changes"},"editInfo":{"editTooltip":"You must be signed in to make or propose changes"},"ghDesktopPath":"https://desktop.github.com","gitLfsPath":null,"onBranch":true,"shortPath":"2838c67","siteNavLoginPath":"/login?return_to=https%3A%2F%2Fgithub.com%2Fparadedb%2Fparadedb%2Fblob%2Fdev%2Fpg_bm25%2FREADME.md","isCSV":false,"isRichtext":true,"toc":[{"level":1,"text":"","anchor":"--","htmlText":""},{"level":2,"text":"Overview","anchor":"overview","htmlText":"Overview"},{"level":3,"text":"Roadmap","anchor":"roadmap","htmlText":"Roadmap"},{"level":2,"text":"Running the Extension","anchor":"running-the-extension","htmlText":"Running the Extension"},{"level":3,"text":"From ParadeDB","anchor":"from-paradedb","htmlText":"From ParadeDB"},{"level":3,"text":"From Self-Hosted Postgres","anchor":"from-self-hosted-postgres","htmlText":"From Self-Hosted Postgres"},{"level":2,"text":"Usage","anchor":"usage","htmlText":"Usage"},{"level":3,"text":"Indexing","anchor":"indexing","htmlText":"Indexing"},{"level":3,"text":"Basic Search","anchor":"basic-search","htmlText":"Basic Search"},{"level":2,"text":"Development","anchor":"development","htmlText":"Development"},{"level":3,"text":"Prerequisites","anchor":"prerequisites","htmlText":"Prerequisites"},{"level":3,"text":"Running the Extension","anchor":"running-the-extension-1","htmlText":"Running the Extension"},{"level":3,"text":"Modifying the Extension","anchor":"modifying-the-extension","htmlText":"Modifying the Extension"},{"level":3,"text":"Testing","anchor":"testing","htmlText":"Testing"},{"level":2,"text":"License","anchor":"license","htmlText":"License"}],"lineInfo":{"truncatedLoc":"260","truncatedSloc":"188"},"mode":"file"},"image":false,"isCodeownersFile":null,"isPlain":false,"isValidLegacyIssueTemplate":false,"issueTemplateHelpUrl":"https://docs.github.com/articles/about-issue-and-pull-request-templates","issueTemplate":null,"discussionTemplate":null,"language":"Markdown","languageID":222,"large":false,"loggedIn":false,"newDiscussionPath":"/paradedb/paradedb/discussions/new","newIssuePath":"/paradedb/paradedb/issues/new","planSupportInfo":{"repoIsFork":null,"repoOwnedByCurrentUser":null,"requestFullPath":"/paradedb/paradedb/blob/dev/pg_bm25/README.md","showFreeOrgGatedFeatureMessage":null,"showPlanSupportBanner":null,"upgradeDataAttributes":null,"upgradePath":null},"publishBannersInfo":{"dismissActionNoticePath":"/settings/dismiss-notice/publish_action_from_dockerfile","dismissStackNoticePath":"/settings/dismiss-notice/publish_stack_from_file","releasePath":"/paradedb/paradedb/releases/new?marketplace=true","showPublishActionBanner":false,"showPublishStackBanner":false},"rawBlobUrl":"https://github.com/paradedb/paradedb/raw/dev/pg_bm25/README.md","renderImageOrRaw":false,"richText":" Check out the The easiest way to test the extension is to run the ParadeDB Dockerfile: This will spin up a Postgres instance with If you are self-hosting Postgres and would like to use the extension within your existing\nPostgres, follow these steps: You can then create the extension in your database by running: If you are using a managed Postgres service like Amazon RDS, you will not be able to install By default, the To index a table, use the following SQL command: Once the indexing is complete, you can run various search functions on it. Execute a search query on your indexed table: This will return: Scoring and highlighting are supported: This will return: Scores can be tuned via boosted queries: New data that arrives or rows that are changed are automatically reindexed and searchable. For instance,\nlet's create and search for a new row in our table: This will return: Please refer to the documentation for a more thorough overview\nof Before developing the extension, ensure that you have Rust installed\n(version >1.70), ideally via Then, install and initialize pgrx: First, start pgrx: This will launch an interactive connection to Postgres. Inside Postgres, create\nthe extension by running: Now, you have access to all the extension functions. If you make changes to the extension code, follow these steps to update it: To run the unit test suite, use the following command: This will run all unit tests defined in To run the integration test suite, simply run: This will create a temporary database, initialize it with the SQL commands defined\nin \n
\n![\"pg_bm25\"](\"/paradedb/paradedb/raw/dev/docs/logo/pg_bm25.svg\"){kind=logo}
\n
\n![\"Testing\"](\"https://github.com/paradedb/paradedb/actions/workflows/test-pg_bm25.yml/badge.svg\"){kind=icon}
Overview
\npg_bm25 is a PostgreSQL extension that enables full text search over SQL tables\nusing the BM25 algorithm, the state-of-the-art ranking function\nfor full text search. It is built on top of Tantivy, the Rust-based alternative to Apache\nLucene, using pgrx.pg_bm25 is supported on PostgreSQL 11+.pg_bm25 benchmarks here.Roadmap
\n\n
\nRunning the Extension
\nFrom ParadeDB
\ndocker run \\n -e POSTGRES_USER=<user> \\n -e POSTGRES_PASSWORD=<password> \\n -e POSTGRES_DB=<dbname> \\n -p 5432:5432 \\n -d \\n paradedb/paradedb:latest
pg_bm25 preinstalled.From Self-Hosted Postgres
\n\n
\ncurl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh\ncargo install cargo-pgrx --version 0.9.8
\n
\n# Clone the repo (optionally pick a specific version)\ngit clone https://github.com/paradedb/paradedb.git --tag <VERSION>\n\n# Install pg_bm25\ncd pg_bm25/\ncargo pgrx init --pg<YOUR-POSTGRES-MAJOR_VERSION>=
</span>which pg_config<span class=\"pl-pds\">\ncargo pgrx installCREATE EXTENSION pg_bm25;
pg_bm25\nuntil the Postgres service explicitly supports it.Usage
\nIndexing
\npg_bm25 extension creates a table called paradedb.mock_items\nthat you can use for quick experimentation.CREATE TABLE mock_items AS SELECT FROM paradedb.mock_items;\n\nCREATE INDEX idx_mock_items\nON mock_items\nUSING bm25 ((mock_items.))\nWITH (text_fields='{\"description\": {}, \"category\": {}}');
Basic Search
\nSELECT description, rating, category\nFROM mock_items\nWHERE mock_items @@@ 'description:keyboard OR category:electronics'\nLIMIT 5;
description | rating | category\n-----------------------------+--------+-------------\n Plastic Keyboard | 4 | Electronics\n Ergonomic metal keyboard | 4 | Electronics\n Innovative wireless earbuds | 5 | Electronics\n Fast charging power bank | 4 | Electronics\n Bluetooth-enabled speaker | 3 | Electronics\n(5 rows)\nSELECT description, rating, category, paradedb.rank_bm25(ctid), paradedb.highlight_bm25(ctid, 'description')\nFROM mock_items\nWHERE mock_items @@@ 'description:keyboard OR category:electronics'\nLIMIT 5;
id | description | rating | category | rank_bm25 | highlight_bm25\n----+-----------------------------+--------+-------------+-----------+---------------------------------\n 1 | Ergonomic metal keyboard | 4 | Electronics | 4.9403534 | Ergonomic metal <b>keyboard</b>\n 2 | Very plasticy keyboard | 4 | Electronics | 4.9403534 | Very plasticy <b>keyboard</b>\n 12 | Innovative wireless earbuds | 5 | Electronics | 2.1096356 |\n 22 | Fast charging power bank | 4 | Electronics | 2.1096356 |\n 32 | Bluetooth-enabled speaker | 3 | Electronics | 2.1096356 |\n(5 rows)\nSELECT description, rating, category\nFROM mock_items\nWHERE mock_items @@@ 'description:keyboard^2 OR category:electronics';
INSERT INTO mock_items (description, rating, category) VALUES ('New keyboard', 5, 'Electronics');\n\nSELECT description, rating, category\nFROM mock_items\nWHERE mock_items @@@ 'description:keyboard OR category:electronics'\nLIMIT 5;
description | rating | category\n-----------------------------+--------+-------------\n New keyboard | 5 | Electronics\n Plastic Keyboard | 4 | Electronics\n Ergonomic metal keyboard | 4 | Electronics\n Innovative wireless earbuds | 5 | Electronics\n Fast charging power bank | 4 | Electronics\n(5 rows)\npg_bm25's query support.Development
\nPrerequisites
\nrustup (we've observed issues with installing Rust\nvia Homebrew on macOS).cargo install cargo-pgrx --version 0.9.8\ncargo pgrx init
Running the Extension
\ncargo pgrx run
CREATE EXTENSION pg_bm25;
Modifying the Extension
\n\n
\ncargo pgrx run
\n
\nDROP EXTENSION pg_bm25;\nCREATE EXTENSION pg_bm25;
Testing
\ncargo pgrx test/src. To add a new unit test, simply add\ntests inline in the relevant files, using the #[cfg(test)] attribute../test/runtests.sh
fixtures.sql, and run the tests in /test/sql against it. To add a new test,\nsimply add a new .sql file to /test/sql and a corresponding .out file to\n/test/expected for the expected output, and it will automatically get picked up\nby the test suite.License
\npg_bm25 is licensed under the GNU Affero General Public License v3.0.