Question about unlogged to logged conversion
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?
Also, at a high level, what happens when the table is converted from
Unlogged to logged? Is the operation time dependent on size and number of
rows?
Thanks.
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.
On Thu, Dec 27, 2018 at 05:52:14PM -0500, Ravi Krishna wrote:
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.
If you are interested of how things happen at code level, you can grep
for INIT_FORKNUM, which is the initial state of an unlogged table used
when doing crash recovery. And while the main fork's data is never
WAL-logged, this initial fork needs to be included in WAL.
--
Michael