Use the ZomboDB extension for full-text search with Elasticsearch - PolarDB

ZomboDB is a PostgreSQL extension that adds full-text search and analytics to PolarDB by integrating directly with Elasticsearch. It manages remote Elasticsearch indexes automatically, so your SQL queries return transactionally correct full-text search results without manual synchronization.

How it works

ZomboDB sits between PolarDB and Elasticsearch as a custom index type. When you create a ZomboDB index on a table, ZomboDB takes ownership of a corresponding remote Elasticsearch index and keeps it in sync automatically. The ==> operator lets you write full-text queries in SQL, using ZomboDB's query language against the Elasticsearch index while joining results with your relational data.

Install and remove the ZomboDB extension

Install the extension:

CREATE EXTENSION zombodb;

Remove the extension:

DROP EXTENSION zombodb;

Get started

The following steps walk through creating a table, indexing it with ZomboDB, and running full-text queries against it.

Step 1: Create a table

This table simulates a product catalog with text fields, keywords, and numeric attributes:

CREATE TABLE products (
    id SERIAL8 NOT NULL PRIMARY KEY,
    name text NOT NULL,
    keywords varchar(64)[],
    short_summary text,
    long_description zdb.fulltext,
    price bigint,
    inventory_count integer,
    discontinued boolean default false,
    availability_date date
);

The zdb.fulltext type marks long_description for full-text indexing in Elasticsearch.

Step 2: Create a ZomboDB index

Create a ZomboDB index on the products table. The WITH (url=...) clause points to your Elasticsearch cluster endpoint:

CREATE INDEX idxproducts
          ON products
       USING zombodb ((products.*))
        WITH (url='localhost:9200/');

The (products.*) syntax tells ZomboDB to index all columns in the table.

The url value must point to a running Elasticsearch cluster. ZomboDB does not support Elasticsearch 7.x or 8.x.

Step 3: Query with the ZomboDB index

All ZomboDB queries use the ==> operator. ZomboDB's query language supports boolean combinations, phrase matching, proximity, and range filters.

Search for products whose keywords contain sports or box, or whose description contains the phrase "wooden away" within 5 words, with a price between 1,000 and 20,000:

SELECT *
  FROM products
 WHERE products ==> '(keywords:(sports OR box) OR long_description:"wooden away"~5) AND price:[1000 TO 20000]';

For the full query syntax reference, see ZomboDB documentation.

What's next

Review the ZomboDB documentation for advanced topics including scoring, nested objects, and custom analyzers.