A Practical Guide to Data Persistence and Logging: From Technology Selection to Pre-Launch Checklist
This guide walks through the core decisions behind keeping business data safe and operational logs searchable. It covers the boundary between persistence and logging, WAL and idempotency design, storage selection with RPO/RTO estimation, the Outbox pattern, log field standards, sampling and retention policies, backup restore drills, canary migration with rollback, and a pre-launch checklist you can apply directly.
Persistence and Logging: A Practical Guide from Selection to Go-Live
1. Draw the Line First: Not Losing Data vs. Tracing What Happened
Data persistence is responsible for reliably saving business state — order amounts, inventory counts, payment records. This data must be readable even after a process crash or a power failure. Logging is responsible for traceability — request paths, error causes, and latency — used for troubleshooting and statistics.
To decide where a piece of information belongs, ask one question: if this data is lost, will the business break? If yes, put it in persistent storage. If it only affects post-hoc analysis, write it to logs. By this standard: payment records belong in persistence; inventory deduction records belong in persistence; view counters losing a few entries typically only affects reports, so they belong in logs or metrics; audit logs must be retained completely — they are compliance evidence and need dedicated storage and retention policies, not just ordinary application logs.
Logs get lost too. Three common causes: disk space running out, collection agent failures, and premature deletion during periodic rotation. Logs therefore cannot substitute for persistence. To guard against log loss, monitor three metrics: disk usage, collection lag, and dropped-record count. Alert when disk usage exceeds 80%, when collection lag exceeds 60 seconds, and whenever the dropped-record count is non-zero. These thresholds are starting points and should be tuned to actual capacity.
Two mechanisms connect the two worlds.
Write-ahead logging (WAL): The database appends changes to a log file before modifying data files. After a crash, the database replays the log to recover to the last successful commit. With WAL archiving enabled, log segments can also be shipped to other storage, providing the basis for point-in-time recovery.
Idempotency design: Every write request carries a unique request ID, and the server records the IDs it has processed. On retry, the same deduction takes effect only once. Four implementation points:
- Store request IDs in a table with a unique constraint; deduplication relies on the database’s unique index. Do not check-then-write — under concurrency that leads to duplicates.
- Commit the deduplication record and the business write in the same transaction: both succeed or neither takes effect.
- Retain request IDs for at least the client’s longest retry window. If the client retries for up to 24 hours, retain IDs for more than 24 hours.
- Clean up expired records with scheduled jobs that delete in batches by time, avoiding large delete transactions during peak hours.
2. Technology Selection and RPO/RTO Estimation
Selection starts with three factors: consistency model, transaction capability, and write latency.
| Dimension | Strong consistency | Eventual consistency |
|---|---|---|
| What reads return | After a successful write, all reads see the latest value | Short-lived lag between replicas; stale reads are acceptable |
| Transaction capability | Typically supports multi-row, multi-table transactions | Cross-node transactions are limited; compensation or event mechanisms needed |
| Write latency | Waits for acknowledgment from a majority of replicas; higher latency | Returns after local acknowledgment; lower latency |
| Cost | Added latency, especially noticeable in cross-region deployments | Requires compensation, reconciliation, and duplicate-message handling |
| Typical use cases | Orders, payments, account balances | View counters, recommendation results, notification status |
Critical writes should complete within a single local transaction. When placing an order, the order and payment records must be written together or fail together.
RPO is the maximum acceptable data-loss window. Estimate it by identifying, in the worst case, the gap between the last recoverable point and the moment of failure. Three main factors: backup interval, asynchronous replication lag, and log flush interval.
Example: a service takes one full backup nightly with no WAL archiving. If a failure occurs at 23:50 the next day, the worst case loses nearly 24 hours of data — far beyond business tolerance. Switching to incremental backups every 15 minutes plus continuous WAL archiving reduces the worst-case loss to roughly the archive interval plus replication lag, typically down to minutes. Exact numbers should be verified through load testing and drills.
RTO is the maximum time from failure to restored service. Sum the durations of each phase:
RTO ≈ detection time + decision time + data recovery time + failover and verification time
Example: detection 5 minutes, decision 10 minutes, data recovery 40 minutes, failover and verification 15 minutes — about 70 minutes total. If the business requires RTO ≤ 60 minutes, you must shorten data recovery, for example by keeping a warm standby instance ready or controlling per-database size.
Backup frequency must align with RPO. When the backup interval is longer than the RPO, backups alone cannot meet the requirement — WAL archiving or replication is mandatory.
The Outbox pattern handles consistent delivery across services. The full flow:
- The business write and the event write happen in the same local transaction. The business table receives the order; the outbox table receives one pending event. Both commit together or both roll back.
- A separate delivery process polls unsent records in id order, reading in batches.
- The delivery process sends events to the message queue and marks records as sent after receiving acknowledgment.
- On send failure, retry with exponential backoff — e.g., 1s, 2s, 4s intervals — up to a limited number of attempts. Records exceeding the limit move to a dead-letter table and trigger an alert for manual or compensating-task handling.
- Consumers deduplicate by event ID. The delivery semantic is at-least-once: the same event may be sent multiple times, and consumers must tolerate duplicates.
- Monitor the count of undelivered records and the wait time of the oldest one. Continuously growing wait time indicates the delivery pipeline is backed up.
- Clean up sent records on a retention schedule, in batches during off-peak hours.
3. Log Fields, Sampling, and Retention
All services share one log field schema to enable cross-service search.
| Field | Type | Example | Description | Required |
|---|---|---|---|---|
| time | string | 2026-11-20T13:55:19.609+00:00 | Millisecond precision, always UTC | Yes |
| level | string | ERROR | DEBUG, INFO, WARN, ERROR | Yes |
| service | string | order-api | Service name | Yes |
| version | string | 1.8.3 | Release version, for locating regressions | Yes |
| trace_id | string | 4bf92f3577b3 | Distributed trace identifier | Yes |
| request_id | string | req_20261120_0001 | Business request ID, matching the idempotency record | Yes |
| operation | string | create_order | API or operation name | Yes |
| duration_ms | integer | 128 | Processing time in milliseconds | Yes |
| error_code | string | PAY_TIMEOUT | Business or system error code, empty if none | No |
| error_msg | string | Payment gateway timeout | Error description, sanitized before writing | No |
| instance | string | order-api-7f9c | Instance identifier, for locating host-specific issues | Yes |
| user_ref | string | u_3a9f81 | Hashed user identifier, never plaintext | No |
Phone numbers, national ID numbers, bank card numbers, passwords, and tokens must never be written to logs. These must be sanitized or replaced with hashes before writing.
Sampling rules by level:
- ERROR and WARN: keep all.
- Requests exceeding a latency threshold (e.g., duration_ms > 1000): keep all.
- INFO logs for normal successful requests: sample at 10%.
- DEBUG: off by default, enabled temporarily for troubleshooting, with an automatic shutoff time.
- Sampling decisions must stay consistent within the same trace_id, so traces don’t end up half missing.
- Audit records on critical paths such as payments and deductions are never sampled.
Retention decision table. Actual day counts must follow compliance requirements and storage budgets; the values below are examples only.
| Log type | Hot storage | Cold storage | Notes |
|---|---|---|---|
| Application ERROR logs | 14 days | 90 days | Primarily for troubleshooting |
| Regular INFO logs | 7 days | Not archived | Volume is small after sampling |
| DEBUG logs | 3 days | Not archived | Temporarily enabled, deleted on expiry |
| Audit logs | Per compliance | Per compliance | Stored separately; deletion requires approval |
| Aggregated metrics | 30 days | 13 months | For capacity and trend analysis |
4. Backup Restore Drills and Canary Rollout with Rollback
Restore drill steps. Run at least quarterly and document every run:
- Verify backup set integrity: check checksums, file counts, and total size. Any missing file fails the drill.
- Restore to a target point in time in an isolated environment, choosing a moment before the simulated failure. The restore target must never be a production database, and production data must never be overwritten.
- Verify the data: compare table row counts, verify key amount totals, and spot-check random primary keys.
- Record actual restore time and check it against the RTO.
- Compute the gap between the restore point and the simulated failure time and check it against the RPO.
- Document: drill date, participants, backup version, restore duration, data discrepancies, issues found, and remediation deadlines. Every failed item must have an owner and a due date.
Canary migration steps:
- Dual-write: write to both old and new storage; treat the old storage’s result as authoritative.
- Reconcile: compare row counts and key field totals in hourly batches. Pause traffic ramp-up when discrepancies exceed the threshold.
- Canary read traffic: ramp up in stages — 1%, 10%, 50%, 100% — observing each stage for a period, e.g., 30 minutes to 1 hour.
- Switch writes: once reads are stable, move writes to the new storage. Keep the old storage read-only during the observation window so you can switch back.
- Decommission the old pipeline only after the observation window closes.
Example canary and rollback criteria: error rate rises more than 0.1 percentage points above baseline for 5 consecutive minutes; P99 latency rises more than 50% above baseline; reconciliation discrepancies exceed the threshold for two consecutive batches. Any one condition triggers a pause. Thresholds must be tuned to the service’s baseline.
Rollback steps:
- Immediately stop ramp-up and switch read traffic back to the old storage.
- Identify all writes made to the new storage after the write switch and replay them into the old storage. Otherwise, rollback loses business data created after the cutover.
- Reconcile both sides by time window, find differing records, and replay them.
- Once reconciliation is consistent, disable writes on the new pipeline.
- Post-mortem: record the trigger, blast radius, and resolution time, and update the criteria.
A common data-consistency check is to aggregate row counts and amount totals into time buckets — for example, hourly order counts and amounts — and compare bucket by bucket on both sides.
Risk notes:
- If post-cutover writes are not replayed before rollback, that data is lost.
- When drill data comes from production backups, it must be sanitized before entering test environments.
- Store backup files separately from encryption keys. Files without keys are just as unreadable at restore time.
- All times and thresholds in this article are examples; actual values must come from load testing and drills. This checklist does not guarantee zero data loss.
5. Pre-Launch Checklist
- Critical writes happen in one local transaction; cross-service scenarios use the Outbox pattern
- Request IDs have a unique constraint; deduplication records commit in the same transaction as business writes
- Request ID retention covers the client’s longest retry window
- WAL and archiving are enabled, and log replay has been verified at least once
- Backup frequency matches the RPO, or point-in-time recovery (PITR) is enabled
- The most recent drill passed, with actual RTO and RPO recorded
- Log fields match the field table, with no missing required fields
- Sampling rules are in effect, and audit records on critical paths are not sampled
- Retention periods are configured and verified against compliance requirements
- Sensitive fields such as phone and bank card numbers are sanitized before writing, with automated checks in place
- Alerts cover disk usage, collection lag, dropped-record count, and Outbox backlog
- Canary switches and rollback scripts are verified in a staging environment
- Reconciliation scripts work, and on-call staff are familiar with the runbook
