Support for 8-byte TOAST values, round two

On Fri, 8 May 2026 15:07:13 +0900
Michael Paquier <michael@paquier.xyz> wrote:

Hi all,

This is a follow-up of the previous thread about $subject, with a
reworked patch set for discussion in v20, as I care about the subject:
/messages/by-id/aFOnKHG7Wn-Srnpv@paquier.xyz

The main feedback of the previous thread is that the previous
implementation with its callbacks for each vartag was not liked much,
and their were concerns with pointer redirections and performance.
This patch set uses what I am calling the "brutal" approach, relying
on a vartag_external of a varlena or the atttype of the TOAST relation
to decide which external toast pointer we should use. This uses no
function pointers, and patches the code to deal with Oid or Oid8 TOAST
values where it matters. So, this time, performance cannot really be
an issue.

The patch set is structured so as all the ground work happens first
(most of it comes from the previous patch set, reorganized a bit), and
the introduction of the varatt pieces are last, based on the following
rules:
- A table can use a 8-byte TOAST value with a new reloption, named
toast_value_type that can be set to "oid" (default) or "oid8",
creating a TOAST table with a value of the assigned type. This
includes support for dumps as well as binary upgrades, so as the
atttype of the chunk_id of the TOAST table is preserved. A table with
a TOAST type assigned cannot be changed to a different type through a
VACUUM FULL or a rewrite, as a matter of implementation simplicity.
- Renames and cleanup of various areas related to varatt_external,
renaming things to use OID.
- The 8-byte TOAST values rely on a Oid8, whose value is retrieved
from the control file extended by 4 bytes. The code supports
wraparound of values so as we don't assign anything between 0 and
FirstNormalObjectId for the lower bytes, same way as before.

The last patch introduces a new vartag_external and the new
varatt_external_oid8, with an Oid8 as value. Well, not exactly, the
patch uses two uint32 fields so as the structure is packed without
padding, as of:
typedef struct varatt_external_oid8
{
int32 va_rawsize;
uint32 va_extinfo;
uint32 va_valueid_lo;
uint32 va_valueid_hi;
Oid va_toastrelid;
} varatt_external_oid8;

Note that if applying all the patches except the last one, the code
would use an varatt_external_oid with an oid8 TOAST table. This
works, the split is to make reviews easier. An oid8 TOAST table
always uses a varatt_external_oid8.

I have done a lot of back-and-forth in the patch to try to find a good
balance between the manipulation of the varlenas in the detoast and
compression paths, as well as reorderbuffer.c and amcheck. And I have
finished with the attached, which is kind of nice. The last patch has
a low footprint:
9 files changed, 537 insertions(+), 208 deletions(-)

I applied the patches to the master branch and run a simple test.

After running:

$ pg_resetwal -o 4300000000 -D data

CREATE TABLE tbl_oid8(t text) with (toast_value_type = 'oid8');
ALTER TABLE tbl_oid8 ALTER t SET STORAGE EXTERNAL;
CREATE TABLE tbl_oid(t text) with (toast_value_type = 'oid');
ALTER TABLE tbl_oid ALTER t SET STORAGE EXTERNAL;

INSERT INTO tbl_oid8 VALUES (repeat(md5('x'),100));
INSERT INTO tbl_oid VALUES (repeat(md5('x'),100));

SELECT tableoid, t = repeat(md5('x'),100) FROM tbl_oid8;
tableoid | ?column?
----------+----------
5032704 | t
(1 row)

SELECT chunk_id, chunk_seq FROM pg_toast.pg_toast_5032704;
chunk_id | chunk_seq
------------+-----------
4300000010 | 0
4300000010 | 1
(2 rows)

SELECT tableoid, t = repeat(md5('x'),100) FROM tbl_oid;
tableoid | ?column?
----------+----------
5032709 | t
(1 row)

SELECT chunk_id, chunk_seq FROM pg_toast.pg_toast_5032709;
chunk_id | chunk_seq
----------+-----------
5032715 | 0
5032715 | 1
(2 rows)

This appears to work as expected.

I noticed one issue, though.
When toast_value_type is specified as something other than oid or oid8, the error
message is:

postgres=# CREATE TABLE tbl(t text) with (toast_value_type = 'x');
ERROR: invalid value for enum option "toast_value_type": x
DETAIL: Valid values are "oid".

The detail message should be updated to include oid8.

I also have a few small comments on the patches.

- 0009

(1)
The commit message says:

TOAST pointers still rely on varatt_external and a single vartag, with
all the values inserted in the bigint TOAST tables fed from the existing
OID value generator.

This made me a bit confused because chunk_ids insereted into TOAST tables using oid8
are generated by GetNewObjectId8().

+		else if (toast_typid == OID8OID)
+			toast_pointer.va_valueid = GetNewObjectId8();

This value is later cast to Oid here, so what is actually stored is a 4-byte chunk_id,
but saying that the values are "fed from the existing OID value generator" seems
a bit inaccurate to me.

(2)
 	/*
-	 * Choose an OID to use as the value ID for this toast value.
+	 * Choose a new value to use as the value ID for this toast value, be it
+	 * for OID or int8-based TOAST relations.

This patch relies on oid8, introduced by b139bd3b6ef, rather than int8, so, I
wonder if this comment sould also be updated.

(3)
 belonging to the owning table.  Every
-<acronym>TOAST</acronym> table has the columns <structfield>chunk_id</structfield> (an OID
-identifying the particular <acronym>TOAST</acronym>ed value),
+<acronym>TOAST</acronym> table has the columns
+<structfield>chunk_id</structfield> (an OID or an 8-byte integer identifying
+the particular <acronym>TOAST</acronym>ed value),
 <structfield>chunk_seq</structfield> (a sequence number for the chunk within its value),

Similarly, I think the documentation should mention oid8 rather than 8-byte
integer. That would also be consistent with the CREATE TABLE documentation for
the toast_value_type parameter.

- 0011

(4)
+					/*
+					 * Check if this value already exists in the new toast
+					 * table (corner case during table rewrite with multiple
+					 * versions of the same row).
+					 */
+					if (toastrel_valueid_exists(toastrel, va_valueid))
+					{
+						/* Match, so short-circuit the data storage loop below */
+						data_todo = 0;
+					}

The same code appears later, together with a more detailed comment. How about
moving the detailed comment here instead, or simply referring to the later comment?

(5)
+/* Is varlena datum a pointer to on-disk toasted data with 8-byte value ID? */
+static inline bool
+VARATT_IS_EXTERNAL_ONDISK_OID8(const void *PTR)
+{
+	return VARATT_IS_EXTERNAL(PTR) && VARTAG_EXTERNAL(PTR) == VARTAG_ONDISK_OID8;
 }

This macro is defined but not used. Is it intended to be used in places that
check VARTAG_ONDISK_OID8 directly, for example:

+ if (VARTAG_EXTERNAL(attr) == VARTAG_ONDISK_OID8)

?

(6)

 any.  Allowing for the varlena header bytes,
-the total size of an on-disk <acronym>TOAST</acronym> pointer datum is therefore 18
-bytes regardless of the actual size of the represented value.
+the total size of an on-disk <acronym>TOAST</acronym> pointer datum is 18
+bytes when using an OID as <structfield>chunk_id</structfield>, or 22 bytes
+when using an 8-byte integer, regardless of the actual size of the represented
+value.
 </para>

Should this also refer to Oid8 rather than 8-byte integner, for consistency?

Regards,
Yugo Nagata

--
Yugo Nagata <nagata@sraoss.co.jp>

On Thu, 30 Jul 2026 13:38:18 +0900
Yugo Nagata <nagata@sraoss.co.jp> wrote:

On Fri, 8 May 2026 15:07:13 +0900
Michael Paquier <michael@paquier.xyz> wrote:

Hi all,

This is a follow-up of the previous thread about $subject, with a
reworked patch set for discussion in v20, as I care about the subject:
/messages/by-id/aFOnKHG7Wn-Srnpv@paquier.xyz

The main feedback of the previous thread is that the previous
implementation with its callbacks for each vartag was not liked much,
and their were concerns with pointer redirections and performance.
This patch set uses what I am calling the "brutal" approach, relying
on a vartag_external of a varlena or the atttype of the TOAST relation
to decide which external toast pointer we should use. This uses no
function pointers, and patches the code to deal with Oid or Oid8 TOAST
values where it matters. So, this time, performance cannot really be
an issue.

The patch set is structured so as all the ground work happens first
(most of it comes from the previous patch set, reorganized a bit), and
the introduction of the varatt pieces are last, based on the following
rules:
- A table can use a 8-byte TOAST value with a new reloption, named
toast_value_type that can be set to "oid" (default) or "oid8",
creating a TOAST table with a value of the assigned type. This
includes support for dumps as well as binary upgrades, so as the
atttype of the chunk_id of the TOAST table is preserved. A table with
a TOAST type assigned cannot be changed to a different type through a
VACUUM FULL or a rewrite, as a matter of implementation simplicity.
- Renames and cleanup of various areas related to varatt_external,
renaming things to use OID.
- The 8-byte TOAST values rely on a Oid8, whose value is retrieved
from the control file extended by 4 bytes. The code supports
wraparound of values so as we don't assign anything between 0 and
FirstNormalObjectId for the lower bytes, same way as before.

The last patch introduces a new vartag_external and the new
varatt_external_oid8, with an Oid8 as value. Well, not exactly, the
patch uses two uint32 fields so as the structure is packed without
padding, as of:
typedef struct varatt_external_oid8
{
int32 va_rawsize;
uint32 va_extinfo;
uint32 va_valueid_lo;
uint32 va_valueid_hi;
Oid va_toastrelid;
} varatt_external_oid8;

Note that if applying all the patches except the last one, the code
would use an varatt_external_oid with an oid8 TOAST table. This
works, the split is to make reviews easier. An oid8 TOAST table
always uses a varatt_external_oid8.

I have done a lot of back-and-forth in the patch to try to find a good
balance between the manipulation of the varlenas in the detoast and
compression paths, as well as reorderbuffer.c and amcheck. And I have
finished with the attached, which is kind of nice. The last patch has
a low footprint:
9 files changed, 537 insertions(+), 208 deletions(-)

I applied the patches to the master branch and run a simple test.

After running:

$ pg_resetwal -o 4300000000 -D data

CREATE TABLE tbl_oid8(t text) with (toast_value_type = 'oid8');
ALTER TABLE tbl_oid8 ALTER t SET STORAGE EXTERNAL;
CREATE TABLE tbl_oid(t text) with (toast_value_type = 'oid');
ALTER TABLE tbl_oid ALTER t SET STORAGE EXTERNAL;

INSERT INTO tbl_oid8 VALUES (repeat(md5('x'),100));
INSERT INTO tbl_oid VALUES (repeat(md5('x'),100));

SELECT tableoid, t = repeat(md5('x'),100) FROM tbl_oid8;
tableoid | ?column?
----------+----------
5032704 | t
(1 row)

SELECT chunk_id, chunk_seq FROM pg_toast.pg_toast_5032704;
chunk_id | chunk_seq
------------+-----------
4300000010 | 0
4300000010 | 1
(2 rows)

SELECT tableoid, t = repeat(md5('x'),100) FROM tbl_oid;
tableoid | ?column?
----------+----------
5032709 | t
(1 row)

SELECT chunk_id, chunk_seq FROM pg_toast.pg_toast_5032709;
chunk_id | chunk_seq
----------+-----------
5032715 | 0
5032715 | 1
(2 rows)

This appears to work as expected.

I also run a simple performance test. I measured the TPS on
the master branch and with the current patches applied under the
following conditions:

Prepare:
drop table if exists tbl;
create table tbl (id int primary key, j int, t text);
alter table tbl alter column t set storage external;
insert into tbl select i, i, repeat(md5(i::text),100)
from generate_series(1,10000) i;

query.sql:
\set id random(1, 10000)
update tbl set (j,t) = (j + 1, repeat(md5((j+1)::text),100))
where id = :id;

Running pbench three times:

$ pgbench test -f query.sql -c 8 -j 4 -T 100

Results:

master: 2686.229104, 2697.655096, 2687.894343
patched: 2685.292256, 2692.080214, 2690.005753

I could not observe any performance degradation with the curernt patches,
at least in my environment.

I haven't tested whether the previous version of the patches showed the
performance degradation that has been a concern, though.

I noticed one issue, though.
When toast_value_type is specified as something other than oid or oid8, the error
message is:

postgres=# CREATE TABLE tbl(t text) with (toast_value_type = 'x');
ERROR: invalid value for enum option "toast_value_type": x
DETAIL: Valid values are "oid".

The detail message should be updated to include oid8.

I also have a few small comments on the patches.

- 0009

(1)
The commit message says:

TOAST pointers still rely on varatt_external and a single vartag, with
all the values inserted in the bigint TOAST tables fed from the existing
OID value generator.

This made me a bit confused because chunk_ids insereted into TOAST tables using oid8
are generated by GetNewObjectId8().

+		else if (toast_typid == OID8OID)
+			toast_pointer.va_valueid = GetNewObjectId8();

This value is later cast to Oid here, so what is actually stored is a 4-byte chunk_id,
but saying that the values are "fed from the existing OID value generator" seems
a bit inaccurate to me.

(2)
/*
-	 * Choose an OID to use as the value ID for this toast value.
+	 * Choose a new value to use as the value ID for this toast value, be it
+	 * for OID or int8-based TOAST relations.

This patch relies on oid8, introduced by b139bd3b6ef, rather than int8, so, I
wonder if this comment sould also be updated.

(3)
belonging to the owning table.  Every
-<acronym>TOAST</acronym> table has the columns <structfield>chunk_id</structfield> (an OID
-identifying the particular <acronym>TOAST</acronym>ed value),
+<acronym>TOAST</acronym> table has the columns
+<structfield>chunk_id</structfield> (an OID or an 8-byte integer identifying
+the particular <acronym>TOAST</acronym>ed value),
<structfield>chunk_seq</structfield> (a sequence number for the chunk within its value),

Similarly, I think the documentation should mention oid8 rather than 8-byte
integer. That would also be consistent with the CREATE TABLE documentation for
the toast_value_type parameter.

- 0011

(4)
+					/*
+					 * Check if this value already exists in the new toast
+					 * table (corner case during table rewrite with multiple
+					 * versions of the same row).
+					 */
+					if (toastrel_valueid_exists(toastrel, va_valueid))
+					{
+						/* Match, so short-circuit the data storage loop below */
+						data_todo = 0;
+					}

The same code appears later, together with a more detailed comment. How about
moving the detailed comment here instead, or simply referring to the later comment?

(5)
+/* Is varlena datum a pointer to on-disk toasted data with 8-byte value ID? */
+static inline bool
+VARATT_IS_EXTERNAL_ONDISK_OID8(const void *PTR)
+{
+	return VARATT_IS_EXTERNAL(PTR) && VARTAG_EXTERNAL(PTR) == VARTAG_ONDISK_OID8;
}

This macro is defined but not used. Is it intended to be used in places that
check VARTAG_ONDISK_OID8 directly, for example:

+ if (VARTAG_EXTERNAL(attr) == VARTAG_ONDISK_OID8)

?

(6)

any.  Allowing for the varlena header bytes,
-the total size of an on-disk <acronym>TOAST</acronym> pointer datum is therefore 18
-bytes regardless of the actual size of the represented value.
+the total size of an on-disk <acronym>TOAST</acronym> pointer datum is 18
+bytes when using an OID as <structfield>chunk_id</structfield>, or 22 bytes
+when using an 8-byte integer, regardless of the actual size of the represented
+value.
</para>

Should this also refer to Oid8 rather than 8-byte integner, for consistency?

I have one additional, very trivial comment:

(7)

 	/*
-	 * Open the toast relation and its indexes
+	 * Determine the pointer type from the datum's vartag and extract the
+	 * toast relation OID and value ID accordingly.  The vartag tells us
+	 * everything we need — no TOAST table schema lookup required.

Non-ASCII hyphen is used in this comment. This may not be prohibitted,
but I don't think it's common in the PostgreSQL code base.

Regards,
Yugo Nagata

--
Yugo Nagata <nagata@sraoss.co.jp>

I am mostly worried about performance degradation caused by the toast index
not fitting in memory.

This is more likely to happen for huge tables, the case for which the
8-byte toast pointer is proposed.

Have you compared the index sizes of OID and 8-byte ID ?

It is possible, that they are of the same size because of alignment, so
there would not be any difference in size.

On Thu, Jul 30, 2026 at 10:01 AM Yugo Nagata <nagata@sraoss.co.jp> wrote:

On Thu, Jul 30, 2026 at 01:16:36PM +0200, Hannu Krosing wrote:

Have you compared the index sizes of OID and 8-byte ID ?

Please do not top-post. This breaks the logical flow of the community
thread. Please see the following about bottom-posting:
https://en.wikipedia.org/wiki/Posting_style#Bottom-posting

It is possible, that they are of the same size because of alignment, so
there would not be any difference in size.

I suspect that the size is the same due to 8-byte alignment (not done
a measurement on that with a fixed loading pattern). Just please note
that the OID8 behavior is available on an opt-in as a reloption, with
OID still being the default. So there is no impact by default,
neither is there an impact across upgrades. A relation set with an
OID8 is preserved across pg_upgrade moving forward to newer versions,
of course.
--
Michael

On Fri, Jul 31, 2026 at 4:37 AM Michael Paquier <michael@paquier.xyz> wrote:

On Thu, Jul 30, 2026 at 01:16:36PM +0200, Hannu Krosing wrote:

Have you compared the index sizes of OID and 8-byte ID ?

Please do not top-post. This breaks the logical flow of the community
thread. Please see the following about bottom-posting:
https://en.wikipedia.org/wiki/Posting_style#Bottom-posting

Sorry about that, I need to get an email client that makes it easier
to see when I'm about to do that :p

It is possible, that they are of the same size because of alignment, so
there would not be any difference in size.

I suspect that the size is the same due to 8-byte alignment (not done
a measurement on that with a fixed loading pattern).

I ran a quick test and at least on x64 this is the case indeed:

hannuk=# create table oid_vs_bigint(id oid primary key, int8 bigint unique);
CREATE TABLE
hannuk=# insert into oid_vs_bigint select i, i from
generate_series(1,100000) g(i);
INSERT 0 100000
hannuk=# select indexrelid::regclass, pg_relation_size(indexrelid)
from pg_index where indrelid = 'oid_vs_bigint'::regclass;
indexrelid │ pg_relation_size
────────────────────────┼──────────────────
oid_vs_bigint_pkey │ 2260992
oid_vs_bigint_int8_key │ 2260992
(2 rows)

So, it's not slower because of size, but it's not faster either.

Just please note
that the OID8 behavior is available on an opt-in as a reloption, with
OID still being the default. So there is no impact by default,
neither is there an impact across upgrades. A relation set with an
OID8 is preserved across pg_upgrade moving forward to newer versions,
of course.

Yes, any new TOAST types should live alongside the existing one and
not try to replace them in one go.

My Direct Toast design does the same: either a reloption or a global
GUC for selecting on write and automatic selection by the VARATT type
on reads.

In the case of Direct Toast this also means that both TOAST types can
coexist in the same table for both the main heap and TOAST storage,
and you can switch back and forth at any time, for example, switching
to direct only when you run into the 4B OID limit.

For OID8 I guess you can still have it, but you will need to create a
second oid8 toast table because the toast table format changed in
incompatible ways.

----
Hannu

On Fri, 31 Jul 2026 at 12:58, Hannu Krosing <hannuk@google.com> wrote:

I ran a quick test and at least on x64 this is the case indeed:

hannuk=# create table oid_vs_bigint(id oid primary key, int8 bigint unique);
CREATE TABLE
hannuk=# insert into oid_vs_bigint select i, i from
generate_series(1,100000) g(i);
INSERT 0 100000
hannuk=# select indexrelid::regclass, pg_relation_size(indexrelid)
from pg_index where indrelid = 'oid_vs_bigint'::regclass;
indexrelid │ pg_relation_size
────────────────────────┼──────────────────
oid_vs_bigint_pkey │ 2260992
oid_vs_bigint_int8_key │ 2260992
(2 rows)

So, it's not slower because of size, but it's not faster either.

This test is not representative of real toast indexes, because it
skips the chunk number column. The addition of the chunk number column
will impact the size of the index, because now the index items are 16B
for OID vs 24B for OID8, for 20B and 28B page data usage respectively
after taking the 4-byte line pointer into account. Back of the
envelope calculations indicate this means the OID8 index will be about
40% larger.

Kind regards,

Matthias van de Meent.

On Fri, Jul 31, 2026 at 1:10 PM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:

...

This test is not representative of real toast indexes, because it
skips the chunk number column. The addition of the chunk number column
will impact the size of the index, because now the index items are 16B
for OID vs 24B for OID8, for 20B and 28B page data usage respectively
after taking the 4-byte line pointer into account. Back of the
envelope calculations indicate this means the OID8 index will be about
40% larger.

Yes, you are right:

hannuk=# create table oid_vs_bigint(id oid, int8 bigint, chunk_id int,
unique(id, chunk_id), unique(int8, chunk_id));
CREATE TABLE
hannuk=# insert into oid_vs_bigint select i, i, 0 from
generate_series(1,100000) g(i);
INSERT 0 100000
hannuk=# select indexrelid::regclass, pg_relation_size(indexrelid)
from pg_index where indrelid = 'oid_vs_bigint'::regclass;
indexrelid │ pg_relation_size
─────────────────────────────────┼──────────────────
oid_vs_bigint_id_chunk_id_key │ 2260992
oid_vs_bigint_int8_chunk_id_key │ 3162112
(2 rows)

So indeed 40% more, and you get the *same* number even if you used
OID6 (i.e 6-byte tid)

hannuk=# create table oid_vs_bigint(id oid, int8 tid, chunk_id int,
unique(id, chunk_id), unique(int8, chunk_id));
CREATE TABLE
hannuk=# insert into oid_vs_bigint select i, format('(%s,1)',i)::tid,
0 from generate_series(1,100000) g(i);
INSERT 0 100000
hannuk=# select * from oid_vs_bigint limit 2;
id │ int8 │ chunk_id
────┼───────┼──────────
1 │ (1,1) │ 0
2 │ (2,1) │ 0
(2 rows)
hannuk=# select indexrelid::regclass, pg_relation_size(indexrelid)
from pg_index where indrelid = 'oid_vs_bigint'::regclass;
indexrelid │ pg_relation_size
─────────────────────────────────┼──────────────────
oid_vs_bigint_id_chunk_id_key │ 2260992
oid_vs_bigint_int8_chunk_id_key │ 3162112
(2 rows)

----
Hannu

On Thu, Jul 30, 2026 at 05:01:02PM +0900, Yugo Nagata wrote:

On Thu, 30 Jul 2026 13:38:18 +0900 Yugo Nagata <nagata@sraoss.co.jp> wrote:
I also run a simple performance test. I measured the TPS on
the master branch and with the current patches applied under the
following conditions:

Running pbench three times:

$ pgbench test -f query.sql -c 8 -j 4 -T 100

Results:

master: 2686.229104, 2697.655096, 2687.894343
patched: 2685.292256, 2692.080214, 2690.005753

Thanks for the test.

I haven't tested whether the previous version of the patches showed the
performance degradation that has been a concern, though.

I am not sure that it is worth doing; I am not planning to get back to
these versions, but it's true that it could be useful to see if your
environment shows an impact of some impact. That's low priority, IMO.

postgres=# CREATE TABLE tbl(t text) with (toast_value_type = 'x');
ERROR: invalid value for enum option "toast_value_type": x
DETAIL: Valid values are "oid".

The detail message should be updated to include oid8.

I had this one already fixed on my local branch.

- 0009

(1)
The commit message says:

TOAST pointers still rely on varatt_external and a single vartag, with
all the values inserted in the bigint TOAST tables fed from the existing
OID value generator.

This made me a bit confused because chunk_ids insereted into TOAST tables using oid8
are generated by GetNewObjectId8().

+		else if (toast_typid == OID8OID)
+			toast_pointer.va_valueid = GetNewObjectId8();

This value is later cast to Oid here, so what is actually stored is a 4-byte chunk_id,
but saying that the values are "fed from the existing OID value generator" seems
a bit inaccurate to me.

Okay, edited that a bit. I tend to rework all my commit messages
before pushing, still you are right that this sounded a bit confusing.

(2)
/*
-	 * Choose an OID to use as the value ID for this toast value.
+	 * Choose a new value to use as the value ID for this toast value, be it
+	 * for OID or int8-based TOAST relations.

This patch relies on oid8, introduced by b139bd3b6ef, rather than int8, so, I
wonder if this comment sould also be updated.

Fixed.

(3)
belonging to the owning table.  Every
-<acronym>TOAST</acronym> table has the columns <structfield>chunk_id</structfield> (an OID
-identifying the particular <acronym>TOAST</acronym>ed value),
+<acronym>TOAST</acronym> table has the columns
+<structfield>chunk_id</structfield> (an OID or an 8-byte integer identifying
+the particular <acronym>TOAST</acronym>ed value),
<structfield>chunk_seq</structfield> (a sequence number for the chunk within its value),

Similarly, I think the documentation should mention oid8 rather than 8-byte
integer. That would also be consistent with the CREATE TABLE documentation for
the toast_value_type parameter.

Hole in the docs, indeed.

- 0011

(4)
+					/*
+					 * Check if this value already exists in the new toast
+					 * table (corner case during table rewrite with multiple
+					 * versions of the same row).
+					 */
+					if (toastrel_valueid_exists(toastrel, va_valueid))
+					{
+						/* Match, so short-circuit the data storage loop below */
+						data_todo = 0;
+					}

The same code appears later, together with a more detailed comment. How about
moving the detailed comment here instead, or simply referring to
the later comment?

FWIW, I was looking again at this block of code, and reminded myself
why this piece is this way: I want GetNewObjectId8(), InvalidOid8 and
varatt_external_oid8 to have their own references in each block
depending on the type of chunk_id. Perhaps a bit repetitive,
efficient at least.

(5)
+/* Is varlena datum a pointer to on-disk toasted data with 8-byte value ID? */
+static inline bool
+VARATT_IS_EXTERNAL_ONDISK_OID8(const void *PTR)
+{
+	return VARATT_IS_EXTERNAL(PTR) && VARTAG_EXTERNAL(PTR) == VARTAG_ONDISK_OID8;
}

This macro is defined but not used. Is it intended to be used in places that
check VARTAG_ONDISK_OID8 directly, for example:

+ if (VARTAG_EXTERNAL(attr) == VARTAG_ONDISK_OID8)

?

Consistency with the other one, for extensions, as all the code paths
checking for VARTAG_ONDISK_OID8 already make sure that we are dealing
with an external pointer, so I did not see a point in checking
VARATT_IS_EXTERNAL_ONDISK_OID8, that would make the extra
VARATT_IS_EXTERNAL() check pointless.

(6)

any.  Allowing for the varlena header bytes,
-the total size of an on-disk <acronym>TOAST</acronym> pointer datum is therefore 18
-bytes regardless of the actual size of the represented value.
+the total size of an on-disk <acronym>TOAST</acronym> pointer datum is 18
+bytes when using an OID as <structfield>chunk_id</structfield>, or 22 bytes
+when using an 8-byte integer, regardless of the actual size of the represented
+value.
</para>

Should this also refer to Oid8 rather than 8-byte integner, for consistency?

Fixed.

(7)

Non-ASCII hyphen is used in this comment. This may not be prohibitted,
but I don't think it's common in the PostgreSQL code base.

Seems like my keyboard has slipped a bit here. Will fix after
replying to your other comments.
--
Michael

Hi Michael

I understand from some earlier messages that in one of the patches you
refactored the code to make it easier to add new VARLENA on disk
types.

Where should I start looking for this?

-----
Hannu

On Sun, Aug 09, 2026 at 11:53:23PM +0200, Hannu Krosing wrote:

I understand from some earlier messages that in one of the patches you
refactored the code to make it easier to add new VARLENA on disk
types.

(Top-posting?)

Where should I start looking for this?

Quoting the top message of the thread:
```
The main feedback of the previous thread is that the previous
implementation with its callbacks for each vartag was not liked much,
and their were concerns with pointer redirections and
performance. This patch set uses what I am calling the "brutal"
approach, relying on a vartag_external of a varlena or the atttype of
the TOAST relation to decide which external toast pointer we should
use. This uses no function pointers, and patches the code to deal
with Oid or Oid8 TOAST values where it matters. So, this time,
performance cannot really be an issue.
```

The refactoring worked around a set of callbacks that could be
assigned to specific vartag_external, and one of the feedback that I
got is that this required function pointers, which could impact
performance for bulk-read or bulk-write of TOAST data. This
refactoring is gone as of round two, switching to direct if/elif
block. Overall the result is not that bad, as long as one is aware of
all the code paths that need to be handled on HEAD.
--
Michael

On Mon, Aug 10, 2026 at 2:08 AM Michael Paquier <michael@paquier.xyz> wrote:

On Sun, Aug 09, 2026 at 11:53:23PM +0200, Hannu Krosing wrote:

I understand from some earlier messages that in one of the patches you
refactored the code to make it easier to add new VARLENA on disk
types.

(Top-posting?)

Not this time, just thought this was the right thread to ask general
questions :)

And I have now written me an Chrome extension which warns me when I am
top-posting in GMail by mistake, so should happen less frequently now.

Where should I start looking for this?

Quoting the top message of the thread:
```
The main feedback of the previous thread is that the previous
implementation with its callbacks for each vartag was not liked much,
and their were concerns with pointer redirections and
performance. This patch set uses what I am calling the "brutal"
approach, relying on a vartag_external of a varlena or the atttype of
the TOAST relation to decide which external toast pointer we should
use. This uses no function pointers, and patches the code to deal
with Oid or Oid8 TOAST values where it matters. So, this time,
performance cannot really be an issue.
```

Thanks, that explains why I could not find it.

I was hoping to us see move to using virtual function tables like in
all other places where we provide extensibility.

The refactoring worked around a set of callbacks that could be
assigned to specific vartag_external, and one of the feedback that I
got is that this required function pointers, which could impact
performance for bulk-read or bulk-write of TOAST data.

Are function pointers really that much of a concern considering that
we use tables of virtual pointers in core code paths like Table and
Index Access Methods?

Was this performance impact actually measured ?

This refactoring is gone as of round two, switching to direct if/elif
block.

Do I understand correctly that with this patch the selection between
oid and oid8 happens at the table level not at the individual toasted
item level ?

Maybe it would make sense to get better backwards compatibility and
smoother upgrades by doing the same I did in my direct toast patch to
allow both versions to live in the same table, namely to add a
separate oid8 column and use conditional indexes for both new oid8 and
old plain oid lookups?

This allows selecting the toast type at the time of creating the
toasted value and also to move back and forth between different toast
types at any time.

In direct toast the support for this kind of backwards compatibility required
* adding the colum to store the tid array replacing the index for
multi-block toast
* making the original toast index conditional on WHERE chunk_id IS NOT
NULL (or WHERE chunk_id <> 0) so that it would not need to generate
unneccessary oids

In case of oid8 it would be
* add a column for new chunk_id8 oid8
* add a conditional index on the (chunk_id8, chunk_seq) WHERE
chunk_id_oid8 IS NOT NULL
* making the original toast index conditional on WHERE chunk_id IS NOT NULL

-----------
Hannu

On Mon, Aug 10, 2026 at 09:37:43AM +0200, Hannu Krosing wrote:

Are function pointers really that much of a concern considering that
we use tables of virtual pointers in core code paths like Table and
Index Access Methods?

I am pretty sure that TOAST paths could get hotter than AMs under some
COPY-related workloads.

Was this performance impact actually measured ?

I didn't measure any. Another comment I had was "overengineered",
which is perhaps fair as the TOAST code is quite linear in terms of
internals.

Do I understand correctly that with this patch the selection between
oid and oid8 happens at the table level not at the individual toasted
item level ?

Yes, it's a design choice to use a table-level comparison. Choose one
with the reloption, then stick to it. Across upgrades, we keep the
same TOAST table. Rewrites are the same: no changes after a VACUUM
FULL, same atttype. There is nothing difference than what we do now,
which is why it's appealing to me.

Maybe it would make sense to get better backwards compatibility and
smoother upgrades by doing the same I did in my direct toast patch to
allow both versions to live in the same table, namely to add a
separate oid8 column and use conditional indexes for both new oid8 and
old plain oid lookups?

Backward-compatibility and upgrades are handled in the patch set.
And TBH, I don't like much mixing multiple vartags in a single TOAST
table because it makes the whole layer more annoying to deal with.
Sticking with one atttype for each toast table is simpler. One
optimization would be to allow a OID external pointer to point to a
oid8 TOAST table, which offers benefits as long as we have less than 4
billion OIDs generated. This would not last long for some
deployments. Another idea that I found overkill was the use of a
per-toast sequence to generate the numbers. This can also be built on
top of what I have here, just found that expensive and a single oid8
counter in the control file is fine for all tables in a cluster
anyway.

In case of oid8 it would be
* add a column for new chunk_id8 oid8
* add a conditional index on the (chunk_id8, chunk_seq) WHERE
chunk_id_oid8 IS NOT NULL
* making the original toast index conditional on WHERE chunk_id IS NOT NULL

Err. You're making TOAST tables larger than they should with
attributes that may not be required because some attributes may not be
required. With the TOAST data, it does not matter overall, but I'm
not much a fan of adding more data to disk than necessary, with more
indexes to maintain on each insert.

As a whole I'm not planning to go beyond what's presented on this
thread. Sticking to the simpler plan of having 8-byte values would
make a lot of customers I know of quite happy, because we have no exit
route now when it comes to core. (Spoiler: if I commit a patch, I own
its maintenance, and I'm well.. OK with what I have sent with a
long-term maintenance picture in mind.)
--
Michael

On Mon, Aug 10, 2026 at 10:03 AM Michael Paquier <michael@paquier.xyz> wrote:

On Mon, Aug 10, 2026 at 09:37:43AM +0200, Hannu Krosing wrote:

Are function pointers really that much of a concern considering that
we use tables of virtual pointers in core code paths like Table and
Index Access Methods?

I am pretty sure that TOAST paths could get hotter than AMs under some
COPY-related workloads.

Was this performance impact actually measured ?

I didn't measure any. Another comment I had was "overengineered",
which is perhaps fair as the TOAST code is quite linear in terms of
internals.

Do I understand correctly that with this patch the selection between
oid and oid8 happens at the table level not at the individual toasted
item level ?

Yes, it's a design choice to use a table-level comparison. Choose one
with the reloption, then stick to it. Across upgrades, we keep the
same TOAST table. Rewrites are the same: no changes after a VACUUM
FULL, same atttype. There is nothing difference than what we do now,
which is why it's appealing to me.

Maybe it would make sense to get better backwards compatibility and
smoother upgrades by doing the same I did in my direct toast patch to
allow both versions to live in the same table, namely to add a
separate oid8 column and use conditional indexes for both new oid8 and
old plain oid lookups?

Backward-compatibility and upgrades are handled in the patch set.
And TBH, I don't like much mixing multiple vartags in a single TOAST
table because it makes the whole layer more annoying to deal with.

Can you elaborate how having multiple vartags in the single table is
more annoying than having them in separate tables.

My experience is that on insert you select the vartag to use from a
GUC or table attributem and on read we already have a number of
different vartags each requiring special handling.

Sticking with one atttype for each toast table is simpler. One
optimization would be to allow a OID external pointer to point to a
oid8 TOAST table, which offers benefits as long as we have less than 4
billion OIDs generated. This would not last long for some
deployments.

Agreed, but we also never need more than 5 bytes worth of space with
current 8 kb pages, as there would be not toast table rows to point to
:)

As you are concerned about space usage, but want to keep accessing
toast chunks via indexes, have you considered something like the
43-bit integers used to represent tuple ids in GIN posting lists?

Another idea that I found overkill was the use of a
per-toast sequence to generate the numbers. This can also be built on
top of what I have here, just found that expensive and a single oid8
counter in the control file is fine for all tables in a cluster
anyway.

I tend to agree. Theoretically it could result in better locking
behaviour between the sequences, but in practice it would be very
unlikely to have a large number of toast tables in active parallel
use.
And the overhead of index inserts will always be larger than getting
the number from sequence, so one sequence should be good for at least
10 tables. And it is unlikely to need more.

In case of oid8 it would be
* add a column for new chunk_id8 oid8
* add a conditional index on the (chunk_id8, chunk_seq) WHERE
chunk_id_oid8 IS NOT NULL
* making the original toast index conditional on WHERE chunk_id IS NOT NULL

Err. You're making TOAST tables larger than they should with
attributes that may not be required because some attributes may not be
required.

The overhead is only in the the pg_attribute table, as we already have
the 8 bits of null bitmnap in the header and this will not grow, and
any values not used will not be stored.
And in case of direct toast storing (potentially compressed) tid lists
will be way more space efficient than having an index, in addition to
having much better performance.

In direct toas case it can even result in (very slighly) smaller space
usage because of not storing oid, and at least in the case of single
chunk toast also not he chunk_seq.

With the TOAST data, it does not matter overall, but I'm
not much a fan of adding more data to disk than necessary, with more
indexes to maintain on each insert.

The benefit of my scheme is that you do not need to maintain the
indexes you are not using.

As a whole I'm not planning to go beyond what's presented on this
thread. Sticking to the simpler plan of having 8-byte values would
make a lot of customers I know of quite happy, because we have no exit
route now when it comes to core.

I still hope to convince you that an even simpler plan to get all the
benefits of larger id space by using tids directly :)

The patch is much simpler and mostly just bypasses code paths that are used.

At high level it

For select
- goes directly to toast tuple, bypassing index lookup with all its
locking and pinning
- if there are more than a single chunk it hets the list of tuple ids,
again bypassing the index lookups, and loads them directly
- if there are larger number of chunks than can fit their tids in one
array, theis is repeated recursively for some of the toast tuples
fetched using this list

At insert/update time
- it bypasses getting an OID
- it inserts the chunks exactly as the current code
- it bypasses index operations, opening, pinning, adding the entry,
poissibly splitting index page, etc.

At vacuum time (not implemented yet) it can bypass collecting the
tuple id for index cleanup for deleted tuples

So the only thing _added_ is collecting the chunk ids in an array and
storing it in selected end-of-run pages instead of insering them in
the toast index.

Everything else streamlines the code bypassing expensive operations.

(And I know adding tid array messes with fitting exacly 4 chunks of
~2000 bytes on each page, but longer term plan is to relax this by
also adding another array of chunk offsets togetehr with the tid array
which would allow using all the page space in toast pages. Also the
tid array is highly compressible if the tuples are allocated together,
as shown in GIN posting lists. And I have a separate patch that would
improve the compression another 6x over GIN posting lists for common
cases, totaling 20-30 x compression)

The main thing (that I know of) that I have not yet done handling
VACUUM FULL and CLUSTER for direct toast tables.

Disabling them if direct toast tuples exist is trivial.

Supporting explicit CLUSTER / VACUUM FULL on main table which also
handles toast would need some work.

Just clustering the toast table only in OID order can be useless for
performance if the toasted columns have been frequently updated or if
data has been added later to some.

Currently this can be done manually using CREATE TABLE (LIKE oldtable
INCLUDING ALL) + INSERT INTO ... SELECT and then swapping the table
but it would be much nicer to have it integrated as CLUSTER / VACUUM
FULL
This would also be what the new

Because this is what the new REPACK command does anyway I am not sure
that also supporting CLUSTER AND VACUUM to include TOAST reqrite is
worth doing.

I am still investigating your patch set to understand if I missed any
other parts that would need changes.

---
Hannu

On Mon, Aug 10, 2026 at 12:43:37PM +0200, Hannu Krosing wrote:

The main thing (that I know of) that I have not yet done handling
VACUUM FULL and CLUSTER for direct toast tables.

Disabling them if direct toast tuples exist is trivial.

Well, you say that. Noted. Now, it also sounds to me that you are
talking about oranges and I am talking about apples, which are two
different things. There is nothing that prevents doing one thing and
the other (I write that after looking at your AI-generated code that
you have mentioned as not reading yourself, yes). Here the apples are
about one full patch set that's posted on this thread, split for
easier review, able to handle all cases we care about in core
including backward-compatibility, relation rewrites, upgrades,
dump/restore, versus oranges clearly presented as partially complete
without a clear complete picture.

Honestly, and to keep it short, I don't see enough arguments to be
convinced that the oranges are better than the apples. Having the
code presenting the oranges to be fully generated by an LLM agent
does not help IMO, either.
--
Michael

Hi,

On Wed, Aug 5, 2026 at 9:39 PM Michael Paquier <michael@paquier.xyz> wrote:

Seems like my keyboard has slipped a bit here. Will fix after
replying to your other comments.

Thanks Michael for working on this feature.

I started looking at the patches and TOAST code. Here's my take.

I understand that when 4-byte chunk_id exhaustion happens, query
(insert/update) latencies can increase from milliseconds to minutes
and hours, or even days as OID use approaches the 4 billion limit.
Although this seems to happen rarely (insert- or update-heavy
workloads with more toastable columns, i.e., more chunk_id use per
row, or dead tuples in the TOAST table when vacuum is busy or yet to
get to the TOAST table and index), when it does happen, the mitigation
for the customer seems hard: partition the table or fix vacuum.

I believe that 8-byte chunk_id is the right direction to solve this
problem in a simpler way, without affecting existing 4-byte chunk_id
tables across upgrades and providing a simple path for customers to
migrate to 8-byte chunk_id. My reading of the patches says that it
follows these principles.

I first tried to reproduce the query latency increase problem with
less time and disk space. Here are the results. I ensured each insert
steps over K ids using pg_resetwal, which equals the number of
iterations spent in GetNewOidWithIndex(). There's a clear benefit as
the 8-byte chunk_id avoids the retries while getting the new OID, and
since a 64-bit OID is almost never exhausted, this works. Attached the
test script that I used for reference.

K HEAD_INSERT patched_INSERT
1,000,000 1.25 s 1.0 ms
10,000,000 12.4 s 1.0 ms
50,000,000 1 min 1.0 ms
100,000,000 2 min 1.0 ms
200,000,000 9 min 0.9 ms

With this context, I started to review the patches. One thing I liked
is the way the patches were split; they made review a lot easier. Here
are some comments.

Comments on 0001:

1/
+table_relation_fetch_toast_slice(Relation toastrel, Oid8 valueid,
While changing Oid to Oid8 is fine for internal functions, is it okay
to change it for the table_relation_fetch_toast_slice table AM? Is
there any chance that an external table AM forgets to update? I'm not
arguing against this change, just checking if there's any way out
here. Of course, the compiler will generate an incompatible function
parameter type warning, and since the change is going in a major
release, that seems fine. One idea could be to have a TOAST version
field elsewhere to detect such changes, but that seems overkill. So,
having it like 0001 seems fine to me unless others have any thoughts.
PS: one option is to send the 8-byte OID via a caller-allocated
varlena result pointer, but this seems a bit ugly.

2/
@@ -4997,7 +4997,7 @@ ReorderBufferToastInitHash(ReorderBuffer *rb,
ReorderBufferTXN *txn)

Assert(txn->toast_hash == NULL);

- hash_ctl.keysize = sizeof(Oid);
+ hash_ctl.keysize = sizeof(Oid8);

Widening the per-toast-chunk hash key to an 8-byte OID still handles
4-byte chunk_ids correctly during logical decoding, since the smaller
values zero-extend. The tradeoff is that with an 8-byte key the hash
no longer uses the uint32_hash fast path and falls back to tag_hash,
so even existing 4-byte chunk_id tables lose that optimization during
logical decoding. I haven't measured the effect, but it seems worth
checking.

3/ Rest of the 0001 changes look good to me, although they use an
8-byte format specifier for error reporting, which can still work for
4-byte chunk_ids.

I think 0002 and 0003 are purely mechanical; they look good to me and
can go in first.

Comments on 0004:

1/
- WHERE o.chunk_id != pg_column_toast_chunk_id(c.value); + WHERE
o.chunk_id::oid8 != pg_column_toast_chunk_id(c.value);

Changing the return value of pg_column_toast_chunk_id() to 8-byte OID
is fine and it can work for existing 4-byte chunk_ids. Do we need the
above typecasting in tests given the chunk_id is captured from
pg_column_toast_chunk_id() while creating the table?

Comments on 0005:

1/ + return murmurhash64(DatumGetObjectId8(datum));

I think having catalog cache support for 8-byte OID might be useful on
its own and it looks good to me except the following: can we typecast
the Oid8 to murmurhash64((uint64) DatumGetObjectId8(datum))?
Comments on 0008:

1/ I think adding reloption seems okay to me, so the 0008 patch looks
good. Just one suggestion: having a GUC to set it once for all new
tables seems a good idea as it avoids application changes, but
starting with reloption is good enough.

I will take a look at 0006, 0007, 0009, 0010, 0011 in the coming weeks.

One more thought: existing tables won't get 8-byte chunk_ids as part
of pg_upgrade. pg_dump and pg_restore could be used after the upgrade
for existing customers to get their tables onto this format. At some
point, providing concurrent repack-like support (or reusing the
underlying machinery) to do this online would be nice to have.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

On Wed, Sep 02, 2026 at 01:27:00PM -0700, Bharath Rupireddy wrote:

I first tried to reproduce the query latency increase problem with
less time and disk space. Here are the results. I ensured each insert
steps over K ids using pg_resetwal, which equals the number of
iterations spent in GetNewOidWithIndex(). There's a clear benefit as
the 8-byte chunk_id avoids the retries while getting the new OID, and
since a 64-bit OID is almost never exhausted, this works. Attached the
test script that I used for reference.

K HEAD_INSERT patched_INSERT
1,000,000 1.25 s 1.0 ms
10,000,000 12.4 s 1.0 ms
50,000,000 1 min 1.0 ms
100,000,000 2 min 1.0 ms
200,000,000 9 min 0.9 ms

This measures how much time we take to grab a free OID value, hoping
that there is a hole.

With this context, I started to review the patches. One thing I liked
is the way the patches were split; they made review a lot easier. Here
are some comments.

Thanks for the comments.

Comments on 0001:

1/
+table_relation_fetch_toast_slice(Relation toastrel, Oid8 valueid,
While changing Oid to Oid8 is fine for internal functions, is it okay
to change it for the table_relation_fetch_toast_slice table AM? Is
there any chance that an external table AM forgets to update? I'm not
arguing against this change, just checking if there's any way out
here. Of course, the compiler will generate an incompatible function
parameter type warning, and since the change is going in a major
release, that seems fine. One idea could be to have a TOAST version
field elsewhere to detect such changes, but that seems overkill. So,
having it like 0001 seems fine to me unless others have any thoughts.
PS: one option is to send the 8-byte OID via a caller-allocated
varlena result pointer, but this seems a bit ugly.

Yes, assuming that a table AM uses its own fetch_toast_slice(), it
will need to update probably for its own ScanKeyInit() if an external
TOAST table is in use for the OID8 case, assuming that they need to do
so based on the reloption for the toast value type. I am not really
convinced that we need to be fancy here, telling that after looking at
out-of-code projets that include their own callbacks:
https://github.com/eatonphil/pgtam (no support)
https://github.com/rohankumardubey/pg_mooncake
https://github.com/neurdb/neurdb (no support)
https://github.com/duckdb/pg_duckdb (totally different callback here)
https://github.com/timescale/timescaledb
https://github.com/jeffreydwalter/pg_tde/ (needs refresh)
https://github.com/ewhauser/unitpg/ (just reuses the heap call)

There are also a few more, but I am seeing nothing beyond an extra
ScanKeyInit() update required, in a sea of non-supported cases.

Widening the per-toast-chunk hash key to an 8-byte OID still handles
4-byte chunk_ids correctly during logical decoding, since the smaller
values zero-extend. The tradeoff is that with an 8-byte key the hash
no longer uses the uint32_hash fast path and falls back to tag_hash,
so even existing 4-byte chunk_id tables lose that optimization during
logical decoding. I haven't measured the effect, but it seems worth
checking.

Yeah, point taken. I'm putting a note down on this one.

I think 0002 and 0003 are purely mechanical; they look good to me and
can go in first.

Thanks. 0002 and 0003 are kind of independent on the rest, not
requiring any Oid8 idea, so I'll go apply them.

1/
- WHERE o.chunk_id != pg_column_toast_chunk_id(c.value); + WHERE
o.chunk_id::oid8 != pg_column_toast_chunk_id(c.value);

Changing the return value of pg_column_toast_chunk_id() to 8-byte OID
is fine and it can work for existing 4-byte chunk_ids. Do we need the
above typecasting in tests given the chunk_id is captured from
pg_column_toast_chunk_id() while creating the table?

I guess we do. Will double-check.

Comments on 0005:

1/ + return murmurhash64(DatumGetObjectId8(datum));

I think having catalog cache support for 8-byte OID might be useful on
its own and it looks good to me except the following: can we typecast
the Oid8 to murmurhash64((uint64) DatumGetObjectId8(datum))?

Ah, you want to add an extra cast here, with (uint64) for the
murmurhash64. Hmm why not, that seems more consistent with the rest
of the area. This one is also a rather independent item for the Oid8,
so I'm also planning to apply it to reduce the stack.

Comments on 0008:

1/ I think adding reloption seems okay to me, so the 0008 patch looks
good. Just one suggestion: having a GUC to set it once for all new
tables seems a good idea as it avoids application changes, but
starting with reloption is good enough.

The round one of the patch set used a GUC. Andres has argued in favor
of a reloption.

I will take a look at 0006, 0007, 0009, 0010, 0011 in the coming weeks.

Cool, thanks.

One more thought: existing tables won't get 8-byte chunk_ids as part
of pg_upgrade. pg_dump and pg_restore could be used after the upgrade
for existing customers to get their tables onto this format. At some
point, providing concurrent repack-like support (or reusing the
underlying machinery) to do this online would be nice to have.

The infrastructure that could be used to switch the toast value type
and its rewrite is out of scope, the patch being complicated enough..
We could always think about that later, giving the choice in core is
much more important to me as a first step, because without the
OID8/OID choice, there is no discussion about the rewrite part.
--
Michael

On Thu, Sep 03, 2026 at 01:58:19PM +0900, Michael Paquier wrote:

I think 0002 and 0003 are purely mechanical; they look good to me and
can go in first.

Thanks. 0002 and 0003 are kind of independent on the rest, not
requiring any Oid8 idea, so I'll go apply them.

0002 has been applied as of 2b3d11aaed89. 0003 does a bunch of
renames and in order to raise a bit more awareness and not get
screamed at after-the-fact, I have posted that on a different thread:
/messages/by-id/apoD1Ulet2PtDrRV@paquier.xyz

The first feedbacks I had we OK, so I don't expect any pushback, just
being careful and we still have time.

After an extra set of checks, I am dropping the catcache thing for
OID8OID, for the simple reason that there is no need for it anymore.
I am pretty sure that I did some OID8 catalog lookup at some point of
this patch set, but nothing shows up now. Perhaps that was in the
round 1 of the discussion.

I'll post a rebase of the remaining pieces once the rename thread is
completely settled, hopefully around the beginning of next week.
--
Michael

Hi,

On Wed, Sep 2, 2026 at 9:58 PM Michael Paquier <michael@paquier.xyz> wrote:

On Wed, Sep 02, 2026 at 01:27:00PM -0700, Bharath Rupireddy wrote:

I first tried to reproduce the query latency increase problem with
less time and disk space. Here are the results. I ensured each insert
steps over K ids using pg_resetwal, which equals the number of
iterations spent in GetNewOidWithIndex(). There's a clear benefit as
the 8-byte chunk_id avoids the retries while getting the new OID, and
since a 64-bit OID is almost never exhausted, this works. Attached the
test script that I used for reference.

K HEAD_INSERT patched_INSERT
1,000,000 1.25 s 1.0 ms
10,000,000 12.4 s 1.0 ms
50,000,000 1 min 1.0 ms
100,000,000 2 min 1.0 ms
200,000,000 9 min 0.9 ms

This measures how much time we take to grab a free OID value, hoping
that there is a hole.

Yes, not all the chunk_ids are occupied in this testing, so there are
holes. I wanted to keep the testing time to a minimum and show how
long it usually takes for insert/update queries to fetch a chunk_id
when it's closer to the 4 billion limit. I attached the test script
used for this testing (courtesy: Claude Code). Do you have any other
test cases in mind?

Comments on 0001:

1/
+table_relation_fetch_toast_slice(Relation toastrel, Oid8 valueid,
While changing Oid to Oid8 is fine for internal functions, is it okay
to change it for the table_relation_fetch_toast_slice table AM? Is
there any chance that an external table AM forgets to update? I'm not
arguing against this change, just checking if there's any way out
here. Of course, the compiler will generate an incompatible function
parameter type warning, and since the change is going in a major
release, that seems fine. One idea could be to have a TOAST version
field elsewhere to detect such changes, but that seems overkill. So,
having it like 0001 seems fine to me unless others have any thoughts.
PS: one option is to send the 8-byte OID via a caller-allocated
varlena result pointer, but this seems a bit ugly.

Yes, assuming that a table AM uses its own fetch_toast_slice(), it
will need to update probably for its own ScanKeyInit() if an external
TOAST table is in use for the OID8 case, assuming that they need to do
so based on the reloption for the toast value type. I am not really
convinced that we need to be fancy here, telling that after looking at
out-of-code projets that include their own callbacks:
https://github.com/eatonphil/pgtam (no support)
https://github.com/rohankumardubey/pg_mooncake
https://github.com/neurdb/neurdb (no support)
https://github.com/duckdb/pg_duckdb (totally different callback here)
https://github.com/timescale/timescaledb
https://github.com/jeffreydwalter/pg_tde/ (needs refresh)
https://github.com/ewhauser/unitpg/ (just reuses the heap call)

There are also a few more, but I am seeing nothing beyond an extra
ScanKeyInit() update required, in a sea of non-supported cases.

Thanks for providing these details. It may help table AM implementers
to have a note in the commit message, the docs, or this thread
explaining how they need to adapt.

Widening the per-toast-chunk hash key to an 8-byte OID still handles
4-byte chunk_ids correctly during logical decoding, since the smaller
values zero-extend. The tradeoff is that with an 8-byte key the hash
no longer uses the uint32_hash fast path and falls back to tag_hash,
so even existing 4-byte chunk_id tables lose that optimization during
logical decoding. I haven't measured the effect, but it seems worth
checking.

Yeah, point taken. I'm putting a note down on this one.

Also, I think there's an issue in ReorderBufferToastAppendChunk() with
0001. The chunk_id is still fetched as a 4-byte value, so past 4
billion chunk_ids the truncated key stored in the TOAST hash won't
match the 64-bit value used for the lookup later in
ReorderBufferToastReplace().

Comments on 0008:

1/ I think adding reloption seems okay to me, so the 0008 patch looks
good. Just one suggestion: having a GUC to set it once for all new
tables seems a good idea as it avoids application changes, but
starting with reloption is good enough.

The round one of the patch set used a GUC. Andres has argued in favor
of a reloption.

That works for me. A quick question, though I haven't tested this.
Does ALTER TABLE error out if the reloption is changed on an existing
table?

I will take a look at 0006, 0007, 0009, 0010, 0011 in the coming weeks.

Cool, thanks.

One more thought: existing tables won't get 8-byte chunk_ids as part
of pg_upgrade. pg_dump and pg_restore could be used after the upgrade
for existing customers to get their tables onto this format. At some
point, providing concurrent repack-like support (or reusing the
underlying machinery) to do this online would be nice to have.

The infrastructure that could be used to switch the toast value type
and its rewrite is out of scope, the patch being complicated enough..
We could always think about that later, giving the choice in core is
much more important to me as a first step, because without the
OID8/OID choice, there is no discussion about the rewrite part.

That's fine by me. That said, it would be good to document at least
one clear way for existing users to move their tables to 8-byte TOAST
chunk_ids, presumably pg_dump and pg_restore after setting the
reloption.

I'm also wondering if pg_dump needs an option to generate table
schemas with the new reloption set, so that users don't have to edit
the dump file. Editing may not always be possible (for example, with
the custom and directory formats). This can be a follow-up patch, but
having at least one supported way to migrate existing tables seems
important to me.

On Thu, Sep 3, 2026 at 9:28 PM Michael Paquier <michael@paquier.xyz> wrote:

After an extra set of checks, I am dropping the catcache thing for
OID8OID, for the simple reason that there is no need for it anymore.
I am pretty sure that I did some OID8 catalog lookup at some point of
this patch set, but nothing shows up now. Perhaps that was in the
round 1 of the discussion.

Agreed, dropping it seems right at a quick glance. The chunk_id comes
from the heap row's TOAST pointer and is used as a scan key against
the TOAST index, so nothing looks up an OID8 value through the catalog
cache.

I'll post a rebase of the remaining pieces once the rename thread is
completely settled, hopefully around the beginning of next week.

Thanks.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

On Fri, Sep 04, 2026 at 08:11:00PM -0700, Bharath Rupireddy wrote:

On Wed, Sep 2, 2026 at 9:58 PM Michael Paquier <michael@paquier.xyz> wrote:

On Wed, Sep 02, 2026 at 01:27:00PM -0700, Bharath Rupireddy wrote:

Widening the per-toast-chunk hash key to an 8-byte OID still handles
4-byte chunk_ids correctly during logical decoding, since the smaller
values zero-extend. The tradeoff is that with an 8-byte key the hash
no longer uses the uint32_hash fast path and falls back to tag_hash,
so even existing 4-byte chunk_id tables lose that optimization during
logical decoding. I haven't measured the effect, but it seems worth
checking.

While navigating through the code, I am not planning to worry about
it at the end, as the performance sits close to some WAL record
handling and tuple copying, where most of the performance is on the
table there.

Also, I think there's an issue in ReorderBufferToastAppendChunk() with
0001. The chunk_id is still fetched as a 4-byte value, so past 4
billion chunk_ids the truncated key stored in the TOAST hash won't
match the 64-bit value used for the lookup later in
ReorderBufferToastReplace().

Hmm. This one may be debatable because it would not be reached until
the OID8 business is plugged into the TOAST values. Adding that in
0001 is less business to deal with after. For now I've fixed that in
0001 to keep the changes with reorderbuffer.c more isolated.

That works for me. A quick question, though I haven't tested this.
Does ALTER TABLE error out if the reloption is changed on an existing
table?

I was not doing that with only simplicity in mind, so one could change
toast_value_type after the initial TOAST table creation, with one
argument in mind but I agree that it may sounds a bit weird: dumps.

One could change the reloption, do a dump, and re-create the relation
with a different TOAST layer. Agreed that it's a bit twisted but we
don't have a lot of precedent for this kind of choices with
reloptions. I recall one similar case in the backend code, with BRIN
indexes and pages_per_range (?) that do not affect post-creation
patterns. The WITH added by the dump is kind of nice for re-creation
based on raw data.

There is also a second scenario where this can be useful:
CREATE TABLE t (a int);
ALTER TABLE t SET (toast_value_type = oid8);
ALTER TABLE t ADD COLUMN b text; -- creates TOAST relation

I've mentioned that in the docs already, as of the "creating":
+      The toast_value_type specifies the attribute type of
+      <literal>chunk_id</literal> used when initially creating a toast
+      relation for this table

I have added some tests for ALTER TABLE with some rewrites, though.
That felt like a hole.

That's fine by me. That said, it would be good to document at least
one clear way for existing users to move their tables to 8-byte TOAST
chunk_ids, presumably pg_dump and pg_restore after setting the
reloption.

Hmm, yeah. Perhaps. The dump/restore trick is one, at least. Not
sure where adding this mention would be best, and if we actually need
to add that. :)

I'm also wondering if pg_dump needs an option to generate table
schemas with the new reloption set, so that users don't have to edit
the dump file. Editing may not always be possible (for example, with
the custom and directory formats). This can be a follow-up patch, but
having at least one supported way to migrate existing tables seems
important to me.

The best thing that I came up for that was a GUC, because you'd want
a dump/restore option able to say "I don't want the TOAST type" to be
dumped or restored, able to enforce a type across the board for a
database, a namespace or more than N objects at the same time. The
GUC idea was shut down in round one by Andres.

Agreed, dropping it seems right at a quick glance. The chunk_id comes
from the heap row's TOAST pointer and is used as a scan key against
the TOAST index, so nothing looks up an OID8 value through the catalog
cache.

Following c5806f2165b5 and 2b3d11aaed89, attached is a rebase of the
rest.

While on it, I have added one StaticAssertDecl() to enforce the
no-padding rule of vartag_external_oid8. There was also a spot I have
missed in pg_column_toast_chunk_id() to handle oid8 toast pointers, in
the last patch.

On top of that, while doing more review, there was also an issue that
I got on my notes for some time but completely forgot about in this
thread: toast_tuple_find_biggest_attribute() needs some relcaching for
the toast value type to enforce a correct decision, or we may be off
in deciding if something should be compressed or not depending on the
toast table we are dealing with. This uses the OID's max chunk size
as a safety measure, which should be OK. This part is added on top of
the rest, as 0009.

Finally, I have asked Claude for a round of reviews for the full patch
set, and it has spotted two stupid mistakes caused by the new vartag
for oid8, where I missed its handling:
- toast_get_compression_id() missed handling for oid8 external
pointers.
- toast_tuple_init() can compare two external TOAST pointers, but we
cannot memcmp() safely when dealing with tuples with different
vartags, the oid8 one being larger than the oid one. That's a nice
catch, for an egde case when doing cross-updates and TOAST pointer
reuse.

Attached is v14, with all that addressed (should perhaps have moved
the tests in 0008 to 0007, but it's late here and that does not change
the stuff to review).
--
Michael