PostgreSQL Deep Dive: numeric vs float8 - precision, performance, and gotchas

You have a price column. It holds monetary amounts. Should it be numeric or float8? The PostgreSQL documentation says “use numeric for money,” and every blog post parrots that advice. How much slower is numeric arithmetic in practice, and what actually breaks when you use float8 instead?

The answer involves IEEE 754 bit patterns, a base-10,000 internal encoding, and a surprising amount of invisible rounding.

How float8 stores values (and why 0.1 + 0.2 is not 0.3)

double precision (float8) stores values as IEEE 754 double-precision binary floating point: 1 sign bit, 11 exponent bits, and 52 mantissa bits packed into 8 bytes. This gives roughly 15-17 significant decimal digits of precision.

The mantissa represents a value as a sum of negative powers of two, which is where the trouble starts. The number 0.5 is 2^-1, so it stores exactly. So does 0.25, 0.125, and any fraction that’s a power of two. But 0.1 in binary is 0.0001100110011..., a repeating expansion that never terminates. The 52-bit mantissa truncates this, storing an approximation:

SELECT 0.1::float8;
-- 0.1

SELECT 0.1::float8 = 0.1::numeric;
-- f

SELECT round(0.1::float8::numeric, 17);
-- 0.10000000000000001

PostgreSQL displays 0.1 because its default output format (extra_float_digits = 1) rounds to the shortest decimal representation that round-trips back to the same float8 value. The actual stored value is 0.1000000000000000055511151231257827021181583404541015625, but PostgreSQL has no reason to show you all of that.

This is why 0.1::float8 + 0.2::float8 does not equal 0.3:

SELECT 0.1::float8 + 0.2::float8 = 0.3::float8;
-- f

SELECT (0.1::float8 + 0.2::float8)::numeric;
-- 0.30000000000000004

Both 0.1 and 0.2 are inexact approximations. Their errors compound during addition, producing a result that differs from the (also inexact) approximation of 0.3 by about 5.5e-17. The error is tiny, but an equality check fails.

This is not a PostgreSQL bug. Every system using IEEE 754 doubles behaves this way, including Java, Python, JavaScript, C, and Rust. PostgreSQL is just honest about it when you look closely.

How numeric stores values: base-10,000 digits

numeric (and its alias decimal) uses a fundamentally different representation. Instead of binary, it encodes values as decimal digits using a base-10,000 (NBASE) scheme. Each base-10,000 “digit” is stored as a 16-bit integer, representing values 0-9999.

Internally, a numeric value is a varlena structure: a variable-length datum with a header containing the total number of digits, the weight (position of the first digit relative to the decimal point), the display scale, and a sign. The digit array follows, packed as int16 values.

For 123.45:

• 3 base-10,000 digits: [1, 2345, 0] (conceptually 0001 | 2345 | 0000)
• Weight: 0 (one digit before the decimal point grouping)
• Scale: 2 (two decimal places)

The NUMERIC_POS or NUMERIC_NEG flag in the header handles sign, and there are also special-case flags for NaN, Infinity, and -Infinity (which PostgreSQL allows in numeric columns despite their exact nature).

This means numeric can represent any decimal value exactly, limited only by the declared precision (up to 131,072 digits before the decimal and 16,383 after). When you store 0.1 in a numeric column, the value is exactly 0.1. No approximation, no rounding error.

SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric;
-- t

The trade-off is storage and computation cost. A float8 is always exactly 8 bytes. A numeric value’s size depends on the number of digits:

SELECT pg_column_size(1::float8);            -- 8
SELECT pg_column_size(1::numeric);           -- 8  (minimum varlena, ~4 digits)
SELECT pg_column_size(1234567890::numeric);  -- 12
SELECT pg_column_size(123456789012345678901234567890::numeric);  -- 24
SELECT pg_column_size('1234567890.1234567890'::numeric);        -- 16

Each base-10,000 digit costs 2 bytes, plus the header overhead. A numeric(20,2) value like 9999999999999999.99 takes about 12 bytes, 50% larger than float8, but manageable. A numeric column with no precision constraint can store arbitrarily large values, consuming unbounded space.

The performance gap: how much does exactness cost?

Arithmetic on float8 maps directly to CPU floating-point instructions. A double addition on x86-64 is a single addsd instruction, typically completing in 3-4 clock cycles. Multiplication and division are similarly fast, and modern CPUs have vectorized SIMD floating-point units that can process multiple doubles simultaneously.

numeric arithmetic is implemented entirely in software. Adding two numeric values requires aligning the digit arrays by weight, performing the addition with carry propagation across the base-10,000 digits, and normalizing the result. There are no CPU instructions for base-10,000 arithmetic. It’s all C code iterating over arrays.

Here’s a benchmark aggregating one million rows:

CREATE UNLOGGED TABLE bench_float8 (val float8);
CREATE UNLOGGED TABLE bench_numeric (val numeric(16,2));

INSERT INTO bench_float8
SELECT (random() * 10000)::float8 FROM generate_series(1, 1000000);

INSERT INTO bench_numeric
SELECT round((random() * 10000)::numeric, 2) FROM generate_series(1, 1000000);

-- float8 aggregation
EXPLAIN ANALYZE SELECT sum(val), avg(val) FROM bench_float8;
-- Aggregate  (cost=15430.00..15430.01 rows=1 width=32) (actual time=45.2..45.2 rows=1 loops=1)

-- numeric aggregation
EXPLAIN ANALYZE SELECT sum(val), avg(val) FROM bench_numeric;
-- Aggregate  (cost=15430.00..15430.01 rows=1 width=64) (actual time=320.1..320.1 rows=1 loops=1)

That 7x difference on aggregation is typical. The gap varies by operation:

Operation	float8	numeric (16,2)	Slowdown
SUM (1M rows)	~45ms	~320ms	~7x
AVG (1M rows)	~50ms	~380ms	~7.5x
SUM + GROUP BY (10 groups)	~80ms	~450ms	~5.5x
Multiplication (1M rows)	~40ms	~350ms	~8.5x
Comparison / ORDER BY	~120ms	~350ms	~3x

Sorting and comparison are the closest in performance because the byte-level comparison logic is simpler than full arithmetic. The worst cases are multiplication and division, where carry propagation across digit arrays dominates.

For OLTP workloads processing a few hundred or thousand rows at a time, this difference is invisible; we’re talking about microseconds versus tens of microseconds. For analytical queries scanning millions of rows with aggregations, the cost compounds and becomes meaningful.

Declaring precision: what NUMERIC(p,s) actually does

The syntax numeric(p, s) declares a maximum precision p and scale s. “Maximum” is the key word. The declared precision and scale do not pre-allocate storage or fix the number of digits. They act as constraints:

CREATE TABLE items (
    price numeric(10,2)
);

INSERT INTO items (price) VALUES (9.99);          -- stored as 9.99 (4 bytes)
INSERT INTO items (price) VALUES (9.9);           -- stored as 9.9, not 9.90
INSERT INTO items (price) VALUES (9.999);         -- rounded to 10.00
INSERT INTO items (price) VALUES (999999999.99);  -- OK, exactly 10 digits
INSERT INTO items (price) VALUES (1000000000);    -- ERROR: numeric field overflow

Values are rounded to the declared scale on input, and rejected if the total digit count exceeds precision. Internally, the stored representation is identical whether or not you declare precision. A bare numeric type and numeric(1000,2) use the same base-10,000 encoding. The constraint only affects input validation.

Starting in PostgreSQL 17, you can declare negative scales:

CREATE TABLE distances (
    altitude numeric(5, -2)  -- rounds to nearest hundred
);

INSERT INTO distances VALUES (1234);    -- stored as 1200
INSERT INTO distances VALUES (1251);    -- stored as 1300
INSERT INTO distances VALUES (99999);   -- ERROR: overflow
INSERT INTO distances VALUES (-99999);  -- ERROR: overflow

A negative scale -N rounds to the nearest 10^N. This is useful for approximations and statistical data where precise values aren’t needed but you want a consistent rounding behavior enforced at the database level.

The hidden cost of implicit casts

PostgreSQL allows mixing float8 and numeric in expressions, but the resolution rules aren’t always what you expect. When you write:

SELECT price * 1.1 FROM items;

If price is numeric, PostgreSQL promotes the literal 1.1 to numeric and performs a numeric multiplication. This is correct behavior, but it means a single float8 literal in a query can cause the entire expression to lose the speed advantage of binary floating point:

-- Both columns are float8, but the literal 0.2 is numeric in origin
EXPLAIN ANALYZE
SELECT float_col * 0.2 FROM large_table;

-- Check: does PG promote to numeric?
SELECT pg_typeof(0.2);  -- numeric

The literal 0.2 has type numeric by default (it’s a decimal literal, not a float literal). PostgreSQL casts the float8 column to numeric for the multiplication, producing a numeric result. To keep everything in float8, use the ::float8 cast or an explicit float literal:

-- Stays in float8
SELECT float_col * 0.2::float8 FROM large_table;

-- Also stays in float8 (E-notation produces float8 literal)
SELECT float_col * 2.0E-1 FROM large_table;

This is a subtle performance trap. A WHERE clause mixing types can force an expensive numeric comparison on every row:

-- If amount is float8, this forces a numeric cast on every row
SELECT * FROM transactions
WHERE amount > 1000000.50;  -- 1000000.50 is numeric

-- Correct: keep the comparison in float8
SELECT * FROM transactions
WHERE amount > 1000000.50::float8;

On a million-row table, this implicit cast can turn a 50ms scan into a 300ms scan.

Comparison gotchas: why you should never use = on float8

Equality comparison on float8 is unreliable for derived values:

SELECT 0.1::float8 + 0.2::float8 = 0.3::float8;
-- f

SELECT (1.0 / 3.0) * 3.0 = 1.0::float8;
-- f

SELECT 0.1::float8 * 10 = 1.0::float8;
-- t  (this one happens to work)

Whether equality holds depends on whether the cumulative rounding errors happen to cancel out. Sometimes they do, sometimes they don’t, and the rules are opaque. The standard workaround is an epsilon comparison:

SELECT abs(a - b) < 1e-10
FROM (SELECT 0.1::float8 + 0.2::float8 AS a, 0.3::float8 AS b) t;
-- t

But epsilon comparisons have their own problems. Choosing the right epsilon depends on the magnitude and number of operations that produced the values, and you can’t easily express this in a SQL WHERE clause.

For ORDER BY and range queries (>, <, BETWEEN), float8 is generally reliable. IEEE 754 guarantees that comparison results are consistent and total. You won’t get surprising ordering. The problem is specifically with exact equality.

numeric has none of these issues:

SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric;
-- t

SELECT (1::numeric / 3::numeric) * 3::numeric = 1::numeric;
-- f  (1/3 produces 0.333... with finite precision)

That last one returns false too. numeric division with no declared precision uses a default of 131,072 digits, but 1/3 still produces a truncated repeating decimal. The result of (1/3) * 3 is 0.999...9 with trailing 9s, not 1.000.... This is a precision truncation issue, not a floating-point representation issue. With declared precision:

SELECT (1.000::numeric / 3.000::numeric) * 3.000::numeric = 1.000::numeric;
-- t  (truncation after 3 decimal places gives 0.333 * 3 = 0.999, which rounds to 1.000)

This works because the declared scale limits both the intermediate result and the comparison precision. The rule: if you use numeric for money, always declare the scale.

The extra_float_digits setting and output precision

PostgreSQL’s extra_float_digits parameter controls how many significant digits are displayed in float8 output. The default is 1 (since PostgreSQL 12), which outputs the shortest decimal that uniquely identifies the stored binary value. This is the “right” setting for almost all use cases.

Setting extra_float_digits = 0 uses the older behavior of rounding to 15 significant digits. This can cause the output to not round-trip. A value printed with extra_float_digits = 0 might convert back to a different float8 when read:

SET extra_float_digits = 0;
SELECT '0.1'::float8::text::float8 = '0.1'::float8;
-- t (works for this case, but edge cases exist)

-- The problematic case: a value whose 15-digit and 17-digit
-- representations differ in meaning
SET extra_float_digits = 3;
SELECT '1.0000000000000002'::float8::text;  -- 1.0000000000000002
SET extra_float_digits = 0;
SELECT '1.0000000000000002'::float8::text;  -- 1 (loses the distinction)

For applications that need to read float values from PostgreSQL and write them back without loss (like logical replication or data exports), extra_float_digits = 1 or higher is required. PostgreSQL 12 changed the default from 0 to 1 specifically to fix data loss in pg_dump.

Aggregation divergence: SUM on float8 accumulates error

A common float8 problem in production is aggregate drift. SUM on float8 processes rows sequentially, and rounding errors accumulate:

CREATE UNLOGGED TABLE float_values (val float8);
-- Insert 1 million copies of 0.1
INSERT INTO float_values
SELECT 0.1::float8 FROM generate_series(1, 1000000);

SELECT sum(val) FROM float_values;
-- 100000.0000000131 (not 100000)

SELECT sum(val)::numeric - 100000;
-- 0.000000013103871593630

After a million additions of 0.1 (which is already slightly over 0.1 in binary), the error has grown to about 1.3e-8. For display purposes this is irrelevant, but for financial reconciliation or equality checks, it’s a trap.

numeric SUM is exact:

CREATE UNLOGGED TABLE numeric_values (val numeric(10,1));
INSERT INTO numeric_values
SELECT 0.1::numeric FROM generate_series(1, 1000000);

SELECT sum(val) FROM numeric_values;
-- 100000.0

But even with numeric, division introduces rounding:

SELECT sum(val) / count(val) FROM numeric_values;
-- 0.10000000000000000000  (exact, because 100000.0 / 1000000 = 0.1 exactly)

-- But with non-round values:
CREATE UNLOGGED TABLE thirds (val numeric(5,2));
INSERT INTO thirds SELECT 100.00 FROM generate_series(1, 1000000);
SELECT sum(val) / 3 FROM thirds;
-- 33333333.3333333333333333  (truncated at declared scale limit)

Practical SQL: diagnostics for your columns

Check which columns in your schema use float types:

SELECT table_schema,
       table_name,
       column_name,
       data_type,
       udt_name
FROM information_schema.columns
WHERE data_type IN ('real', 'double precision')
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name, ordinal_position;

Find float8 columns that might be holding monetary data (heuristic by name):

SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE data_type = 'double precision'
  AND (
    column_name ILIKE '%price%'
    OR column_name ILIKE '%amount%'
    OR column_name ILIKE '%cost%'
    OR column_name ILIKE '%fee%'
    OR column_name ILIKE '%balance%'
    OR column_name ILIKE '%total%'
    OR column_name ILIKE '%tax%'
    OR column_name ILIKE '%salary%'
    OR column_name ILIKE '%rate%'
    OR column_name ILIKE '%revenue%'
  )
  AND table_schema NOT IN ('pg_catalog', 'information_schema');

Check the actual storage size of your numeric columns:

SELECT attrelid::regclass AS table_name,
       attname AS column_name,
       pg_size_pretty(avg(pg_column_size(atttypid))) AS avg_size
FROM pg_attribute
WHERE atttypid = 'numeric'::regtype
  AND attnum > 0
  AND NOT attisdropped
GROUP BY attrelid, attname
ORDER BY avg(pg_column_size(atttypid)) DESC
LIMIT 20;

Test whether your float8 aggregations have drifted:

-- Compare float8 SUM to numeric SUM (runs a full scan twice)
SELECT float_sum, numeric_sum,
       float_sum::numeric - numeric_sum AS difference
FROM (
    SELECT sum(val::float8) AS float_sum,
           sum(val::numeric) AS numeric_sum
    FROM your_table
) t;

When float8 is the right choice

Despite all the warnings, float8 is the correct choice in many scenarios:

• Scientific and statistical data. Sensor readings, measurements, and computed values are inherently approximate. The precision of the measurement typically exceeds what float8 can represent anyway.
• Geometric and spatial calculations. PostGIS uses float8 internally for all coordinate operations. Storing latitude/longitude as numeric provides no benefit since the computations happen in float8 regardless.
• Performance-sensitive aggregations. Analytical dashboards computing averages and sums over millions of rows, where the 7x speed advantage matters and a 1e-15 relative error is acceptable.
• Machine learning features. Embeddings, weights, and scores are floating-point by nature.

Use numeric when:

• Financial amounts. The accounting team will notice 0.1 + 0.2 not equaling 0.3, and so will auditors.
• Quantities with fixed decimal places. Prices, quantities, rates, percentages that are entered and displayed with a specific number of decimal places.
• Exact comparison is required. Equality checks in WHERE clauses, UNION, DISTINCT, or GROUP BY where two values that should be identical must compare as identical.

Key takeaways

• float8 stores binary approximations. 0.1 + 0.2 = 0.3 is false. This is IEEE 754 behavior, not a bug.
• numeric stores exact decimal values using base-10,000 digit arrays. Arithmetic is correct but 5-8x slower than float8.
• Declared precision numeric(p,s) constrains input but doesn’t pre-allocate storage. A bare numeric can store arbitrarily large values.
• Decimal literals like 0.2 default to numeric type. Mixing them with float8 columns forces implicit casts and can silently degrade query performance.
• Never use = comparison on float8 derived values. Use epsilon comparison or avoid the pattern entirely.
• extra_float_digits = 1 (the default since PG 12) is required for lossless round-trip output.
• SUM on float8 accumulates rounding error. Over millions of rows, the drift can exceed 1e-8.
• The real question isn’t “float8 or numeric” but “can I tolerate approximate results.” If yes, float8 is faster. If no, numeric is exact.

What’s next

The numeric types are just the beginning. PostgreSQL’s type system has more traps hiding in plain sight. numeric vs float8 is the obvious one, but what about timestamp vs timestamptz, the text vs varchar non-distinction, or the serial identity footgun? Tomorrow we’ll look at timestamptz and why almost every timestamp column in your database should have a time zone, and what happens when it doesn’t.

---

Previously: The Lock Hierarchy: Table Locks, Row Locks, Page Locks, and What pg_locks Actually Shows