What makes ALTER TABLE X SET UNLOGGED; to be slow?

Question

We develop and maintain a JEE Application with Postgres (multiple version of it as we've got different customers using different versions). The Code evolves over time; database changes are organized using Liquibase.

Every now and then a customer decided to upgrade to another major version, and many individual updates may be applied in an aggregated fashion (multiple individual change column type on the same table). I am investigating an option, if one could speed up the process using the SET (UN)LOGGED clause (of course making appropriate backups before).

Consider X to be a 5GB (with 4GB of indexes, in four of indexes) large table residing on an SSD. If I switch the mode:

CHECKPOINT;
ALTER TABLE X SET UNLOGGED;
--ALTER TABLE X SET LOGGED;

it takes around 3 minutes to execute (the alter table is what is slow, checkpoint was fast). Why? What is happening behind the scenes? How can I speed it up?

EDIT:

Now what I experience is the reverse. I started with the unlogged setting and tried to set it to logged one:

This cannot be a lock-related issue:

select pg_class.relname, pg_locks.locktype, pg_locks.mode, pg_locks.granted, pg_locks.fastpath, pg_locks.virtualtransaction
from pg_locks
left join pg_class on pg_class.oid=pg_locks.relation


              relname              |  locktype  |      mode       | granted | fastpath | virtualtransaction
-----------------------------------+------------+-----------------+---------+----------+--------------------
 pg_class_tblspc_relfilenode_index | relation   | AccessShareLock | t       | t        | 6/6235
 pg_class_relname_nsp_index        | relation   | AccessShareLock | t       | t        | 6/6235
 pg_class_oid_index                | relation   | AccessShareLock | t       | t        | 6/6235
 pg_class                          | relation   | AccessShareLock | t       | t        | 6/6235
 pg_locks                          | relation   | AccessShareLock | t       | t        | 6/6235
                                   | virtualxid | ExclusiveLock   | t       | t        | 6/6235
(6 rows)

The Database is idle. The JEE App is not running.

ALTER TABLE X SET LOGGED;
Query returned successfully with no result in 03:04 minutes.

What a hack? Does it copy all pages into the transaction log?

Locks during execution (from another session):

              relname              | relation |   locktype    |        mode         | granted | fastpath | virtualtransaction
-----------------------------------+----------+---------------+---------------------+---------+----------+--------------------
 pg_class_tblspc_relfilenode_index |     3455 | relation      | AccessShareLock     | t       | t        | 6/6239
 pg_class_relname_nsp_index        |     2663 | relation      | AccessShareLock     | t       | t        | 6/6239
 pg_class_oid_index                |     2662 | relation      | AccessShareLock     | t       | t        | 6/6239
 pg_class                          |     1259 | relation      | AccessShareLock     | t       | t        | 6/6239
 pg_locks                          |    11695 | relation      | AccessShareLock     | t       | t        | 6/6239
                                   |          | virtualxid    | ExclusiveLock       | t       | t        | 6/6239
                                   |          | virtualxid    | ExclusiveLock       | t       | t        | 2/1524
                                   |  4351386 | relation      | AccessExclusiveLock | t       | f        | 2/1524
                                   |  4351391 | relation      | AccessExclusiveLock | t       | f        | 2/1524
                                   |  4351389 | relation      | ShareLock           | t       | f        | 2/1524
                                   |          | transactionid | ExclusiveLock       | t       | f        | 2/1524
 X                                 |  3518884 | relation      | AccessExclusiveLock | t       | f        | 2/1524
(12 rows)

I wonder what the other 3 relations are. They are not pointing to the indexes. As there are four of them..

EDIT 2 Setting to logged state will refill the transaction log. Especially if some of the shared buffers were not flushed. So it is safe to remove my 1st edit. After checkpoint'ing again and restarting the server I fired the unlogged query again (idle DB, no JEE App, table was in logged state):

ALTER TABLE X SET UNLOGGED;
Query returned successfully with no result in 02:38 minutes.

From /src/backend/commands/tablecmds.c I can see that the code for LOGGED and UNLOGGED is very similar:

    case AT_SetUnLogged:    /* SET UNLOGGED */
        ATSimplePermissions(rel, ATT_TABLE);
        tab->chgPersistence = ATPrepChangePersistence(rel, false);
        /* force rewrite if necessary; see comment in ATRewriteTables */
        if (tab->chgPersistence)
        {
            tab->rewrite |= AT_REWRITE_ALTER_PERSISTENCE;
            tab->newrelpersistence = RELPERSISTENCE_UNLOGGED;
        }
        pass = AT_PASS_MISC;
        break;

anyone can help me to interpret the code? The DB thinks it might need to flush push the data into the transaction log after checkpoint and restart of postgres?

What makes ALTER TABLE X SET UNLOGGED; to be slow?

0 Answers0