Part 042 — Redis and PostgreSQL Integration

Redis dan PostgreSQL sering dipakai bersama, tetapi mereka memiliki karakter yang sangat berbeda.

PostgreSQL biasanya menjadi:

• source of truth
• transactional database
• relational integrity boundary
• audit/history store
• durable state store
• query engine untuk data canonical

Redis biasanya menjadi:

• cache
• derived state
• ephemeral coordination state
• idempotency helper
• rate limiter backend
• session/security state store
• queue/stream primitive untuk beberapa use case
• performance accelerator

Masalah muncul ketika engineer memperlakukan Redis dan PostgreSQL seolah-olah mereka berada dalam satu transaksi yang sama. Mereka tidak.

Redis dan PostgreSQL tidak otomatis atomic bersama. Jika Java service menulis PostgreSQL lalu Redis, atau Redis lalu PostgreSQL, selalu ada failure window.

Prinsip inti:

PostgreSQL is usually the source of truth.
Redis is usually a derived, accelerated, or coordination-oriented view.
The boundary between them must be explicit.

1. Core Mental Model

Dalam service Java/JAX-RS, integrasi Redis dan PostgreSQL biasanya terlihat seperti ini:

HTTP Request
  -> JAX-RS Resource
  -> Service Layer
  -> Transaction Boundary
  -> MyBatis/JDBC/PostgreSQL
  -> Redis Cache / Redis State
  -> Response

Ada dua lifecycle yang berjalan berdampingan:

PostgreSQL Lifecycle:
  validate -> begin tx -> query/update -> commit/rollback -> durable state

Redis Lifecycle:
  build key -> get/set/delete -> TTL/eviction -> possible stale/expired state

Keduanya tidak memiliki commit protocol bersama secara default. Karena itu setiap desain harus menjawab:

Jika DB commit berhasil tapi Redis write/delete gagal, apa yang terjadi?
Jika Redis update berhasil tapi DB commit gagal, apa yang terjadi?
Jika cache stale, apakah user melihat data salah?
Jika Redis hilang, apakah DB mampu menerima load?
Jika DB down, apakah cache boleh menjawab stale?

2. Redis Over PostgreSQL: Common Use Cases

Use case umum:

Satu pertanyaan selalu menentukan desain:

Can Redis data be safely rebuilt from PostgreSQL?

Jika jawabannya “ya”, Redis lebih aman diperlakukan sebagai derived cache. Jika jawabannya “tidak”, Redis mulai menjadi source of truth dan harus mendapatkan durability, backup, audit, dan recovery treatment yang jauh lebih serius.

3. Cache-Aside with PostgreSQL/MyBatis

Cache-aside pattern paling umum:

Read Request
  -> Redis GET
  -> hit: return cached value
  -> miss: query PostgreSQL
  -> serialize result
  -> Redis SET with TTL
  -> return result

Contoh pseudo-code:

public QuoteSummary getQuoteSummary(String tenantId, String quoteId) {
    RedisKey key = keys.quoteSummary(tenantId, quoteId);

    Optional<QuoteSummary> cached = cache.get(key, QuoteSummary.class);
    if (cached.isPresent()) {
        return cached.get();
    }

    QuoteSummary loaded = quoteMapper.findSummary(tenantId, quoteId)
        .orElseThrow(() -> new NotFoundException("Quote not found"));

    cache.set(key, loaded, ttl.quoteSummary());
    return loaded;
}

Ini sederhana, tetapi failure mode-nya banyak:

• cache miss storm membebani DB
• data stale setelah DB update
• cache fill gagal setelah DB query
• serialization gagal saat fill
• key tidak tenant-scoped
• TTL terlalu panjang
• negative cache menahan entity baru
• cache DTO tidak kompatibel setelah deployment

Cache-aside bukan hanya pattern read. Ia harus disertai write/invalidation discipline.

4. Write Path: DB Update and Cache Invalidation

Untuk write path, pola umum:

Begin PostgreSQL Transaction
  -> update rows
  -> commit
  -> delete/update Redis cache

Contoh:

@Transactional
public void updateQuoteStatus(String tenantId, String quoteId, QuoteStatus newStatus) {
    quoteMapper.updateStatus(tenantId, quoteId, newStatus);
}

public void handleUpdate(...) {
    updateQuoteStatus(...);
    redis.del(keys.quoteSummary(tenantId, quoteId));
}

Hal penting: cache invalidation sebaiknya dilakukan setelah commit berhasil. Jika cache dihapus sebelum commit, request lain bisa reload data lama dari DB lalu mengisi cache lagi.

Failure window setelah commit:

DB commit succeeds
Redis DEL fails
=> cache remains stale until TTL or later invalidation

Ini tidak bisa dihilangkan tanpa mekanisme tambahan. Yang bisa dilakukan:

• TTL cukup pendek untuk membatasi stale window
• retry invalidation
• outbox event setelah commit
• versioned cache key
• compare version saat read/fill
• background reconciliation
• observability invalidation failure

5. Transaction Boundary Mismatch

PostgreSQL transaction punya ACID semantics. Redis command punya atomicity per command/script, tetapi bukan bagian dari PostgreSQL transaction.

Kesalahan umum:

@Transactional
public void updateAndCache(...) {
    quoteMapper.update(...);       // DB transaction not committed yet
    redis.set(key, newValue, ttl); // Redis visible immediately
    // later DB rollback occurs
}

Jika DB rollback setelah Redis set, Redis bisa memuat state yang tidak pernah commit di DB.

Anti-pattern:

Write Redis inside DB transaction as if it will rollback with DB.

Lebih aman:

1. Execute DB transaction.
2. Commit successfully.
3. Publish after-commit action.
4. Invalidate/update Redis.

Di Java framework, gunakan after-commit hook jika tersedia. Jika tidak, pisahkan transactional method dan post-commit cache action secara eksplisit.

6. Database Commit + Redis Failure

Failure scenario:

1. Service updates PostgreSQL.
2. DB commit succeeds.
3. Service tries Redis DEL.
4. Redis timeout occurs.
5. Response returns success or failure?

Biasanya business update sudah berhasil. Mengembalikan error ke client karena cache invalidation gagal bisa membingungkan dan menyebabkan retry yang tidak perlu. Tetapi mengabaikan Redis failure membuat stale cache.

Pilihan:

Untuk sistem enterprise, pilihan harus eksplisit berdasarkan impact stale data.

7. Redis Update + Database Failure

Failure scenario kebalikannya:

1. Service writes Redis first.
2. Service updates PostgreSQL.
3. DB commit fails.
4. Redis now contains uncommitted state.

Ini biasanya lebih buruk.

Contoh:

redis.set(quoteCacheKey, updatedQuote, ttl);
quoteMapper.update(...); // fails

Akibat:

• cache menampilkan data yang tidak pernah durable
• downstream service membaca state palsu
• user melihat status/order yang tidak valid

Rule:

Do not publish durable-looking state to Redis before PostgreSQL commit unless the design explicitly treats Redis as tentative state.

Jika butuh tentative state, beri namespace/status jelas:

quote-processing:{tenantId}:{quoteId}

Bukan:

quote-summary:{tenantId}:{quoteId}

8. Versioned Cache Key

Versioned key membantu menghindari stale update/delete problem.

Contoh:

quote-summary:{tenantId}:{quoteId}:v{version}

Flow:

1. PostgreSQL row has version = 42.
2. Read loads row version 42.
3. Cache key includes v42.
4. Update DB increments version to 43.
5. New read uses key v43.
6. Old v42 key naturally expires.

Keuntungan:

• tidak selalu perlu delete old key segera
• stale write lebih sulit menimpa fresh value
• rolling deployment lebih mudah jika schema version masuk key

Risiko:

• key growth jika TTL panjang
• butuh version dari DB
• query awal tetap harus tahu version
• tidak cocok untuk semua access pattern

Versi bisa berasal dari:

• numeric row version
• updated_at timestamp
• catalog version
• ruleset version
• schema/payload version

Untuk CPQ/catalog/pricing, versioned key sering sangat berguna karena rule/catalog freshness sangat penting.

9. Write-Through with PostgreSQL

Write-through berarti aplikasi menulis DB dan cache dalam write path agar cache langsung fresh.

Flow ideal:

Write Request
  -> validate
  -> DB update
  -> commit
  -> Redis set fresh value
  -> response

Masalah tetap sama: DB dan Redis tidak atomic bersama.

Jika Redis set gagal setelah commit:

DB fresh, cache stale/missing

Jika Redis set dilakukan sebelum commit:

cache may show uncommitted data

Write-through cocok jika:

• payload fresh bisa dibangun dari write command
• stale read tidak boleh lama
• Redis failure behavior jelas
• cache update setelah commit bisa di-retry

Tetapi untuk banyak enterprise system, delete/invalidate setelah commit lebih aman daripada update cache langsung, karena read path akan reload canonical state dari DB.

10. Read Model Cache

Read model cache adalah Redis berisi view yang sudah dihitung.

Contoh:

quote-read-model:{tenantId}:{quoteId}
customer-order-summary:{tenantId}:{customerId}
catalog-pricing-index:{tenantId}:{catalogVersion}

Read model cache sering lebih kompleks daripada entity cache biasa karena:

• datanya derived dari banyak tabel
• invalidation trigger banyak
• rebuild mahal
• stale window lebih sulit dipahami
• event ordering penting

Desain read model cache harus menjawab:

Apa source table-nya?
Event apa yang membuatnya invalid?
Bagaimana rebuild dilakukan?
Apakah cache bisa stale?
Apakah stale bisa menyebabkan keputusan bisnis salah?
Bagaimana versioning dilakukan?
Bagaimana mendeteksi projection lag?

Jika read model cache berisi hasil pricing/eligibility/order validation, correctness lebih penting daripada hit ratio.

11. Idempotency with Redis and PostgreSQL

Untuk API command penting, Redis bisa dipakai sebagai idempotency fast path. PostgreSQL tetap menyimpan business result.

Pattern:

Request with Idempotency-Key
  -> Redis SET NX processing marker
  -> execute DB transaction
  -> commit business result
  -> store completed response/status in Redis
  -> return response

Failure windows:

11.1 Processing marker set, DB not yet committed, service crashes

Result:

• duplicate request melihat PROCESSING
• original work mungkin tidak selesai

Mitigation:

• processing TTL
• status machine
• safe retry after timeout
• DB check by business key

11.2 DB commit succeeds, Redis completed update fails

Result:

• retry may not find completed response in Redis
• duplicate prevention depends on DB uniqueness/business key

Mitigation:

• PostgreSQL unique constraint/business idempotency key
• lookup DB result on retry
• outbox/recovery job to complete Redis state

11.3 Redis completed state exists, DB rolled back

This should not happen if completed state is written only after commit.

Rule:

Redis idempotency improves fast duplicate handling.
PostgreSQL must still defend business uniqueness where correctness matters.

12. Lock Around Database Update

Redis lock sering dipakai untuk mencegah concurrent update:

Acquire Redis lock
  -> update PostgreSQL
  -> release Redis lock

Ini bisa membantu, tetapi tidak menggantikan database constraint/transaction isolation.

Failure modes:

• lock expires while DB transaction still running
• GC pause causes process to continue after lease expiry
• another process acquires lock and updates DB
• lock release fails
• Redis failover loses lock
• network partition creates ambiguous ownership

Untuk DB correctness kuat, PostgreSQL harus tetap punya perlindungan:

• unique constraint
• foreign key
• row lock
• optimistic version
• transaction isolation
• advisory lock where appropriate

Redis lock cocok untuk:

• reducing duplicate work
• best-effort serialization
• protecting expensive rebuild
• scheduler/job singleton
• single-flight reload

Redis lock kurang cocok sebagai satu-satunya penjaga:

• money movement
• irreversible order submission
• legal/compliance state transition
• inventory allocation tanpa DB constraint
• external side effect tanpa fencing/idempotency

13. PostgreSQL Advisory Lock vs Redis Lock

PostgreSQL advisory lock dan Redis lock punya karakter berbeda.

Rule of thumb:

If the protected resource is PostgreSQL state, prefer PostgreSQL constraints/locks first.
If the protected resource is distributed work outside DB, Redis lock may fit.

Redis lock around DB update should be treated as optimization or coordination aid, not the only correctness mechanism.

14. Cache Invalidation After Database Migration

Database migration can silently break Redis cache.

Examples:

• column removed but cached JSON still contains field
• enum value renamed but old cache has old enum
• BigDecimal precision changed
• status state machine changed
• tenant scoping added to DB but cache key is global
• pricing rule schema changed but old catalog cache remains

Migration checklist:

1. Does this migration change data shape used by Redis payload?
2. Does this migration change business semantics of cached fields?
3. Does this migration require cache namespace version bump?
4. Does old cached payload deserialize on new code?
5. Does new cached payload deserialize on old code during rollback?
6. Should cache be flushed selectively after deployment?
7. Is cache warming needed?

Safer strategies:

• versioned payload
• versioned key namespace
• backward-compatible readers
• short TTL during migration window
• selective invalidation after migration
• deployment plan for rollback compatibility

Avoid broad FLUSHALL unless explicitly approved. It can cause system-wide cache stampede and data loss for non-cache Redis use cases.

15. Cache Warming After Deployment

Cache warming can reduce latency after deploy or failover. But it can also overload PostgreSQL or populate wrong/stale data.

Cache warming sources:

• hot entity list from analytics/logs
• recently accessed quotes/orders
• active tenant catalog/config
• pricing rules
• feature config

Risks:

• warming too many keys
• warming data for inactive tenants
• warming stale version
• bypassing authorization/tenant isolation
• overloading DB after deployment
• warming sensitive data unnecessarily

Safe warming principles:

Warm only known hot keys.
Rate-limit warming.
Use current schema/version.
Respect tenant boundary.
Do not warm sensitive data unless justified.
Observe DB load and Redis memory.

16. PostgreSQL Outage Behavior

Redis changes the behavior of PostgreSQL outages.

If Redis has warm cache, reads might continue temporarily. But writes and cache misses still need DB.

Questions:

Can the service serve stale cache if PostgreSQL is down?
Which endpoints are allowed to return stale data?
How old can stale data be?
Should response include degraded indicator?
Do we protect DB on recovery from cache miss storm?
What happens to idempotency and locks during DB outage?

Patterns:

For CPQ/order workflows, be careful with stale pricing, eligibility, order status, and contract-affecting data. Not all reads are safe to serve stale.

17. Redis Outage Behavior with PostgreSQL Available

If Redis is unavailable but PostgreSQL is healthy:

• cache read should miss/degrade to DB if safe
• cache write failure should not usually fail read endpoint
• rate limiter may fail open/closed depending policy
• idempotency may need DB fallback
• lock-based protection may be unavailable
• cache stampede risk increases

Danger:

Redis outage can shift all traffic to PostgreSQL.

Mitigation:

• circuit breaker around Redis
• local short-lived cache for hot config
• DB query rate limiting/backpressure
• request coalescing/single-flight inside instance
• degraded mode for non-critical endpoints
• alerts for Redis fallback rate

If Redis outage causes DB overload, Redis is part of DB capacity planning.

18. MyBatis/JDBC Specific Concerns

With MyBatis/JDBC, Redis integration is often hand-coded. That gives flexibility but also inconsistency risk.

Review points:

• Are mapper methods called inside clear transaction boundary?
• Is Redis updated inside or outside transaction?
• Are cache keys built from the same tenant/entity criteria as SQL where clause?
• Does SQL include tenant filter but Redis key does not?
• Does cache DTO match mapper result, not table entity blindly?
• Are null results cached? For how long?
• Are DB exceptions mapped differently from Redis exceptions?
• Is there a common cache wrapper or each DAO/service invents its own logic?

Dangerous mismatch:

SELECT * FROM quote WHERE tenant_id = ? AND quote_id = ?

but Redis key:

quote:{quoteId}

This creates cross-tenant cache leakage.

19. Event-Driven Invalidation with Outbox

For stronger invalidation after DB commit, use outbox pattern:

DB Transaction
  -> update business table
  -> insert outbox event in same transaction
  -> commit

Outbox Publisher
  -> publish event to Kafka/RabbitMQ

Consumer
  -> invalidate/update Redis

Advantages:

• event exists if DB commit exists
• invalidation can retry
• failure is observable
• decouples request latency from Redis invalidation

Risks:

• invalidation lag
• duplicate events
• out-of-order events
• consumer failure
• cache stale until event processed

Design requirements:

• idempotent consumer
• version-aware cache update
• dead-letter/retry policy
• lag metrics
• event schema compatibility

For high correctness data, combine outbox invalidation with TTL and version checks.

20. Consistency Patterns

20.1 TTL-only consistency

DB update occurs.
Cache remains stale until TTL expires.

Good for:

• low criticality data
• short TTL
• low write frequency

Bad for:

• pricing decisions
• status transitions
• security state
• entitlement/permission changes

20.2 Explicit invalidation

DB update commits.
Service deletes Redis key.

Good for:

• simple entity cache
• direct write path ownership

Risk:

• invalidation failure
• missing invalidation path

20.3 Event-driven invalidation

DB commit emits event.
Consumer invalidates Redis.

Good for:

• multiple writers
• cross-service invalidation
• projection cache

Risk:

• lag/out-of-order/duplicate events

20.4 Versioned key

Cache key includes DB version.
Old keys expire naturally.

Good for:

• catalog/rules/pricing/versioned domains
• rollback-safe payload changes

Risk:

• more keys
• still need version discovery

20.5 Read repair

Read detects stale version and refreshes cache.

Good for:

• gradual correction

Risk:

• stale detection must be reliable
• extra DB read

21. Failure Mode Matrix

22. Production-Safe Debugging Flow

When Redis/PostgreSQL inconsistency is suspected:

1. Identify entity, tenant, and expected source-of-truth row.
2. Read PostgreSQL canonical state.
3. Identify Redis key pattern from code, not guesswork.
4. Check Redis TYPE/TTL/MEMORY before value.
5. Check cached payload version/timestamp if available.
6. Check recent DB write logs/transaction timing.
7. Check invalidation logs/events/outbox.
8. Check Redis errors/timeouts around commit time.
9. Check deployment/migration timeline.
10. Decide: delete one key, invalidate namespace, or fix code.

Avoid production-wide destructive commands. Prefer targeted invalidation:

DEL quote-summary:{tenantId}:{quoteId}:v42

Not:

FLUSHDB
FLUSHALL
KEYS quote-summary:*

23. Java/JAX-RS Design Pattern

A clean service design separates concerns:

JAX-RS Resource
  -> validates HTTP input
  -> delegates to service

Service Layer
  -> owns transaction boundary
  -> owns business decision

Repository/Mapper
  -> PostgreSQL access

Cache Adapter
  -> Redis access
  -> key construction
  -> serialization
  -> TTL policy

Invalidation Publisher
  -> after-commit invalidation/outbox

Avoid:

JAX-RS resource directly builds Redis key and calls mapper/client ad hoc.

Better:

public QuoteSummary getQuoteSummary(TenantId tenantId, QuoteId quoteId) {
    return quoteSummaryCache.getOrLoad(
        tenantId,
        quoteId,
        () -> quoteRepository.findSummary(tenantId, quoteId)
    );
}

The cache adapter should own:

• key format
• TTL
• serializer
• safe logging
• cache metrics
• fallback behavior

The service should own:

• whether stale data is acceptable
• transaction semantics
• business correctness
• invalidation timing

24. Observability

Important metrics:

Redis side

• cache hit/miss ratio per namespace
• Redis latency per operation
• timeout/error rate
• evicted/expired keys
• memory usage
• key count/cardinality per namespace if available

PostgreSQL side

• query latency for cache-backed reads
• DB QPS during cache miss spikes
• transaction error/rollback rate
• lock wait time
• connection pool usage

Integration side

• invalidation success/failure
• invalidation lag
• outbox lag
• stale version detected
• fallback to DB due to Redis failure
• stale response served count
• cache fill failure
• serialization/deserialization failure

Dashboard should answer:

Is Redis protecting PostgreSQL or hiding a correctness problem?
Is PostgreSQL overloaded because Redis is failing/missing?
Is Redis serving stale data after writes?
Are invalidation events delayed or failing?

25. PR Review Checklist

When reviewing Redis/PostgreSQL integration:

• What is the source of truth?
• Is Redis derived, temporary, or authoritative?
• Is Redis written inside or after DB transaction?
• What happens if DB commit succeeds but Redis fails?
• What happens if Redis succeeds but DB fails?
• Is invalidation after commit?
• Is there retry/outbox for important invalidation?
• Is TTL enough to bound stale data?
• Does key include tenant and version where needed?
• Does cached payload include schema/payload version?
• Does cache DTO differ from DB entity intentionally?
• Are null/not-found results cached? For how long?
• Is stale data acceptable for this endpoint?
• Does Redis outage overload PostgreSQL?
• Does PostgreSQL outage allow stale cache serving? Should it?
• Are migration and rollback cache-compatible?
• Are observability metrics present?
• Are tests covering DB/Redis partial failure?

26. Testing Checklist

Test beyond happy path:

• cache hit
• cache miss then DB load
• cache fill failure
• DB not found with negative cache
• DB update invalidates cache after commit
• DB rollback does not update cache
• Redis delete failure after DB commit
• stale cache bounded by TTL
• versioned key uses new DB version
• tenant A cannot read tenant B cache
• serializer compatibility across versions
• Redis unavailable fallback behavior
• DB unavailable cache behavior
• cache stampede on miss
• outbox invalidation duplicate event
• out-of-order invalidation event

For integration tests, Testcontainers can validate Redis behavior, but DB/Redis partial failure scenarios may need explicit fault injection or mockable adapters.

27. Internal Verification Checklist

Verify internally:

• Which PostgreSQL tables/entities are cached in Redis.
• Which services own each cache namespace.
• Whether Redis keys include tenant/environment/service/entity/version prefix.
• Whether cached payloads are DTOs or DB entities.
• TTL policy per cache namespace.
• Invalidation mechanism: direct delete, event-driven, TTL-only, versioned key, or hybrid.
• Whether invalidation happens after DB commit.
• Whether outbox is used for cross-service invalidation.
• Whether DB migration process includes cache invalidation/version bump.
• Whether Redis outage can overload PostgreSQL.
• Whether PostgreSQL outage allows stale cache response.
• Whether Redis lock is used around DB updates and whether DB constraints still protect correctness.
• Whether PostgreSQL advisory locks are used or should be considered.
• Dashboard for Redis hit/miss and DB query load.
• Incident history involving stale cache, cache stampede, or DB overload due to Redis failure.

28. Practical Rule of Thumb

Use this rule in architecture review:

If Redis and PostgreSQL disagree, PostgreSQL usually wins.
If Redis must win, Redis is no longer just cache and needs source-of-truth discipline.

And:

Never design as if DB commit and Redis write/delete are one atomic operation.
They are separate operations with a failure window.

29. Summary

Redis/PostgreSQL integration is not just cache implementation. It is distributed consistency design.

The senior engineer view focuses on:

• source-of-truth boundary
• DB transaction timing
• cache invalidation after commit
• stale data window
• Redis/DB partial failure
• migration compatibility
• tenant-safe key design
• idempotency fallback
• lock correctness boundary
• Redis outage impact on DB capacity
• PostgreSQL outage behavior with warm cache
• observability across both systems

A production-ready design does not claim perfect consistency unless it has a real mechanism for it. It states the consistency model clearly, bounds the stale window, instruments failures, and keeps PostgreSQL constraints as the final guard for durable business correctness.