how to use the zombodb plugin to support native access - ApsaraDB RDS

ZomboDB integrates Elasticsearch's full-text search and analytics capabilities directly into PostgreSQL. It provides a full set of query languages to query relational data, takes over remote Elasticsearch indexes, and ensures transaction correctness of query results from text search — so you can run Elasticsearch-powered queries using standard SQL without handling synchronization or communication issues.

Prerequisites

Before you begin, make sure you have:

An RDS instance running PostgreSQL 11 with minor engine version 20230830 or later

Important

This plugin was supported before the 20230830 minor engine version. However, to standardize plugin management and improve security, RDS plans to optimize plugins with security risks in kernel version iterations. Some plugins cannot be created in lower minor engine versions. If your instance runs a minor engine version earlier than 20230830:

If you have already used this plugin, your existing setup is not affected.
If you are creating the plugin for the first time or recreating it, upgrade the minor engine version to the latest before proceeding.

For details, see Restrictions on creating plugins.

How it works

ZomboDB adds a custom index access method (USING zombodb) to PostgreSQL. When you create a ZomboDB index on a table, ZomboDB takes over remote Elasticsearch indexes and ensures transaction correctness of query results from text search — no manual synchronization required.

Install and remove the extension

Install:

CREATE EXTENSION zombodb;

Remove:

DROP EXTENSION zombodb;

Use ZomboDB

The following steps use a products table to demonstrate how to create a ZomboDB index and run full-text queries.

Step 1: Create the table

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
);

Step 2: Create a ZomboDB index

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

The WITH (url=...) clause specifies the Elasticsearch endpoint, which points to a running Elasticsearch cluster. Replace localhost:9200/ with the address of your Elasticsearch cluster.

Step 3: Query using ZomboDB

ZomboDB's query language (ZQL) supports boolean operators, proximity matching, and numeric range conditions. The following query combines multiple conditions:

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

For the full ZQL syntax reference, see ZomboDB documentation.

ApsaraDB RDS:Use the ZomboDB extension