Re: Disable WAL logging to speed up data loading

Greetings,

* tsunakawa(dot)takay(at)fujitsu(dot)com (tsunakawa(dot)takay(at)fujitsu(dot)com) wrote:
> From: Stephen Frost <sfrost(at)snowman(dot)net>
> > First- what are you expecting would actually happen during crash recovery in
> > this specific case with your proposed new WAL level?
> ...
> > 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.
>
> As Laurenz-san kindly replied, the database server refuses to start with a clear message. So, it's similarly very clear what happens. The user will never unknowingly resume operation with possibly corrupt data.

No, instead they'll just have a completely broken system that has to be
rebuilt or restored from a backup. That doesn't strike me as a good
result.

> > We're talking about two different ways to accomplish essentially the same
> > thing- one which introduces a new WAL level, vs. one which adds an
> > optimization for a WAL level we already have. That the second is more elegant
> > is more-or-less entirely the point I'm making here, so it seems pretty relevant.
>
> So, I understood the point boils down to elegance. Could I ask what makes you feel ALTER TABLE UNLOGGED/LOGGED is (more) elegant? I'm purely asking as a user.

The impact is localized to those specific tables. The rest of the
system should come up cleanly and there won't be corruption, instead
merely the lack of data in UNLOGGED tables.

> (I don't want to digress, but if we consider the number of options for wal_level as an issue, I feel it's not elegant to have separate "replica" and "logical".)

Do you know of a way to avoid having those two distinct levels while
still writing only the WAL needed depending on if a system is doing
logical replication or not..? If you've got suggestions on how to
eliminate one of those levels, I'm sure there would be interest in doing
so. I don't see the fact that we have those two levels as justification
for adding another spelling of 'minimal'.

> > 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.
>
> The elegance of wal_level = none is that the user doesn't have to remember to add ALTER TABLE to the data loading job when they add load target tables/partitions. If they build and use their own (shell) scripts to load data, that won't be burdon or forgotten. But what would they have to do when they use ETL tools like Talend, Pentaho, and Informatica Power Center? Do those tools allow users to add custom processing like ALTER TABLE to the data loading job steps for each table? (AFAIK, not.)

I don't buy the argument that having to 'remember' to do an ALTER TABLE
is such a burden when it means that the database will still be
consistent and operational after a crash.

As for data loading tools, surely they support loading data into
UNLOGGED tables and it's certainly not hard to have a script run around
and flip those tables to LOGGED after they're loaded, and I do actually
believe some of those tools support building processes of which one step
could be such a command (I'm fairly confident Pentaho, in particular,
does as I remember building such pipelines myself...).

Thanks,

Stephen