Balance Type Comparison

Date: 2026-03-04 21:28:14

Purpose: Compare point-in-time balance lookup performance across BIGINT, DOUBLE PRECISION, and NUMERIC(20,7) data types

Database

  • Source: external (BENCH_PG_DSN)
  • DSN: postgres://bench:***@localhost:15432/bench?sslmode=disable

SQL: Point-in-time balance lookup

SELECT value, pending_in, pending_out FROM balances_<type>
  WHERE account_id = $1 AND value_time <= $2
  ORDER BY value_time DESC LIMIT 1

Methods

  • Data types tested: BIGINT (int64), DOUBLE PRECISION (float64), NUMERIC(20,7) (arbitrary precision)
  • Schema: Each balance row has value, pending_in, pending_out columns
  • N: Rows per account (balance snapshots over time)
  • M: Number of accounts. Total rows = N * M
  • Data generation: Fixed random seed (42), one row per account per time step
  • Bulk load: pgx CopyFrom in 10K-row batches, ANALYZE after load
  • Warmup: None — first iteration included (captures cold-cache behaviour)
  • Timing: Per-iteration wall-clock, statistics over all iterations
  • Query: Point-in-time lookup — latest balance at or before a given timestamp for one account

Table DDL

One table per type per (N, M) scenario:

CREATE TABLE balances_<type>_<N>_<M> (
    id SERIAL PRIMARY KEY,
    account_id INT NOT NULL,
    value_time TIMESTAMP NOT NULL,
    value       <TYPE> NOT NULL,
    pending_in  <TYPE> NOT NULL,
    pending_out <TYPE> NOT NULL
);
CREATE INDEX ON balances_<type>_<N>_<M> (account_id, value_time DESC);

Results: Point-in-time lookup (N=10, M=10_000)

Type N M Mean P50 P99 Min Max Iters
bigint 10 10_000 130.3us 70.3us 1.72ms 59.2us 2.11ms 100
double 10 10_000 163.8us 144.1us 646.3us 77.7us 786.5us 100
numeric 10 10_000 246.6us 218.1us 849.3us 88.9us 1.56ms 100

Results: Point-in-time lookup (N=2, M=1_000_000)

Type N M Mean P50 P99 Min Max Iters
bigint 2 1_000_000 201.2us 132.5us 962.2us 87.6us 1.36ms 100
double 2 1_000_000 183.6us 121.2us 1.10ms 68.3us 1.21ms 100
numeric 2 1_000_000 277.4us 84.9us 1.30ms 64.3us 1.70ms 100

Results: Point-in-time lookup (N=30, M=1_000_000)

Type N M Mean P50 P99 Min Max Iters
bigint 30 1_000_000 216.4us 121.8us 1.16ms 59.1us 2.12ms 100
double 30 1_000_000 174.3us 128.4us 359.8us 100.7us 2.97ms 100
numeric 30 1_000_000 184.3us 140.8us 1.09ms 86.3us 1.29ms 100

Results: Point-in-time lookup (N=365, M=100_000)

Type N M Mean P50 P99 Min Max Iters
bigint 365 100_000 115.5us 83.2us 511.0us 58.6us 1.18ms 100
double 365 100_000 140.9us 124.7us 327.4us 57.8us 1.10ms 100
numeric 365 100_000 196.3us 163.2us 936.6us 102.2us 1.10ms 100

Purpose

This is a simulation of realtime balance lookup without caching. In a bank the most time critical element is movement in of money and then movement out of money. This is where this query starts to be realistic.

This started out testing summing three PostgreSQL data types (BIGINT, DOUBLE PRECISION, NUMERIC) for calculated balances or sums. However it is clear that sum is about 40-50% slower for NUMERIC and the other two are about the same speed.

This is a test of how much it slows down a calculation if you have live balances with only a single row or you have to query with a date. The idea is that there will be a balance with todays date and balance with tomorrows date. As the query rolls over midnight you will return different data due to future dated postings. I want a realistic test with up to a million accounts in the table.

Analysis

This shows that future date timing has marginal effect on accessing balance information for millions of accounts so it is practical from this point of view.

With the (account_id, value_time DESC) index, all three types deliver sub-millisecond point-in-time lookups even at 36.5M rows. The data type choice has negligible impact on indexed single-row retrieval.

Use BIGINT (integer cents/pence) for balance storage. It matches go-luca's existing int64 amount model, avoids floating-point rounding (DOUBLE), and has no NUMERIC overhead. Reserve NUMERIC for external reporting views where decimal display formatting is needed.

AI Summary

All three data types (BIGINT, DOUBLE PRECISION, NUMERIC) perform equivalently for indexed point-in-time balance lookups across all tested scales (100K to 36.5M rows). P50 latencies remain 100-220us regardless of type or table size, confirming that the B-tree index dominates query cost — the arithmetic type is irrelevant for single-row retrieval.

BIGINT is recommended: zero precision loss, smallest storage footprint, and native alignment with go-luca's int64 amount representation.