Postgres, Large Arrays & MVCC

Question

I'm contemplating the following schema:

create table pool
(
    id integer not null
        constraint pool_pk
            primary key,
    contact_index integer not null,
    contact_id integer[]
);

Basically a large array of integers, which could be as many as 1 million - and are only written once, and an index value into them that is updated over time.

My question is:

If I update the contact_index field will that cause a rewrite of the entire row, or is the large array field stored "off page" somehow?

"If I update the contact_index field will that cause a rewrite of the entire row," yes, it will. You are better off with a properly normalized model using a one-to-many relationship — user330315
– user330315, Commented Aug 25, 2020 at 7:31

jjanes · Accepted Answer · 2020-08-25 15:40:14Z

1

An array of 1e6 integers will be stored off page in TOAST. If you update something else in the row, the TOAST data does not get copied for the new row version, just the TOAST pointer does.

answered Aug 25, 2020 at 15:40

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres, Large Arrays & MVCC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related