Encryption at Rest: TDE, Key Management, and Performance

Data breaches make headlines, but the real question is what happens when someone walks away with your database files. Encryption at rest is the answer: without the keys, the data is just noise.

This guide covers Transparent Data Encryption at the database level, application-layer encryption for sensitive fields, key management infrastructure, and the performance realities you need to understand before deploying to production.

flowchart LR
    subgraph KEK["Key Hierarchy"]
        MK[("Master Key<br/>KMS/HSM")]
    end

    subgraph DEK["Data Key Layer"]
        DK1[("DEK v1<br/>User Data")]
        DK2[("DEK v2<br/>User Data")]
        DK3[("DEK<br/>Config Data")]
    end

    subgraph Storage["Storage Layer"]
        E1[("Encrypted<br/>User Table")]
        E2[("Encrypted<br/>Config Table")]
        E3[("Encrypted<br/>Backup")]
    end

    MK -->|encrypts| DK1
    MK -->|encrypts| DK2
    MK -->|encrypts| DK3

    DK1 -->|encrypts| E1
    DK2 -->|encrypts| E2
    DK3 -->|encrypts| E3

    E1 -.->|rotate| DK2

Understanding Encryption at Rest

Encryption at rest protects stored data by encrypting it when written to disk. The data remains encrypted while stored, and only becomes plaintext when read by authorized processes with access to the decryption keys.

The fundamental components:

• Plaintext: Original, readable data
• Ciphertext: Encrypted data that appears random without the key
• Encryption algorithm: Mathematical transformation (AES-256, ChaCha20)
• Key: Secret value used for encryption/decryption
• Key encryption key (KEK): Master key that encrypts data encryption keys
• Data encryption key (DEK): Key that actually encrypts the data

This hierarchy—DEKs protected by KEKs, KEKs stored in key management systems—allows rotation, revocation, and access control without re-encrypting all data.

Transparent Data Encryption (TDE)

TDE encrypts data at the storage level, between the database and disk. The database engine handles encryption and decryption transparently—applications continue working without modification.

PostgreSQL TDE with pgcrypto

PostgreSQL doesn’t have native TDE like Oracle or SQL Server, but you can achieve similar results:

-- Enable pgcrypto extension
CREATE EXTENSION pgcrypto;

-- Encrypt specific columns
CREATE TABLE customer_data (
    id SERIAL PRIMARY KEY,
    name TEXT,
    ssn_encrypted BYTEA ENCRYPT WITH ('AES256'),
    credit_card_encrypted BYTEA ENCRYPT WITH ('AES256')
);

-- Insert encrypted data
INSERT INTO customer_data (name, ssn_encrypted, credit_card_encrypted)
VALUES (
    'Jane Smith',
    pgp_sym_encrypt('123-45-6789', 'encryption_key_here'),
    pgp_sym_encrypt('4111111111111111', 'encryption_key_here')
);

MySQL TDE

MySQL Enterprise and MySQL 8.0+ support TDE:

-- Enable TDE for MySQL
ALTER INSTANCE ENABLE TDE_KEYRING;

-- Create encrypted tablespace
CREATE TABLE sensitive_data (
    id INT PRIMARY KEY,
    data VARCHAR(255)
) ENCRYPTION='Y';

Microsoft SQL Server TDE

SQL Server TDE encrypts entire databases:

-- Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ComplexPassword123!';

-- Create certificate
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'TDE Certificate';

-- Create database encryption key
USE mydatabase;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

-- Enable encryption
ALTER DATABASE mydatabase SET ENCRYPTION ON;

TDE Limitations

TDE has fundamental limitations:

1. Key per database: TDE typically encrypts at the database or tablespace level, not the row level
2. Memory exposure: Data is plaintext in buffer pool after decryption
3. Access control still applies: Anyone with database access sees decrypted data
4. Backup encryption separate: Database backups may not inherit TDE protection

Application-Level Encryption

For sensitive fields—PII, credentials, financial data—application-layer encryption provides finer-grained control. You encrypt specific columns before sending data to the database.

Column-Level Encryption Example

from cryptography.fernet import Fernet
from cryptography.hazmat.primitives import hashes
from cryptography.hazmat.primitives.kdf.pbkdf2 import PBKDF2HMAC
import base64

class FieldEncryptor:
    def __init__(self, master_key: bytes):
        # Derive key from master key
        kdf = PBKDF2HMAC(
            algorithm=hashes.SHA256(),
            length=32,
            salt=b'your-app-salt',  # In production, use unique salt per field
            iterations=480000,
        )
        key = base64.urlsafe_b64encode(kdf.derive(master_key))
        self.fernet = Fernet(key)

    def encrypt(self, plaintext: str) -> str:
        """Returns base64-encoded ciphertext"""
        return self.fernet.encrypt(plaintext.encode()).decode()

    def decrypt(self, ciphertext: str) -> str:
        """Decrypts base64-encoded ciphertext"""
        return self.fernet.decrypt(ciphertext.encode()).decode()

# Usage
encryptor = FieldEncryptor(b'super-secret-master-key')

# Encrypt before storing
email = 'user@example.com'
encrypted_email = encryptor.encrypt(email)
# Store encrypted_email in database

# Decrypt when reading
decrypted_email = encryptor.decrypt(encrypted_email)

Searchable Encryption

A challenge with application-layer encryption: how do you search encrypted data? Options include:

1. Deterministic encryption: Same plaintext always produces same ciphertext, enabling equality searches but leaking pattern information

2. Searchable encryption schemes: Specialized algorithms that allow searches without full decryption (research actively evolving)

3. Search index separate from data: Maintain an unencrypted index mapping search terms to encrypted record IDs (requires careful access control)

-- Pattern-matching on encrypted data is impossible
-- You need application-level search
CREATE INDEX idx_email_hash ON users(email_hash);

Encryption in Transit vs. At Rest

Don’t confuse the two:

Protection	What it guards	Implementation
Encryption in transit	Data moving over network	TLS/SSL, mTLS
Encryption at rest	Data stored on disk	TDE, application-layer
Encryption in use	Data in memory	Confidential computing (emerging)

You typically need both. TDE protects against physical media theft. TLS protects against network interception. Application-layer encryption provides defense-in-depth if other layers fail.

Key Management

Here’s the uncomfortable truth about encryption: it’s only as strong as your key management. Hardcoded keys, unrotated secrets, inadequate access controls—any of these undermine the entire encryption strategy.

AWS KMS Integration

AWS Key Management Service provides centralized key storage and management:

import boto3
import base64
from cryptography.fernet import Fernet

def encrypt_with_kms(aws_region: str, key_id: str, plaintext: str) -> dict:
    kms = boto3.client('kms', region_name=aws_region)

    # Generate data key from KMS
    response = kms.generate_data_key(
        KeyId=key_id,
        KeySpec='AES_256'
    )

    # Encrypt plaintext with data key
    data_key = response['Plaintext']
    encrypted_data_key = response['CiphertextBlob']

    fernet = Fernet(data_key)
    encrypted_plaintext = fernet.encrypt(plaintext.encode())

    return {
        'encrypted_data_key': base64.b64encode(encrypted_data_key).decode(),
        'ciphertext': base64.b64encode(encrypted_plaintext).decode()
    }

def decrypt_with_kms(aws_region: str, encrypted_blob: dict) -> str:
    kms = boto3.client('kms', region_name=aws_region)

    # Decrypt the data key
    encrypted_data_key = base64.b64decode(encrypted_blob['encrypted_data_key'])
    response = kms.decrypt(CiphertextBlob=encrypted_data_key)
    data_key = response['Plaintext']

    # Decrypt the data
    ciphertext = base64.b64decode(encrypted_blob['ciphertext'])
    fernet = Fernet(data_key)
    return fernet.decrypt(ciphertext).decode()

KMS best practices:

• Use CMKs (Customer Master Keys) for production, not AWS-managed keys
• Implement key rotation (automatic or manual)
• Use key policies and IAM for access control
• Enable and audit CloudTrail logging for key usage

HashiCorp Vault for Key Management

Vault provides a comprehensive secrets management solution:

# Enable transit secrets engine for encryption as a service
vault secrets enable transit

# Create an encryption key
vault write -f transit/keys/myapp-key

# Encrypt data via Vault API
vault write transit/encrypt/myapp-key \
    plaintext=$(echo -n "sensitive data" | base64)

# Decrypt via Vault API
vault write transit/decrypt/myapp-key \
    ciphertext="vault:v1:..."

# Rotate the key (new version available, old preserved)
vault write -f transit/keys/myapp-key/rotate

import hvac

def encrypt_with_vault(vault_url: str, token: str, key: str, plaintext: str) -> str:
    client = hvac.Client(url=vault_url, token=token)

    response = client.secrets.transit.encrypt_data(
        name=key,
        plaintext=plaintext
    )
    return response['data']['ciphertext']

def decrypt_with_vault(vault_url: str, token: str, key: str, ciphertext: str) -> str:
    client = hvac.Client(url=vault_url, token=token)

    response = client.secrets.transit.decrypt_data(
        name=key,
        ciphertext=ciphertext
    )
    return response['data']['plaintext']

Vault advantages over KMS:

• Single control plane for all secrets (keys, passwords, certificates)
• Fine-grained access policies
• Audit logging of all secret access
• Dynamic secrets (on-demand credentials)
• Encryption as a service (no key material leaves Vault)

Key Rotation Strategy

Key rotation limits the blast radius of a compromised key:

import os
from functools import wraps
from cryptography.fernet import Fernet

class KeyRotator:
    def __init__(self, kms_client, key_id: str):
        self.kms = kms_client
        self.key_id = key_id
        self._key_cache = {}

    def get_dek(self, key_version: int) -> bytes:
        """Get data encryption key for specific version"""
        if key_version in self._key_cache:
            return self._key_cache[key_version]

        # In production, retrieve from secure key storage
        # This is simplified - use proper key versioning
        response = self.kms.generate_data_key(
            KeyId=self.key_id,
            KeySpec='AES_256'
        )
        self._key_cache[key_version] = response['Plaintext']
        return self._key_cache[key_version]

    def rotate_and_reencrypt(self, old_version: int, new_version: int, records: list) -> list:
        """Re-encrypt records with new key version"""
        old_key = self.get_dek(old_version)
        new_key = self.get_dek(new_version)

        result = []
        for record in records:
            # Decrypt with old key
            fer_old = Fernet(old_key)
            plaintext = fer_old.decrypt(record['encrypted_data'])

            # Encrypt with new key
            fer_new = Fernet(new_key)
            result.append({
                **record,
                'encrypted_data': fer_new.encrypt(plaintext),
                'key_version': new_version
            })

        return result

Performance Overhead

Encryption has measurable costs. Understanding them helps you design appropriately.

Benchmark: TDE Performance Impact

Typical overhead from TDE:

Workload	No TDE	TDE Enabled	Overhead
Read-heavy (100% reads)	baseline	+2-5%	Minimal
Mixed (70/30 read/write)	baseline	+5-15%	Moderate
Write-heavy (100% writes)	baseline	+15-30%	Significant
Bulk load	baseline	+20-40%	Consider

Mitigating Performance Impact

1. Hardware acceleration: AES-NI CPU instructions reduce overhead significantly

2. Key caching: Minimize key material access during operations

3. Encrypt only what matters: Column-level encryption for sensitive data, not entire database

4. Batch operations: Group multiple operations to amortize key access overhead

# Poor: Key access per row
for record in records:
    encrypted = encrypt(record['sensitive_data'])  # Key access each time

# Better: Batch encryption
data_key = get_data_key()  # One key access
fernet = Fernet(data_key)
for record in records:
    encrypted = fernet.encrypt(record['sensitive_data'].encode())

5. Asynchronous encryption for non-critical paths: Decouple encryption from write path when eventual consistency is acceptable

When to Use / When Not to Use TDE vs Application-Level Encryption

Use TDE when:

• You need baseline protection against physical media theft
• Compliance requires encryption at rest without application changes
• You want minimal operational overhead

Do not use TDE alone when:

• You need column-level access control (TDE decrypts everything for privileged users)
• You need to encrypt only specific fields (use application-layer)
• Regulatory requirements mandate key-per-tenant isolation

Use Application-Level Encryption when:

• You need field-level encryption with separate keys per user or tenant
• Privileged users (DBAs) must not see sensitive data
• You need searchable encrypted data (deterministic encryption)
• Compliance requires key custody separate from data storage

Do not use Application-Level Encryption when:

• Performance overhead is unacceptable and TDE suffices
• Your team lacks secure key management expertise

Encryption Strategy Trade-offs

Dimension	TDE (Database-Level)	App-Level Column Encryption	TDE + App-Level Combined
Coverage	Entire database	Selected columns only	Entire DB + sensitive columns
Privileged user access	Sees plaintext	Stays encrypted	Encrypted (app holds keys)
Performance overhead	5-15% on writes	10-30% depending on ops	15-40% combined
Key management complexity	Low — DB manages	High — app must manage	Highest — dual key systems
Implementation effort	Low	High	Highest
Compliance scope	Media theft protection	Access control on data	Defense in depth

Production Failure Scenarios

Failure	Impact	Mitigation
Key rotation breaking encrypted data	Data permanently unreadable	Test rotation thoroughly, maintain key version history
KMS throttling during high write load	Write latency spikes or failures	Cache DEKs locally, implement retry with backoff
Backup encryption without key export	Cannot restore to different account	Export key material with backups, test cross-account restore
Hardcoded encryption keys in code	Key exposure in version control	Use KMS/Vault exclusively, scan code for secrets
TDE without key rotation	Compromised key grants perpetual access	Implement automatic rotation schedule

Capacity Estimation: Encryption Overhead on I/O Throughput

Encryption at rest adds CPU overhead to every read and write operation. The impact depends on the encryption layer and workload characteristics.

TDE (storage-layer) overhead formula:

effective_throughput = raw_throughput × (1 - encryption_overhead_ratio)
encryption_overhead_ratio ≈ 2-15% for AES-NI hardware acceleration

With AES-NI instructions (modern CPUs), TDE overhead is typically 2-5% for sequential I/O and 5-15% for random I/O due to additional CPU cache pressure. Without AES-NI (older CPUs), overhead can reach 30-50%.

Column-level encryption overhead formula:

per_value_encrypt_time = key_setup_time + block_cipher_time × (value_size / block_size)
per_value_decrypt_time ≈ per_value_encrypt_time × 0.8  # decryption often slightly faster

For a 256-bit AES key with 16-byte block size: encrypting a 100-byte string requires 7 block cipher operations. At 1 microsecond per AES operation, that’s 7 microseconds per value. For bulk operations processing 1M values, this adds 7 seconds of CPU time.

Application-level encryption overhead: Encrypt before writing, decrypt after reading. For a database with 100K reads/second and 100K writes/second, each with 10 encrypted columns: encrypt overhead = 100K × 10 × 7μs = 7 seconds of CPU per second — requiring approximately 7 additional CPU cores.

Key rotation overhead: Re-encrypting data with a new key requires reading all encrypted data, decrypting with old key, re-encrypting with new key, and writing back. For 1TB of encrypted data with 10MB/s re-encryption throughput: 1TB / 10MB/s = 100,000 seconds ≈ 27 hours. Plan key rotation during maintenance windows or use key versioning (encrypt new data with new key, decrypt old data lazily on read) to avoid bulk re-encryption.

Observability Hooks: KMS API Call Monitoring and Key Expiration Alerts

Key metrics: KMS API call latency, KMS throttling events, key rotation timestamps, and encryption operation latency.

-- PostgreSQL with pgcrypto: monitor encryption function call latency
SELECT
    query,
    calls,
    total_exec_time_ms,
    mean_exec_time_ms,
    max_exec_time_ms
FROM pg_stat_statements
WHERE query LIKE '%pgp_sym_encrypt%'
   OR query LIKE '%pgp_sym_decrypt%'
ORDER BY total_exec_time_ms DESC;

# Alert: KMS API latency spike (could indicate KMS throttling)
- alert: KmsApiLatencyHigh
  expr: histogram_quantile(0.95, kms_api_latency_seconds) > 0.5
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "KMS API P95 latency {{ $value }}s exceeds 500ms"

# Alert: KMS throttling events
- alert: KmsThrottlingEvents
  expr: rate(kms_throttling_total[5m]) > 10
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "KMS throttling events rate: {{ $value }}/s"

# Critical: Encryption key approaching expiration
- alert: EncryptionKeyExpiring
  expr: (key_expiration_timestamp - now()) < 2592000 # 30 days
  for: 1h
  labels:
    severity: critical
  annotations:
    summary: "Encryption key {{ $labels.key_id }} expires in {{ $value }} seconds"

# Alert: Encrypted volume showing high CPU during encryption operations
- alert: EncryptionCpuOverhead
  expr: instance:cpu_encryption_overhead_percent > 15
  for: 10m
  labels:
    severity: warning
  annotations:
    summary: "Encryption overhead consuming {{ $value }}% CPU on {{ $labels.instance }}"

Quick Recap Checklist

Use this checklist when designing or reviewing encryption at rest for a database system:

• TDE enabled for all production databases
• Sensitive fields use column-level encryption where required
• Encryption keys stored in KMS/Vault, not in application code
• Key rotation scheduled and verified (90-day minimum recommended)
• Backup encryption implemented with keys separate from production
• AES-NI hardware acceleration verified for production systems
• KMS throttling handled via DEK caching
• CloudTrail/Vault audit logs enabled and reviewed
• Encryption overhead benchmarked under realistic write load
• Key access termination procedure documented and tested

---

Compliance Considerations

Encryption at rest is often mandatory for compliance:

• PCI-DSS: Requires encryption for cardholder data at rest
• HIPAA: Requires encryption for PHI “to the extent feasible”
• GDPR: Encryption is recommended (and sometimes required) for personal data
• SOC 2: Encryption at rest is a common control

Document your encryption strategy for auditors:

• What data is encrypted (and where)
• Encryption algorithms and key lengths
• Key management procedures
• Access controls for key material
• Rotation schedules and procedures

Interview Questions

---

Further Reading

• Cloud Security — Broader security patterns
• Compliance Automation — Maintaining security posture

---

Conclusion

For further reading on related security topics, see our cloud security guide and explore compliance automation for keeping your security posture up to standards.