Set VMware PostgreSQL table to LOGGED after data loading takes a long time

Products

• VMware Tanzu Greenplum

Issue/Introduction

Changing the table from unlogged to logged using ALTER TABLE takes a long time.

UNLOGGED:

If specified, then the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log, which makes them considerably faster than ordinary tables. However, they are not crash-safe; an unlogged table is automatically truncated after a crash or unclean shutdown. The contents of an unlogged table are also not replicated to standby servers. Any indexes created on an unlogged table are automatically unlogged as well.

Environment

Product Version: 11.5

Resolution

First, check to see if there are any locks:

 select relation::regclass, * from pg_locks where not granted;

If not then this is functioning as designed and changing to logged can take a long time. The design of Set Logged leads to performance problems with large relations because we need to rewrite the entire content of the relation twice, one into a new heap and another into the WAL.

There is no advantage to taking this two-step approach. 

The advantage to loading as unlogged and then altering to logged would come if you were doing some kind of large-scale manipulation of the table (update ... from ...) after loading it but before setting to logged. Or if you, for some reason, couldn't load it with COPY but had to use individual INSERT statements. Neither of those applies here, so this 2-step method is of no benefit.