[PATCH v1 1/1] Add GENERATED ALWAYS AS ROW IDENTITY columns

On 2026-Oct-23, Hannu Krosing wrote:

This is something I have been thinking of for some time and here is a
proof of concept implementation to verify the advantages I have hoped
it will have.

I think you have to stop flooding this list with code that nobody wrote
and nobody will read. The pgbench thread is a ridiculous code dump(*).
This one looks just like a repeat of that.

You should know how this works: a human person needs to take that code
and vet it carefully before it becomes part of the distribution. Have a
look at the commitfest page; it's flooded with items that need review
work, there's a ton of bugfixes that need to be reviewed and backpatched
and committed. Each of those things, every line of code, needs careful
work, and we just don't have enough people reviewing and finding the
huge flaws and the tiny flaws. How do I know there isn't some buffer
overflow here that's going to become a CVE next year?

A decade ago, companies throwing enormous code dumps over the wall
benefitted nobody, because the limiting factor was our ability to review
and integrate it. Nowadays, it's even more worthless because anybody
can send LLM written code, and tons of it.

I want progress here, I really do, but this is not the way.

(*) The latest pgbench post was stuck in the moderator queue because of
the message size. I was looking at it earlier today, thinking ... 3 MB
of code ... should I just reject it? In the end, it's not the
moderator's job to take that kind of shot, so it's now published. But
you should know better than to post multiple 400kB patches of new
features of unknown quality that have never before even been mentioned
in this list, much less discussed about or agreed, in a "Refactor
pgbench code" subject thread.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Los trabajadores menos efectivos son sistematicamente llevados al lugar
donde pueden hacer el menor daño posible: gerencia." (El principio Dilbert)

On 23/10/2026 13:06, Hannu Krosing wrote:

What it is
========

A row identity type using a 64-bit expanded tuple ID as the identity,
can do direct access, with HOT chains following and backup index
access as backup.

What is an "expanded tuple ID"? Is that like the current 'ctid'? Why
does it need to be 64 bits wide, why is the current 32+16 bits not
sufficient?

What I hope to get out of this is
=======================

- immediate no-downtime ALTER TABLE ... ADD COLUMN .. GENERATED ALWAYS
AS ROW IDENTITY
- much faster point lookups by avoiding index access in most cases
- not needing the index maintenance in most cases, much cheaper in
remaining cases
- should be especially useful in many graph query cases

How does a 64-bit "expanded tuple ID" help to achieve those goals?

What index maintenance?

+/*
+ * 64-bit Row Identity key layout (GENERATED ALWAYS AS ROW IDENTITY):
+ *
+ * Bit 63:        0 (sign bit; keys are always positive 1 .. INT64_MAX)
+ * Bit 62:        ROWID_EXTENDED_FLAG (1 if allocated via sequence beyond ctid)
+ * Bits [61:48-S]: partition_id (P bits, where S = Max(0, P - 14))
+ * Bits [47-S:16]: BlockNumber (32 - S bits)
+ * Bits [15:0]:   OffsetNumber (16 bits, 1 .. MaxHeapTuplesPerPage)
+ */

Huh, ok so you're storing a partition id in the tid itself. Why? What if
you have more than 2^14 partitions?

I don't understand what you're trying to accomplish here, and this patch
is clearly dead on arrival. Maybe you can explain in simpler terms what
you're trying to accomplish. Everyone is busy, so please try to keep the
scope as narrow as possible.

- Heikki

On Fri, Oct 23, 2026 at 1:41 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:

On 2026-Oct-23, Hannu Krosing wrote:

This is something I have been thinking of for some time and here is a
proof of concept implementation to verify the advantages I have hoped
it will have.

I think you have to stop flooding this list with code that nobody wrote
and nobody will read.

This was not meant to be final in any way; it was meant to introduce
an *idea* with a PoC implementation for people who want to verify its
merits.

Was marking it [PATCH] the main confusing thing, or was there also
something else?

...

I want progress here, I really do, but this is not the way.

What do you think would be a good way to discuss larger ideas that go
beyond bugfixes ?

(*) The latest pgbench post was stuck in the moderator queue because of
the message size. I was looking at it earlier today, thinking ... 3 MB
of code ... should I just reject it? In the end, it's not the
moderator's job to take that kind of shot, so it's now published.

Thanks!
I expect this to be complete for some time regarding the planned functionality.

The thing is actually about 5 or six individual patches that need to
be applied one after another, first pure moving code from a single one
into into separate files, then more structural refactoring,

And only after that come the actual enhancements and new features.

I now plan to start discussions about adding a patch linking feature
to the commitfest app, so one does not need to post same patches in
multiple entries (I found 23 such cases over last few years)

But
you should know better than to post multiple 400kB patches of new
features of unknown quality that have never before even been mentioned
in this list, much less discussed about or agreed, in a "Refactor
pgbench code" subject thread.

The first 10 patches are indeed refactoring.

And I did not want to open another entry that included all the
refactoring, so I explained it in the email and the CF entry

----
Hannu

On Fri, Oct 23, 2026 at 2:15 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:

Thanks for taking a look and giving feedback.

Very valuable for me

On 23/10/2026 13:06, Hannu Krosing wrote:

What it is
========

A row identity type using a 64-bit expanded tuple ID as the identity,
can do direct access, with HOT chains following and backup index
access as backup.

What is an "expanded tuple ID"? Is that like the current 'ctid'?

Yes, the current ctid with extra space for non-ctid values

Why does it need to be 64 bits wide,

It just felt cleaner to expose it as 64-bit entity. Also the 32+16
almost always still takes up 64 bits in use.

Also I was not sure that the thing needs to be its own type, maybe a
bigint + table attribute being marked as ROW IDENTITY is enough. It
should not be in any way special outside the DML and lookup behaviour

We *could* use the extra bits inside the 16-bit part, as currently we
are currently drifting toward only ever using 8k pages, meaning a
maximum of 9 bits are ever used there.

why is the current 32+16 bits not sufficient?

More than 32+16 was originally also needed for the case when you add
an index to an existing table and must have extra address space for
allocating new ids guaranteed to not clash with ones allocated before
the index , including the ones that may have been in now-truncated
pages.

What I hope to get out of this is
=======================

- immediate no-downtime ALTER TABLE ... ADD COLUMN .. GENERATED ALWAYS
AS ROW IDENTITY
- much faster point lookups by avoiding index access in most cases
- not needing the index maintenance in most cases, much cheaper in
remaining cases
- should be especially useful in many graph query cases

How does a 64-bit "expanded tuple ID" help to achieve those goals?

Sorry for leading with the 64-bit thing. It is not directly relevant
for any of the above - having a stable row id that does not need
idexes in most case is the important part.

What index maintenance?

At a high level: vacuum index cleanup passes, possible reindexing in
case index gets bloated, etc.

At a lower level, this involves fetching index pages, followed by
locking, pinning, unpinning and unlocking. It takes measurable, and in
some cases very visible, extra time even when it all is purely in RAM

Just using Direct Toast instead of the indexed one makes many vector
operations 5%-30% faster by avoiding index manipulation. I expect the
same to be true for ROW IDENTITY in some places. One of the places is
reducing contention and improving scalability.

+/*
+ * 64-bit Row Identity key layout (GENERATED ALWAYS AS ROW IDENTITY):
+ *
+ * Bit 63:        0 (sign bit; keys are always positive 1 .. INT64_MAX)
+ * Bit 62:        ROWID_EXTENDED_FLAG (1 if allocated via sequence beyond ctid)
+ * Bits [61:48-S]: partition_id (P bits, where S = Max(0, P - 14))
+ * Bits [47-S:16]: BlockNumber (32 - S bits)
+ * Bits [15:0]:   OffsetNumber (16 bits, 1 .. MaxHeapTuplesPerPage)
+ */

Huh, ok so you're storing a partition id in the tid itself. Why? What if
you have more than 2^14 partitions?

Then you cannot use this alone, but must fall back to using extra 16
bits (in case you have 2^30 partitions) or extra 32 bits (if you have
2^46 partitions)

I don't understand what you're trying to accomplish here, and this patch
is clearly dead on arrival. Maybe you can explain in simpler terms what
you're trying to accomplish.

Very briefly: better performance with less load on database.

But I see that I have not explained it nearly enough.

I will write some blog posts where I can go both deeper and wider,
with benchmarks and graphs.

----
Hannu

On Fri, Oct 23, 2026 at 08:39:36PM +0200, Hannu Krosing wrote:

On Fri, Oct 23, 2026 at 1:41 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:

I think you have to stop flooding this list with code that nobody wrote
and nobody will read.

+1.

This was not meant to be final in any way; it was meant to introduce
an *idea* with a PoC implementation for people who want to verify its
merits.

You could send ideas, without patches attached, or just let them live
on a branch somewhere else and post a link to it. If you just post a
concept in a few words or a design, an LLM could most likely digest
your idea and produce code matching with what's been done, these days.

Was marking it [PATCH] the main confusing thing, or was there also
something else?

Sending a zillion number of AI-generated POC ideas on the lists is not
a particularly useful thing to do and not a useful thing to load the
Postgres infrastructure servers with.

FWIW, my state of mind regarding these submissions gets rather simpler
these days: as a committer, I'm not going to spend time in brushing
and fixing code that the author has visibly not written or thought
much about, especially if there is a high quantity of it, and I'm
seeing a lot of quantity and what looks like low-quality code from
your email address. I'd suggest to focus on quality, not AI-generated
quantity. The former is much harder to do than the latter these days.
When it comes to the amount of code to review, as a committer the
former (quality) matters much, much, more to me than the latter
(quantity) these days, even more if the patch submitter has visibly
put some efforts into it and is ready to argue over it.

Spoiler: my own impression is that your contents are mostly
copy-pasted from AI-generated contents, they give zero impression that
you put efforts in it yourself, and that you just gave orders to an
LLM to do something. I tend to just ignore these as noise; and I'm in
peace with that depending on the contents posted because it's less
noise to deal with and I can focus on getting actual quality work
done. There are exchanges with your ideas where I see that folks
(senior contributors, committers) clearly raise eyebrows and say that
they may not be good ideas. Overall, it feels to me that you keep
rejecting these arguments on the ground that your ideas are better.
Consensus is a multi-dimensional concept, it is not one-dimensional.

What makes me sad is that these MBs worth of patches just act as a way
to backup your data in everybody's mail boxes, for stuff that I
suspect few are really interested to look at. Posting ideas in the
shape of an email is fine, bumping dozens of MBs worth of data that
only another AI is able to digest is an insane loop to enter. If you
are not ready to argue over each line of code, perhaps the code is not
good for posting it yet.

After writing that, I'll go back to enjoying my weekend.
--
Michael

On Sat, Oct 24, 2026 at 3:02 AM Michael Paquier <michael@paquier.xyz> wrote:

On Fri, Oct 23, 2026 at 08:39:36PM +0200, Hannu Krosing wrote:

On Fri, Oct 23, 2026 at 1:41 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:

I think you have to stop flooding this list with code that nobody wrote
and nobody will read.

+1.

This was not meant to be final in any way; it was meant to introduce
an *idea* with a PoC implementation for people who want to verify its
merits.

You could send ideas, without patches attached,

That does not work too well, as people often claim it can not be done.
Having a proof of concept demonstrates that it can be done, and that
it has the benefits I claimed.

or just let them live on a branch somewhere else and post a link to it.

Yup, I figured out this wis actually the way to do it.

It will make the patch evolution less visible here, so at some point
the patches should still go through the list, but that can be later.

If you just post a
concept in a few words or a design, an LLM could most likely digest
your idea and produce code matching with what's been done, these days.

Nah. Any Larger things I have posted here have gone through at least
weeks if not months of development, debugging and tuning.

I probably could send the resulting, quite long, detailed design docs
if you think these could get some traction more easily?

Was marking it [PATCH] the main confusing thing, or was there also
something else?

Sending a zillion number of AI-generated POC ideas on the lists is not
a particularly useful thing to do and not a useful thing to load the
Postgres infrastructure servers with.

I don't think the infrastructure is that bad. But if this distracts
people who are already severely overloaded by AI finding gazillion
bugs in existing code, then I of course should not continue doing it.

Spoiler: my own impression is that your contents are mostly
copy-pasted from AI-generated contents, they give zero impression that
you put efforts in it yourself, and that you just gave orders to an
LLM to do something.

These things are not at all mutually exclusive. In fact, I have put
many man-months of work into the larger ones. But the work has mainly
been at the architectural level, not low level code.

I tend to just ignore these as noise; and I'm in
peace with that depending on the contents posted because it's less
noise to deal with and I can focus on getting actual quality work
done. There are exchanges with your ideas where I see that folks
(senior contributors, committers) clearly raise eyebrows and say that
they may not be good ideas.

That's why I try to post PoC code. Often the only way to convince
people that something works is to *show* that it works.

Occasionally I have been able to convince them, sometimes I just give
up and keep using the things myself and have a better (more
performant, more stable, les lock-ups, etc) product.

The reason we have Vvacuum now that can clean up dead tuples while
othe long vacuums are running is that there was a 10 year PostgreSQL
anniversary conference in 2006 and there was a hacking session, where
I was able to tell Alvaro to put that in too.
Else the patch was done, had some useful comments from Tom and others
in first month or so and then was used for production at Skype for a
year, and every two months Bruce asked about getting it in, but nobody
really seemed to care. I guess we were the only ones using PostgreSQL
with large production workloads at that time.

And the same thing continues to this day,

I also have some nice ancient hand-crafted, fully artisanal patches
which solve specific cases, and these do not get any attention ether,
fter the initial issues get fixed. Basically people are ok to tell
what is wrong (thank you all, very valuable!), but once the patch has
no obvious flaws, it just lingers there.

1. https://commitfest.postgresql.org/patch/5774/ - "Extend skipping FK
checks on replicas to include ADD FK and TRUNCATE", 1.5 years old

This avoids many hours (on hge tables) of completely unneccesary work
when replica or restore checks foreign key validity between two tables
which were copied out from a valid database.

2. https://commitfest.postgresql.org/patch/6219/ - "pg_dump flag for
dumping tables data in multiple chunks, for huge tables can often give
near-linear speedup when parallelized", 1 year old

This allows speeding up dumping data from a single huge table if you
do have extra CPU, disk and network bandwidth.

And yes, often also the stupid indexed TOAST is involved, but at least
this allows operations to run near disk speed by throwing lots of CPU
at it.

This isn't a trivial speedup, it can reduce a 10-day dump to just a few hours.

I also had a patch that solves it by just dumping TOAST table
separately which makes it all run at good speeds without extra CPU,
but I did not even try to upstream that one knowing how unorthodox
that would be considered. This one btw works well only with the
Indexed TOAST :)

Overall, it feels to me that you keep
rejecting these arguments on the ground that your ideas are better.

What other grounds there could be?

Consensus is a multi-dimensional concept, it is not one-dimensional.

They mostly are *technically* better, but yes, consensus is a tricky
thing and can take a long time to get to.

Interestingly some of them seem to understand much better when talked
to in person.

What makes me sad is that these MBs worth of patches just act as a way
to backup your data in everybody's mail boxes, for stuff that I
suspect few are really interested to look at. Posting ideas in the
shape of an email is fine, bumping dozens of MBs worth of data that
only another AI is able to digest is an insane loop to enter. If you
are not ready to argue over each line of code, perhaps the code is not
good for posting it yet.

This is more a comment on how PostgreSQL development and code reviews work :)

After writing that, I'll go back to enjoying my weekend.

Sure, have a nice weekend! :)

And thanks for comments.

----
Hannu