Encrypted Tables and Indexes

pg_vault_tde adds two access methods: encrypted_heap for tables and tde_btree for indexes. This page covers what they do, how to use them, and exactly what is (and isn’t) covered by each.

encrypted_heap — Table Access Method

CREATE TABLE secrets (id serial, token text) USING encrypted_heap;

Every column value in the table is encrypted with AES-256-GCM before it reaches disk, and decrypted transparently when read back — no application changes required. The row header (xmin, xmax, ctid, infomask bits) stays plaintext, because PostgreSQL’s MVCC machinery needs to read it without going through the encryption layer.

Converting an Existing Table

ALTER TABLE mytable SET ACCESS METHOD encrypted_heap;   -- plain heap → encrypted
ALTER TABLE mytable SET ACCESS METHOD heap;              -- encrypted → plain heap (decrypts)

Do not toggle pg_vault_tde.enabled instead of doing this properly. Rows are written using the wire format active at the time of the write: encrypted when enabled = on, verbatim heap tuple when off. Flipping the GUC and restarting does not retroactively convert existing rows — reads use whichever format is currently active, so any encrypted_heap table containing rows written under the other setting will have those rows misread as silent data corruption, not an error. Only ever change enabled on a database where every encrypted_heap table is empty or has been fully rewritten under the target setting first (e.g. via CREATE TABLE ... AS SELECT).

TOAST (Large Column Values)

Controlled by pg_vault_tde.toast_encryption (default on): TOAST chunks for large column values are encrypted per-chunk with the parent table’s DEK, using the same encrypted_heap machinery. Set it to off only if you specifically need plaintext TOAST storage for performance reasons and have already accepted the confidentiality trade-off for large values.

tde_btree — Index Access Method

CREATE INDEX ON secrets USING tde_btree (id);
Property Value
Algorithm AES-256-SIV (deterministic, misuse-resistant authenticated encryption)
Equality Preserved — the same plaintext always produces the same ciphertext under the same DEK, so = lookups work
Ordering Not preserved — range predicates (>, <, BETWEEN) return empty results
Use case Equality predicates only: =, IN, ON CONFLICT
Column types Both varlena (text, bytea, numeric) and fixed-size (int4, int8, uuid, date, timestamptz) — all encrypted since v1.7

Operator Classes

CREATE EXTENSION pg_vault_tde registers encrypted-key operator classes as the default opclass for their type, so a plain USING tde_btree (col) already picks the encrypted variant — you do not need to name the opclass explicitly:

CREATE TABLE employees (
    id       int4,
    username text,
    salary   numeric
) USING encrypted_heap;

CREATE INDEX ON employees USING tde_btree (id);         -- tde_int4_enc_ops (default)
CREATE INDEX ON employees USING tde_btree (username);   -- tde_text_ops (default)

SELECT salary FROM employees WHERE id = 1;              -- uses the index
SELECT * FROM employees WHERE id > 1;                   -- seq scan — index returns empty by design

Legacy non-encrypted opclasses (tde_int4_ops, etc.) still exist for backward compatibility but are not the default — prefer the enc_ops classes so index keys stay encrypted.

Index-Only Scans Are Not Supported (By Design)

An index-only scan would return column values straight from the index page without visiting the heap — but tde_btree stores AES-SIV ciphertext as the index key, so that would leak raw ciphertext to the client with no decryption step. PostgreSQL’s planner is prevented from choosing this path for tde_btree indexes; the heap tuple is always fetched instead.

Parallel Index Build Is Disabled

amcanbuildparallel = false on tde_btree: PostgreSQL’s parallel index build workers run in separate processes not intercepted by the TAM/IAM encryption wrappers, so a parallel worker would read raw ciphertext as if it were plaintext. Index builds and REINDEX always run single-process on tde_btree.

Index Access Method Whitelist

tde_btree is, today, the only index access method that encrypts the key it stores. Because of that, CREATE INDEX / CREATE UNIQUE INDEX with any other access method — btree, gin, gist, hash, brin — against an encrypted_heap table is rejected outright:

CREATE TABLE docs (id int, body text) USING encrypted_heap;

CREATE INDEX ON docs USING gin (body gin_trgm_ops);
-- ERROR:  pg_vault_tde: index access method "gin" is not supported on
--         encrypted_heap table "docs"
-- HINT:   Use "CREATE INDEX ... USING tde_btree" with an encrypted operator
--         class instead, or set pg_vault_tde.allow_plaintext_index = on to
--         allow this with a WARNING.

This is a whitelist, not a tde_btree-specific carve-out — it blocks GIN trigram/full-text indexes and GiST indexes exactly the same way it blocks a plain btree index, because none of them have an encrypted variant yet (GIN/Hash/GiST encryption is planned for v1.8 — see Known Limitations and Troubleshooting).

Escape Hatch: pg_vault_tde.allow_plaintext_index

If you need trigram search, full-text search, a spatial GiST index, or a plain range-scanning index on an encrypted_heap table today, set:

SET pg_vault_tde.allow_plaintext_index = on;   -- default: off
CREATE INDEX ON docs USING gin (body gin_trgm_ops);
-- WARNING:  pg_vault_tde: index access method "gin" on encrypted_heap
--           table "docs" is not encrypted
-- DETAIL:   The indexed column's plaintext value will be stored on disk
--           in this index.

The index is created and works exactly like a normal PostgreSQL index — gin_trgm_ops, gist_trgm_ops, hash, brin and plain btree all build and scan correctly against the decrypted values the TAM hands the index AM during the build/insert path. The trade-off is exactly what the WARNING says: whatever that access method stores internally (trigrams, tsvector lexemes, raw keys, block min/max…) is plaintext on disk, unlike tde_btree’s AES-256-SIV ciphertext. Prefer tde_btree for anything security-sensitive; reach for this GUC only for non-sensitive columns, or as a stop-gap until v1.8 ships encrypted GIN/Hash/GiST.

allow_plaintext_index has no effect on PRIMARY KEY/UNIQUE table constraints (inline in CREATE TABLE, or ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY/UNIQUE without USING INDEX) — those are always allowed with a WARNING regardless of this setting, because PostgreSQL core always backs a declarative constraint with a native btree index and gives pg_vault_tde no way to block that. See the next section.

PRIMARY KEY / UNIQUE Constraints Always Work — With a Caveat

CREATE TABLE accounts (id int PRIMARY KEY, balance numeric) USING encrypted_heap;
-- WARNING:  pg_vault_tde: the constraints on column "id" of encrypted_heap
--           table "accounts" will be backed by a standard (unencrypted)
--           btree index

Both spellings — inline in CREATE TABLE, and ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY (col) / ... UNIQUE (col) added after the fact — emit this WARNING and then work exactly like on a plain heap table: duplicate inserts are rejected, the index stays in sync, ON CONFLICT works. The id column’s value sits in plaintext in that one btree index; everything else about the row is still encrypted.

What does not work this way: building the unique index as two separate steps — CREATE UNIQUE INDEX ... USING btree (id) followed by ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY USING INDEX <name> (the pattern most tools use for CREATE INDEX CONCURRENTLY-based, low-lock PK creation). The first statement is a standalone CREATE INDEX, not a constraint, so it hits the whitelist above and is rejected — and because it never created anything, the second statement then fails too (index "..." does not exist). If either failure goes unnoticed (non-interactive script, no ON_ERROR_STOP), the table ends up with no index and no constraint at all, and duplicate INSERTs go through silently — not because indexes “aren’t updated”, but because there is no index to update. Either build the index with USING tde_btree instead, or set pg_vault_tde.allow_plaintext_index = on before the CREATE UNIQUE INDEX ... USING btree step so it actually succeeds.

Compatibility Matrix

Feature Status Notes
Sequential scan ✅ Full
Index scan ✅ Full
Bitmap heap scan ✅ Full
ANALYZE ✅ Full Statistics computed on decrypted values
TABLESAMPLE ✅ Full
SELECT FOR UPDATE / SHARE ✅ Full
INSERT / COPY ✅ Full
UPDATE ✅ Full
DELETE ✅ Full
VACUUM / VACUUM FULL / CLUSTER ✅ Full
CREATE TABLE ... AS SELECT ✅ Full
pg_dump (plain) ⚠️ Dump is plaintext Reads via the decrypt-on-read path; use pg_dump_tde to keep the dump encrypted — see Backup and Restore
Streaming (physical) replication ✅ Full WAL ships encrypted bytes; standby decrypts at the TAM layer
Page checksums ✅ Full Checksums cover the encrypted bytes
Logical replication (non-TOAST) ✅ Full See Logical Replication
Logical replication (TOAST columns) ✅ Full (opt-in) Requires REPLICA IDENTITY FULL + primary key — see Logical Replication
Range scans on tde_btree ⚠️ By design, empty results Use sequential scan, or a plain (unencrypted) index if range queries are required on that column
HOT updates ⚠️ Disabled by design See below
Column-level encryption 🔜 Planned (v1.8) Currently all-or-nothing per table
GIN / Hash / GiST index encryption 🔜 Planned (v1.8) Only tde_btree exists today; a plaintext GIN/GiST/Hash/BRIN/btree index is available meanwhile via allow_plaintext_index = on

Why HOT Updates Are Disabled

An encrypted_heap table never uses a HOT (Heap-Only Tuple) update, even when only non-indexed columns change. This is intentional: it guarantees tde_btree indexes never silently drift out of sync with the heap. heap_update decides HOT eligibility by comparing the encrypted bytes of indexed columns between the old and new tuple — and because every encryption uses a fresh random IV, the encrypted bytes always differ, even when the underlying plaintext hasn’t changed. The practical consequence: every UPDATE on an encrypted_heap table maintains indexes explicitly, which is slightly more write amplification than an equivalent plain-heap table, in exchange for indexes that can never silently go stale.

See Known Limitations and Troubleshooting for this and the rest of the current limitation list.

See Also