Re: COPY FROM - to avoid WAL generation - Mailing list pgsql-general

From Andres Freund
Subject Re: COPY FROM - to avoid WAL generation
Date
Msg-id 20180821153128.uwjx724qvu2e5thz@alap3.anarazel.de
Whole thread Raw
In response to COPY FROM - to avoid WAL generation  (Ravi Krishna <sravikrishna@aol.com>)
List pgsql-general
Hi,

On 2018-08-21 15:00:03 +0000, Ravi Krishna wrote:
> In a recent thread of mine I learned something very interesting.  If a table is created and data is loaded via COPY
FROMwithin the same transaction, then PG will be smart enough to not generate WAL logs because all it needs to do is to
trackthe status of the transaction and let the data load go to the new data file created for the table.  If committed,
thetable is released for other sessions, if rolledback, vaccum will delete the data file later on.
 
> I tested it as follows for a table with 50 milllion rows.  No indexes.

Please note this is only the case if wal_level = minimal. If replication
(or PITR) is supported, that mode can't be used, because the data has to
go into the WAL.

Were you using wal_level = minimal?

(FWIW, it's not VACUUM that'd unlink the data in cause of failure, but
that doesn't really matter much).

Greetings,

Andres Freund


pgsql-general by date:

Previous
From: Jeff Ross
Date:
Subject: Re: COPY FROM - to avoid WAL generation
Next
From: Ravi Krishna
Date:
Subject: Re: COPY FROM - to avoid WAL generation