Re: Question about unlogged to logged conversion

On Thu, Dec 27, 2018, at 5:23 PM, Bhavin Gandhi wrote:
> Hello,
> I'm trying to understand for a given unlogged table of a specific size
> and # of rows, if I do "alter table" on it to convert it to logged
> table, is there a performance difference between 9.5, 9.6 and 10? in
> other words are there specific improvements in this area that may not
> be available in all Postgres versions?>

Unlogged tables are not stored in the catalog like a regular table. When
it is converted to a logged table , the entire operation is logged (goes
into wal logs) in one single transaction, blocking the entire table
during the process. IMO this makes unlogged -> logged workflow not a
right fit for large tables.  In other products I have used, large tables
are set to unlogged mode before bulk load and then set back to normal
logged mode.