Opensource internals

PostgreSQL's numeric Implementation in C

10 min read Jay Hubbert

numeric is not IEEE floating point. It is a sign-magnitude array of base-10000 digits with an explicit weight, two different on-disk headers, and an in-memory form that keeps a spare digit so carries never trigger a realloc.

numeric is PostgreSQL’s exact decimal type: arbitrary precision, no rounding surprises, no binary-vs-decimal conversion error. It is also the type where the storage format and the arithmetic format are deliberately different, and the difference is the whole design.

The short version: numeric stores an array of digits in base 10000, with a separate sign and a “weight” that says where the decimal point goes. It comes in two on-disk header sizes. And the in-memory working form, NumericVar, keeps a spare leading digit so that a carry out of the top of the number never forces a repalloc.

This is a walk through src/backend/utils/adt/numeric.c and src/include/utils/numeric.h, based on reading the PostgreSQL source (version 20devel, commit e75fcc9867f).

The catalog entry

From src/include/catalog/pg_type.dat:

text 
{ oid => '1700', array_type_oid => '1231',
  descr => '\'numeric(precision, scale)\' arbitrary precision number',
  typname => 'numeric', typlen => '-1', typbyval => 'f', typcategory => 'N',
  typinput => 'numeric_in', typoutput => 'numeric_out',
  typreceive => 'numeric_recv', typsend => 'numeric_send',
  typmodin => 'numerictypmodin', typmodout => 'numerictypmodout',
  typalign => 'i', typstorage => 'm' },

Read against the float types, the differences are the point:

FieldValueMeaning
typlen-1varlena — variable length, 4-byte or 1-byte header
typbyvalfby reference; the Datum is a pointer
typaligni4-byte alignment
typstoragemTYPSTORAGE_MAIN: toastable, but prefer to keep inline
array oid1231_numeric
typmod I/Onumerictypmodin/outsupports numeric(p,s)

typstorage => 'm' is the interesting one. A numeric can be compressed and moved out of line, but the type asks the toaster to try harder to keep it in the main tuple than an 'x' (EXTENDED) column would — a reasonable choice for a type that is often small and whose exact value you usually want on the page.

On disk: two headers over base-10000 digits

The representation is introduced by a long comment at the top of numeric.c, which settles the base first:

c 
#if 1
#define NBASE		10000
#define HALF_NBASE	5000
#define DEC_DIGITS	4			/* decimal digits per NBASE digit */
#define MUL_GUARD_DIGITS	2	/* these are measured in NBASE digits */
#define DIV_GUARD_DIGITS	4

typedef int16 NumericDigit;
#endif

Base 10000 is not an accident: it is a power of ten, so decimal I/O and rounding are trivial, and it is comfortably below sqrt(INT_MAX), leaving headroom for mul_var/div_var to postpone carries. The comment notes that other bases (10, 100) exist only as historical #if 0 blocks and are no longer supported; the on-disk format is always base 10000.

The stored value is a sign-magnitude digit array with two possible headers:

c 
struct NumericShort
{
	uint16		n_header;		/* Sign + display scale + weight */
	NumericDigit n_data[FLEXIBLE_ARRAY_MEMBER]; /* Digits */
};

struct NumericLong
{
	uint16		n_sign_dscale;	/* Sign + display scale */
	int16		n_weight;		/* Weight of 1st digit	*/
	NumericDigit n_data[FLEXIBLE_ARRAY_MEMBER]; /* Digits */
};

union NumericChoice
{
	uint16		n_header;		/* Header word */
	struct NumericLong n_long;	/* Long form (4-byte header) */
	struct NumericShort n_short;	/* Short form (2-byte header) */
};

struct NumericData
{
	int32		vl_len_;		/* varlena header (do not touch directly!) */
	union NumericChoice choice; /* choice of format */
};

Which header you get is encoded in the top two bits of the first word:

c 
#define NUMERIC_SIGN_MASK	0xC000
#define NUMERIC_POS			0x0000
#define NUMERIC_NEG			0x4000
#define NUMERIC_SHORT		0x8000
#define NUMERIC_SPECIAL		0xC000

So 0xC000 means “special” (NaN or infinity), 0x8000 means the short form, and 0x0000/0x4000 mean the long form with positive/negative sign. Because both SHORT and SPECIAL have the high bit set, the code can test a single bit instead of comparing against each constant:

c 
#define NUMERIC_HEADER_IS_SHORT(n)	(((n)->choice.n_header & 0x8000) != 0)

The short header is the clever one. Its 16 bits are packed as 1 sign bit, 6 bits of display scale, and 7 bits of weight:

c 
#define NUMERIC_SHORT_SIGN_MASK			0x2000
#define NUMERIC_SHORT_DSCALE_MASK		0x1F80
#define NUMERIC_SHORT_DSCALE_SHIFT		7
#define NUMERIC_SHORT_WEIGHT_MASK		0x003F
#define NUMERIC_SHORT_WEIGHT_MAX		NUMERIC_SHORT_WEIGHT_MASK
#define NUMERIC_SHORT_WEIGHT_MIN		(-(NUMERIC_SHORT_WEIGHT_MASK+1))

The long header instead stores a full int16 weight and a 14-bit display scale (NUMERIC_DSCALE_MASK == 0x3FFF). The two header sizes are:

c 
#define NUMERIC_HDRSZ	(VARHDRSZ + sizeof(uint16) + sizeof(int16))  /* 8 */
#define NUMERIC_HDRSZ_SHORT (VARHDRSZ + sizeof(uint16))              /* 6 */

The number of digits is never stored — it is derived from the varlena length:

c 
#define NUMERIC_NDIGITS(num) \
	((VARSIZE(num) - NUMERIC_HEADER_SIZE(num)) / sizeof(NumericDigit))

Special values are distinguished by the two bits just below the NUMERIC_SPECIAL flag: 0xC000 is NaN, 0xD000 positive infinity, 0xF000 negative infinity (NUMERIC_NAN, NUMERIC_PINF, NUMERIC_NINF). In this tree they are always written with the 2-byte short header; the comment notes that 4-byte NaNs may still exist on disk after a pg_upgrade.

In memory: NumericVar

Arithmetic is not done on the packed form. It is done on NumericVar, which keeps the digit buffer separate from the metadata:

c 
typedef struct NumericVar
{
	int			ndigits;		/* # of digits in digits[] - can be 0! */
	int			weight;			/* weight of first digit */
	int			sign;			/* NUMERIC_POS, _NEG, _NAN, _PINF, or _NINF */
	int			dscale;			/* display scale */
	NumericDigit *buf;			/* start of palloc'd space for digits[] */
	NumericDigit *digits;		/* base-NBASE digits */
} NumericVar;

Two things to notice. First, weight is the base-10000 exponent of the first digit, so the value is `digits[0] * 10000^weight + digits[1] * 10000^(weight-1)

  • …; weightcan be negative. Second,bufanddigitsare distinct pointers.alloc_var` deliberately leaves one unused digit in front:
c 
static void
alloc_var(NumericVar *var, int ndigits)
{
	digitbuf_free(var->buf);
	var->buf = digitbuf_alloc(ndigits + 1);
	var->buf[0] = 0;			/* spare digit for rounding */
	var->digits = var->buf + 1;
	var->ndigits = ndigits;
}

That spare slot is where a carry out of the top digit lands, and where round_var can write when a value rounds up to one more digit. Because the buffer is separate from the struct, the same variable can be passed as both input and output to add_var, mul_var, and friends — the comment calls this out explicitly.

The distinction between init_var_from_num and set_var_from_num is a real API choice: the former aliases the packed digits (no copy, faster, but you must not modify them, because the changes would propagate back into the Numeric), while the latter copies into a fresh buffer.

Input and output

numeric_in is more involved than you might expect. It skips whitespace, handles a sign, recognizes NaN/Infinity (the same spellings float8in accepts), and then — in this tree — also recognizes non-decimal integer prefixes:

c 
		if (cp[0] == '0')
		{
			switch (cp[1])
			{
				case 'x':
				case 'X':
					base = 16;
					break;
				case 'o':
				case 'O':
					base = 8;
					break;
				case 'b':
				case 'B':
					base = 2;
					break;
				default:
					base = 10;
			}
		}

So '0x10'::numeric parses as 16, and '0b101'::numeric as 5. Decimal input goes through set_var_from_str, which extracts the decimal digits into a temporary byte array, computes the decimal weight and scale, and then packs them into base-10000 digits. Either way, apply_typmod enforces numeric(p,s) before make_result packs the value for storage.

numeric_out reverses this: special values print as NaN / Infinity / -Infinity, and everything else goes through get_str_from_var, which walks the digit array and emits exactly dscale digits after the point.

Binary I/O is a flat sequence of int16s, documented in the numeric_recv comment:

text 
External format is a sequence of int16's:
ndigits, weight, sign, dscale, NumericDigits.

numeric_recv validates every field — the sign must be one of the five legal values, the scale must fit NUMERIC_DSCALE_MASK, and every digit must be in [0, NBASE) — before rebuilding the value.

The numeric(p,s) typmod is packed into 32 bits: precision in the high 16, scale (which may be negative) in the low 11, plus VARHDRSZ as a historical offset:

c 
static inline int32
make_numeric_typmod(int precision, int scale)
{
	return ((precision << 16) | (scale & 0x7ff)) + VARHDRSZ;
}

make_result: stripping zeros and choosing a header

The packing function is where the “zero has no digits” invariant lives. make_result_safe truncates leading and trailing zero digits, forces a zero result to weight = 0 and positive sign, and then picks the header:

c 
	/* truncate leading zeroes */
	while (n > 0 && *digits == 0)
	{
		digits++;
		weight--;
		n--;
	}
	/* truncate trailing zeroes */
	while (n > 0 && digits[n - 1] == 0)
		n--;

	/* If zero result, force to weight=0 and positive sign */
	if (n == 0)
	{
		weight = 0;
		sign = NUMERIC_POS;
	}

It then uses NUMERIC_CAN_BE_SHORT(var->dscale, weight) to decide between the 6-byte and 8-byte headers, and finally checks that the packed fields actually round-trip — if the weight or scale did not survive the narrow fields, it raises “value overflows numeric format”. The consequence of the zero-stripping is that 1.0 and 1.00 compare equal but have different dscale, and hash_numeric deliberately does not hash the scale:

c 
	/*
	 * Note that we don't hash on the Numeric's scale, since two numerics can
	 * compare equal but have different scales. ...
	 */

Two non-obvious decisions

Sorting abbreviates values into 64 bits. Sorting numeric is slow, so the sortsupport path packs a value into an int64 key: 7 bits of excess-44 weight plus four 14-bit base-10000 digit words. The comment in numeric_abbrev_convert_var is candid about the trade-off — it compares at least 13 decimal digits, is only meaningful in the range 10^-176 to 10^332, and the abbreviation is negated relative to the value so that the NaN/infinity ordering falls out for free:

c 
	/* the abbrev is negated relative to the original */
	if (var->sign == NUMERIC_POS)
		result = -result;

Because abbreviation can be a pessimization on low-cardinality data, numeric_abbrev_abort uses a HyperLogLog sketch to estimate how many distinct values it has seen and aborts if the cardinality is too low to pay off.

SUM() uses a dedicated accumulator. NumericSumAccum stores digits as int32 rather than int16, so it can add up to NBASE - 1 values before it has to propagate carry. It also accumulates positive and negative values in separate buffers:

c 
typedef struct NumericSumAccum
{
	int			ndigits;
	int			weight;
	int			dscale;
	int			num_uncarried;
	bool		have_carry_space;
	int32	   *pos_digits;
	int32	   *neg_digits;
} NumericSumAccum;

The comment explains why: adding to one running value would require deciding add-vs-subtract and renormalizing on every input, whereas keeping the two signs apart is “simpler and faster”; accum_sum_final combines them once at the end.

Rounding, in round_var, is half-away-from-zero on the magnitude (extra >= pow10 / 2), and it accepts a negative rscale, which rounds before the decimal point:

Round the value of a variable to no more than rscale decimal digits after the decimal point. NOTE: we allow rscale < 0 here, implying rounding before the decimal point.

Look at it yourself

The header choice and the typmod are both visible from SQL:

sql 
SELECT typname, typlen, typbyval, typalign, typstorage
FROM pg_type WHERE typname = 'numeric';

-- the short header is used for small, modest-scale values
SELECT pg_column_size(1.5::numeric),
       pg_column_size(0.000000000000000000000000000000001::numeric),
       pg_column_size(123456789012345678901234567890::numeric);

-- zero stores no digits at all
SELECT pg_column_size(0::numeric);

-- scale is metadata: equal values, different dscale
SELECT 1.0::numeric = 1.00::numeric;

-- non-decimal integer input
SELECT '0x10'::numeric, '0b101'::numeric, '0o17'::numeric;

-- typmod limits
SELECT 123.456::numeric(5,2);   -- 123.46

pg_column_size(0::numeric) is small because make_result stripped the zero digit away and emitted a header with ndigits == 0. The numeric(5,2) case shows apply_typmod calling round_var to two decimal places before storage.

Key takeaways

  • numeric is base-10000 sign-magnitude, not IEEE. Digits are int16 values in [0, 10000), with an explicit weight locating the decimal point.
  • Two on-disk headers. The short form packs sign, 6-bit scale, and 7-bit weight into one word; the long form carries an int16 weight and a 14-bit scale. A single high bit selects between them.
  • The arithmetic form is different. NumericVar holds the digit buffer separately and reserves a leading digit for carries, which is what lets the same variable be input and output.
  • Zero has no digits. make_result strips leading and trailing zeros, so ndigits == 0 is a valid, canonical zero, and values with different scales can compare equal.
  • numeric is toastable but MAIN, and it supports both the numeric(p,s) typmod and, in this tree, non-decimal integer literals.

Further reading

  • src/backend/utils/adt/numeric.c — representation, I/O, arithmetic, aggregates, sortsupport.
  • src/include/utils/numeric.h — Numeric handle and precision/scale limits.
  • src/include/catalog/pg_type.dat — the numeric catalog entry (oid 1700).
  • src/include/catalog/pg_type.h — TYPSTORAGE_* and TYPALIGN_* semantics.
  • src/include/varatt.h — the varlena headers NumericData builds on.

PostgresInternalsNumericDecimal