Use the encdb_btree extension to improve query performance on Always confidential databases - ApsaraDB RDS

The encdb_btree extension is used to enhance the performance of an ApsaraDB RDS for PostgreSQL instance for which the Always confidential database feature is enabled. The Always confidential database feature delivers an end-to-end encryption solution that ensures the security of user data but deteriorates system performance. The extension helps facilitate operations on ciphertext indexes of Always confidential databases on the RDS instance. After you create an enc_btree index, the Always confidential database feature automatically generates execution plans that use it—your existing SQL statements work without modification.

Prerequisites

Before you begin, make sure you have:

The Always confidential database feature enabled and configured on the RDS instance. To set it up, enable the Always confidential database feature, then define sensitive data and configure client access
Minor engine version 20230830 or later. To update the minor engine version, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance

Limitations

Most B-tree features are supported. The following are not:

`ON CONFLICT` with `UNIQUE INDEX`: The enc_btree access method does not implement speculative insertion, which the ON CONFLICT clause requires. Create unique constraints without ON CONFLICT instead.
`FOREIGN KEY`: Foreign key constraints are not compatible with enc_btree indexes.

Install the extension

The encdb_btree extension depends on the EncDB extension. Install EncDB first, then install encdb_btree:

CREATE EXTENSION IF NOT EXISTS encdb;
CREATE EXTENSION encdb_btree;

Create enc_btree indexes

After a table is encrypted in an Always confidential database, use the USING enc_btree clause to create indexes on encrypted columns.

Given the following encrypted table:

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

Create indexes using the same syntax as standard B-tree indexes, replacing USING btree with USING enc_btree:

-- Single-column index
CREATE INDEX ON test USING enc_btree (t1);

-- Unique index
CREATE UNIQUE INDEX ON test USING enc_btree (t2);

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

-- Index with sort order
CREATE INDEX ON test USING enc_btree (t1 desc, t2 asc);

No changes to your application SQL are required. The Always confidential database feature automatically uses these indexes when generating query execution plans.

Uninstall the extension

DROP EXTENSION encdb_btree;

This command fails if any enc_btree indexes exist on the instance. Either drop all enc_btree indexes manually with DROP INDEX before running the command, or use CASCADE to drop them automatically: CASCADE removes all enc_btree indexes but does not affect table data.

DROP EXTENSION encdb_btree CASCADE;

Performance considerations

For benchmark results, see Performance testing reports of the Always confidential database feature.

FAQ

I get `ERROR: unexpected non-btree speculative unique index` when creating a unique index. What's wrong?

This error occurs when you combine UNIQUE INDEX with the ON CONFLICT clause. The enc_btree access method does not support speculative insertion, which ON CONFLICT requires. Remove the ON CONFLICT clause and retry.

I get `ERROR: only b-tree indexes are supported for foreign keys` when defining a foreign key. What's wrong?

Foreign key constraints are not compatible with enc_btree indexes. This is a known limitation.