PostgreSQL performance: Encrypted vs. unencrypted

Test environment

PostgreSQL TDE is an open source build of PostgreSQL that encrypts data before writing it to disk, shipped as part of CYBERTEC PostgreSQL Enterprise Edition (PGEE). To quantify what that encryption costs, we ran read-only and read-write benchmarks across several database sizes.

The hardware was a AMD Ryzen 5950X with 16 cores / 32 threads, 72MB cache and AES-NI support, backed by 64GB of DDR4 2666 ECC dual-channel memory and a Samsung 980 PRO NVMe SSD on PCIe v4 ×4, presented as a simple LVM volume with an XFS filesystem for exclusive use. Software: Linux Centos 7.9.2009 on kernel 5.10.11-1.el7.elrepo.x86_64, and postgresql13-13.1-3PGDG.rhel7.x86_64 from the PGDG repo.

Two local TDE builds were produced with the standard compile options used for PGDG packages: one with the base GCC 4.8.5 20150623 (Red Hat 4.8.5-44), the other with devtoolset-9 GCC 9.3.1 20200408 (Red Hat 9.3.1-2) and -march=znver2.

Configurations and workload setup

Each measurement ran on a freshly initialized, empty cluster (initdb), so PostgreSQL-side caching effects are not a factor. Five builds were compared:

1. stock — standard PostgreSQL binaries from PGDG packages.
2. TDE-4 — TDE with encryption disabled, gcc 4.8 binaries.
3. TDE-9 — TDE with encryption disabled, gcc 9.3 binaries optimized for znver2.
4. TDE-4-e — TDE with encryption enabled, gcc 4.8 binaries.
5. TDE-9-e — TDE with encryption enabled, gcc 9.3 binaries optimized for znver2.

Comparing compiler versions, not just encrypted against unencrypted, proved worthwhile — the toolchain turns out to matter considerably.

The postgresql.conf variables were derived with the CYBERTEC configurator.

max_connections = 100
unix_socket_directories = '/var/run/postgresql'
port = 15432
shared_preload_libraries = 'pg_stat_statements'
shared_buffers = 16GB
work_mem = 64MB
maintenance_work_mem = 620MB
effective_cache_size = 45GB
effective_io_concurrency = 100
huge_pages = try
track_io_timing = on
track_functions = pl
wal_level = logical
max_wal_senders = 10
synchronous_commit = on
checkpoint_timeout  = '15 min'
checkpoint_completion_target = 0.9
max_wal_size = 1GB
min_wal_size = 512MB
wal_compression = on
wal_buffers = -1
wal_writer_delay = 200ms
wal_writer_flush_after = 1MB
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
bgwriter_flush_after = 0
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_maintenance_workers = 8
max_parallel_workers = 16
parallel_leader_participation = on
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
jit = on

Scale factors and their resulting database sizes:

• 15: 244–287MB
• 20: 332–404MB
• 25: 400–458MB
• 30: 479–543MB
• 35: 557–629MB
• 50: 793–880MB
• 500: 7849–8028MB
• 5000: 78409–78756MB (exceeds total available memory)

Each scale factor was exercised with pgbench's built-in read-only benchmark and its TPC-like read/write benchmark, at default options. Before every TPC run the database was re-indexed so that index state would not skew results. Connection counts started at 12 and stepped up through 16 (physical cores), 20, 24, 28, 32 (hardware threads), 36, 40, 44 and 48.

Read-only results

At a scale factor of 50 (880MB — a small database) the 12-connection row for standard PostgreSQL showed 598,000 read-only transactions per second. The compiler line is the interesting one: 651k TPS versus 598k TPS is a 9% gain that comes essentially for free with gcc 9.

PostgreSQL TDE encrypts 8k blocks on their way to disk and decrypts them when a block is fetched back from the operating system. Because of that placement, encrypted and unencrypted runs show no meaningful difference — only the expected fluctuations.

Scale factor 500 (8GB) looks the same, and again stays fully in RAM. Encryption on or off makes no real difference here either:

Scale factor 5000 pushes the dataset beyond what RAM can hold, and performance drops sharply. Disk access costs many times more than pointer arithmetic in shared memory; the added latency per query depresses TPS when there are too few connections. More concurrency offsets this somewhat on SSD, but only until disk capacity is saturated.

Read-write results

Read-write performance is substantially lower overall. A pgbench read-write transaction issues far more commands, and PostgreSQL must flush (fsync) changes to disk on commit.

Here the cost of encryption begins to show: encrypted variants can be significantly slower than the non-encrypted configuration.

At scale factor 500 the picture is similar:

At scale factor 5000 overall throughput falls dramatically because much of the data must come from disk, producing substantial disk wait and latency:

Caveats

All of these numbers should be taken with a grain of salt — run-times vary, particularly on SSDs, as we have seen repeatedly. Expected performance also depends heavily on PostgreSQL cache behavior, because encryption and decryption happen every time a block moves between shared buffers and disk. It can therefore make sense to run PostgreSQL TDE with higher shared_buffers settings than usual, to limit the impact of security.

A follow-up round of these analyses is in preparation, with proper TRIM management via the -o discard mount option and scales chosen to better isolate the effect of dataset size against available memory: 12 (an order of magnitude below shared buffers), 900 (90% of shared buffers), 1600 (120% of shared buffers, still fitting in RAM) and 5000 (120% of RAM). Other parameters and tooling stay the same.