Vailati
Go back

Partitioning a ledger at two levels: account group and year

LIST by account group, RANGE by year, a composite primary key and one rule nobody may forget: every query carries both keys.

Context

I needed three tables of a points ledger to stay fast with billions of rows and millions of inserts a day, without rewriting the application. The decision was to partition each one at two levels, first by a group derived from the account id and then by year, and to accept the cost that comes with it: a composite primary key, two extra columns on every child row, a lookup table for searches by number, and a query discipline that the database does not enforce on its own.

The setting is a loyalty platform with tens of millions of accounts. The ledger_entry table stores every entry; credit and debit store the detail of each credit and each consumption, with a foreign key to the entry. All three grow at the same rate, and the most frequent queries are always “this account’s entries in a period” and “this account’s available balance”. Almost nobody asks anything that crosses accounts.

Problem

A single table with billions of rows in PostgreSQL has three pains that indexes do not solve.

The first is maintenance. VACUUM, ANALYZE and REINDEX on a terabyte-scale table take hours and compete for I/O with the application. XID anti-wraparound on a single table is a middle-of-the-night event nobody wants.

The second is reading. Even with an index per account, the account’s heap is scattered across the whole table. A twelve-month statement becomes hundreds of random page reads.

The third is lifecycle. Entries from old years are rarely read, but they keep taking space in the hot indexes and taking part in every maintenance pass. Without partitioning by time, there is no way to drop or archive a whole year with one command.

Partitioning only by date solves the third pain and helps with the first, but does not improve locality per account. Partitioning only by account improves locality and maintenance, but gives no lifecycle. I wanted both.

What I measured

Before choosing the design, the arithmetic was about partition size, not query time. The account id is a sequential integer. Grouping two million accounts per partition, the historical volume that already existed fit in ten groups, and the sign-up rate, measured from the daily series of ids, was a little over eight thousand accounts a day. That gives a new group every seven or eight months. With yearly partitions inside each group, no leaf exceeds a few tens of GB, which is the size at which VACUUM and REINDEX are still operations of minutes.

After partitioning, what I measured was the cost of forgetting the rule. In a batch job, a join between ledger_entry and credit used the entry id but not the partition columns. The planner probed all 70 leaves of credit on every call. Median 21 ms per query, peaks of 1 s, and about half a second of planning alone with a cold cache, because planning over 70 relations costs. The same query with both partition keys touches one leaf and drops to a few milliseconds.

I also measured what PostgreSQL does with foreign keys between partitioned tables, because the exported DDL is scary: one constraint shows up per pair of partitions, almost six hundred, and it is easy to read that as redundancy to be dropped. In the catalog, pg_constraint with conparentid = 0 showed four real foreign keys, all root to root. The other 588 were internal clones, children of the four, which PostgreSQL creates to implement the FK partition by partition. The cost of each INSERT is one check, not six hundred. And a DROP of the clones would fail, because an inherited constraint cannot be dropped on its own. The lesson went on record: DDL exported by a tool does not distinguish a declared constraint from an internal clone; conparentid does.

Solution

The first level is LIST by account_group, an integer derived from the account id. The second is RANGE by created_at, one partition per year. The years before the system went live ended up in a single partition.

CREATE TABLE ledger_entry (
    id             uuid        NOT NULL,
    account_id     integer     NOT NULL,
    account_group  integer     NOT NULL,
    created_at     timestamptz NOT NULL,
    -- other columns
    PRIMARY KEY (id, account_group, created_at)
) PARTITION BY LIST (account_group);

CREATE TABLE ledger_entry_g1 PARTITION OF ledger_entry
    FOR VALUES IN (1) PARTITION BY RANGE (created_at);

CREATE TABLE ledger_entry_g1_2020_2023 PARTITION OF ledger_entry_g1
    FOR VALUES FROM ('2020-01-01') TO ('2024-01-01');
CREATE TABLE ledger_entry_g1_2024 PARTITION OF ledger_entry_g1
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
-- … one per year, and the same for credit and debit

The group comes from a generated column on the accounts table, and is copied to every child row at write time:

ALTER TABLE account
    ADD COLUMN account_group integer
    GENERATED ALWAYS AS (((id - 1) / 2000000) + 1) STORED;

Five consequences of this design deserve an explanation.

The primary key is composite because PostgreSQL requires it. Every primary or unique key on a partitioned table must contain the partition columns, at both levels. So the key of ledger_entry is (id, account_group, created_at), and the same holds for credit and debit. The practical consequence is that the foreign keys between them are composite too, and the ORM has to map the three columns in every relationship.

Every query carries both keys. It is the most important rule and the one the database does not enforce. A WHERE account_id = $1 on its own uses the index inside each leaf, but visits every leaf. With account_group the planner prunes to one group; with created_at it prunes to the years of the period. An account statement looks like this:

SELECT …
FROM ledger_entry e
WHERE e.account_id = $1
  AND e.account_group = $2
  AND e.created_at BETWEEN $3 AND $4
ORDER BY e.created_at DESC
LIMIT $5 OFFSET $6;

The query’s account_group comes from the account row, which the application has already read to validate the caller. It costs nothing to carry. In the ORM, even an UPDATE by id carries the three columns in the WHERE, otherwise EF Core generates a statement the planner has to route through every leaf.

Searching by a business number requires a lookup table. Entries have a sequential business number, and external systems query by it, with no account and no date. That number is not a partition key and cannot be unique on the partitioned table without including the keys. The solution is a small, unpartitioned table:

CREATE TABLE ledger_lookup (
    entry_number  bigint      NOT NULL UNIQUE,
    created_at    timestamptz NOT NULL,
    account_id    integer     NOT NULL,
    account_group integer     NOT NULL
);

A search by number reads the lookup first, finds the group and the date, and only then goes to the right partition. The same table solves idempotency: it is inserted before the entry, in the same transaction, and a uniqueness violation is the signal that this number has already been processed.

Root statistics are your responsibility. Autovacuum vacuums and analyzes the leaves, but does not run ANALYZE on the partitioned parent table. Without statistics on the root, queries the planner needs to estimate at root level, such as joins between the three tables, get bad plans. A daily job runs ANALYZE on the three roots, during low traffic, with a generous timeout.

Foreign keys work, and the catalog lies in the export. Declare the four FKs between the roots and let PostgreSQL create the clones. Do not try to “optimize” by dropping what the export shows.

flowchart LR
    Q[Query with account_id,<br/>account_group and created_at] --> R[ledger_entry root]
    R -->|LIST account_group| G[ledger_entry_g7]
    G -->|RANGE created_at| Y[ledger_entry_g7_2026]
    Y --> I[index on account_id<br/>inside the leaf]
    Q2[Query with account_id only] --> R2[ledger_entry root]
    R2 -.->|Append over 70 leaves| X[every leaf]

What I would do differently

I would put the query discipline in one place. Today it is spread across every repository and every endpoint, and a comment in the project guide warns that forgetting the keys takes production down. It works because the team knows. I would prefer an integration test that runs EXPLAIN on the critical queries and fails if the plan has an Append over more than one leaf per group, plus a single filter helper that takes the account and the period and emits the three conditions. The database does not enforce the rule; a test can.

I would create partitions by automation from the start. The initial migration created the groups and the years up to a fixed horizon, and from there creation became a manual task with a script, a validation and a window. The risk of a missing year or group partition is total write unavailability. A job that always keeps the next year and the next group created, ahead of time, is small and takes the item off the list of things to remember.

I would have measured the partition-creation lock before I needed it. That became an article of its own.

References

Share this post:
Share this post on LinkedinShare this post on WhatsappShare this post on XShare this post via email
Previous Post
Creating new partitions without locking the root table
Next Post
Distributed tracing across the outbox and the in-memory queue