如何使用ZomboDB插件支持原生的访问 - ApsaraDB RDS

ZomboDB is a PostgreSQL extension. It supports the access methods that are provided by native PostgreSQL. It also provides powerful text search and analytics features by using Elasticsearch for an ApsaraDB RDS for PostgreSQL instance.

Prerequisites

The RDS instance runs PostgreSQL 11.
The RDS instance runs a minor engine version of 20230830 or later.
Important
The extension is supported in minor engine versions that are earlier than 20230830. To standardize extension management and enhance extension security for ApsaraDB RDS for PostgreSQL, ApsaraDB RDS plans to optimize vulnerable extensions in minor engine version iterations. As a result, some extensions can no longer be created for RDS instances that run earlier minor engine versions. For more information, see [Product changes/Feature changes] Limits on extension creation for ApsaraDB RDS for PostgreSQL instances.
- If you have created the extension for your RDS instance that runs a minor engine version earlier than 20230830, the extension is not affected.
- If this is the first time you create the extension for your RDS instance or re-create the extension, you must update the minor engine version of the RDS instance to the latest version. For more information, see Update the minor engine version.

Background information

ZomboDB provides a full set of query languages to query relational data. You can also create ZomboDB indexes. In this case, ZomboDB takes over remote Elasticsearch indexes and ensures transaction correctness of query results from text search.

ZomboDB allows you to use Elasticsearch without the need to handle synchronization or communication issues.

Create and delete the extension

Create the extension.
```
CREATE EXTENSION zombodb;
```
Delete the extension.
```
DROP EXTENSION zombodb;
```

Examples

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,
    price bigint,
    inventory_count integer,
    discontinued boolean default false,
    availability_date date
);

Create a ZomboDB index for the table.
```
CREATE INDEX idxproducts
          ON products
       USING zombodb ((products.*))
        WITH (url='localhost:9200/');
```
Note
The WITH clause is followed by an Elasticsearch endpoint, which points to a running Elasticsearch cluster.

Query data by using the ZomboDB index.

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

Note

For more information about the query syntax, see ZomboDB Documentation.