Linked Questions

Popular Questions

I'm working on a software that initially populates tables with a lot of data (bulk mode), after which it switches to "normal" mode of operation.

To make bulk mode fast, I'm starting with UNLOGGED tables with almost no indices (among other things).

After loading all the initial data I switch to to normal mode: I'm creating indices and altering the tables to be LOGGED again.

My question is: performance wise - is there any difference in order of creating indices and changing to LOGGED tables? From reliability perspective, it seems that the fastest I switch to LOGGED, the better (lower chance of loosing all my precious data). But will I have to pay for it in time it takes to create all the indices?

In my initial tests, it seems that ALTER TABLE x SET LOGGED is taking quite a bit of time, which I don't understand because I thought it affects only operations after it, and in itself is almost an NO-OP.

My PG is:

PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit

though I'm asking more generally. Any links to things that would help my understand what exactly is going on with UNLOGGED and LOGGED (especially in combination with indicies) would be also appreciated.

Related Questions