💡 Intro
YSQL (like PostgreSQL) gives us INT (32-bit) and BIGINT (64-bit), but there’s no native 128-bit integer.
So what happens when you try to store values beyond the 64-bit range? 🤔
This tip shows:
● What happens when
BIGINToverflows,● How to store 128-bit integers safely using
NUMERIC(39,0)with range checks, and● How to handle exact 16-byte values using
bytea(16)for crypto-friendly counters and keys
what fits where? 📏
●
INT/INTEGER(int4): 32-bit signed, range = −2,147,483,648 … 2,147,483,647●
BIGINT(int8): 64-bit signed, range = −9,223,372,036,854,775,808 … 9,223,372,036,854,775,807● 128-bit unsigned max = 340,282,366,920,938,463,463,374,607,431,768,211,455 (≈ 3.4e38)
Try putting a >64-bit value into BIGINT:
-- YSQL (works the same as Postgres)
CREATE TABLE demo64 (v BIGINT);
-- 2^63 - 1 succeeds
INSERT INTO demo64 VALUES (9223372036854775807);
-- 2^63 (one more) fails
INSERT INTO demo64 VALUES (9223372036854775808);
-- ERROR: value out of range for type bigint
Example:
yugabyte=# -- YSQL (works the same as Postgres)
yugabyte=# CREATE TABLE demo64 (v BIGINT);
CREATE TABLE
yugabyte=# -- 2^63 - 1 succeeds
yugabyte=# INSERT INTO demo64 VALUES (9223372036854775807);
INSERT 0 1
yugabyte=# -- 2^63 (one more) fails
yugabyte=# INSERT INTO demo64 VALUES (9223372036854775808);
ERROR: bigint out of range
Once you cross 2⁶³-1, YSQL’s BIGINT can’t hold the value… it’s 64-bit max.
🧩 Workaround #1: NUMERIC(39,0) for True 128-Bit Math
A 128-bit integer needs up to 39 decimal digits.
You can store them precisely using NUMERIC(39,0), no special types required.
CREATE TABLE big_numbers (
id numeric(39,0) PRIMARY KEY,
ctr numeric(39,0) NOT NULL
);
INSERT INTO big_numbers VALUES
(340282366920938463463374607431768211455, 170141183460469231731687303715884105726);
-- Works okay!
UPDATE big_numbers SET ctr = ctr + 1;
-- Some multiplication...
SELECT id, ctr, ctr * 2 AS ctr_doubled FROM big_numbers ORDER BY id;
--Signed max + 1 => (expected to fail, but it does not!)
UPDATE big_numbers SET ctr = ctr + 1;
Example:
ugabyte=# CREATE TABLE big_numbers (
yugabyte(# id numeric(39,0) PRIMARY KEY,
yugabyte(# ctr numeric(39,0) NOT NULL
yugabyte(# );
CREATE TABLE
yugabyte=# INSERT INTO big_numbers VALUES
yugabyte-# (340282366920938463463374607431768211455, 170141183460469231731687303715884105726);
INSERT 0 1
yugabyte=# -- Works okay!
yugabyte=# UPDATE big_numbers SET ctr = ctr + 1;
UPDATE 1
yugabyte=# -- Some multiplication...
yugabyte=# SELECT id, ctr, ctr * 2 AS ctr_doubled FROM big_numbers ORDER BY id;
id | ctr | ctr_doubled
-----------------------------------------+-----------------------------------------+-----------------------------------------
340282366920938463463374607431768211455 | 170141183460469231731687303715884105727 | 340282366920938463463374607431768211454
(1 row)
yugabyte=# --Signed max + 1 => (expected to fail, but it does not!)
yugabyte=# UPDATE big_numbers SET ctr = ctr + 1;
UPDATE 1
yugabyte=# SELECT id, ctr, ctr * 2 AS ctr_doubled FROM big_numbers ORDER BY id;
id | ctr | ctr_doubled
-----------------------------------------+-----------------------------------------+-----------------------------------------
340282366920938463463374607431768211455 | 170141183460469231731687303715884105728 | 340282366920938463463374607431768211456
(1 row)
As we can see in the example above, NUMERIC(39,0) doesn’t overflow at 2^127; it happily stores up to 10^39−1. The “signed max + 1 fails” only happens if you enforce a 128-bit range.
We could wrap the NUMERIC(39,0)in a domain so the range is enforced… something like:
-- Signed int128: −2^127 .. 2^127−1
CREATE DOMAIN int128 AS numeric(39,0)
CHECK (VALUE >= -170141183460469231731687303715884105728
AND VALUE <= 170141183460469231731687303715884105727);
-- Unsigned uint128: 0 .. 2^128−1
CREATE DOMAIN uint128 AS numeric(39,0)
CHECK (VALUE >= 0
AND VALUE <= 340282366920938463463374607431768211455);
However, there’s a known issue where if an indexed column is a domain type (even over a primitive like text), EXPLAIN may report index usage, but the query actually performs a full index scan with a filter instead of an efficient seek. You can track it here: yugabytedb-gh-26726.
Since it’s advisable to avoid using domains at this time, we can use table-level CHECK constraints instead.
-- 2^127 - 1 = 170141183460469231731687303715884105727
-- 2^128 - 1 = 340282366920938463463374607431768211455
-- 2^128 = 340282366920938463463374607431768211456
DROP TABLE IF EXISTS big_numbers;
CREATE TABLE big_numbers (
id numeric(39,0) PRIMARY KEY,
ctr numeric(39,0) NOT NULL,
-- Enforce signed 128-bit range for ctr
CONSTRAINT ctr_int128_range
CHECK (ctr >= -170141183460469231731687303715884105728
AND ctr <= 170141183460469231731687303715884105727),
-- Example unsigned 128-bit range for id
CONSTRAINT id_uint128_range
CHECK (id >= 0
AND id <= 340282366920938463463374607431768211455)
);
Example:
yugabyte=# INSERT INTO big_numbers VALUES
yugabyte-# (340282366920938463463374607431768211455, 170141183460469231731687303715884105726);
INSERT 0 1
yugabyte=# -- Works okay!
yugabyte=# UPDATE big_numbers SET ctr = ctr + 1;
UPDATE 1
yugabyte=# -- Some multiplication...
yugabyte=# SELECT id, ctr, ctr * 2 AS ctr_doubled FROM big_numbers ORDER BY id;
id | ctr | ctr_doubled
-----------------------------------------+-----------------------------------------+-----------------------------------------
340282366920938463463374607431768211455 | 170141183460469231731687303715884105727 | 340282366920938463463374607431768211454
(1 row)
yugabyte=# --Signed max + 1 => (expected to fail, and it does!)
yugabyte=# UPDATE big_numbers SET ctr = ctr + 1;
ERROR: new row for relation "big_numbers" violates check constraint "ctr_int128_range"
DETAIL: Failing row contains (340282366920938463463374607431768211455, 170141183460469231731687303715884105728).
👉 If you omit the CHECK, then NUMERIC(39,0) can store up to 10³⁹−1 … wider than 128 bits.
That’s fine if you just need “huge INTs” without strict bounds.
🔐 Option 2: Exact 16-byte storage (bytea(16)) for crypto
When you need exact 16 bytes (for AES counters, UUIDv7 seeds, or opaque 128-bit keys), store the bits directly in a bytea column.
No domain wrapper needed… just a length check and a few clean helper functions.
2.1 Table definition
CREATE TABLE ctr_example (
key_id text NOT NULL,
counter bytea NOT NULL CHECK (length(counter) = 16),
PRIMARY KEY (key_id, counter)
);
2.2 Helper functions (hex-only, no bytea mutation)
-- HEX → NUMERIC
CREATE OR REPLACE FUNCTION hex_to_uint128(hex text)
RETURNS numeric
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
s text := upper(replace(hex, '0X', ''));
i int; c text; d int; v numeric := 0;
BEGIN
IF length(s) = 0 OR length(s) > 32 OR s ~ '[^0-9A-F]' THEN
RAISE EXCEPTION 'invalid hex for uint128: %', hex;
END IF;
s := lpad(s, 32, '0');
FOR i IN 1..length(s) LOOP
c := substr(s, i, 1);
d := CASE c WHEN '0' THEN 0 WHEN '1' THEN 1 WHEN '2' THEN 2 WHEN '3' THEN 3
WHEN '4' THEN 4 WHEN '5' THEN 5 WHEN '6' THEN 6 WHEN '7' THEN 7
WHEN '8' THEN 8 WHEN '9' THEN 9 WHEN 'A' THEN 10 WHEN 'B' THEN 11
WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 END;
v := v * 16 + d;
END LOOP;
RETURN v;
END $$;
-- NUMERIC → HEX (32 nibbles)
CREATE OR REPLACE FUNCTION uint128_to_hex32(n numeric)
RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
v numeric := n; i int; d int;
digits constant text := '0123456789ABCDEF';
out text := '';
BEGIN
IF v < 0 OR v > 340282366920938463463374607431768211455 THEN
RAISE EXCEPTION 'uint128 out of range: %', v;
END IF;
FOR i IN 1..32 LOOP
d := (v % 16)::int;
out := substr(digits, d+1, 1) || out;
v := trunc(v / 16);
END LOOP;
RETURN out;
END $$;
-- HEX ↔ BYTEA
CREATE OR REPLACE FUNCTION u128_from_hex(hex text)
RETURNS bytea LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT decode(lpad(upper(replace(hex, '0X', '')), 32, '0'), 'hex')
$$;
CREATE OR REPLACE FUNCTION u128_to_hex(x bytea)
RETURNS text LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT upper(encode(x, 'hex'))
$$;
-- BYTEA ↔ NUMERIC
CREATE OR REPLACE FUNCTION bytea_to_uint128(b bytea)
RETURNS numeric LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT hex_to_uint128(encode(b, 'hex'))
$$;
CREATE OR REPLACE FUNCTION uint128_to_bytea(n numeric)
RETURNS bytea LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT decode(uint128_to_hex32(n), 'hex')
$$;
-- Add n (0..2^63-1) modulo 2^128
CREATE OR REPLACE FUNCTION u128_add_small(x bytea, n bigint)
RETURNS bytea LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT uint128_to_bytea(
mod((bytea_to_uint128(x) + n)::numeric,
'340282366920938463463374607431768211456'::numeric)
)
$$;
2.3 Demo
INSERT INTO ctr_example VALUES ('k1', u128_from_hex('0'));
UPDATE ctr_example
SET counter = u128_add_small(counter, 1)
WHERE key_id = 'k1';
SELECT key_id, u128_to_hex(counter) AS counter_hex
FROM ctr_example;
✅ Output:
yugabyte=# INSERT INTO ctr_example VALUES ('k1', u128_from_hex('0'));
INSERT 0 1
yugabyte=# UPDATE ctr_example
yugabyte-# SET counter = u128_add_small(counter, 1)
yugabyte-# WHERE key_id = 'k1';
UPDATE 1
yugabyte=# SELECT key_id, u128_to_hex(counter) AS counter_hex
yugabyte-# FROM ctr_example;
key_id | counter_hex
--------+----------------------------------
k1 | 00000000000000000000000000000001
(1 row)
Use when: You need exact 16-byte values (non-domain-safe, crypto-ready, sortable big-endian).
🚀 Summary: Go Big or Byte 🧩
64 bits are cute… until your math, crypto, or ID space outgrows them.
YSQL doesn’t (yet) ship an INT128, but with a few smart tricks you can:
● 🧠 Think big: Use
NUMERIC(39,0)domains for real 128-bit integer math.● 🔐 Go byte-level: Use
bytea(16)with simple helpers for crypto-clean counters and keys.● ⚙️ Stay portable: Both approaches work everywhere YugabyteDB runs… same SQL, same precision.
Whether you’re crunching massive numbers or counting AES-CTR nonces, YugabyteDB lets you go bigger, faster, and smarter… one byte at a time. 💪
Have Fun!
