Use the encdb_btree extension to facilitate operations on ciphertext indexes - ApsaraDB RDS

The encdb_btree extension is used to enhance the performance of an ApsaraDB RDS for PostgreSQL instance for which the fully encrypted database feature is enabled. The extension helps facilitate operations on ciphertext indexes of fully encrypted databases on the RDS instance. This topic describes how to use the encdb_btree extension.

Background information

The fully encrypted database feature delivers an end-to-end encryption solution. The feature ensures the security of user data but deteriorates system performance. For more information, see Performance testing reports of the fully encrypted database feature. To resolve this issue, Alibaba Cloud provides the encdb_btree extension to facilitate operations on ciphertext indexes of fully encrypted databases.

Prerequisites

The fully encrypted database feature is enabled and configured for the RDS instance. For more information, see Enable the fully encrypted database feature, Define sensitive data, and Use the fully encrypted database feature from a client.
The minor engine version of the RDS instance is 20230830 or later. For more information about how to update the minor engine version, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.

Limits

Most features of the extension are compatible with B-tree indexes. The following list describes the limits:

You cannot use the ON CONFLICT syntax to create UNIQUE INDEX.
You cannot use FOREIGN KEY.

Manage the extension

Create the extension.
```
CREATE EXTENSION IF NOT EXISTS encdb;
CREATE EXTENSION encdb_btree;
```
Note
The encdb_btree extension depends on the EncDB extension. Before you create the encdb_btree extension, you must create and load the EncDB extension.
Uninstall the extension.
```
DROP EXTENSION encdb_btree;
```
Note
- If an enc_btree index exists on your RDS instance, the extension fails to be uninstalled. You must execute the DROP INDEX statement to delete the existing enc_btree index before you uninstall the extension.
- You can also execute the DROP EXTENSION encdb_btree CASCADE; statement to uninstall the extension. This method automatically deletes all existing enc_btree indexes without affecting table data.

Use the extension

After a table is encrypted in a fully encrypted database, you can use the extension to create enc_btree indexes for encrypted table columns.

The following table is used as an example.

CREATE TABLE test(
  t1 enc_int4,
  t2 enc_int8,
  t3 enc_text
);

You can create an enc_btree index in the encrypted table by using the USING enc_btree keyword.

CREATE INDEX ON test USING enc_btree (t1);

CREATE UNIQUE INDEX ON test USING enc_btree (t2);

CREATE INDEX ON test USING enc_btree (t1, t2, t3);

CREATE INDEX ON test USING enc_btree (t1 desc, t2 asc);

After you create the enc_btree index, you do not need to modify your business SQL statements. The fully encrypted database feature automatically generates an execution plan to call the enc_btree index when you execute the SQL statements. This facilitates operations on ciphertext indexes of the fully encrypted database.

Performance improvement

For more information, see Performance testing reports of the fully encrypted database feature.

FAQ

What do I do if the ERROR: unexpected non-btree speculative unique index error message appears when I use the extension?
The extension does not support the ON CONFLICT syntax when you create UNIQUE INDEX.
What do I do if the ERROR: only b-tree indexes are supported for foreign keys error message appears when I use the extension?
The extension does not support FOREIGN KEY.