Обсуждение: 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