pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joe Conway <mail@joeconway.com>
Cc: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: Jim Mlodgenski <jimmy76@gmail.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: alter table xxx set unlogged take long time
Date: Wed, 27 Jul 2022 10:46:23 -0400
Message-ID: <2883168.1658933183@sss.pgh.pa.us> (raw)
In-Reply-To: <e1920272-57a6-7c5a-924d-be26065ad9ad@joeconway.com>
References: <PH0PR11MB5191F1E9F55101897E453814D6949@PH0PR11MB5191.namprd11.prod.outlook.com>
	<CAB_5SRdsCDx8Mq974S=WxWBG+vSD7axZVtAoTvMvzQ+6ghffuA@mail.gmail.com>
	<PH0PR11MB51919A15ED33C356120145F7D6949@PH0PR11MB5191.namprd11.prod.outlook.com>
	<2240967.1658839372@sss.pgh.pa.us>
	<PH0PR11MB51917F811DDACDBC4B0D9AEAD6949@PH0PR11MB5191.namprd11.prod.outlook.com>
	<CAB_5SRf6JmebYvcVXmzRtpDB1e9+KsGHLJGznjmWzpg2LxcPAQ@mail.gmail.com>
	<PH0PR11MB51918C3648CF7FECAC90CABED6949@PH0PR11MB5191.namprd11.prod.outlook.com>
	<e1920272-57a6-7c5a-924d-be26065ad9ad@joeconway.com>

Joe Conway <mail@joeconway.com> writes:
> Then (completely untested) I *think* you could create the "partition" 
> initially as a free standing unlogged table, load it, index it, switch 
> to logged, and then attach it to the partitioned table.

I'm still of the opinion that this plan to load the data unlogged
and switch to logged later is a loser.  Sooner or later you have
got to write the data to WAL, and this approach doesn't eliminate
that cost.  What it does do is create one whole extra cycle of
writing the data to disk and reading it back.  I don't think
it's an oversight that no such thing is suggested in our standard
tips for bulk-loading data:

https://www.postgresql.org/docs/current/populate.html

What perhaps *is* an oversight is that we don't suggest
use of COPY FREEZE there.  AFAIK that doesn't reduce the initial
data loading cost directly, but it would save overhead later.

			regards, tom lane





view thread (14+ messages)  latest in thread

Message-ID: <2883168.1658933183@sss.pgh.pa.us>
Permalink:  ../2883168.1658933183@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2883168.1658933183@sss.pgh.pa.us

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, mail@joeconway.com, chaolpan@cisco.com, jimmy76@gmail.com, pgsql-performance@lists.postgresql.org
  Subject: Re: alter table xxx set unlogged take long time
  In-Reply-To: <2883168.1658933183@sss.pgh.pa.us>

* 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