Yuki Matsukura

Posted on Jun 12 • Originally published at blog.teraren.com

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

#database #postgres #sql #systemdesign

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.

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.

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

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

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:

Each withdraw equals the sum of its allocations. (Aggregate per withdraw first — naively joining double-counts when a spend splits across grants.)
No grant is over-consumed (deposit.amount >= Σ allocations).
BS reconciles with P&L via the accounting identity:

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:

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:

Single-node write throughput — bounded by WAL fsync, tens of thousands of TPS on a real server. Only a hundreds-of-millions/day concern.
Full-scan batch time — linear; solved by incremental checks and snapshots.
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.