Re: Disable WAL logging to speed up data loading

On Mon, 2021-03-22 at 13:22 -0400, Stephen Frost wrote:
> * 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.

The confusion is probably caused by my ignorance.
If the actual data files of the table are forced out to disk on COMMIT,
that should indeed work. And if you fsync a file that has not been changed
since it was last persisted, that should be cheap.

So if I got that right, I agree with you that that would be a much better
way to achieve the goal than wal_level = 'none'.

The only drawback is that we don't have that feature yet...

Yours,
Laurenz Albe