Storage overhead of TCE types

TCE adds new types to PostgreSQL that transparently encrypt and decrypt data.

Encryption increases the size of stored values and makes them effectively incompressible. This page measures the resulting storage overhead and its potential impact on database storage.

Methodology

For each test, we created two tables: one using the native PostgreSQL type and one using its TCE counterpart. Each table was populated with 500,000 rows, inserted in batches of 100,000 rows.

Autovacuum was disabled on the test tables to avoid background maintenance affecting the measurements.

Example of int / tceint:

\echo --
\echo -- INT
DROP TABLE IF EXISTS tint;
CREATE TABLE tint( i int);
ALTER TABLE tint SET (autovacuum_enabled = FALSE);
INSERT INTO tint SELECT generate_series(1, 100000);
INSERT INTO tint SELECT generate_series(1, 100000);
INSERT INTO tint SELECT generate_series(1, 100000);
INSERT INTO tint SELECT generate_series(1, 100000);
INSERT INTO tint SELECT generate_series(1, 100000);

SELECT 4 as "Byte Len",
       avg(pg_column_size(i)) as "Col size",
       4 / avg(pg_column_size(i))  as "Ratio Data/Stored",
       count(*) filter (where pg_column_compression(i) IS NOT NULL) AS "NCompressed",
       count(*) filter (where pg_column_toast_chunk_id(i) IS NOT NULL) AS "NToasted?",
       count(*) AS count
  FROM tint
 ORDER BY 1;

\dt+ tint

\echo --
\echo -- TCEINT
DROP TABLE IF EXISTS ttceint;
CREATE TABLE ttceint( i tceint);
ALTER TABLE ttceint SET (autovacuum_enabled = FALSE);
INSERT INTO ttceint SELECT generate_series(1, 100000);
INSERT INTO ttceint SELECT generate_series(1, 100000);
INSERT INTO ttceint SELECT generate_series(1, 100000);
INSERT INTO ttceint SELECT generate_series(1, 100000);
INSERT INTO ttceint SELECT generate_series(1, 100000);


SELECT 4 as "Octet Len",
       avg(pg_column_size(i)) as "Col size",
       4 / avg(pg_column_size(i))  as "Ratio Data/Stored",
       count(*) filter (where pg_column_compression(i) IS NOT NULL) AS "NCompressed",
       count(*) filter (where pg_column_toast_chunk_id(i) IS NOT NULL) AS "NToasted?",
       count(*) AS count
  FROM ttceint
 ORDER BY 1;

\dt+ ttceint

Different cases have been tested :

• Integers stored in int / tceint

• Dates stored in date / tcedate

• Small text:

• names stored in text / tcetext of approximately 13 bytes. The data is neither toasted nor compressed.

• card numbers stored has text / tcetext of approximately 15 bytes.

• Medium text: phrases of approximately 2,300 bytes. Native text values are generally compressed but remain inline. Because encrypted values are significantly larger and effectively incompressible, the TCE values are expected to be stored out-of-line. Both external and extended storage strategies were tested.

• Large text: phrases of approximately 23,000 bytes. Native text values are both compressed and toasted. As with medium text, both external and extended storage strategies were tested for TCE.

We collect :

• Data size: the expected size of the unencrypted value.
• Column size: the value size returned by pg_column_size().
• Octet Len/Col Size: the ratio between the expected unencrypted value size and pg_column_size(). A ratio greater than 1 means that the original value was compressed. A ratio below 1 means that the stored value is larger than the original value.
• Compressed values: the number of rows for which PostgreSQL applied TOAST compression.
• Toasted values: the number of rows stored out-of-line in the TOAST table.
• Table size: the size returned by pg_table_size(), including the main table, TOAST data, and associated forks.

The test was done on PostgreSQL version pg17 with toast compression set to pglz (the default in this version). lz4 would be more efficient.

These results are workload-dependent. The exact storage overhead depends on the data distribution, value lengths, tuple layout, TOAST thresholds, compression algorithm, and PostgreSQL version.

The result

TCE significantly increases the size of individual values. The largest relative overhead occurs for small fixed-width types: int and date increase from 4 bytes to approximately 82 bytes, or about 20.5×.

The increase factor in total table size is lower than the per-column one for small values. For example, the int table grows from 17 MB to 55 MB, or approximately 3.2×. For larger text values, however, the interaction with TOAST and compression leads to a larger table size increase: up to approximately 5.4× for larger text.

There is no difference between the extended and external storage volume numbers, which confirms that compression has poor results on the encrypted data. The compression is attempted nonetheless for extended storage, leading to longer insertion times. That's why TCE types now uses external storage by default.

The fact that the data is not compressible makes it more likely that the data will end up in the toast table.

The increased storage size also has consequences beyond disk usage. Larger values can increase WAL volume, backup size, replication traffic, and data transfer time for queries. They also reduce the amount of data that can fit in memory and can increase the cost of retrieving toasted values (because there is more to retrieve).

The additional WAL volume may also increase the risk of replication falling behind.

Using lz4 for toast compression is usually better for performance and compression and would probably increase the difference in favor of PostgreSQL's native types for long and compressible data.

Indexing of encrypted values does not provide useful plaintext search semantics.

Worse, the increased size of encrypted values also means that users can reach PostgreSQL's B-tree index tuple size limit (2704 bytes) earlier than they would with the corresponding plain text values.