Re: Disable WAL logging to speed up data loading

Greetings,

* Laurenz Albe (laurenz(dot)albe(at)cybertec(dot)at) wrote:
> On Mon, 2021-03-22 at 11:05 -0400, Stephen Frost wrote:
> > > Perhaps allowing to set unlogged tables to logged ones without writing WAL
> > > is a more elegant way to do that, but I cannot see how that would be any
> > > more crash safe than this patch. Besides, the feature doesn't exist yet.
> >
> > I'm not suggesting it's somehow more crash safe- but it's at least very
> > clear what happens in such a case, to wit: the entire table is cleared
> > on crash recovery.
>
> I didn't look at the patch, but are you saying that after changing the
> table to LOGGED, it somehow remembers that it is not crash safe and is
> emptied if there is a crash before the next checkpoint?

I'm not sure where the confusion is, but certainly once the table has
been turned into LOGGED and that's been committed then it should be
crash safe, even under 'minimal' with the optimization to avoid actually
copying the entire table into the WAL. I don't see any particular
reason why that isn't possible to do.

Under the proposed 'none', you basically have to throw out the entire
cluster on a crash, all because you don't want to use 'UNLOGGED' when
you created the tables you want to load data into, or 'TRUNCATE' them in
the transaction where you start the data load, either of which gives us
enough indication and which we have infrastructure around dealing with
in the event of a crash during the load without everything else having
to be tossed and everything restored from a backup. That's both a
better user experience from the perspective of having fewer WAL levels
to understand and from just a general administration perspective so you
don't have to go all the way back to a backup to bring the system back
up.

Thanks,

Stephen