Re: BUG #13846: INSERT ON CONFLICT consumes sequencers onconflicts

On Wed, Jan 6, 2016 at 1:00 PM, Paul <paul(at)salesintel(dot)com> wrote:
> My only point is, there’s another great capability in Postgres, for doing
> efficient concurrent UPSERTS, but that one of the most common and prime use
> cases for it is going to cost more money from having to use 64bit numbers
> everywhere for no good reason.

Not really; at least, it won't increase the size of a plain BIGSERIAL
primary key index (which will generally not be composite):

postgres=# create table foo as select (random() * 100000000)::int4
pkey from generate_series(1, 100000);
SELECT 100000
postgres=# create index on foo (pkey);
CREATE INDEX

postgres=# \dt+ foo
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
────────┼──────┼───────┼───────┼─────────┼─────────────
public │ foo │ table │ pg │ 3544 kB │
(1 row)

postgres=# \di+ foo_pkey_idx
List of relations
Schema │ Name │ Type │ Owner │ Table │ Size │ Description
────────┼──────────────┼───────┼───────┼───────┼─────────┼─────────────
public │ foo_pkey_idx │ index │ pg │ foo │ 2208 kB │
(1 row)

postgres=# alter table foo alter column pkey type int8;
ALTER TABLE

postgres=# \di+ foo_pkey_idx
List of relations
Schema │ Name │ Type │ Owner │ Table │ Size │ Description
────────┼──────────────┼───────┼───────┼───────┼─────────┼─────────────
public │ foo_pkey_idx │ index │ pg │ foo │ 2208 kB │
(1 row)

The unchanged size of the index foo_pkey_idx seen here due to
alignment considerations. Granted, the heap might still be a bit
larger than it would otherwise be, because it will usually be
"composite", but I think the "cost" of your using int8 will not break
the bank.

--
Peter Geoghegan