Re: Disable WAL logging to speed up data loading - Mailing list pgsql-hackers

Greetings,

* tsunakawa.takay@fujitsu.com (tsunakawa.takay@fujitsu.com) wrote:
> * ALTER TABLE SET UNLOGGED/LOGGED without data copy
> Good:
> - Does not require server restart (if this feature can be used in all wal_level settings).
>
> Bad:
> - The user has to maintain and modify some scripts to use ALTER TABLE when adding or removing the tables/partitions
toload data into.  For example, if the data loading job specifies a partitioned table, he may forget to add ALTER TABLE
fornew partitions, resulting in slow data loading. 

I'm not sure that I see this as really being much of an issue.  Perhaps
there are some things we can do, as I mentioned before, to make it
easier for users to have tables be created as unlogged from the start,
or to be able to ALTER TABLE a bunch of tables at once (using all in
tablespace, or maybe having an ALTER TABLE on a partitioned table
cascade to the partitions), but overall the risk here seems very low-
clearly whatever processing is running to load the data into a
particular table knows what the table is and adding an ALTER TABLE into
it would be trivial.

Specifically, for a partitioned table, I would think the load would go
something like:

CREATE UNLOGGED TABLE ...
load all of the data
ALTER TABLE ... SET LOGGED
ALTER TABLE ... ATTACH PARTITION

If the data load could all be done in a single transaction then you
wouldn't even need to create the table as UNLOGGED or issue the SET
LOGGED, with wal_level=minimal, you just need to create the table in the
same transaction that you do the data load in.

> * wal_level = none
> Good:
> - Easy to use.  The user does not have to be aware of what tables are loaded.  This can come in handy when migrating
froman older version or another DBMS, building test databases, and consolidating databases. 

A GUC that allowed users to set a default for newly created tables to be
unlogged would also address this.

> Bad:
> - Requires server restart.

Introducing yet another wal_level strikes me as a very large step, one
that the arguments presented here for why it'd be worth it don't come
anywhere near justifying that step.

> I expect both features will be able to meet our customer's needs.  The worst scenario (I don't want to imagine!) is
thatneither feature fails to be committed.  So, let us continue both features.  I'll join Horiguchi-san's new thread,
andplease help us here too.  (I'll catch up with the recent discussion in this thread and reply.) 

While you're certainly welcome to spend your time where you wish to, if,
as you say, making these changes to how tables can be switched from
unlogged to logged with wal_level=minimal meets this use-case then that
strikes me as definitely the right approach and removes any
justification for adding another wal_level.

> > Couldn't we have something like the following?
> >
> >  ALTER TABLE table1, table2, table3 SET UNLOGGED;
> >
> > That is, multiple target object specification in ALTER TABLE sttatement.
>
> Likewise, can't we do ALTER TABLE SET UNLOGGED/LOGGED against a partitioned table?  Currently, the statement succeeds
butnone of the partitioned table nor its partitions is set unlogged (pg_class.relpersistence remains 'p').  Is this
intended? If it's a bug, I'm willing to fix it so that it reports an eror.  Of course, it's good to make all partitions
unloggedat once. 

I agree that this doesn't seem quite right and considering the way other
commands work like CREATE INDEX, I would think that doing such an ALTER
TABLE would recurse to the individual partitions (skipping over any
which are already set to the persistance desired..).

Thanks,

Stephen