From: Fujii Masao <[email protected]>
> > The speedup has already been achieved with higher durability by
> > wal_level=minimal in that case.
>
> I was thinking the same, i.e., wal_level=minimal + wal_skip_threshold would
> speed up that initial data loading.
First of all, thank you Horiguchi-san for trying to improve ALTER TABLE SET
UNLOGGED/LOGGED. That should also be appealing.
At the same time, as I said before, both features have good points. TBH, as a
user, I'm kind of attracted by MySQL's approach because of its simplicity for
users (although DBMS developers may be worried about this and that.) What
tempts me is that I can just switch on the feature with a single configuration
parameter, and continue to use existing SQL scripts and other data integration
software without knowing what tables those load data into. In the same
context, I don't have to add or delete ALTER TABLE statements when I have to
change the set of tables to be loaded. For the same reason, I'm also
interested in Oracle's another feature ALTER TABLESPACE LOGGING/NOLOGGING.
BTW, does ALTER TABLE LOGGED/UNLOGGED on a partitioned table get the change to
its all partitions? It would be a bit tedious to add/delete ALTER TABLE
LOGGED/UNLOGGED when I add/drop a partition.
Regarding data migration, data movement is not limited only to major upgrades.
It will be convenient to speed up the migration of the entire database cluster
into a new instance for testing and new deployment. (I'm not sure about recent
pg_upgrade, but pg_upgrade sometimes cannot upgrade too older versions.)
To conclude, I hope both features will be realized, and wish we won't fall in a
situation where the words fly such as "Mine is enough. Yours is risky and not
necessary."
With that said, I think we may as well separate the thread some time later for
CF entry. Otherwise, we will have trouble in finding the latest patch from the
CF entry.
> No, I have no strong objection against your trial. But I was thinking
> that it's not so easy to design and implement wal_level=none.
> For example, there are some functions and commands depending on
> the existence of WAL, like pg_switch_wal(), PREPARE TRANSACTION
> and COMMIT PREPARED. Probably you need to define how they should
> work in wal_level=none, e.g., emit an error.
Yeah, we thought pg_switch_wal() may need some treatment. We'll check
PREPARE and COMMIT PREPARED as well. I'd appreciate it if you share what you
notice at any time. It is possible that we should emit WAL records of some
resource managers, like the bootstrap mode emits WAL only for RM_XLOG_ID.
Regards
Takayuki Tsunakawa
