Implement UNLOGGED clause for COPY FROM

From: "osumi(dot)takamichi(at)fujitsu(dot)com" <osumi(dot)takamichi(at)fujitsu(dot)com>
To: "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>
Subject: Implement UNLOGGED clause for COPY FROM
Date: 2020-07-09 02:36:36
Message-ID: OSBPR01MB488887C0BDC5129C65DFC5E5ED640@OSBPR01MB4888.jpnprd01.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello, hackers.

I've been thinking to suggest
a peformance-oriented feature for COPY FROM.
It's UNLOGGED clause, which means data loading skipping WAL generation.

How to make it work is the following.
1. Aquire ACCESS EXCLUSIVE mode to lock the target table and its indexes.
2. Mark those relations 'unrecoverable' in pg_class/pg_index.
3. Issue one WAL to indicate when COPY UNLOGGED is executed.
4. Execute the data loading, bypassing WAL generation for data.
5. Sync the data to disk by performing checkpoint.

During the recovery,
I'd like to make postgres recognize both marked 'unrecoverable' flags of the second step
and when the command was issued by the third step
in order to recover data that the target table had before the execution of COPY UNLOGGED.

Oracle's SQL*Loader has
such a feature called UNRECOVERABLE to boost the loading speed
for severe time limit of workload.

Best,
Takamichi Osumi

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2020-07-09 02:40:42 Re: Is this a bug in pg_current_logfile() on Windows?
Previous Message Amit Kapila 2020-07-09 02:27:44 Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions