Re: Disable WAL logging to speed up data loading

On 2020/10/01 12:04, osumi(dot)takamichi(at)fujitsu(dot)com wrote:
> Hello.
>
>>>> Can they use a database with all unlogged tables?
>>> Unfortunately, no. They want to switch a cluster condition to "without WAL
>> logging"
>>> only when they execute night bulk loading for their data warehouse.
>>> In other words, they would like to keep their other usual operations with WAL.
>>> In addition, using all tables as unlogged brings about the risk to
>>> lose data warehouse's data caused by an unexpected server crash or power
>> outage.
>>
>> But the same issue can happen even in the proposed approach because
>> Tsunakawa-san explains as follows?
> Sorry, my last expression about "to lose data" was not precise.
>
> When unlogged tables are used and the server is crashed unexpectedly,
> all data after the last backup is truncated without WAL, right ?
> In this case, sequential commands for unlogged tables disappear.

To avoid this issue we can mark the table as logged. You may be worried about
that ALTER TABLE SET LOGGED/UNLOGGED would take a long time because
the table needs to be rewriitten. One idea for that is to improve that command
so that it skips the table rewrite if wal_level=minimal.
Of course, also you can change wal_level after marking the table as unlogged.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION