[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Please help me understand unlogged tables
From:       Jeff Janes <jeff.janes () gmail ! com>
Date:       2018-01-31 22:44:43
Message-ID: CAMkU=1zw1_PWL-P7kaCBeC8ePGCvQTjPDZ=8O5y8Awb7qm_nMA () mail ! gmail ! com
[Download RAW message or body]

On Wed, Jan 31, 2018 at 8:52 AM, Alexander Stoddard <
alexander.stoddard@gmail.com> wrote:

> If a table is set to unlogged is it inherently non-durable? That, is any
> crash or unsafe shutdown _must_ result in truncation upon recovery?
>

Yes.


> I can imagine a table that is bulk loaded in a warehousing scenario and
> then sitting statically could be safe, but maybe the question becomes how
> could the system know it is unchanged if it isn't logged...
>

Right, that is the problem.  It would be nice to solve it, but there are no
immediate plans that I know of to implement that.  It is hard, because
during recovery the system can't query system catalog tables to get
information out of them.  That is why unlogged tables are indicate by the
existence of certain files in the filesystem, so the system can know they
are unlogged without querying the system catalogs to find out.


> Would a correct solution be to bulk load into an unlogged table and then
> (fairly cheaply?) set the table back to being logged?
>

Unfortunately, it is not cheap for large tables.

I have some large datasets which I could always regenerate if needed, which
I don't want blowing out my wal archive.  I've resigned myself to just
reloading them after a crash (or after using pg_basebackup to reclone
production to make a fresh test/dev server).  If the ETL step from the raw
source is expensive, then I \copy the table out to a file, and then \copy
it back in after a crash, to save on the ET time.


Cheers,

Jeff

[Attachment #3 (text/html)]

<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Wed, Jan 31, 2018 \
at 8:52 AM, Alexander Stoddard <span dir="ltr">&lt;<a \
href="mailto:alexander.stoddard@gmail.com" \
target="_blank">alexander.stoddard@gmail.com</a>&gt;</span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr">If a table is set to unlogged is it inherently \
non-durable? That, is any crash or unsafe shutdown _must_ result in truncation upon \
recovery?</div></blockquote><div><br></div><div>Yes.</div><div>  </div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div>I can imagine a table that is bulk loaded \
in a warehousing scenario and then sitting statically could be safe, but maybe the \
question becomes how could the system know it is unchanged if it isn&#39;t \
logged...</div></div></blockquote><div><br></div><div>Right, that is the problem.   \
It would be nice to solve it, but there are no immediate plans that I know of to \
implement that.   It is hard, because during recovery the system can&#39;t query \
system catalog tables to get information out of them.   That is why unlogged tables \
are indicate by the existence of certain files in the filesystem, so the system can \
know they are unlogged without querying the system catalogs to find out.</div><div>  \
</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div>Would a correct solution be to bulk load \
into an unlogged table and then (fairly cheaply?) set the table back to being \
logged?</div></div></blockquote><div><br></div><div>Unfortunately, it is not cheap \
for large tables.</div><div><br></div><div>I have some large datasets which I could \
always regenerate if needed, which I don&#39;t want blowing out my wal archive.   \
I&#39;ve resigned myself to just reloading them after a crash (or after using \
pg_basebackup to reclone production to make a fresh test/dev server).   If the ETL \
step from the raw source is expensive, then I \copy the table out to a file, and then \
\copy it back in after a crash, to save on the ET time.</div><div>    \
</div><div><br></div><div>Cheers,</div><div><br></div><div>Jeff</div></div></div></div>




[prev in list] [next in list] [prev in thread] [next in thread]