pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andres Freund <andres@anarazel.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Fujii Masao <masao.fujii@oss.nttdata.com>
Cc: skoposov@ed.ac.uk, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16722: PG hanging on COPY when table has close to 2^32 toasts in the table.
Date: Wed, 18 Nov 2020 17:40:43 -0800
Message-ID: <20201119014043.mpf6g467hu55w7fc@alap3.anarazel.de> (raw)
In-Reply-To: <1013618.1605748675@sss.pgh.pa.us>
References: <16722-93043fb459a41073@postgresql.org>
	<95bfb4b5-2e0e-8ab7-12cf-03fae3367c52@oss.nttdata.com>
	<CABUevExw6AHqC_U7nH83=rvF-xY_U8AUDbR94kBDqDR0hGqKXw@mail.gmail.com>
	<20201118192743.kr43onoxci5gexjx@alap3.anarazel.de>
	<CABUevEw69Es-s7fcvrNfZx9SWoPt-ZX+GJNFn_wNPoMtYAvNCw@mail.gmail.com>
	<20201118230529.6u7fyl6trnepekgc@alap3.anarazel.de>
	<1010421.1605744154@sss.pgh.pa.us>
	<20201119010637.uu5ub4qjnywytpt4@alap3.anarazel.de>
	<1013618.1605748675@sss.pgh.pa.us>

Hi,

On 2020-11-18 20:17:55 -0500, Tom Lane wrote:
> I'm not really on board with creating an actual sequence object for each
> toast table.  I don't think they are efficient enough.

I'm not convinced that's true. Sure, nextval() could stand to be cheaper
- but on the other hand, this would allow us to entirely avoid doing
ahead-of-time uniqueness probes. Needing one extra WAL record every
SEQ_LOG_VALS/32 toast insertions seems likely to be win over doing
at least SEQ_LOG_VALS-1 additional uniqueness index probes.

It's not like the oid counter is free of contention - one benefit of
what I suggest would be that the potential contention point would move
from system wide to table wide.

I think reducing the rate of oid counter advancement is a significant
benefit, moving the point of where we have to care about efficiency
problems around system oid assignment costs quite a bit further into the
future.

I think there's also some small potential benefits around indexing in
having densely allocated toast chunk ids for each toast table, over the
sparser allocation we have right now.

Greetings,

Andres Freund





view thread (28+ messages)  latest in thread

Message-ID: <20201119014043.mpf6g467hu55w7fc@alap3.anarazel.de>
Permalink:  ../20201119014043.mpf6g467hu55w7fc@alap3.anarazel.de/
Also on:    postgresql.org/message-id/20201119014043.mpf6g467hu55w7fc@alap3.anarazel.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: andres@anarazel.de, tgl@sss.pgh.pa.us, magnus@hagander.net, masao.fujii@oss.nttdata.com
  Subject: Re: BUG #16722: PG hanging on COPY when table has close to 2^32 toasts in the table.
  In-Reply-To: <20201119014043.mpf6g467hu55w7fc@alap3.anarazel.de>
  References: <16722-93043fb459a41073@postgresql.org> <95bfb4b5-2e0e-8ab7-12cf-03fae3367c52@oss.nttdata.com> <CABUevExw6AHqC_U7nH83=rvF-xY_U8AUDbR94kBDqDR0hGqKXw@mail.gmail.com> <20201118192743.kr43onoxci5gexjx@alap3.anarazel.de> <CABUevEw69Es-s7fcvrNfZx9SWoPt-ZX+GJNFn_wNPoMtYAvNCw@mail.gmail.com> <20201118230529.6u7fyl6trnepekgc@alap3.anarazel.de> <1010421.1605744154@sss.pgh.pa.us> <20201119010637.uu5ub4qjnywytpt4@alap3.anarazel.de> <1013618.1605748675@sss.pgh.pa.us> <20201119014043.mpf6g467hu55w7fc@alap3.anarazel.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox