Lifecycle-Aware Archival for Asymmetric Financial Datasets: A Production Study

Large-scale financial transaction databases must balance operational freshness with storage efficiency. At Razorpay, a financial transaction service manages billions of records on PostgreSQL Aurora (version 14), occupying tens of terabytes and sustaining peak write throughput in the thousands of TPS. The core challenge is the tension between keeping recent data hot for fast access and archiving older data to reduce costs. This paper presents the design, implementation, and production evaluation of a lifecycle-aware archival system that addresses this tension. The authors make two key contributions: first, an analytical characterization of the Celebrity Partition Problem, where lifecycle-state partitioning concentrates all operationally active rows in a single default partition, leading to planning overhead, execution I/O regression, and write amplification; second, an ID-monotonicity-based deduplication technique that exploits temporal encoding in monotonically increasing ID schemes (e.g., Snowflake IDs, ULIDs) to route only potentially-archived re-inserts to a warm database lookup, eliminating the need for Bloom filters, idempotency tables, or external dependencies. The system is fully deployed and reports significant production results.

The methodology centers on two innovations. First, the Celebrity Partition Problem is analytically characterized. In lifecycle-state partitioning, a table is partitioned by a lifecycle state (e.g., active, archived). However, all operationally active rows end up in a single default partition, causing the query planner to incur overhead of $O(N\times M)$, where $N$ is the number of partitions and $M$ is the number of active rows. Execution I/O regresses to $O(N)$, and every state transition causes write amplification. This problem is formalized and its impact on performance is quantified.