Why is Postgres's numeric Type So Slow?
pgEdge digs into Postgres numeric internals to explain why aggregates over numeric are much slower than over integer or float types, and what could make them faster.
Extract
The numeric type has existed in Postgres for more than 25 years. Over that time, it has undergone many optimisations and refinements. And yet operations on this type - aggregation in particular - still look rather inefficient.Since the PostgreSQL community always fixes problems with more or less simple and obvious solutions, let's find out whether numeric has structural limitations that prevent the DBMS operators for this type from working faster.To make the investigation more visual, let's also trace how DuckDB solves the same tasks - luckily, AI agents have made source-code analysis and testing much easier.One thing to keep in mind here: DuckDB is built exclusively for OLAP queries. That means, as I noted earlier, it places weaker demands on the exactness of operations - and, as you will see below, the DuckDB developer team actively uses this to make query execution more efficient.ContentsThe teaserFour decisions Postgres pays forHow an exact decimal type is built insideThe numeric implementation in PostgreSQLThe DECIMAL implementation in DuckDBPass by value or by referenceTuple deformingThe scale of an intermediate resultManaging the precision of intermediate results in numericManaging the precision of intermediate results in decimalThe cost of being decimalWhere we are goingThe TeaserTo avoid making empty claims, let me draw your attention to the two EXPLAINs below. The first one groups the rows of a table by a set of 12 numeric columns.Finalize HashAggregate (actual time=4320 rows=591894 loops=1) Group Key: _q_000_f_010rref, _q_000_f_008_rrref, ... Batches: 1 Memory Usage: 1835033kB Buffers: shared hit=111632 -> Gather (actual time=1441 rows=1582591 loops=1) Buffers: shared hit=111632 -> Partial HashAggregate (actual time=868 rows=263765.17 loops=6) Group Key: _q_000_f_010rref, _q_000_f_008_rrref, ... Batches: 1 Memory Usage: 704537kB -> Parallel Seq Scan on tt4 t2 (actual time=68 rows=500000.00 loops=6) Execution Time: 4512.880 msThe second is exactly the same query, but the columns have type double precision - for the sake of the test, we have neglected exactness.Finalize HashAggregate (actual time=1553 rows=591894 loops=1) Group Key: _q_000_f_008_type, _q_000_f_008_rtref, ... Batches: 1 Memory Usage: 196641kB Buffers: shared hit=125000 -> Gather (actual time=596 rows=1577001 loops=1) Buffers: shared hit=125000 -> Partial HashAggregate (actual time=337 rows=262833 loops=6) Group Key: _q_000_f_008_type, _q_000_f_008_rtref, ... …
Explained
What happened
A pgEdge post measures why numeric in PostgreSQL is slow and traces each cost to a design decision, using DuckDB as the contrast. The teaser: a GROUP BY over 12 numeric columns took 4.5 s and 1.8 GB of hash memory. The same query over double precision took 1.6 s and 197 MB, so roughly 3x faster with 9x less memory.
Three earlier attempts at a fast decimal extension (pgDecimal, pgdecimal2, fixeddecimal) made the arithmetic faster but stalled. So the problem is not only arithmetic.
Why it works
PostgreSQL made four deliberate choices, and each has a price:
- Width belongs to the type, not the column. A
numericvalue is variable-length: a header, sign, weight,dscaleand base-10000 digit groups. So it is always avarlena. - Always pass-by-reference. Values travel through the executor as an 8-byte
Datum.bigintfits in a register.numericdoes not, so everya + bmeans align scales, add, check limits,pallocthe result and write it to memory. - Expensive tuple deforming. Postgres caches column offsets only up to the first variable-width column. After a
numericcolumn, finding column 20 means reading the header of columns 1 to 19, on every row. - Scale computed from the data. Division picks a scale to keep at least 16 significant digits, so intermediate results grow, and every operation needs a rounding policy.
On top of that, short values are stored "packed" with a 1-byte header and must be unpacked (allocation plus copy) before every operation.
DuckDB does the opposite: DECIMAL(15,2) holding 123456.00 is just the integer 12345600, with the scale in the column type. Width picks the carrier: INT16, INT32, INT64 or INT128. Result: SUM over DECIMAL(18,2) runs in 8.7 ms vs 8.5 ms for BIGINT. At DECIMAL(19,2) the same data takes ~880 ms, a 100x cliff, because the value no longer fits in 64 bits.
The bigger pattern
You pay for width and storage format, not for decimal logic. Fixed-width, pass-by-value types let the CPU work in registers. Self-describing variable-length values buy flexibility (no runtime overflow, NaN/Infinity, preserved display scale) with allocation and pointer chasing. PostgreSQL 18 and 19 tuple-deforming work (smaller CompactAttribute, up to 44% faster) shows core is attacking the deform cost, but the root cause stays.
What it means in practice
- In schemas that store money or points, money as
bigintminor units (cents, points) is often the fastest exact choice for hot aggregates. Keepnumericfor rates and values that need it. - Put fixed-width columns before
numeric/textcolumns in wide, scan-heavy tables. Deforming gets cheaper. - For analytics over large ledgers, push aggregation to a columnar engine (DuckDB, ClickHouse) with
DECIMAL(p<=18), staying under the 18-digit boundary. - Don't "fix" it with
float: you lose exactness, and the article shows integers beat it anyway.