How to Encrypt Data in Production (Real-World Approach + Pros & Cons)

In modern applications, protecting user data isn’t optional—it’s a responsibility. Here’s a practical breakdown of how we implement data encryption in production systems, along with the trade-offs every engineering team should understand.

Enable EXTENSION in Postgrsql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Generate secret_key
SELECT encode(gen_random_bytes(32), 'base64') as your_secret_key;

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    mobile_encrypted BYTEA
);


INSERT INTO customers (name, mobile_encrypted)
VALUES (
    'John Doe',
    pgp_sym_encrypt('9876543210', 'your_secret_key', 'cipher-algo=aes256')
);

SELECT
    id,
    name,
    pgp_sym_decrypt(mobile_encrypted, 'your_secret_key') AS mobile
FROM customers;


SELECT * FROM customers;



UPDATE customers
SET mobile_encrypted =
    pgp_sym_encrypt(mobile, 'your_secret_key', 'cipher-algo=aes256');
	
	
SELECT
    'XXXXXX' || RIGHT(pgp_sym_decrypt(mobile_encrypted, 'your_secret_key'),4)
FROM customers;

How to Encrypt Data in Production

1. Encrypt Sensitive Data at Rest

Use strong encryption like AES-256
In PostgreSQL, leverage pgcrypto:
```
pgp_sym_encrypt(data, key)
```
Store encrypted values as BYTEA (binary format)

2. Never Store Plaintext Secrets

Keep encryption keys in environment variables or a secret manager
NEVER hardcode keys in your codebase

3. Enable Secure Lookups with Hashing

Use SHA-256 hashing for searchable fields (like mobile/email)
Example:
```
encode(digest(value, 'sha256'), 'hex')
```

4. Encrypt in Transit

Always enforce HTTPS (TLS)
Protect API communication and database connections

5. Restrict Decryption

Decrypt data only when absolutely needed
Keep decryption logic inside controlled application layers

6. Add Audit Logging

Track all changes (INSERT, UPDATE, DELETE)
Store old/new values (JSONB) for forensic analysis

Pros of Encryption

Protects sensitive user data (PII, financial, KYC)
Helps achieve compliance (GDPR, SOC2, ISO)
Reduces risk of data breaches
Builds user trust and brand credibility
Even database admins can’t read raw data

Cons / Trade-offs

Performance overhead (encryption/decryption cost)
Complex key management (rotation, storage)
Harder debugging (no readable data)
Limited querying on encrypted columns
Requires additional logic for search (hashing/indexing)

Pro Tips from Production Experience

Combine encryption + hashing for best balance
Use rate limiting + validation to prevent abuse
Keep audit logs encrypted as well
Rotate keys periodically without downtime
Design your schema with encryption in mind (don’t retrofit later)

Bottom Line

Encryption is not just a feature—it’s a system design decision. Done right, it protects your users, your business, and your reputation.

If you're building systems handling sensitive data, this should be your baseline—not an afterthought.