RE: Disable WAL logging to speed up data loading

Horiguchi-San


On Tuesday, November 10, 2020 10:00 AM  Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote:
> FWIW, the following is that, I think it works not only when wal_level=minimal
> for SET UNLOGGED, and only works when minimal for SET LOGGED.
>
> https://www.postgresql.org/message-id/20201002.100621.166891875652013
> 6893.horikyota.ntt@gmail.com
>
> | For fuel(?) of the discussion, I tried a very-quick PoC for in-place
> | ALTER TABLE SET LOGGED/UNLOGGED and resulted as attached. After
> some
> | trials of several ways, I drifted to the following way after poking
> | several ways.
> |
> | 1. Flip BM_PERMANENT of active buffers 2. adding/removing init fork 3.
> | sync files, 4. Flip pg_class.relpersistence.
> |
> | It always skips table copy in the SET UNLOGGED case, and only when
> | wal_level=minimal in the SET LOGGED case.  Crash recovery seems
> | working by some brief testing by hand.
> I agree to this aspect of the in-place flipping of UNLOGGED.
>
> Couldn't we have something like the following?
>  ALTER TABLE table1, table2, table3 SET UNLOGGED;
>
> That is, multiple target object specification in ALTER TABLE sttatement.
I *really* appreciate the 1st patch.
Did you register this patch with the current or next commitfest ?
I cannot find it. We should avoid that this patch misses people's attention.
When I'm available, I'd like to join the review for the patch as well.

Also, I supposed that as long as
something impossible to implement for wal_level=none is not founded,
we could develop both patches in parallel, because both have good points
described in the previous e-mails of this thread.

Best,
    Takamichi Osumi