Release History
Help

YMatrix

Quick Start

Deployment

Data Usage

Manage Clusters

Security

Best Practice

Technical Principles

Advanced Features

Graph Database

Guide

SQL Reference

FAQ

mxnumeric User Guide

Overview

PostgreSQL’s numeric type (hereafter pg numeric) offers higher precision than equivalent types in other databases, but its implementation is more complex and incurs relatively poor performance. Due to structural constraints, pg numeric cannot be effectively vectorized. As a result, in vectorized execution engines, it delivers no performance benefit — and often performs worse than non-vectorized execution due to compatibility-layer overhead. Compared to vectorized integer or floating-point types, pg numeric is typically 1–2 orders of magnitude slower.

To improve performance for numeric workloads, YMatrix introduces a bounded-precision numeric type (hereafter mxnumeric), supporting up to 38 digits of precision. This type is provided via the mxnumeric extension.

Installation and Upgrade

mxnumeric as the Default numeric Type

Starting with version 6.5, mxnumeric is installed and enabled by default as the numeric type. No additional installation or configuration is required: after database initialization, all references to numeric resolve to mxnumeric.

The mxnumeric type is implemented as public.numeric. The original pg_catalog.numeric remains available and unchanged.

Manual Installation

Install the extension using the standard PostgreSQL extension command:

CREATE EXTENSION mxnumeric;

Upon successful installation, public.numeric is created in the current database. The built-in pg_catalog.numeric is unaffected.

Extension Upgrade

When mxnumeric receives bug fixes or optimizations, upgrading usually requires only installing the new package — no manual intervention is needed.

However, if the extension adds or modifies functions, operators, or other catalog objects, administrators must manually upgrade the extension on existing clusters. The procedure varies depending on scope. Below are instructions assuming an upgrade from version 0.1 to 0.2.

Upgrade for an Existing Database

To upgrade mxnumeric in an already-created database:

-- Connect to the target database
\c db1

-- Check current extension status and version
\dx mxnumeric

-- Method 1: Upgrade to a specific version
ALTER EXTENSION mxnumeric UPDATE TO '0.2';

-- Method 2: Upgrade to the latest available version
ALTER EXTENSION mxnumeric UPDATE;

Reinstalling the extension (DROP EXTENSION mxnumeric; CREATE EXTENSION mxnumeric;) also updates the version, but it drops all columns of type public.numeric due to dependency constraints. Avoid this method unless strictly necessary.

Upgrade for Template Databases

To ensure newly created databases automatically use the latest mxnumeric version, upgrade the extension in template databases:

-- Update mxnumeric in template1
\c template1
ALTER EXTENSION mxnumeric UPDATE;

-- Update mxnumeric in template0 (requires enabling connections first)
\c postgres
ALTER DATABASE template0 ALLOW_CONNECTIONS ON;

\c template0
ALTER EXTENSION mxnumeric UPDATE;

\c postgres
ALTER DATABASE template0 ALLOW_CONNECTIONS OFF;

After these steps, any newly created database will use the latest mxnumeric version by default.

Identifying mxnumeric Types

Because both pg_catalog.numeric and public.numeric coexist, distinguishing which numeric type is in use is essential.

Two common methods are supported:

  • Check the GUC mx_force_system_types:
    If mx_force_system_types = false, public.numeric is used.

    SHOW mx_force_system_types;
 mx_force_system_types
-----------------------
 off
(1 row)

  • Check the oid of the numeric type:
    If the oid is not 1700, then public.numeric is in use.

    SELECT 'numeric'::regtype::oid;
  oid
-------
 13862
(1 row)

To determine whether numeric columns in a table use mxnumeric, use the helper functions:

SELECT attnum, attname, type FROM mxnumeric.list_numeric_columns('t1');
 attnum | attname |        type
--------+---------+--------------------
      2 | col1    | pg_catalog.numeric
      3 | col2    | public.numeric
(2 rows)

SELECT attnum, attname, type FROM mxnumeric.list_numeric_columns('public', 't1');
 attnum | attname |        type
--------+---------+--------------------
      2 | col1    | pg_catalog.numeric
      3 | col2    | public.numeric
(2 rows)

Usage

Configure the GUC mx_force_system_types

Setting mx_force_system_types = false causes numeric to resolve to public.numeric. When mxnumeric is the default numeric type (v6.5+), this GUC defaults to false.

  • true: Uses pg_catalog.numeric.
  • false: Uses public.numeric (i.e., mxnumeric).

Precision Range

mxnumeric supports precision values from 1 to 38. Attempts to declare greater precision raise an error:

CREATE TABLE t2(a INT, b NUMERIC(39, 0));
ERROR:  NUMERIC precision 39 must be between 1 and 38

Arithmetic results exceeding 38-digit precision trigger an overflow error:

-- Expected result: NUMERIC(40,0) → exceeds mxnumeric limit
SELECT 12345678901234567890::public.numeric * 12345678901234567890::public.numeric;
ERROR:  value overflows numeric format (Error.cc:26)

-- pg_catalog.numeric handles it without error
SELECT 12345678901234567890::pg_catalog.numeric * 12345678901234567890::pg_catalog.numeric;
                ?column?
-----------------------------------------
 152415787532388367501905199875019052100

SELECT pg_typeof(12345678901234567890::pg_catalog.numeric * 12345678901234567890::pg_catalog.numeric);
 pg_typeof
-----------
 numeric

Scale Overflow

When scale is set to 8 and precision to 38, operations yielding more than 8 fractional digits cause overflow:

-- pg_catalog.numeric supports up to 9 fractional digits
SELECT 123456789012345678901234567890.12345678::pg_catalog.numeric(38,8) * 0.9;
                 ?column?
------------------------------------------
 111111110111111111011111111101.111111102
(1 row)

-- public.numeric overflows
SELECT 123456789012345678901234567890.12345678::public.numeric(38,8) * 0.9;
ERROR:  value overflows numeric format (Error.cc:26)

Division Return Type

mxnumeric division returns DOUBLE PRECISION. In contrast, pg_catalog.numeric division returns NUMERIC.

Because SQL does not define precision requirements for numeric division, and because PostgreSQL mandates that division precision must be at least that of float8, mxnumeric uses float8 for division results to maximize performance.

This introduces minor precision loss relative to pg_catalog.numeric:

SELECT 1234.5678901234567890::pg_catalog.numeric(20,16) / 666;
      ?column?
--------------------
 1.8537055407259111
(1 row)

-- mxnumeric division exhibits slight precision loss
SELECT 1234.5678901234567890::public.numeric(20,16) / 666;
      ?column?
--------------------
 1.8537055407259109

Function Return Types

The following functions return FLOAT8 instead of NUMERIC for improved performance:

  • exp()
  • ln()
  • log()
  • pow() / power()
  • sqrt()

Feature Customization

Some features cannot be enabled during extension creation and require explicit configuration.

The mxnumeric.set_config(text, bool) function enables or disables optional mxnumeric features. Current settings are visible in the mxnumeric.configs view:

SELECT * FROM mxnumeric.configs;
       config       | enabled |                                          description
--------------------+---------+------------------------------------------------------------------------------------------------
 linear_interpolate | f       | replace original linear_interpolate functions with mxnumeric version to support mxnumeric type
 mars3              | f       | create operator classes to support mars3 btree & brin index for mxnumeric type

linear_interpolate Function Support

The kernel’s linear_interpolate() function is hardcoded to accept only built-in types and does not natively support mxnumeric. Because usage is infrequent, mxnumeric does not enable this support by default.

To enable mxnumeric support for linear_interpolate(), call:

SELECT mxnumeric.set_config('linear_interpolate', true);

This operation:

  • Renames pg_catalog.linear_interpolate() to preserve it,
  • Creates public.linear_interpolate() optimized for mxnumeric.

To revert to the default behavior:

SELECT mxnumeric.set_config('linear_interpolate', false);

This restores pg_catalog.linear_interpolate() and drops the public version.

mars3 Index Support

mars3 and mxnumeric are independent extensions. Since mars3 installation order is not guaranteed, mxnumeric does not provide mars3_btree or mars3_brin operator classes by default.

To enable mxnumeric support for mars3 indexes, run:

SELECT mxnumeric.set_config('mars3', true);

Prerequisite: The matrixts extension must already be installed; otherwise, the command fails.

To disable support and drop the operator classes:

SELECT mxnumeric.set_config('mars3', false);

Appendix

mxnumeric Function Reference

Ordinary Functions

Ordinary functions operate independently of context (unlike aggregate functions, which apply only within GROUP BY clauses).

Math Functions
oid UDF Name Target Schema Lossy Original Result Type Optimized Result Type Parameter Types Result Precision Function Type Category Alias Of
1705 abs public N numeric numeric = plain math
1711 ceil public N numeric numeric (P,0) plain math
2167 ceiling public N numeric numeric (P,0) plain math ceil
1973 div public Y numeric double numeric, numeric plain math
1732 exp public Y numeric double numeric plain math
1376 factorial public N numeric bigint (MAX,0) plain math
1712 floor public N numeric numeric (P,0) plain math
1734 ln public Y numeric double numeric plain math
1736 log public Y numeric double numeric, numeric plain math
1741 log public Y numeric double numeric plain math
1481 log10 ? Y numeric double numeric plain math
1728 mod public N numeric numeric, numeric = plain math
1704 numeric_abs mxnumeric N numeric numeric = plain math abs
1724 numeric_add mxnumeric N numeric numeric, numeric + plain math
6997 numeric_dec mxnumeric N numeric numeric = plain math
1727 numeric_div mxnumeric Y numeric double numeric, numeric plain math
1980 numeric_div_trunc mxnumeric Y numeric double numeric, numeric plain math
1733 numeric_exp mxnumeric Y numeric double numeric plain math exp
111 numeric_fac mxnumeric N numeric bigint (MAX,0) plain math factorial
1764 numeric_inc mxnumeric N numeric numeric = plain math
1767 numeric_larger mxnumeric N numeric numeric, numeric = plain math
1735 numeric_ln mxnumeric Y numeric double numeric plain math ln
1737 numeric_log mxnumeric Y numeric double numeric plain math log
1729 numeric_mod mxnumeric N numeric numeric, numeric = plain math mod
1726 numeric_mul mxnumeric N numeric numeric, numeric * plain math
1739 numeric_power mxnumeric Y numeric double numeric, numeric plain math power
1766 numeric_smaller mxnumeric N numeric numeric, numeric = plain math
1731 numeric_sqrt mxnumeric Y numeric double numeric plain math sqrt
1725 numeric_sub mxnumeric N numeric numeric, numeric + plain math
1771 numeric_uminus mxnumeric N numeric numeric = plain math
1915 numeric_uplus mxnumeric N numeric numeric = plain math
1738 pow public Y numeric double numeric plain math power
2169 power public Y numeric double numeric plain math
1707 round public N numeric numeric, integer (P,arg) plain math
1708 round public N numeric numeric (P,0) plain math
1730 sqrt public Y numeric double numeric plain math
1709 trunc public N numeric numeric, integer (P,arg) plain math
1710 trunc public N numeric numeric (P,0) plain math
  • numeric_larger() and numeric_smaller() support MIN()/MAX() aggregates and assume identical input precisions.
  • numeric_uminus() and numeric_uplus() implement unary +x and -x.
  • Some functions (e.g., log10(x)) are SQL-language wrappers (e.g., log(10,x)). Because mxnumeric restricts language to C, dedicated C implementations are provided.
Operator Functions
oid UDF Name Target Schema Lossy Original Result Type Optimized Result Type Parameter Types Result Precision Function Type Category
1769 numeric_cmp mxnumeric integer numeric, numeric plain oper
1718 numeric_eq mxnumeric bool numeric, numeric plain oper
1721 numeric_ge mxnumeric bool numeric, numeric plain oper
1720 numeric_gt mxnumeric bool numeric, numeric plain oper
1723 numeric_le mxnumeric bool numeric, numeric plain oper
1722 numeric_lt mxnumeric bool numeric, numeric plain oper
1719 numeric_ne mxnumeric bool numeric, numeric plain oper
Index Functions
oid UDF Name Target Schema Lossy Original Result Type Optimized Result Type Parameter Types Result Precision Function Type Category
6145 cdblegacyhash_numeric public integer numeric plain index
432 hash_numeric public integer numeric plain index
780 hash_numeric_extended public bigint numeric plain index
4141 in_range public bool numeric, numeric, numeric, boolean, boolean plain index
Cast Functions
oid UDF Name Target Schema Lossy Original Result Type Optimized Result Type Parameter Types Result Precision Function Type Category
1745 float4 public real numeric plain cast
1746 float8 public double numeric plain cast
1783 int2 public smallint numeric plain cast
1744 int4 public integer numeric plain cast
1779 int8 public bigint numeric plain cast
3824 money public money numeric plain cast
1703 numeric public numeric numeric, int plain cast
1740 numeric public numeric integer plain cast
1742 numeric public numeric real plain cast
1743 numeric public numeric double plain cast
1781 numeric public numeric bigint plain cast
1782 numeric public numeric smallint plain cast
3449 numeric public numeric jsonb plain cast
3823 numeric public numeric money plain cast
7597 numeric2complex public complex numeric plain cast
3844 numrange public numrange numeric, numeric plain cast
3845 numrange public numrange numeric, numeric, text plain cast
3924 numrange_subdiff public double numeric, numeric plain cast
1772 to_char public text numeric, text plain cast
1777 to_number public numeric text, text plain cast
Utility Functions
oid UDF Name Target Schema Lossy Original Result Type Optimized Result Type Parameter Types Result Precision Function Type Category
3259 generate_series public N numeric numeric, numeric, numeric = plain util
3260 generate_series public N numeric numeric, numeric = plain util
3281 scale public integer numeric plain util
1706 sign public N numeric numeric = plain util
2170 width_bucket public integer numeric, numeric, numeric, integer plain util
Internal Functions
oid UDF Name Target Schema Lossy Original Result Type Optimized Result Type Parameter Types Result Precision Function Type Category
7082 interval_bound public N numeric numeric, numeric = plain internal
7083 interval_bound public N numeric numeric, numeric, integer = plain internal
7084 interval_bound public N numeric numeric, numeric, integer, numeric = plain internal
6082 linear_interpolate public numeric anyelement, anyelement, numeric, anyelement, numeric plain internal
1701 numeric_in mxnumeric numeric cstring, oid, integer (P,S) plain internal
1702 numeric_out mxnumeric cstring numeric plain internal
2460 numeric_recv mxnumeric numeric internal, oid, integer (P,S) plain internal
2461 numeric_send mxnumeric bytea numeric plain internal
3237 pg_lsn_mi ? N numeric pg_lsn, pg_lsn plain internal
3166 pg_size_pretty ? text numeric plain internal
2022 pg_stat_get_activity ? record integer plain internal
1136 pg_stat_get_wal ? record plain internal
3165 pg_wal_lsn_diff ? numeric pg_lsn, pg_lsn plain internal

Aggregate Functions

Wrapper Functions
oid UDF Name Schema Lossy Original Result Type Optimized Result Type Parameter Types Result Precision Function Type Category
2100 avg public numeric bigint (MAX, S) agg
2101 avg public numeric integer (MAX, S) agg
2102 avg public numeric smallint (MAX, S) agg
2103 avg public numeric numeric (MAX, S) agg
2130 max public numeric numeric = agg
2146 min public numeric numeric = agg
2154 stddev public numeric bigint agg
2155 stddev public numeric integer agg
2156 stddev public numeric smallint agg
2159 stddev public numeric numeric agg
2724 stddev_pop public numeric bigint agg
2725 stddev_pop public numeric integer agg
2726 stddev_pop public numeric smallint agg
2729 stddev_pop public numeric numeric agg
2712 stddev_samp public numeric bigint agg
2713 stddev_samp public numeric integer agg
2714 stddev_samp public numeric smallint agg
2717 stddev_samp public numeric numeric agg
2107 sum public numeric bigint (MAX, S) agg
2114 sum public numeric numeric (MAX, S) agg
2718 var_pop public numeric bigint agg
2719 var_pop public numeric integer agg
2720 var_pop public numeric smallint agg
2723 var_pop public numeric numeric agg
2641 var_samp public numeric bigint agg
2642 var_samp public numeric integer agg
2643 var_samp public numeric smallint agg
2646 var_samp public numeric numeric agg
2148 variance public numeric bigint agg
2149 variance public numeric integer agg
2150 variance public numeric smallint agg
2153 variance public numeric numeric agg
Implementation Functions
oid UDF Name Schema Lossy Original Result Type Optimized Result Type Parameter Types Result Precision Function Type Category
1964 int8_avg public numeric bigint[] agg helper legacy
1842 int8_sum public numeric numeric, bigint agg helper legacy
1833 numeric_accum mxnumeric internal internal, numeric agg helper
3548 numeric_accum_inv mxnumeric internal internal, numeric agg helper
1837 numeric_avg mxnumeric numeric internal agg helper
2858 numeric_avg_accum mxnumeric internal internal agg helper
3389 numeric_poly_avg mxnumeric numeric internal agg helper
3392 numeric_poly_stddev_pop mxnumeric numeric internal agg helper
3393 numeric_poly_stddev_samp mxnumeric numeric internal agg helper
3388 numeric_poly_sum mxnumeric numeric internal agg helper
3390 numeric_poly_var_pop mxnumeric numeric internal agg helper
3391 numeric_poly_var_samp mxnumeric numeric internal agg helper
2596 numeric_stddev_pop mxnumeric numeric internal agg helper
1839 numeric_stddev_samp mxnumeric numeric internal agg helper
3178 numeric_sum mxnumeric numeric internal agg helper
2514 numeric_var_pop mxnumeric numeric internal agg helper
1838 numeric_var_samp mxnumeric numeric internal agg helper
← Previous
JSON/JSONB、MXKV/MXKV2
Next →
Spatial Type