0

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?

  • 1
    Most possibly some lock. Have you checked if it is the case? – dezso Jan 18 at 13:39
  • This is an interesting idea. Checked: it is not lock related issue. Any other things to try out? – arthur Jan 18 at 14:14
  • 1
    Does it copy all pages into the transaction log?: Yes. How could it become crash safe and/or replicated if it didn't do that? See dba.stackexchange.com/questions/195780/… – Daniel Vérité Jan 18 at 15:04
  • Good point. And it has a link to the source code that does the job – arthur Jan 18 at 15:12
  • 1
    @a_horse_with_no_name It needs to be prepared to rollback the transaction, perhaps after a crash, which means it needs to keep the old versions around while creating the new version. It can't just flip a bit in the catalogs, because it needs to make some decisions about this during recovery, and the catalogs are not accessible during recovery. So it has to coordinate atomicity between the catalog and the filesystem, and there is not a very good of doing this during recovery without making a copy. – jjanes Jan 18 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.