Designing an Expiring-Points System on an RDBMS (with Benchmarks)

--- title: "Designing an Expiring-Points System on an RDBMS (with Benchmarks)" published: true description: "A battle-tested relational design for airline-mile-style points that expire per grant, support exact cancellation, and satisfy accounting audits — plus real PostgreSQL benchmarks and a scalability analysis." tags: postgres, database, sql, systemdesign canonical_url: https://blog.teraren.com/posts/point-system-design-rdb/ --- > This is an English rewrite of an article I originally published in Japanese. I've run the design on PostgreSQL 17 in Docker and folded the measured numbers into this post. ## TL;DR - Designing a points system where **each grant expires on its own date** (think airline miles, not "12 months since last activity") is far harder than it looks. - I compare three relational designs and land on an **object-oriented ledger** with three tables: `deposit`, `withdraw`, and a `deposit_withdraw` allocation table. - That allocation table is the whole trick: it records *which grant each spend drew from*, which makes **exact cancellation** and **accounting reconciliation** fall out for free. - I benchmarked it on PostgreSQL 17 with **3M deposits** and walk through the throughput, the contention limits, and how it scales to a 100k-events/day service. - This design has **15 years of production use across two services with zero balance inconsistencies**. ## Two kinds of "expiring points" Before anything else, let's separate two systems that people lump together. **Last-activity expiry (easy).** Your whole balance expires N months after your last earn or spend. Every transaction pushes the single expiry date forward. One column, done. Most electronics-retailer point cards work this way. **Per-grant expiry (hard).** Every time you earn points, *that batch* gets its own expiry date. 100 points earned in June expire end of June next year; 100 earned in July expire end of July. You can be holding points with five different expiry dates at once. ```mermaid graph LR A[Points system] --> B[Per-grant expiry<br/>airline miles] A --> C[Last-activity expiry<br/>retail cards] B --> D[Hard] C --> E[Easy] ``` This article is entirely about the **hard** one. If last-activity expiry is all you need, stop here and just store one date. ## The requirements that make it hard "It's just points, how complex can it be?" — famous last words. Here's what real operation demands: - **Grant and spend** points, with an arbitrary expiry date per grant. - **Consume in First-Expire-First-Out (FEFO) order.** Not FIFO. People conflate the two, but you want the *soonest-to-expire* batch first, and a batch earned *later* can expire *sooner*. They don't coincide. - **Cancel a spend** and restore the exact pre-spend state — including the original expiry dates of the points that were consumed. This is the single hardest requirement. - **Point-in-time balance**: "what was this user's balance at the end of last March?" - **Accounting**: produce a balance sheet (BS) and a P&L at any point in time, and prove they reconcile. - **Integrity checks** you can run as a nightly batch. The cancellation requirement is the one that kills naive designs. Let me show why. ## Three designs ### 1. Transaction model (too simple) Two tables: a deposit history (amount, used_amount, expiry) and a withdraw history (amount). Minimal and easy. **Fatal flaw:** there's no link between a withdrawal and the deposits it drew from. You literally cannot implement cancellation, because you don't know which grants to credit back, or what their expiry dates were. Only choose this if you are *certain* you'll never need to reverse a spend. In practice you always do. ### 2. Accounting model (good for reporting, still can't cancel) Store a P&L stream and a BS (balance) stream separately, so aggregation is trivial. Reads are great. **Same fatal flaw:** still no grant↔spend linkage, so still no cancellation. And reconstructing a historical balance means replaying the entire P&L from the beginning. ### 3. Object-oriented ledger (the one I ship) Keep deposits and withdrawals as separate entities, **and add a junction table that records each allocation of a spend against a specific grant.** ```mermaid erDiagram User ||--o{ Deposit : has User ||--o{ Withdraw : has Deposit ||--o{ DepositWithdraw : references Withdraw ||--o{ DepositWithdraw : references Deposit { bigint id PK int user_id FK int amount date expiration_date timestamptz created_at } Withdraw { bigint id PK int user_id FK int amount timestamptz created_at } DepositWithdraw { bigint id PK bigint deposit_id FK bigint withdraw_id FK int amount timestamptz created_at } ``` `DepositWithdraw` is the keystone. When a user spends 150 points: - 100 from the June-expiry grant → one `deposit_withdraw` row - 50 from the July-expiry grant → another `deposit_withdraw` row To **cancel**, you delete those two rows (and the withdraw) and the state is *exactly* restored — original grants, original expiry dates, everything. No reconstruction, no drift. | | Cancellation | Reporting | Complexity | |---|---|---|---| | Transaction model | ❌ | OK | Low | | Accounting model | ❌ | Great | Low | | **OO ledger** | ✅ | ✅ | Medium | The cost is three+ tables, more rows per spend, and the need for disciplined locking. Worth it. ## The three operations that need care ### Spend (FEFO) — the dangerous one ```sql BEGIN; -- 1. Lock the user's valid grants FIRST, in a deterministic order. SELECT * FROM deposit WHERE user_id = $1 AND expiration_date >= CURRENT_DATE -- "last valid day" semantics ORDER BY expiration_date, id -- id makes lock order unique FOR UPDATE; -- 2. Recompute the balance FROM THE LOCKED ROWS, then check. -- 3. INSERT the withdraw. -- 4. INSERT deposit_withdraw rows, FEFO, until the amount is covered. COMMIT; ``` Three traps live in those four steps: **Deadlocks.** If two transactions lock the same grants in different orders, they deadlock. Fix the order globally. Crucially, `ORDER BY expiration_date` alone is *not* deterministic — rows with the same expiry can be locked in plan-dependent order. Append the primary key: `ORDER BY expiration_date, id`. **TOCTOU / negative balances.** If you check the balance *before* taking the lock, two concurrent spends can both pass the check and over-draw the account into the negative. You must **lock first, then recompute the balance from the locked rows, then decide.** The second request will block on the first's COMMIT and see the truth. **Date-boundary off-by-one.** Pick a meaning for `expiration_date` and never deviate. I define it as the **last valid day**: valid is `expiration_date >= CURRENT_DATE`, expired is `< CURRENT_DATE`. Mix the two and you get one-day errors at month-end, which in a points system is a real money bug. ### Cancel — mind the delete order ```sql BEGIN; SELECT * FROM deposit_withdraw WHERE withdraw_id = $1 FOR UPDATE; DELETE FROM deposit_withdraw WHERE withdraw_id = $1; -- child first DELETE FROM withdraw WHERE id = $1; -- then parent COMMIT; ``` Delete the **child (`deposit_withdraw`) before the parent (`withdraw`)**, or the foreign key blows up (unless you've set `ON DELETE CASCADE`). I had this backwards in the first draft — building it for real surfaced it immediately. > ⚠️ **Physical delete is only OK when you don't need an audit trail or historical balances.** If "what was the balance last March?" is a requirement, deleting a past withdrawal silently rewrites history. Use a `cancelled_at` soft-delete, or post a reversing entry, and keep closed-period rows immutable. This is just double-entry bookkeeping's reversing-entry rule. ### Breakage (expired points) without a batch You don't need a job that writes "expired" rows. Since expired grants can't be spent (the FEFO query filters them out), each grant's allocations are final by its expiry date. So: > **breakage = `Deposit.amount − Σ(its DepositWithdraw)`**, recognized at `expiration_date`. One query derives expired value per date. No batch, no clock to chase. ## Integrity checks (the part that lets me sleep) Three checks, run nightly: 1. **Each withdraw equals the sum of its allocations.** (Aggregate per withdraw *first* — naively joining double-counts when a spend splits across grants.) 2. **No grant is over-consumed** (`deposit.amount >= Σ allocations`). 3. **BS reconciles with P&L** via the accounting identity: ```plaintext granted − consumed − expired − current_balance = 0 ``` That third one is a single query over the three tables. If it returns zero, the books balance. **You can derive granted, consumed, expired, and balance from three tables alone — no materialized "expired" or "balance" tables required.** That's the payoff of the design. ## Benchmarks I built exactly this on PostgreSQL 17.10 in Docker (Apple M4, 10 cores, 16 GB, `shared_buffers=1GB`) and loaded dummy data: | Table | Rows | |---|---| | users | 100,000 | | deposit | 3,000,000 (≈1,000,000 still valid) | | withdraw | 1,500,000 | | deposit_withdraw | 1,500,000 | Database size: **731 MB**. The spend/cancel logic is implemented as PL/pgSQL functions following the steps above; measured with `pgbench`, 10 s per run. **Functional check first.** I replayed the canonical scenario (grant 100 + grant 100 → spend 150 → cancel) and it behaved exactly as designed: FEFO split 100+50, an over-draw of 51 was rejected, and cancel restored both grants to 100. Good. **Online throughput** (random users): | Operation | 1 conn | 10 conns | 50 conns | |---|---|---|---| | Balance query | 4,693 TPS / 0.21 ms | 18,677 TPS / 0.54 ms | 18,158 TPS / 2.75 ms | | Spend (FEFO) | 1,991 TPS / 0.50 ms | 4,163 TPS / 2.40 ms | 5,375 TPS / 9.30 ms | | Spend + cancel pair | — | 5,375 pairs/s / 1.86 ms | — | Even with 3M deposits, balance reads hit ~18k TPS and the locking spend path still does thousands of TPS. Comfortable for any small-to-mid service. **Single hot user** (all spends hammering one account): | Conns | TPS | Latency | |---|---|---| | 1 | 973 | 1.03 ms | | 10 | 410 | 24.4 ms | | 50 | 323 | 154.9 ms | This is **not a bug** — it's the intended serialization. The same user's concurrent spends *must* line up to keep the balance correct. Fine for human users; something to plan for if one corporate account fires thousands of concurrent spends. **Indexes are not optional.** Balance query, one execution: | | Time | Buffers read | |---|---|---| | With indexes | 0.81 ms | 53 pages | | Without | 399.81 ms | 37,354 pages | A **~490× difference.** At minimum: ```sql CREATE INDEX idx_deposit_user_expiration ON deposit (user_id, expiration_date, id); CREATE INDEX idx_dw_deposit ON deposit_withdraw (deposit_id); CREATE INDEX idx_dw_withdraw ON deposit_withdraw (withdraw_id); ``` **Batch / accounting queries** (full scan at 3M deposits): | Query | Time | |---|---| | Check 1 (withdraw vs allocations) | 1.29 s | | Check 2 (over-consumption) | 1.09 s | | Check 3 (BS = P&L) | 2.07 s | | Breakage per expiry date | 0.90 s | | Point-in-time balance (one user) | 8.4 ms | | Point-in-time BS (all users) | 1.48 s | And the reassuring one: after driving ~130k concurrent spends and cancels at up to 50 connections, re-running all three integrity checks returned **zero inconsistencies and a zero accounting-identity diff.** The lock-then-check-then-allocate recipe holds under contention. ## Will it scale? A back-of-the-envelope for 100k events/day Say a large service does **100k grants + 100k spends per day**. I didn't physically load this (my laptop's disk would melt) — these are extrapolations from the measured anchors above (~128 bytes/row including indexes). **Storage:** | Age | deposit | withdraw | deposit_withdraw | Size | |---|---|---|---|---| | 1 year | 36.5M | 36.5M | ~55M | ~16 GB | | 5 years | 182M | 182M | ~270M | ~78 GB | | 10 years | 365M | 365M | ~550M | ~160 GB | 160 GB after a decade. A non-issue for modern NVMe. **Online ops don't depend on total row count.** Every read or spend touches only *that user's valid grants* (a few dozen pages via index). The table can be 3M or 3B rows; the plan is the same. And the working set — grants valid in the last ~365 days — stays roughly constant over time (~5 GB). Expired history piles up linearly, but online queries never read it. 200k ops/day is **2.3 ops/s average.** Even at 50× peak that's ~120 ops/s, against a measured 5,375 spend-TPS — a 40× margin. Write throughput is not your problem at this scale. **Batches degrade linearly** — that's the thing to manage: | Query | 3M (measured) | 36.5M (1yr) | 365M (10yr) | |---|---|---|---| | Check 1 | 1.29 s | ~30 s | ~5 min | | Check 3 | 2.07 s | ~25 s | ~4 min | Mitigations, in order of when you need them: - **Incremental checks** — verify only withdrawals created since the last run; full scans become monthly/closing-time only. - **Monthly BS snapshots** — compute point-in-time and Check 3 as snapshot + delta. - **Partitioning** by `expiration_date` / `created_at` — `DETACH PARTITION` lets you archive fully-expired, fully-consumed data instantly, keeping the hot table at a steady ~1–2 years of data. ### Where the real ceiling is | Scale | avg ops/s | Verdict | |---|---|---| | 100k+100k/day | ~2.3 | Easy. Single node, 10 years. | | 1M/day | ~23 | Single node. Add incremental checks + partitioning. | | 10M/day | ~230 (peaks in thousands) | Single-node edge. Move aggregation to a replica. | | 100M/day+ | 2,300+ | Time to shard by `user_id`. | Hard limits — PostgreSQL's 32 TB per table (millennia at this pace) and a `bigint` PK at 9.2×10¹⁸ — are effectively unreachable. The practical walls are: 1. **Single-node write throughput** — bounded by WAL fsync, tens of thousands of TPS on a real server. Only a hundreds-of-millions/day concern. 2. **Full-scan batch time** — linear; solved by incremental checks and snapshots. 3. **Hot-user serialization** — measured at a few hundred ops/s per user. **This one neither sharding nor archiving can remove; it's the essential cost of guaranteeing the balance.** The good news for #3: every transaction here is **closed within a single user** (no cross-user row locks), so `user_id` sharding is clean, and global reconciliation becomes a per-shard-then-sum offline job. The only genuinely awkward case is thousands of concurrent spends on *one* account — there you'd bucket the account, queue and level the writes, or move to an optimistic-locking scheme. The headline: **what bounds this design is operations-per-second and per-user concentration, not total row count.** ## Appendix: this isn't novel, and that's the point I wondered whether this design was something I'd invented. It isn't — and tracing where it *does* come from is genuinely useful, because each lineage hands you vocabulary and prior art: - **WMS / FEFO lot allocation.** Warehouses manage perishable stock as lots, pick soonest-to-expire first, and record which lot each shipment drew from. That's `Deposit` → `DepositWithdraw` exactly. (See NetSuite's FEFO Lot Assignments.) - **Securities tax-lot accounting.** Deciding which purchase lot a sale relieves (FIFO / specific identification) and reversing it on correction is literally called *lot relief*. Brokerages have had `DepositWithdraw`-shaped allocation records for decades. - **Commercial loyalty platforms.** Salesforce Loyalty Management tracks per-accrual expiry in ledger objects; Voucherify groups points into "expiration buckets." The "earn creates a bucket, spend drains oldest-first" model is now standard. - **Double-entry bookkeeping.** "Reverse with an entry, never delete" is centuries old. Treat points as a liability and a points system *is* an accounting system. So the mechanism is a convergent recombination of established patterns — which is exactly why it's robust. If there's anything publishable here, it's not the mechanism but the *synthesis*: a minimal three-table formalization, the `granted − consumed − expired − balance = 0` invariant, a concurrency-correctness argument, and 15 years of zero-inconsistency operation as evidence. ## Original post This is the original post written in Japanese. {% embed https://blog.teraren.com/posts/point-system-design-rdb/ %}