Re: Disable WAL logging to speed up data loading

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: "tsunakawa(dot)takay(at)fujitsu(dot)com" <tsunakawa(dot)takay(at)fujitsu(dot)com>
Cc: 'David Steele' <david(at)pgmasters(dot)net>, "osumi(dot)takamichi(at)fujitsu(dot)com" <osumi(dot)takamichi(at)fujitsu(dot)com>, 'Kyotaro Horiguchi' <horikyota(dot)ntt(at)gmail(dot)com>, "sawada(dot)mshk(at)gmail(dot)com" <sawada(dot)mshk(at)gmail(dot)com>, "robertmhaas(at)gmail(dot)com" <robertmhaas(at)gmail(dot)com>, "masao(dot)fujii(at)oss(dot)nttdata(dot)com" <masao(dot)fujii(at)oss(dot)nttdata(dot)com>, "laurenz(dot)albe(at)cybertec(dot)at" <laurenz(dot)albe(at)cybertec(dot)at>, "ashutosh(dot)bapat(dot)oss(at)gmail(dot)com" <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Disable WAL logging to speed up data loading
Date: 2021-03-19 15:13:52
Message-ID: 20210319151352.GP20766@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Greetings,

* tsunakawa(dot)takay(at)fujitsu(dot)com (tsunakawa(dot)takay(at)fujitsu(dot)com) wrote:
> From: David Steele <david(at)pgmasters(dot)net>
> > After reading through the thread (but not reading the patch) I am -1 on
> > this proposal.
> >
> > The feature seems ripe for abuse and misunderstanding, and as has been
> > noted in the thread, there are a variety of alternatives that can
> > provide a similar effect.
> >
> > It doesn't help that at several points along the way new WAL records
> > have been found that still need to be included even when wal_level =
> > none. It's not clear to me how we know when we have found them all.
> >
> > The patch is marked Ready for Committer but as far as I can see there
> > are no committers in favor of it and quite a few who are not.
>
> I can understand that people are worried about not having WAL. But as far as I remember, I'm afraid those concerns were emotional, not logical, i.e., something like "something may happen.". Regarding concrete concerns that Stephen-san, Magnus-san, Horiguchi-san, Sawada-san and others raised, Osumi-san addressed them based on their advice and review, both in this thread and other threads.
>
> I also understand we want to value people's emotion for worry-free PostgreSQL. At the same time, I'd like the emotion understood that we want Postgres to have this convenient, easy-to-use feature. MySQL recently introduced this feature. Why can't Postgres do it?

I don't see there being anything particularly 'emotional' around
suggesting that we already have multiple ways to avoid WAL writes when
populating tables and suggesting that we work to improve those instead
of adding yet another WAL level (note: we've been removing them of late,
for the good reason that having more is confusing and not helpful).

> > Perhaps it would be better to look at some of the more targeted
> > approaches mentioned in the thread and see if any of them can be
> > used/improved to achieve the desired result?
>
> Other methods are not as easy-to-use, and more complex to implement.

The argument here seems to stem from the idea that issueing a 'TRUNCATE'
inside the transaction before starting the 'COPY' command is 'too hard'.
That's clearly a subjective question and perhaps it's my emotional
response to that which is at issue, but I don't think I'm alone in the
feeling that just isn't enough of a justification for adding a new WAL
level.

> What kind of destiny does this type of feature end up in?

I could be wrong and perhaps opinions will change in the future, but it
really doesn't seem like the there's much support for adding a new WAL
level just to avoid doing the TRUNCATE. Appealing to what other
database systems support can be helpful in some cases but that's usually
when we're looking at a new capability which multiple other systems have
implemented. This isn't actually a new capability at all- the WAL level
that you're looking for already exists and already gives the
optimization you're looking for, as long as you issue a TRUNCATE at the
start of the transaction.

Thanks,

Stephen

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Steele 2021-03-19 15:27:50 Re: [PATCH] ProcessInterrupts_hook
Previous Message Emre Hasegeli 2021-03-19 14:55:24 Re: default result formats setting