Numbers — int, bigint, and the numeric Rule

Postgres offers three families of number and they are not interchangeable. Fixed-width integers cost one CPU instruction per operation and compare exactly. real and double precision are IEEE 754 binary floating point: compact, fast, and incapable of representing 0.1. numeric is an exact decimal implemented in software, correct to the last cent at any scale, and roughly an order of magnitude slower than either of the other two.

Nadia's audit opens on \d+ products, and the first line is already wrong: price is a double precision inherited from the prototype, while orders.total two tables over is numeric(10,2). The same quantity is modelled two different ways in one schema. Money in a float is the bug most engineers have met at least once. A counter in numeric is the one that never gets reported, because it never produces a wrong answer — only a slow one.

The Integer Family

Each integer width is a fixed number of bytes with a fixed range, and there are no unsigned variants. Arithmetic on them is native machine arithmetic, comparison is byte comparison, and an index on one is as cheap as an index gets. What Postgres refuses to do is wrap around.

smallint   2 bytes   -32768 .. 32767
integer    4 bytes   -2147483648 .. 2147483647
bigint     8 bytes   -9223372036854775808 .. 9223372036854775807

SELECT 2147483647::integer + 1;
ERROR:  integer out of range

C would have handed back -2147483648 and moved on. Postgres raises an error and aborts the statement, which is the behaviour you want and also the behaviour that makes a full integer key column an outage rather than a data-quality incident. Nothing is corrupted. Inserts stop, at whatever hour the 2,147,483,648th value is requested.

Floating Point, and Why Money Is Not a Float

real is 4 bytes with roughly 6 decimal digits of precision; double precision is 8 bytes with roughly 15. Both store a binary fraction, and 0.1 has no finite binary expansion any more than one third has a finite decimal one. The stored value is the nearest representable neighbour, and the error is tiny, deterministic, and cumulative.

SELECT 0.1::float8 + 0.2::float8;             -- 0.30000000000000004
SELECT 0.1::float8 + 0.2::float8 = 0.3;       -- false
SELECT 0.1::numeric + 0.2::numeric = 0.3;     -- true

Floating-point addition is not associative, so the sum of a Saturday's Cartwheel order totals depends on the order the planner happened to read the rows in. Change the plan — add an index, run the same report on pg-replica-a, let a parallel worker split the scan — and the total moves by a few cents. Both answers are equally defensible and neither is reproducible. That is what an auditor means when they say the books do not reconcile. No constraint catches it and no error is raised.

The second failure is narrower and harder to find. Floats were designed for measurements — a route distance, a parcel weight, a sensor reading — where a relative error of one part in ten quadrillion sits far below the accuracy of the instrument that produced the number. Equality is the other trap: on a float it is a coin toss, so the row is physically present, the predicate misses it, and the bug reproduces only for the values whose binary expansion happens not to be exact.

numeric, Exact and Expensive

numeric stores decimal digits in groups of four, at two bytes per group plus three to eight bytes of overhead, and does its arithmetic in software rather than in the FPU. The result is exact wherever exactness is defined, and the price is in the documentation: calculations on numeric values are very slow compared to the integer types or the floating-point types. It also rounds ties away from zero, where double precision on most machines rounds ties to even.

CREATE TEMP TABLE t (loose numeric, tight numeric(10,2));
INSERT INTO t VALUES (0.005, 0.005);

SELECT loose, tight FROM t;
 loose | tight
-------+-------
 0.005 |  0.01

An unconstrained numeric accepts half a cent and keeps it. The rounding rule then lives in whichever service reads the column next, and every language rounds differently — Python's round() is banker's rounding, most financial code is not. Declaring numeric(10,2) puts the rule in one place, in the schema, where every reader inherits it. The declared precision costs nothing when unused: values are stored without leading or trailing zeros, so 12.50 in a numeric(10,2) occupies the same handful of bytes it would anywhere else.

The cost never shows up on a single-row read. It shows up in aggregates, where software decimal arithmetic runs roughly an order of magnitude behind the single instruction a bigint gets. That trade is obviously right for products.price and orders.total, and obviously wrong for a counter incremented a million times a minute or for anything on delivery_events that gets summed across 1.2 billion rows. Nadia's first change to the schema is to bring price into line with total as a numeric(10,2).

The Cents-as-Integer Alternative

Storing money as a bigint count of cents is exact and as fast as arithmetic gets: eight fixed bytes, one instruction, index-friendly. The bill arrives everywhere else. Every query, every report, every ORM mapping and every hand-written CSV export has to remember the scale, and the day Cartwheel sells into a currency with no minor unit or with three of them, the constant 100 stops being a constant. It is the right call for a ledger summing hundreds of millions of rows and the wrong call for a schema whose main audience is people reconciling it. Cartwheel keeps numeric(10,2).

Choosing the Width Before It Chooses You

The audit's second finding is the one with a deadline attached. orders.id is an integer, and the only honest way to ask how much room is left is to ask the sequence.

SELECT last_value,
       2147483647 - last_value                        AS remaining,
       round(100.0 * last_value / 2147483647, 1)      AS pct_used
  FROM orders_id_seq;

 last_value  | remaining | pct_used
-------------+-----------+----------
  1352914698 | 794568949 |     63.0

There are 40 million rows in orders and the sequence has issued 1.35 billion values, because a sequence counts allocations rather than rows and never takes a number back. "We only have 40 million orders" is not an answer to "how much of the id space is left". Where the other 1.31 billion went is the topic on identity and sequences.

The last twelve months consumed 210 million values, so the remaining 794 million run out in a little under four years. That is not a soft limit and there is no degraded mode: the first request for 2,147,483,648 raises integer out of range, and checkout stops taking orders. Chapter 3 ships the widening on a live table without an outage. The plain ALTER TABLE … TYPE bigint does not: it rewrites all 40 million rows and every index that references them, holding an ACCESS EXCLUSIVE lock for the whole of it — reads included, not just writes.

The money Type

Postgres does have a money type. It is 8 bytes, its fractional precision is decided by the database's lc_monetary setting, and with two fractional digits that gives a range from -92,233,720,368,547,758.08 to +92,233,720,368,547,758.07. The documentation warns that money data may not load into a database whose lc_monetary differs from the one it was dumped from — a strange property for a column that is supposed to survive a restore. The cast to numeric is lossless and runs as a single ALTER TABLE.