Use the ZomboDB extension with Elasticsearch for text search - PolarDB

ZomboDB is a PostgreSQL extension that brings Elasticsearch's full-text search and analytics capabilities directly into PolarDB. It integrates with PostgreSQL's native access methods, so you query your relational data using standard SQL while ZomboDB handles all synchronization and communication with Elasticsearch behind the scenes.

How it works

ZomboDB creates an index on a table and maps it to a remote Elasticsearch index. When you run a query using the ==> operator, ZomboDB retrieves transactionally correct results from Elasticsearch and returns them as a standard PostgreSQL result set. You don't need to manage synchronization between the database and Elasticsearch manually.

Prerequisites

Before you begin, make sure you have:

A running Elasticsearch cluster. ZomboDB does not support Elasticsearch 7.x or 8.x.
The Elasticsearch cluster endpoint URL (for example, localhost:9200)

Install and uninstall ZomboDB

Install the ZomboDB extension:

CREATE EXTENSION zombodb;

Uninstall the ZomboDB extension:

DROP EXTENSION zombodb;

Index a table and run a full-text query

The following example shows how to create a table, build a ZomboDB index, and run a full-text query.

Step 1: Create a table

CREATE TABLE products (
    id SERIAL8 NOT NULL PRIMARY KEY,
    name text NOT NULL,
    keywords varchar(64)[],
    short_summary text,
    long_description zdb.fulltext,  -- zdb.fulltext: a ZomboDB type optimized for full-text indexing
    price bigint,
    inventory_count integer,
    discontinued boolean default false,
    availability_date date
);

Step 2: Create a ZomboDB index

CREATE INDEX idxproducts
          ON products
       USING zombodb ((products.*))   -- index all columns in the table
        WITH (url='localhost:9200/'); -- replace with your Elasticsearch cluster endpoint

The index definition has three key parts:

Syntax	Description
`USING zombodb`	Specifies the index type. ZomboDB uses its own access method instead of the default btree, gin, or gist.
`(products.*)`	Tells ZomboDB to index all columns. ZomboDB automatically converts each row to JSON before sending it to Elasticsearch.
`WITH (url='...')`	The URL of a running Elasticsearch cluster. Replace `localhost:9200/` with your cluster's endpoint.

Step 3: Query with the ZomboDB index

Use the ==> operator to pass a ZQL expression in a WHERE clause:

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

This query returns products where the keywords field contains sports or box, or the long_description field contains the phrase wooden away within a distance of 5 words, and where the price is between 1,000 and 20,000.

What's next

For the full ZQL syntax reference, see ZomboDB documentation.