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.