Choosing types: the $0.07 that broke a finance team

The $0.07 that broke a finance team

A fintech I knew stored transaction amounts in double precision because someone in 2017 thought "it's a number, double is fine".

Four years later, a routine reconciliation between the warehouse and the books was off by $0.07 on a daily $40M revenue. Seven cents. Across 12 million transactions. Finance refused to sign off the quarterly report until the discrepancy was understood.

It took two engineers a week to find the cause: 0.10 + 0.20 in double is 0.30000000000000004. Sum a million such transactions and the error compounds to a few cents. The fix — migrate every monetary column to numeric(12,2) — took three months because of all the downstream pipelines that had hardcoded the type.

The lesson: the type you pick on day one is the type your data has for the next decade. Choose deliberately.

Money: never float

float and double are binary — they cannot represent 0.10 exactly, only the nearest binary fraction. The error is small per row, catastrophic at scale.

Two correct choices:

• numeric(precision, scale) — arbitrary precision decimal. Slower than int math, but exact. numeric(12, 2) is enough for amounts up to ~$10B with cent precision.
• bigint cents — store the amount in the smallest unit ($1.99 → 199). Fast integer math, no precision loss. The convention every payment processor uses.

Which? bigint cents is simpler at the application layer (no Decimal.js) and faster in aggregations. numeric is more flexible if you have multi-currency with different scales (JPY has 0 decimals, BHD has 3).

Time: always timestamptz

timestamp without time zone is a label, not a moment. Two servers in different regions interpret it differently. Daylight savings rolls over and now 2025-11-02 01:30 happens twice in the US east coast. You will, eventually, have a bug whose blast radius is all your historical data is now wrong by an hour, twice a year.

timestamptz is the answer. It stores UTC internally and converts on read using the session's TIME ZONE. Both INSERT '2025-11-02 01:30 America/New_York' and SELECT created_at AT TIME ZONE 'UTC' are explicit. No ambiguity.

The one exception: a naked date or a clock time without date — birthdays, opening hours. Those are date and time, deliberately without zone.

Identity keys: bigint vs UUIDv4 vs UUIDv7

Three real options for primary keys:

• bigint generated by default as identity — sequential, cache-friendly, easy to read in logs. The right default for OLTP tables. The downside: not unique across systems, exposes sign-up order.
• UUIDv4 — globally unique, opaque, random. Random insertion thrashes B-tree leaves: every insert hits a different page, the page cache evicts everything, write throughput tanks. Avoid as a PK on hot OLTP tables.
• UUIDv7 (RFC 9562, 2024) — globally unique, time-ordered. Fixes UUIDv4's locality problem: a v7 generated now sorts after one generated a second ago, so inserts append to the rightmost B-tree leaf like a bigint. The right choice when you need cross-service uniqueness.

One convention worth knowing: store the user-facing ID separately from the storage ID. id bigint PRIMARY KEY for joins; slug text UNIQUE for URLs. Don't put your sequential primary key in a public URL (/orders/12345 lets anyone count your orders).