Обсуждение: What is the use case for UNLOGGED tables

Поиск
Список
Период
Сортировка

What is the use case for UNLOGGED tables

От
Ravi Krishna
Дата:

I am trying to understand the use case for UNLOGGED tables in PG. I am specifically talking about normal tables which
needto be turned into UNLOGGED for a specific purpose like bulk loading because generating WAL logs during the load
makesno sense, even when we take into consideration that this generally breaks replication.  
Typically work flow is:

1. Turn off logging on the table.
2. Load a large set of data using some bulk load tool.
3. Turn back logging.

Depending on RDBMS, some make (3) less painful and some make it painful by rebuilding all indexes.  DB2 is very good in
thatas the only 
penalty you have is in step (2) when the table is locked exclusively.

With PG step (3) basically rebuilds entire table.  Imagine a 500 million row table, which got additional 30 million
rowsvia a batch load. Does PG 
rebuild entire 530 million rows ?  Then what exactly is the benefit of UNLOGGED tables, unless we have permanent
unloggedtables for disposable 
data.

thanks.





Re: What is the use case for UNLOGGED tables

От
"David G. Johnston"
Дата:
On Tuesday, August 14, 2018, Ravi Krishna <sravikrishna@aol.com> wrote:
Then what exactly is the benefit of UNLOGGED tables, unless we have permanent unlogged tables for disposable
data.

I use them for "ELT" oriented processing where the final results get stored on permanently logged tables but I want to manipulate tables while transforming from the original input.

David J.

Re: What is the use case for UNLOGGED tables

От
Ravi Krishna
Дата:
>
> I use them for "ELT" oriented processing where the final results get stored on permanently logged tables but I want
tomanipulate tables while transforming from the original input. 

Yeah I see the use case.  Basically raw data -> rolled up data -> to final results in normal tables.
However flipping a normal table between LOGGED and UNLOGGED does not seem to be a use case.