Numbers, identity, UUIDv7 and generated columns

Ten payments of 0.10 summed in double precision come to 0.9999999999999999, and a key declared as integer runs out at 2,147,483,647, which takes under 25 days at 1,000 inserts a second. Both choices are made on a schema’s first day and paid for years later, so this first lesson of the module covers the columns nearly every table has: numbers, keys and computed values.

You will measure each choice on recorded runs of PostgreSQL 18.6: exact and approximate sums, the money type under two locales, a gap in an identity column, the rewrite behind widening a key, UUIDv7 against random keys, and virtual against stored generated columns.

Numbers that must add up exactly belong in numeric

The first query of the session below adds 0.1 and 0.2. In double precisiondouble precision: PostgreSQL's 8-byte binary floating-point type, also called float8. It gives about 15 significant digits and fast arithmetic, but a decimal fraction such as 0.1 is kept as the nearest binary value, so sums of such values can drift., also called float8, the sum prints as 0.30000000000000004 and the comparison with 0.3 is false; in numericnumeric: PostgreSQL's exact decimal type. numeric(p,s) keeps s digits after the point and p digits in all, rounds extra decimal places on input and refuses a value that does not fit; its arithmetic is exact but slower than that of integers or floats. the same comparison is true. Ten payments of 0.10 show what that means for money: 0.9999999999999999 in float8, exactly 1.0 in numeric.

Double precision and real are binary floating-point types, the IEEE 754 formats processors compute in. A decimal fraction such as 0.1 has no exact binary form, so the column keeps the nearest binary value and every sum carries the error forward. For measurements that is a good trade: 8 bytes, about 15 significant digits and fast arithmetic, for values that were approximate when a sensor read them. For amounts that must add up to the last cent it is the wrong one.

numeric stores decimal digits, so 0.1 is exactly 0.1 and sums, differences and products are exact. The manual recommends it for amounts of money and other quantities that must be exact, and warns that its arithmetic is much slower than that of integers or floats. A column declared numeric(10,2) keeps two digits after the point and ten in all: the run’s insert of 12.345 was rounded to 12.35, and 123456789.00 was refused with numeric field overflow, because ten digits with two after the point stay below 100,000,000. Four types cover most number columns:

Whole numbers only need the right size. integer stops at 2,147,483,647: the run’s 2147483647::int + 1 fails with integer out of range, while the same sum in bigint returns 2147483648. The session runs these queries and, at its end, the money type of the next section:

"""Exact and approximate numbers, integer limits and the money type, on a throwaway PostgreSQL 18.6 server."""

from pg_session import Server

# The session expects two errors: a value too wide for numeric(10,2) and an integer overflow.
with Server(settings={"autovacuum": "off", "timezone": "'UTC'"}) as pg:
    pg.up()
    pg.psql("numbers.sql", errors=2)

$ psql -X -a -f numbers.sql
-- The same sums in double precision (float8, binary and approximate) and in numeric (decimal and exact).
SELECT 0.1::float8 + 0.2::float8 AS float_sum,
       0.1::float8 + 0.2::float8 = 0.3::float8 AS float_equal,
       0.1::numeric + 0.2 = 0.3 AS numeric_equal;
      float_sum      | float_equal | numeric_equal 
---------------------+-------------+---------------
 0.30000000000000004 | f           | t
(1 row)

-- Ten payments of 0.10 each.
SELECT sum(0.1::float8) AS float_total, sum(0.1::numeric) AS numeric_total
FROM generate_series(1, 10);
    float_total     | numeric_total 
--------------------+---------------
 0.9999999999999999 |           1.0
(1 row)

-- A tie: numeric rounds 2.5 away from zero, float8 to the nearest even number.
SELECT round(2.5::numeric) AS numeric_round, round(2.5::float8) AS float_round;
 numeric_round | float_round 
---------------+-------------
             3 |           2
(1 row)

-- numeric(10,2) rounds to two places on the way in and refuses a value with too many digits.
CREATE TABLE price (amount numeric(10,2));
CREATE TABLE
INSERT INTO price VALUES (12.345), (99999999.99);
INSERT 0 2
INSERT INTO price VALUES (123456789.00);
psql:numbers.sql:13: ERROR:  numeric field overflow
DETAIL:  A field with precision 10, scale 2 must round to an absolute value less than 10^8.
SELECT amount FROM price;
   amount    
-------------
       12.35
 99999999.99
(2 rows)

-- integer stops at 2,147,483,647; bigint goes on to 9,223,372,036,854,775,807.
SELECT 2147483647::int + 1;
psql:numbers.sql:16: ERROR:  integer out of range
SELECT 2147483647::bigint + 1 AS as_bigint;
 as_bigint  
------------
 2147483648
(1 row)

-- money keeps a whole number of minor units and takes its scale and symbol from lc_monetary.
SHOW lc_monetary;
 lc_monetary 
-------------
 en_US.UTF-8
(1 row)

CREATE TABLE wallet (balance money);
CREATE TABLE
INSERT INTO wallet VALUES (1234.5);
INSERT 0 1
SELECT balance FROM wallet;
  balance  
-----------
 $1,234.50
(1 row)

SET lc_monetary = 'ja_JP.UTF-8';
SET
SELECT balance FROM wallet;
  balance  
-----------
 ￥123,450
(1 row)

RESET lc_monetary;
RESET
-- Whole minor units in a bigint (cents, or paise for rupees) are exact and need no locale.
CREATE TABLE wallet2 (balance_minor bigint NOT NULL);
CREATE TABLE
INSERT INTO wallet2 VALUES (123450);
INSERT 0 1
SELECT balance_minor, round(balance_minor / 100.0, 2) AS balance FROM wallet2;
 balance_minor | balance 
---------------+---------
        123450 | 1234.50
(1 row)

-- The same sums in double precision (float8, binary and approximate) and in numeric (decimal and exact).
SELECT 0.1::float8 + 0.2::float8 AS float_sum,
       0.1::float8 + 0.2::float8 = 0.3::float8 AS float_equal,
       0.1::numeric + 0.2 = 0.3 AS numeric_equal;
-- Ten payments of 0.10 each.
SELECT sum(0.1::float8) AS float_total, sum(0.1::numeric) AS numeric_total
FROM generate_series(1, 10);
-- A tie: numeric rounds 2.5 away from zero, float8 to the nearest even number.
SELECT round(2.5::numeric) AS numeric_round, round(2.5::float8) AS float_round;
-- numeric(10,2) rounds to two places on the way in and refuses a value with too many digits.
CREATE TABLE price (amount numeric(10,2));
INSERT INTO price VALUES (12.345), (99999999.99);
INSERT INTO price VALUES (123456789.00);
SELECT amount FROM price;
-- integer stops at 2,147,483,647; bigint goes on to 9,223,372,036,854,775,807.
SELECT 2147483647::int + 1;
SELECT 2147483647::bigint + 1 AS as_bigint;
-- money keeps a whole number of minor units and takes its scale and symbol from lc_monetary.
SHOW lc_monetary;
CREATE TABLE wallet (balance money);
INSERT INTO wallet VALUES (1234.5);
SELECT balance FROM wallet;
SET lc_monetary = 'ja_JP.UTF-8';
SELECT balance FROM wallet;
RESET lc_monetary;
-- Whole minor units in a bigint (cents, or paise for rupees) are exact and need no locale.
CREATE TABLE wallet2 (balance_minor bigint NOT NULL);
INSERT INTO wallet2 VALUES (123450);
SELECT balance_minor, round(balance_minor / 100.0, 2) AS balance FROM wallet2;

Rounding differs as well. round(2.5) gave 3 in numeric, which rounds a tie away from zero, and 2 in double precision, which on most machines rounds a tie to the nearest even number. Two reports that round the same amounts in different types can therefore disagree by a cent on every tie.

The money type changes meaning with the locale

PostgreSQL also has a money typemoney type: A PostgreSQL type that stores a currency amount as a whole number of the currency's smallest units. The lc_monetary setting decides how many decimal places it shows and which symbol it prints, so the same value reads differently under another locale., and the end of the same session shows why it is rarely the right one. A balance of 1234.5 went into a money column and came back as $1,234.50 under the server’s lc_monetary setting, en_US.UTF-8. The session then set lc_monetary to ja_JP.UTF-8 and read the same row again: ￥123,450.

Nothing in the table changed. money keeps a whole number of the currency’s smallest units, here 123,450, and lc_monetary decides how many of them make one unit and which symbol to print. The Japanese locale formats yen with no decimal places, so the same stored number now reads as an amount a hundred times larger:

Money’s text form depends on the locale, so the manual warns that a dump may not load into a database whose lc_monetary differs. Dividing a money value by an integer also truncates toward zero instead of rounding.

Two exact choices need no locale at all. numeric(12,2) holds amounts up to 9,999,999,999.99 with the cents in place, and a currency code in its own column says what the digits are. A bigint of minor unitsminor unit: The smallest unit of a currency, such as the cent of a dollar or the paisa of a rupee. Amounts kept as whole minor units in an integer column stay exact without a decimal type and are divided only for display. stores the run’s balance as 123450 and divides by 100 only for display, as the session’s last query does; its arithmetic is exact and fast.

Identity columns hand out numbers that are never taken back

An invoice table numbers its rows with id bigint GENERATED ALWAYS AS IDENTITY. In the next session Asha’s row took id 1. Ravi’s insert took 2 inside a transaction that then rolled back, an insert with no customer took 3 and failed its NOT NULL constraint, and Meera’s row took 4. The table holds ids 1 and 4, and no row was ever deleted.

An identity columnidentity column: A column declared GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY, which takes its values from an implicit sequence. It is the SQL-standard form of an auto-numbered key; ALWAYS refuses a typed value unless the statement says OVERRIDING SYSTEM VALUE. is the SQL-standard way to number rows. PostgreSQL creates an implicit sequencesequence: A database object that hands out increasing numbers through nextval. A number once handed out is never taken back, even when the transaction that took it rolls back, so the values it gives a table can have gaps. for it, here invoice_id_seq, and every insert that leaves the column out takes the sequence’s next value. The sequence never takes a value back. If it did, each insert would have to wait until the transactions holding lower numbers had committed or rolled back; handing every number out once lets many sessions insert at the same moment. The manual names rollbacks, crashes and INSERT ... ON CONFLICT, which computes its row before it meets the conflict, as causes of gaps, so a gap is the normal result and not a sign of lost data. Step through the four inserts and watch the sequence move on while rows disappear:

ALWAYS is the stricter of the two forms. It refused a hand-typed id 2 with a hint to write OVERRIDING SYSTEM VALUE, and accepted the row once the statement said so; GENERATED BY DEFAULT AS IDENTITY would have taken the typed value without a word. Either form makes the column NOT NULL, but only a PRIMARY KEY or UNIQUE constraint makes its values unique. The run also reads the sequence from the catalogsystem catalog: One of the tables in the pg_catalog schema where PostgreSQL records its objects, such as pg_class for relations, pg_attribute for columns and pg_constraint for constraints. They can be queried like any table. More in “System catalogs, pages and TOAST”: a bigint like its column, with a maximum of 9,223,372,036,854,775,807.

The older shorthand, serialserial: PostgreSQL shorthand, not a real type, for an integer column whose default calls nextval on a sequence the column owns; bigserial does the same with bigint. A value typed by hand goes around the default and leaves the sequence where it was., is not a real type: it creates an integer column whose default calls nextval on a sequence. A value typed by hand goes around that default and leaves the sequence where it was, so in the run the next default insert also took 1 and failed on the primary key. Its sequence is an integer, with a maximum of 2,147,483,647. The session runs every statement of this section:

"""Identity columns, the gaps a sequence leaves, and serial, on a throwaway PostgreSQL 18.6 server."""

from pg_session import Server

# The session expects three errors: a NULL customer, a typed id that ALWAYS refuses, and serial's duplicate key.
with Server(settings={"autovacuum": "off", "timezone": "'UTC'"}) as pg:
    pg.up()
    pg.psql("identity.sql", errors=3)

$ psql -X -a -f identity.sql
-- An identity column: the SQL-standard way to number rows, backed by an implicit sequence.
CREATE TABLE invoice (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer text NOT NULL
);
CREATE TABLE
INSERT INTO invoice (customer) VALUES ('Asha') RETURNING id;
 id 
----
  1
(1 row)

INSERT 0 1
-- A transaction takes the next number and then rolls back.
BEGIN;
BEGIN
INSERT INTO invoice (customer) VALUES ('Ravi') RETURNING id;
 id 
----
  2
(1 row)

INSERT 0 1
ROLLBACK;
ROLLBACK
-- An insert that fails takes a number too.
INSERT INTO invoice (customer) VALUES (NULL);
psql:identity.sql:12: ERROR:  null value in column "customer" of relation "invoice" violates not-null constraint
DETAIL:  Failing row contains (3, null).
INSERT INTO invoice (customer) VALUES ('Meera') RETURNING id;
 id 
----
  4
(1 row)

INSERT 0 1
SELECT id, customer FROM invoice ORDER BY id;
 id | customer 
----+----------
  1 | Asha
  4 | Meera
(2 rows)

-- ALWAYS refuses a value typed by hand, unless the statement says OVERRIDING SYSTEM VALUE.
INSERT INTO invoice (id, customer) VALUES (2, 'Kiran');
psql:identity.sql:16: ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
INSERT INTO invoice (id, customer) OVERRIDING SYSTEM VALUE VALUES (2, 'Kiran');
INSERT 0 1
-- The implicit sequence: its type and limits follow the column.
SELECT pg_get_serial_sequence('invoice', 'id') AS sequence_name;
     sequence_name     
-----------------------
 public.invoice_id_seq
(1 row)

SELECT seqtypid::regtype AS type, seqstart, seqmax, seqcache
FROM pg_sequence WHERE seqrelid = pg_get_serial_sequence('invoice', 'id')::regclass;
  type  | seqstart |       seqmax        | seqcache 
--------+----------+---------------------+----------
 bigint |        1 | 9223372036854775807 |        1
(1 row)

-- serial, the older shorthand: an integer column with a default, which a typed value bypasses.
CREATE TABLE legacy (id serial PRIMARY KEY, note text);
CREATE TABLE
INSERT INTO legacy (id, note) VALUES (1, 'typed by hand');
INSERT 0 1
INSERT INTO legacy (note) VALUES ('from the sequence');
psql:identity.sql:25: ERROR:  duplicate key value violates unique constraint "legacy_pkey"
DETAIL:  Key (id)=(1) already exists.
SELECT seqtypid::regtype AS type, seqmax
FROM pg_sequence WHERE seqrelid = pg_get_serial_sequence('legacy', 'id')::regclass;
  type   |   seqmax   
---------+------------
 integer | 2147483647
(1 row)

-- An identity column: the SQL-standard way to number rows, backed by an implicit sequence.
CREATE TABLE invoice (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer text NOT NULL
);
INSERT INTO invoice (customer) VALUES ('Asha') RETURNING id;
-- A transaction takes the next number and then rolls back.
BEGIN;
INSERT INTO invoice (customer) VALUES ('Ravi') RETURNING id;
ROLLBACK;
-- An insert that fails takes a number too.
INSERT INTO invoice (customer) VALUES (NULL);
INSERT INTO invoice (customer) VALUES ('Meera') RETURNING id;
SELECT id, customer FROM invoice ORDER BY id;
-- ALWAYS refuses a value typed by hand, unless the statement says OVERRIDING SYSTEM VALUE.
INSERT INTO invoice (id, customer) VALUES (2, 'Kiran');
INSERT INTO invoice (id, customer) OVERRIDING SYSTEM VALUE VALUES (2, 'Kiran');
-- The implicit sequence: its type and limits follow the column.
SELECT pg_get_serial_sequence('invoice', 'id') AS sequence_name;
SELECT seqtypid::regtype AS type, seqstart, seqmax, seqcache
FROM pg_sequence WHERE seqrelid = pg_get_serial_sequence('invoice', 'id')::regclass;
-- serial, the older shorthand: an integer column with a default, which a typed value bypasses.
CREATE TABLE legacy (id serial PRIMARY KEY, note text);
INSERT INTO legacy (id, note) VALUES (1, 'typed by hand');
INSERT INTO legacy (note) VALUES ('from the sequence');
SELECT seqtypid::regtype AS type, seqmax
FROM pg_sequence WHERE seqrelid = pg_get_serial_sequence('legacy', 'id')::regclass;

Widening int to bigint later rewrites the whole table

An integer key works until its sequence nears 2,147,483,647, and the fix then costs far more than bigint would have cost on the first day. The next session creates two such keys, an identity column on event and a serial column on click, with 20,000 rows each, and widens both to bigint. Inside the transaction, pg_locks shows the ALTER TABLE holding an AccessExclusiveLockAccessExclusiveLock: The strongest table lock mode in PostgreSQL, taken by many forms of ALTER TABLE and by DROP TABLE, TRUNCATE and VACUUM FULL. It conflicts with every other mode, including the AccessShareLock that a plain SELECT holds. More in “Table locks, lock queues, deadlocks, timeouts” on event until COMMIT, and afterwards the table’s relfilenoderelfilenode: The number that names a table's or index's files on disk (pg_class.relfilenode). It often equals the relation's OID at first, but TRUNCATE, REINDEX and table rewrites give a relation a new one. More in “PostgreSQL architecture: processes, memory, files” is a new number: PostgreSQL wrote the whole table into a new file.

An integer takes 4 bytes and a bigint 8, so no row keeps its old shape. The manual says a type change normally rewrites the table and its indexes, and that ALTER TABLE takes ACCESS EXCLUSIVE unless a form says otherwise. That lock conflicts with every other mode, including the one a plain SELECT takes, so for the whole table rewritetable rewrite: Copying every row of a table into a new file, building its indexes again and putting the new file in place of the old one, as most column type changes and VACUUM FULL do. The table gets a new relfilenode and is locked against all other queries meanwhile. nobody can read the table. Follow the statement through its stages:

The ShareLock the run lists beside it adds nothing that the AccessExclusiveLock does not already cover. Twenty thousand rows take a moment; a table of a few hundred million rows keeps every query waiting for as long as the copy and the index builds last.

The last query compares the two keys afterwards. Both columns are bigint, but only the identity’s sequence was widened with its column: event’s sequence goes up to 9,223,372,036,854,775,807, while the serial sequence of click is still an integer that stops at 2,147,483,647, the same limit as before. ALTER SEQUENCE click_id_seq AS bigint fixes that, and raises the maximum because the old one was integer’s default.

"""Changing an integer key to bigint later: the rewrite, its lock and the sequences, on PostgreSQL 18.6."""

from pg_session import Server

with Server(settings={"autovacuum": "off", "timezone": "'UTC'"}) as pg:
    pg.up()
    pg.psql("widen.sql")

$ psql -X -a -f widen.sql
-- Two keys that started as integer: an identity column and a serial column.
CREATE TABLE event (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, kind text NOT NULL);
CREATE TABLE
CREATE TABLE click (id serial PRIMARY KEY, kind text NOT NULL);
CREATE TABLE
INSERT INTO event (kind) SELECT 'view' FROM generate_series(1, 20000);
INSERT 0 20000
INSERT INTO click (kind) SELECT 'view' FROM generate_series(1, 20000);
INSERT 0 20000
-- Remember the file event lives in (psql keeps the number in a variable and prints nothing).
SELECT relfilenode AS event_file FROM pg_class WHERE oid = 'event'::regclass \gset
-- Widening the column inside a transaction, to see the lock it holds until COMMIT.
BEGIN;
BEGIN
ALTER TABLE event ALTER COLUMN id TYPE bigint;
ALTER TABLE
SELECT mode, granted FROM pg_locks
WHERE locktype = 'relation' AND relation = 'event'::regclass AND pid = pg_backend_pid();
        mode         | granted 
---------------------+---------
 ShareLock           | t
 AccessExclusiveLock | t
(2 rows)

COMMIT;
COMMIT
SELECT relfilenode <> :event_file AS event_rewritten FROM pg_class WHERE oid = 'event'::regclass;
 event_rewritten 
-----------------
 t
(1 row)

-- The identity's sequence was widened with the column; serial's sequence was not.
ALTER TABLE click ALTER COLUMN id TYPE bigint;
ALTER TABLE
SELECT c.relname AS table_name, format_type(a.atttypid, a.atttypmod) AS column_type,
       s.seqtypid::regtype AS sequence_type, s.seqmax
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'id'
JOIN pg_sequence s ON s.seqrelid = pg_get_serial_sequence(c.relname, 'id')::regclass
WHERE c.relname IN ('event', 'click') ORDER BY c.relname;
 table_name | column_type | sequence_type |       seqmax        
------------+-------------+---------------+---------------------
 click      | bigint      | integer       |          2147483647
 event      | bigint      | bigint        | 9223372036854775807
(2 rows)

-- Two keys that started as integer: an identity column and a serial column.
CREATE TABLE event (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, kind text NOT NULL);
CREATE TABLE click (id serial PRIMARY KEY, kind text NOT NULL);
INSERT INTO event (kind) SELECT 'view' FROM generate_series(1, 20000);
INSERT INTO click (kind) SELECT 'view' FROM generate_series(1, 20000);
-- Remember the file event lives in (psql keeps the number in a variable and prints nothing).
SELECT relfilenode AS event_file FROM pg_class WHERE oid = 'event'::regclass \gset
-- Widening the column inside a transaction, to see the lock it holds until COMMIT.
BEGIN;
ALTER TABLE event ALTER COLUMN id TYPE bigint;
SELECT mode, granted FROM pg_locks
WHERE locktype = 'relation' AND relation = 'event'::regclass AND pid = pg_backend_pid();
COMMIT;
SELECT relfilenode <> :event_file AS event_rewritten FROM pg_class WHERE oid = 'event'::regclass;
-- The identity's sequence was widened with the column; serial's sequence was not.
ALTER TABLE click ALTER COLUMN id TYPE bigint;
SELECT c.relname AS table_name, format_type(a.atttypid, a.atttypmod) AS column_type,
       s.seqtypid::regtype AS sequence_type, s.seqmax
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'id'
JOIN pg_sequence s ON s.seqrelid = pg_get_serial_sequence(c.relname, 'id')::regclass
WHERE c.relname IN ('event', 'click') ORDER BY c.relname;

UUIDv7 keys arrive in order, random keys land anywhere

A UUID is a 128-bit identifier that any program can create without asking the database. The next session puts the same 25,000 rows under three keys: a bigint identity, a uuid filled by uuidv7(), and a uuid made from md5(n), which scatters keys over the range as random UUIDs do but repeats on every run. Then it reads each primary key index page by page with the pageinspect extension:

A UUIDv4UUIDv4: A version 4 UUID: 122 random bits plus the bits that mark its version and variant. PostgreSQL makes one with gen_random_uuid() or, from version 18, uuidv4(); new keys of this kind land anywhere in an index. holds 122 random bits; gen_random_uuid() makes one, and PostgreSQL 18 adds the alias uuidv4(). A UUIDv7UUIDv7: A version 7 UUID as RFC 9562 defines it: the Unix time in milliseconds in its first 48 bits, then random bits, so keys made later sort later. PostgreSQL 18 makes one with uuidv7(), and uuid_extract_timestamp() reads its time back. starts with the Unix time in milliseconds in its first 48 bits, and the uuidv7() of PostgreSQL 18 follows it with a sub-millisecond fraction and random bits. Keys made later therefore sort later: in the run, none of the 25,000 UUIDv7 keys sorted below the key inserted just before it, against 12,509 of the scattered keys, about half, as chance predicts.

Where a key sorts decides which leaf page of the B-tree it is written to. Each new UUIDv7 key is the largest so far, so it goes to the rightmost leaf; when that page is full, PostgreSQL starts a new one, leaves the full page at the index’s fillfactorfillfactor: The storage parameter, from 10 to 100 percent, that sets how full inserts pack each page of a table, 100 by default. The rest of every page is kept free for updated versions of its rows, which makes HOT updates more likely. More in “HOT updates, fillfactor, bloat and rebuilds”, 90 % by default, and new keys never go back to it. A scattered key goes to whichever leaf covers its value, and a full leaf in the middle splits: a page splitpage split: What a B-tree index does when a new entry does not fit a full page: it moves part of the page's entries to a new page and adds a link to that page in the parent. A split in the middle of an index leaves two partly empty pages. moves part of its entries to a new page and leaves both part empty. Step through the two indexes:

The last two queries measure where the newest rows went. The 1,000 newest UUIDv7 rows sit on 5 leaf pages; the 1,000 newest scattered rows are spread over 136, every leaf of their index. A time-ordered key keeps a handful of pages busy, while a random key needs the whole index close at hand, and each page it touches may first have to be read from disk. Size follows from the same cause: at 64 % full the scattered index is 41 % larger than the UUIDv7 one, and both UUID indexes are larger than bigint’s because a UUID takes 16 bytes and a bigint 8.

"""bigint, uuidv7() and scattered UUID keys for 25,000 rows: index size, leaf fill and where new keys land."""

from pg_session import Server

# Autovacuum is off so that nothing but the session touches the indexes; the UTC time zone fixes v7_time's offset.
# This cluster is deleted when the run ends, so it skips flushing files to disk (initdb --no-sync, fsync off),
# which halves the run time and changes no number. Never switch fsync off on a server whose data you keep.
SETTINGS = {"autovacuum": "off", "timezone": "'UTC'", "fsync": "off"}

with Server(settings=SETTINGS, initdb=("--no-sync",)) as pg:
    pg.up()
    pg.psql("uuid.sql")

$ psql -X -a -f uuid.sql
-- pageinspect reads index pages; it is for superusers, on a test server like this one.
CREATE EXTENSION pageinspect;
CREATE EXTENSION
-- The same 25,000 rows under three keys; n is the order the rows arrive in.
CREATE TABLE k_bigint (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, n int);
CREATE TABLE
CREATE TABLE k_v7 (id uuid DEFAULT uuidv7() PRIMARY KEY, n int);
CREATE TABLE
CREATE TABLE k_random (id uuid PRIMARY KEY, n int);
CREATE TABLE
INSERT INTO k_bigint (n) SELECT g FROM generate_series(1, 25000) g;
INSERT 0 25000
INSERT INTO k_v7 (n) SELECT g FROM generate_series(1, 25000) g;
INSERT 0 25000
-- md5(n) scatters keys over the whole range as uuidv4() does, but gives the same keys on every run.
INSERT INTO k_random SELECT md5(g::text)::uuid, g FROM generate_series(1, 25000) g;
INSERT 0 25000
-- Row counts for the planner, so the queries on index pages below take well under a second.
ANALYZE k_bigint, k_v7, k_random;
ANALYZE
-- The version digit, and the time a version 7 UUID carries in its first 48 bits.
SELECT uuid_extract_version(uuidv4()) AS uuidv4, uuid_extract_version(gen_random_uuid()) AS gen_random_uuid,
       uuid_extract_version(uuidv7()) AS uuidv7;
 uuidv4 | gen_random_uuid | uuidv7 
--------+-----------------+--------
      4 |               4 |      7
(1 row)

SELECT uuid_extract_timestamp('01a39e94-0dc0-7000-8000-000000000001') AS v7_time,
       uuid_extract_timestamp(uuidv4()) AS v4_time;
        v7_time         | v4_time 
------------------------+---------
 2027-02-10 09:30:00+00 | 
(1 row)

-- How many keys sort below the key of the row inserted just before them?
SELECT (SELECT count(*) FROM (SELECT id < lag(id) OVER (ORDER BY n) AS back FROM k_v7) s WHERE back) AS v7,
       (SELECT count(*) FROM (SELECT id < lag(id) OVER (ORDER BY n) AS back FROM k_random) s WHERE back) AS random;
 v7 | random 
----+--------
  0 |  12509
(1 row)

-- Size, leaf pages and how full the leaf pages are, for each primary key index.
SELECT c.relname AS index, pg_size_pretty(pg_relation_size(c.oid)) AS size, count(*) AS leaf_pages,
       round(100 * (1 - avg(s.free_size) / 8192)) AS leaf_used_pct
FROM pg_class c, bt_multi_page_stats(c.relname::text, 1, -1) s
WHERE c.relname IN ('k_bigint_pkey', 'k_v7_pkey', 'k_random_pkey') AND s.type = 'l'
GROUP BY c.oid, c.relname ORDER BY pg_relation_size(c.oid);
     index     |  size   | leaf_pages | leaf_used_pct 
---------------+---------+------------+---------------
 k_bigint_pkey | 568 kB  |         69 |            89
 k_v7_pkey     | 784 kB  |         96 |            90
 k_random_pkey | 1104 kB |        136 |            64
(3 rows)

-- Which leaf pages hold the keys of the 1,000 newest rows (n > 24000)? htid is the row an entry points to.
SELECT count(DISTINCT b.blkno) AS v7_leaf_pages
FROM bt_multi_page_stats('k_v7_pkey', 1, -1) b, bt_page_items('k_v7_pkey', b.blkno) i
WHERE b.type = 'l' AND i.htid IN (SELECT ctid FROM k_v7 WHERE n > 24000);
 v7_leaf_pages 
---------------
             5
(1 row)

SELECT count(DISTINCT b.blkno) AS random_leaf_pages
FROM bt_multi_page_stats('k_random_pkey', 1, -1) b, bt_page_items('k_random_pkey', b.blkno) i
WHERE b.type = 'l' AND i.htid IN (SELECT ctid FROM k_random WHERE n > 24000);
 random_leaf_pages 
-------------------
               136
(1 row)

-- pageinspect reads index pages; it is for superusers, on a test server like this one.
CREATE EXTENSION pageinspect;
-- The same 25,000 rows under three keys; n is the order the rows arrive in.
CREATE TABLE k_bigint (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, n int);
CREATE TABLE k_v7 (id uuid DEFAULT uuidv7() PRIMARY KEY, n int);
CREATE TABLE k_random (id uuid PRIMARY KEY, n int);
INSERT INTO k_bigint (n) SELECT g FROM generate_series(1, 25000) g;
INSERT INTO k_v7 (n) SELECT g FROM generate_series(1, 25000) g;
-- md5(n) scatters keys over the whole range as uuidv4() does, but gives the same keys on every run.
INSERT INTO k_random SELECT md5(g::text)::uuid, g FROM generate_series(1, 25000) g;
-- Row counts for the planner, so the queries on index pages below take well under a second.
ANALYZE k_bigint, k_v7, k_random;
-- The version digit, and the time a version 7 UUID carries in its first 48 bits.
SELECT uuid_extract_version(uuidv4()) AS uuidv4, uuid_extract_version(gen_random_uuid()) AS gen_random_uuid,
       uuid_extract_version(uuidv7()) AS uuidv7;
SELECT uuid_extract_timestamp('01a39e94-0dc0-7000-8000-000000000001') AS v7_time,
       uuid_extract_timestamp(uuidv4()) AS v4_time;
-- How many keys sort below the key of the row inserted just before them?
SELECT (SELECT count(*) FROM (SELECT id < lag(id) OVER (ORDER BY n) AS back FROM k_v7) s WHERE back) AS v7,
       (SELECT count(*) FROM (SELECT id < lag(id) OVER (ORDER BY n) AS back FROM k_random) s WHERE back) AS random;
-- Size, leaf pages and how full the leaf pages are, for each primary key index.
SELECT c.relname AS index, pg_size_pretty(pg_relation_size(c.oid)) AS size, count(*) AS leaf_pages,
       round(100 * (1 - avg(s.free_size) / 8192)) AS leaf_used_pct
FROM pg_class c, bt_multi_page_stats(c.relname::text, 1, -1) s
WHERE c.relname IN ('k_bigint_pkey', 'k_v7_pkey', 'k_random_pkey') AND s.type = 'l'
GROUP BY c.oid, c.relname ORDER BY pg_relation_size(c.oid);
-- Which leaf pages hold the keys of the 1,000 newest rows (n > 24000)? htid is the row an entry points to.
SELECT count(DISTINCT b.blkno) AS v7_leaf_pages
FROM bt_multi_page_stats('k_v7_pkey', 1, -1) b, bt_page_items('k_v7_pkey', b.blkno) i
WHERE b.type = 'l' AND i.htid IN (SELECT ctid FROM k_v7 WHERE n > 24000);
SELECT count(DISTINCT b.blkno) AS random_leaf_pages
FROM bt_multi_page_stats('k_random_pkey', 1, -1) b, bt_page_items('k_random_pkey', b.blkno) i
WHERE b.type = 'l' AND i.htid IN (SELECT ctid FROM k_random WHERE n > 24000);

Two functions read the fields of a UUID. uuid_extract_version() returned 4 for uuidv4() and gen_random_uuid() and 7 for uuidv7(). uuid_extract_timestamp() read the time out of a fixed UUIDv7 literal, so that the run repeats, and returned NULL for a version 4 key. That is the price of time-ordered keys: anyone who sees a UUIDv7 can read when it was made. The UUID generator makes both versions and shows the time inside a version 7 key.

Which key fits depends on who creates the ids and what they may give away. Answer the questions for your own table:

In short: a bigint identity when only the database makes ids, a UUIDv7 when ids come from outside and may show their time, and a random UUID when an id must show nothing.

Generated columns are virtual by default, stored when you need an index

The orders table of the last session keeps an amount, an 18 % tax computed from it and a total. Both computed columns are generated columnsgenerated column: A column computed by an immutable expression from other columns of the same row, which a statement cannot set. In PostgreSQL 18 it is virtual by default, computed when read, or STORED, computed when its row is written.: tax is declared with no keyword and total with STORED. After an UPDATE changed order 1’s amount to 1200.00, both followed on their own, with a tax of 216.00 and a total of 1416.00. An INSERT that tried to set the tax itself failed, because a generated column takes no value from a statement.

Generated columns come in two kinds, and PostgreSQL 18 makes the virtual kind the default. A virtual column has no bytes on disk: its expression runs each time a query reads it, and pg_attribute marks it with attgenerated v. A stored column is computed when its row is inserted or updated and kept in the row like any other value, marked s. Either kind may use only immutable functions and the columns of its own row, with no subqueries and no other generated column.

The session then tries the limits of the virtual kind. An index on tax fails with indexes on virtual generated columns are not supported, while the same index on the stored total is built. A virtual column whose expression calls with_tax(), a SQL function the session created, fails because virtual columns may call built-in functions only; the same expression is accepted as STORED. ANALYZE gathers no statistics for virtual columns, so pg_stats lists amount, gross, id and total but not tax, and extended statisticsextended statistics: Statistics that CREATE STATISTICS defines over several columns or over an expression, such as functional dependencies, combined distinct counts and lists of common combinations. ANALYZE fills them; they are not indexes. More in “Planner statistics and extended statistics” on tax are refused as well.

"""Virtual and stored generated columns on PostgreSQL 18.6: what each computes, stores and refuses."""

from pg_session import Server

# The session expects four errors: a value for a generated column, an index on a virtual column, a virtual column
# that calls a user-defined function, and statistics on a virtual column.
with Server(settings={"autovacuum": "off", "timezone": "'UTC'"}) as pg:
    pg.up()
    pg.psql("generated.sql", errors=4)

$ psql -X -a -f generated.sql
-- An 18 % tax as a virtual generated column (the default kind) and the total as a stored one.
CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL,
  tax numeric(12,2) GENERATED ALWAYS AS (round(amount * 0.18, 2)),
  total numeric(12,2) GENERATED ALWAYS AS (amount + round(amount * 0.18, 2)) STORED
);
CREATE TABLE
INSERT INTO orders (amount) VALUES (1000.00), (249.99);
INSERT 0 2
UPDATE orders SET amount = 1200.00 WHERE id = 1;
UPDATE 1
SELECT * FROM orders ORDER BY id;
 id | amount  |  tax   |  total  
----+---------+--------+---------
  1 | 1200.00 | 216.00 | 1416.00
  2 |  249.99 |  45.00 |  294.99
(2 rows)

-- attgenerated: v for virtual (computed when read), s for stored (computed when written).
SELECT attname, attgenerated FROM pg_attribute
WHERE attrelid = 'orders'::regclass AND attnum > 0 ORDER BY attnum;
 attname | attgenerated 
---------+--------------
 id      | 
 amount  | 
 tax     | v
 total   | s
(4 rows)

-- Neither kind takes a value from the statement.
INSERT INTO orders (amount, tax) VALUES (10.00, 1.80);
psql:generated.sql:15: ERROR:  cannot insert a non-DEFAULT value into column "tax"
DETAIL:  Column "tax" is a generated column.
-- An index needs the stored kind.
CREATE INDEX orders_tax_idx ON orders (tax);
psql:generated.sql:17: ERROR:  indexes on virtual generated columns are not supported
CREATE INDEX orders_total_idx ON orders (total);
CREATE INDEX
-- A virtual column may call built-in functions only; a stored one may call your own.
CREATE FUNCTION with_tax(numeric) RETURNS numeric LANGUAGE sql IMMUTABLE RETURN round($1 * 1.18, 2);
CREATE FUNCTION
ALTER TABLE orders ADD COLUMN gross numeric GENERATED ALWAYS AS (with_tax(amount)) VIRTUAL;
psql:generated.sql:21: ERROR:  generation expression uses user-defined function
DETAIL:  Virtual generated columns that make use of user-defined functions are not yet supported.
-- Adding a virtual column leaves the table's file alone; adding a stored one rewrites the table.
SELECT relfilenode AS orders_file FROM pg_class WHERE oid = 'orders'::regclass \gset
ALTER TABLE orders ADD COLUMN tax_cents bigint GENERATED ALWAYS AS (round(amount * 18)::bigint) VIRTUAL;
ALTER TABLE
SELECT relfilenode <> :orders_file AS rewritten_by_virtual FROM pg_class WHERE oid = 'orders'::regclass;
 rewritten_by_virtual 
----------------------
 f
(1 row)

ALTER TABLE orders ADD COLUMN gross numeric GENERATED ALWAYS AS (with_tax(amount)) STORED;
ALTER TABLE
SELECT relfilenode <> :orders_file AS rewritten_by_stored FROM pg_class WHERE oid = 'orders'::regclass;
 rewritten_by_stored 
---------------------
 t
(1 row)

SELECT id, tax_cents, gross FROM orders ORDER BY id;
 id | tax_cents |  gross  
----+-----------+---------
  1 |     21600 | 1416.00
  2 |      4500 |  294.99
(2 rows)

-- ANALYZE skips virtual columns, and PostgreSQL 18 refuses extended statistics on them as well.
ANALYZE orders;
ANALYZE
SELECT attname FROM pg_stats WHERE tablename = 'orders' ORDER BY attname;
 attname 
---------
 amount
 gross
 id
 total
(4 rows)

CREATE STATISTICS orders_tax_stats ON (tax) FROM orders;
psql:generated.sql:32: ERROR:  statistics creation on virtual generated columns is not supported

-- An 18 % tax as a virtual generated column (the default kind) and the total as a stored one.
CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL,
  tax numeric(12,2) GENERATED ALWAYS AS (round(amount * 0.18, 2)),
  total numeric(12,2) GENERATED ALWAYS AS (amount + round(amount * 0.18, 2)) STORED
);
INSERT INTO orders (amount) VALUES (1000.00), (249.99);
UPDATE orders SET amount = 1200.00 WHERE id = 1;
SELECT * FROM orders ORDER BY id;
-- attgenerated: v for virtual (computed when read), s for stored (computed when written).
SELECT attname, attgenerated FROM pg_attribute
WHERE attrelid = 'orders'::regclass AND attnum > 0 ORDER BY attnum;
-- Neither kind takes a value from the statement.
INSERT INTO orders (amount, tax) VALUES (10.00, 1.80);
-- An index needs the stored kind.
CREATE INDEX orders_tax_idx ON orders (tax);
CREATE INDEX orders_total_idx ON orders (total);
-- A virtual column may call built-in functions only; a stored one may call your own.
CREATE FUNCTION with_tax(numeric) RETURNS numeric LANGUAGE sql IMMUTABLE RETURN round($1 * 1.18, 2);
ALTER TABLE orders ADD COLUMN gross numeric GENERATED ALWAYS AS (with_tax(amount)) VIRTUAL;
-- Adding a virtual column leaves the table's file alone; adding a stored one rewrites the table.
SELECT relfilenode AS orders_file FROM pg_class WHERE oid = 'orders'::regclass \gset
ALTER TABLE orders ADD COLUMN tax_cents bigint GENERATED ALWAYS AS (round(amount * 18)::bigint) VIRTUAL;
SELECT relfilenode <> :orders_file AS rewritten_by_virtual FROM pg_class WHERE oid = 'orders'::regclass;
ALTER TABLE orders ADD COLUMN gross numeric GENERATED ALWAYS AS (with_tax(amount)) STORED;
SELECT relfilenode <> :orders_file AS rewritten_by_stored FROM pg_class WHERE oid = 'orders'::regclass;
SELECT id, tax_cents, gross FROM orders ORDER BY id;
-- ANALYZE skips virtual columns, and PostgreSQL 18 refuses extended statistics on them as well.
ANALYZE orders;
SELECT attname FROM pg_stats WHERE tablename = 'orders' ORDER BY attname;
CREATE STATISTICS orders_tax_stats ON (tax) FROM orders;

Adding a column shows the other side of the choice. Adding the virtual tax_cents left the table’s file alone, while adding the stored gross gave the table a new relfilenode. The manual lists an added stored generated column, like an added identity column, among the changes that rewrite the table and its indexes, and says a virtual one never needs a rewrite.

Three questions decide the choice, against a third option: a plain column that the application fills in.

Practise: fix a schema that stores money in float8

A billing table was written before anyone thought about types: id serial, amount float8, tax float8 and total float8, where the application computes the 18 % tax and the total itself, and one older job forgets the tax. Write down, column by column, what you would declare instead and why, then answer the questions. They mix all six sections and come back to what you missed.

One schema that meets every point: id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, amount numeric(12,2) NOT NULL, a virtual tax numeric(12,2) GENERATED ALWAYS AS (round(amount * 0.18, 2)), and total numeric(12,2) GENERATED ALWAYS AS (amount + round(amount * 0.18, 2)) STORED when reports search or sort by it.

Interview questions

Schema interviews often open with the gaps in an id column, then carry this page’s money and key choices into production: a ledger whose totals drift, or inserts that slow down as a table grows. The first question is answered right here; the other two open the module’s bank, which shows what each one tests and how a strong answer starts.

Key takeaways

• numeric keeps every decimal digit, so 0.1 + 0.2 = 0.3 holds and ten payments of 0.10 sum to 1.0, while double precision drifts and suits measurements, not money.
• The money type stores minor units whose scale and symbol come from lc_monetary, so one row read $1,234.50 or ￥123,450; keep amounts as numeric or as minor units in a bigint.
• An identity column’s sequence never takes a value back, so a rollback and a failed insert left ids 1 and 4, and numbers that must be consecutive need a locked counter.
• Changing an integer key to bigint rewrote the table under an AccessExclusiveLock, and a serial column’s sequence stayed an integer, so declare keys bigint from the start.
• uuidv7() keys grow with time and fill leaf pages to 90 %: the newest 1,000 rows sat on 5 pages, while scattered keys reached all 136 leaves of an index 41 % larger.
• Generated columns are virtual by default in PostgreSQL 18, computed when read and cheap to add, and must be STORED to take an index, a function of your own or statistics.