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

On Fri, Oct 23, 2026 at 2:15 PM Heikki Linnakangas <hlinnaka(at)iki(dot)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