Partitioning and historical snapshots
Bound the hot working set. Snapshot at period close. Archive the rest.
By Solomon Ajayi · Free to read, no signup
Ten years of entries. Billions of rows. Even with cached wallet balances, queries that need history (statements, audits, fraud investigations) hit cold storage and crawl. The architectural fix is two-layered: (1) PARTITION journal_entry by month (or quarter) so most queries scan only recent partitions, and (2) SNAPSHOT balances at every period close (Lesson 15) so any historical balance query becomes 'find the snapshot + apply deltas since.' The ledger NEVER grows unboundedly hot, each closed period becomes a fixed-cost lookup. This lesson uses three entries to simulate activity across periods; the teaching is in the architecture.
An immutable ledger only grows, so left alone it eventually becomes a table with billions of rows that every history query has to wade through. The cached live balance from Lesson 38 saves the 'what is it right now' question, but statements, audits, and fraud investigations all ask 'what was it back then,' and that still drags you across years of entries. You need the ledger to stop getting hotter as it gets bigger.
Two structural moves do it. First, partition journal_entry by posting month, so a query about February only touches February's partition and old partitions can be shipped off to cheap cold storage. Second, write a balance snapshot for every account at each period close, so any historical balance becomes the nearest snapshot plus the deltas posted since it. A balance-as-of query at ten years stops being a scan of 3,650 days and becomes one row lookup plus a few weeks of recent lines.
These two layers compose with the cache from Lesson 38: live balances are O(1) reads, historical balances are snapshot plus a short delta scan, and the raw entries are still all there, untouched and immutable, for the rare full replay. The snapshot table grows by the number of accounts per period, a rounding error next to the entry count, and the journal_entry partitions are individually small enough to vacuum, index, and archive on their own.
Worked example, step by step
January: deposit ₦10,000
Entry posted in January. In production, this would land in the journal_entry partition for January.
| Account | Debit | Credit |
|---|---|---|
| FBO at Sponsor Bank (1300) | ₦10,000.00 | |
| User Wallet (2000) | ₦10,000.00 |
FBO UP ₦10,000. User Wallet UP ₦10,000. The partition for January 2026 holds this entry. Queries that only need February+ data never touch the January partition.
Declarative range partitioning by posting month.
-- Parent table holds no rows of its own; partitions hold the data.
CREATE TABLE journal_entry (
id uuid NOT NULL DEFAULT gen_random_uuid(),
description text NOT NULL,
posted_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (id, posted_at) -- partition key must be in PK
) PARTITION BY RANGE (posted_at);
CREATE TABLE journal_entry_2026_01
PARTITION OF journal_entry
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE journal_entry_2026_02
PARTITION OF journal_entry
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- Recent partitions stay on fast disk; old ones move to cheap storage:
ALTER TABLE journal_entry_2022_01 SET TABLESPACE cold_archive;
-- A cron creates next month's partition ahead of time.
SELECT cron.schedule('create-next-partition', '0 0 25 * *', $$
-- function that issues the next CREATE TABLE ... PARTITION OF ...
$$);Period close: snapshot balances at end of January
Production: at month-end, a batch job writes a 'balance_snapshot' row for every account, capturing the running balance as of the close. After this, any 'balance at end of Jan' query becomes one row lookup, never a full scan of January's entries.
| Account | Debit | Credit |
|---|---|---|
| User Wallet (2000) | ₦0.01 | |
| FBO at Sponsor Bank (1300) | ₦0.01 |
This step doesn't post a new entry, it's a snapshot. For lesson purposes, we add a small marker entry so the lesson progresses. In production this would be a batch job: INSERT INTO balance_snapshot SELECT account_id, period_end, computed_balance FROM ledger_account ..., one row per account per closed period. The snapshot table grows by N (accounts) per period, while the journal_entry table grows by entry-count.
Period close: one snapshot row per account, forever queryable.
CREATE TABLE balance_snapshot (
account_code text NOT NULL,
period_end date NOT NULL,
balance bigint NOT NULL,
taken_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (account_code, period_end)
);
-- Month-end job. Runs once per closed period; never rewrites prior rows.
INSERT INTO balance_snapshot (account_code, period_end, balance)
SELECT account_code,
date '2026-01-31',
SUM(credit) - SUM(debit) AS balance
FROM journal_line jl
JOIN journal_entry je ON je.id = jl.entry_id
WHERE je.posted_at < date '2026-02-01'
GROUP BY account_code
ON CONFLICT (account_code, period_end) DO NOTHING;
-- Balance-at-any-historical-instant query becomes:
-- latest snapshot before T + SUM(deltas since that snapshot up to T)
SELECT (s.balance
+ COALESCE((
SELECT SUM(jl.credit) - SUM(jl.debit)
FROM journal_line jl
JOIN journal_entry je ON je.id = jl.entry_id
WHERE jl.account_code = s.account_code
AND je.posted_at > s.period_end
AND je.posted_at <= $1 -- the "as of" instant
), 0)) AS balance_as_of
FROM balance_snapshot s
WHERE s.account_code = $2
AND s.period_end <= $1::date
ORDER BY s.period_end DESC
LIMIT 1;February: service charge ₦500
New month. Entry lands in February's partition. To compute the current wallet balance, production uses: last snapshot (₦10,000 at end of Jan) + sum of deltas since (₦500 debit) = ₦9,500. No need to scan January.
| Account | Debit | Credit |
|---|---|---|
| User Wallet (2000) | ₦500.00 | |
| Fee Revenue (4400) | ₦500.00 |
User Wallet DOWN ₦500. Fee Revenue UP ₦500. Switch to Statement view, full ledger replay still works and shows the same running balance (₦9,500). But the SCALE-AWARE query never replays from beginning of time. It loads the latest snapshot and adds the deltas since. At 10 years of entries, that's the difference between scanning 1 row + 30 days of entries vs scanning 3,650 days of entries.
Takeaway
At scale, you don't compute balances from beginning of time. You PARTITION journal_entry by month so each partition is bounded in size and cold partitions can move to cheaper storage. You SNAPSHOT balances at every period close so any historical query becomes 'snapshot + deltas since.' The hot working set stays small forever, regardless of how many years of history you accumulate. Pair this with the cache-as-projection pattern (Lesson 38) and the live wallet balance is also O(1) reads. Five-year-old fintechs with billions of entries can still answer 'what's this user's balance' in single-digit milliseconds, but only if they architected the snapshots in from year one.
The code behind it
Reference solution
CREATE TABLE journal_entry (
id uuid NOT NULL DEFAULT gen_random_uuid(),
description text NOT NULL,
posted_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (id, posted_at)
) PARTITION BY RANGE (posted_at);
CREATE TABLE journal_entry_2026_01
PARTITION OF journal_entry
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE journal_entry_2026_02
PARTITION OF journal_entry
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- The fix: create March's partition with the same half-open range shape.
-- In production the cron that was meant to do this needs its window
-- widened to cover N months ahead, not just next month.
CREATE TABLE journal_entry_2026_03
PARTITION OF journal_entry
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');Practice this on a real ledger
Reading is half of it. Open this lesson in the lab to post the entries yourself against a real Postgres-backed double-entry ledger, with the validation on. Free, your sandbox is yours.