Same rows, different SUM | boringSQL
Table of Contents
The schema
Floating point does not associate
Why parallelism surfaces it
It was never really parallelism
Deterministic is not the same as correct
Where it actually bites
Postgres already knows this is dangerous
What to do about it
Everyone knows not to store money as a double precision. One can hope. The rule is so well drilled that it has stopped being interesting, and it is also not where the trouble usually starts. The float is already in the schema before anyone weighs in on it: a measurement column someone later sums for a report, telemetry that drifts into a finance dashboard, a third-party feed ingested as double precision because that is how it arrived.
Here is the part the rule does not warn you about. Take a table of five million floating-point readings, sum the column, and run it three times in a row. Nothing else touches the table. Same connection, same data, same statement.
SELECT sum(reading) FROM measurements; sum<br>2500519211.7874823
sum<br>2500519211.787477
sum<br>2500519211.7874575<br>Three runs, three different totals. No UPDATE, no concurrent writer, no random seed. The rows did not change between runs, and the query is the same character for character. Yet the answer is not.
This is not a Postgres bug, and it is not specific to Postgres. It is what happens when floating-point arithmetic meets parallel aggregation, and it has been generating "my dashboard total changed and I have no idea why" tickets for as long as databases have parallelized. The non-determinism does not wait for you to opt into bad practice; it shows up the moment a parallel plan runs over whatever floats you happen to have.
The schema
One column of double precision, five million rows.
CREATE TABLE measurements (<br>id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,<br>reading double precision NOT NULL<br>);
INSERT INTO measurements (reading)<br>SELECT random() * 1000<br>FROM generate_series(1, 5000000);
ANALYZE measurements;<br>Five million rows is enough that Postgres parallelizes the sum on its own. No settings forced, defaults all the way:
EXPLAIN (COSTS OFF) SELECT sum(reading) FROM measurements; QUERY PLAN<br>Finalize Aggregate<br>-> Gather<br>Workers Planned: 2<br>-> Partial Aggregate<br>-> Parallel Seq Scan on measurements<br>Two workers each sum part of the table in parallel, and a final step folds the partial sums together. Everything that follows is in that split.
Floating point does not associate
On paper, (a + b) + c and a + (b + c) are the same number. Postgres is not doing arithmetic on paper. Floating-point addition is not associative: change the grouping and you can change the answer, because every intermediate result is rounded to fit 64 bits. Move the parentheses and you move which roundings happen.
The textbook demonstration takes three values and one subtraction:
SELECT 1.0e20::float8 + 1.0::float8 - 1.0e20::float8; ?column?<br>The answer should be 1. Read the expression left to right and watch where it goes wrong:
1.0e20 + 1.0 should be 100000000000000000001. But a double only carries about 16 significant digits, and that number needs 21. The trailing 1 falls off the end, below the smallest digit the number can represent. So the addition rounds back to exactly 1.0e20.
1.0e20 - 1.0e20 is then exactly 0.
The 1 never made it past the first step. It was swallowed by the addition, before the subtraction even ran. This is absorption: a value too small relative to its neighbour to leave a mark. It is not a database phenomenon. The same expression evaluates to 0 in C, Python, JavaScript, and every other language that uses IEEE 754 doubles.
A double precision number keeps 52 bits for the mantissa, the digits that carry precision. This is around 15 to 17 decimal digits, no more. When the running total becomes big and the next number is small, the small one does not fit into those bits, so the rounding throws its tail away. Sum five million numbers and the total is, for most of its life, much bigger than any single number you add to it.
So the sum of a column depends on the order in which the rows are added. Not on which rows, on the order. That is the seam parallel query pries open.
Why parallelism surfaces it
The plan spells out exactly how. Parallel Seq Scan does not split the table into fixed slices upfront. It hands every worker a shared pointer into the same block iterator, and each worker grabs the next block, processes it, and comes back for another. Each worker runs its own Partial Aggregate, summing only the rows it happened to grab, and by default the leader process pitches in as a third one (parallel_leader_participation). Gather collects those partial sums in whatever order they arrive, and Finalize Aggregate folds them into a single total.
Two things vary from run to run, and neither is under your control:
Which rows each process sees. Because the blocks are pulled from a shared iterator on demand, the split is a race. A worker that gets a few...