Can they use a database with all unlogged tables? On Tue, Sep 29, 2020 at 1:58 PM tsunakawa.ta...@fujitsu.com <tsunakawa.ta...@fujitsu.com> wrote: > > Hello, > > > We'd like to propose a feature to disable WAL to speed up data loading. This > was inspired by a feature added in the latest MySQL. I wish you won't fear > this feature... > > > BACKGROUND > ======================================== > > This branches off from [1] as mentioned therein. Briefly speaking, a > customer wants to shorten the time for nightly loading of data into their > data warehouse as much as possible to be prepared for using the data > warehouse for new things. > > Currently, they are using Oracle's SQL*Loader with its no-logging feature. > They want a similar feature to migrate to Postgres. Other than the data > loading performance, they don't want to be concerned about the storage for > large volumes of WAL. > > In [1], we thought about something like Oracle's per-table no-logging > feature, but it seems difficult (or at least not easy.) Meanwhile, I found > another feature added in the latest MySQL 8.0.21 [2]. This proposal follows > it almost directly. That satisfies the customer request. > > As an aside, it's also conceivable that in the near future, users could see > the WAL bottleneck (WAL buffer or disk) when they utilize the parallel COPY > that is being developed in the community. > > > FUNCTIONAL SPECIFICATION > ======================================== > > Add a new value 'none' to the server configuration parameter wal_level. With > this setting: > > * No WAL is emitted. > > * The server refuses to start (pg_ctl start fails) after an abnormal shutdown > due to power outage, pg_ctl's immediate shutdown, etc, showing a > straightforward message like MySQL. > > * Features like continuous archiving, pg_basebackup, and streaming/logical > replication that requires wal_level >= replica are not available. > > * The user can use all features again if you shut down the server > successfully after data loading and reset wal_level to a value other than > none. He needs to take a base backup or rebuild the replication standby > after restarting the server. > > > In addition to the cosmetic modifications to the manual articles that refer > to wal_level, add a clause or paragraphs to the following sections to let > users know the availability of this feature. > > 14.4. Populating a Database > 18.6.1. Upgrading Data via pg_dumpall > > > PROGRAM DESIGN (main point only) > ======================================== > > As in the bootstrap mode (during initdb), when wal_level = none, XLogInsert() > does nothing and just returns a fixed value, which is the tail of the last > shutdown checkpoint WAL record. As a result, the value is set to the > relation page header's LSN field. > > In addition, it might be worth having XLogBeginInsert() and XLogRec...() to > check wal_level and just return. I don't expect much from this, but it may > be interesting to give it a try to see the squeezed performance. > > StartupXLOG() checks the wal_level setting in pg_control and quits the > startup with ereport(FATAL) accordingly. > > > [1] > Implement UNLOGGED clause for COPY FROM > https://www.postgresql.org/message-id/osbpr01mb488887c0bdc5129c65dfc5e5ed...@osbpr01mb4888.jpnprd01.prod.outlook.com > > [2] > Disabling Redo Logging > https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html#innodb-disable-redo-logging > > > Regards > Takayuki Tsunakawa > > >
-- Best Wishes, Ashutosh Bapat
