Re: Disable WAL logging to speed up data loading

On Mon, Nov 9, 2020 at 8:18 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Presently, my feeling is that we could address this use-case without
>
having to introduce a new cluster-wide WAL level, and that's the
> direction I'd want to see this going. Perhaps I'm missing something
> about why the approach I've set forth above wouldn't work, and
> wal_level=none would, but I've not seen it yet.
>
>
+1

We are trying to address a performance optimization for an insert-only
scenario on a limited set of tables by placing the entire cluster in a
dangerous state. The "copy table unlogged" solution is definitely closer
to what we want - this is
demonstrably worse.

For this case the fundamental feature that would seem to be required is an
ability for a transaction commit to return only after the system has
ensured that all of the new pages added to the relation during the scope of
the transaction have made it to disk. Something like:

BEGIN UNLOGGED TRANSACTION FOR table1, table2;
-- locking probably allows reads, definitely disallows concurrent writes,
to the named tables
-- Disallow updates and deletes, do not use dead tuple space, for the
tables named. Should be able to do normal stuff for other tables?
-- Always create new pages
COPY TO table1;
COPY TO table2;
COMMIT; -- wait here until data files for table1 and table2 are completely
written and the transaction alive flag is committed to the WAL.

I suppose the above could be written "BEGIN UNLOGGED TRANSACTION FOR ALL
TABLES" and you'd get the initial database population optimization
capability.

If the commit doesn't complete all of the newly created pages are junk.
Otherwise, you have a crash-recoverable state for those tables as regards
those specific pages.

Conceptually, we need an ability to perform a partial CHECKPOINT that names
specific tables, and make sure the crash-recovery works for those tables
while figuring out what amount of effort to expend on informing the dba and
alerting/preventing features that require wal from using those tables.

David J.