public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jeff Janes <[email protected]>
To: [email protected] <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Unlogged tables
Date: Wed, 9 Aug 2017 09:14:48 -0700
Message-ID: <CAMkU=1yPxorV7Soe9nFJc1-1yRZuMq-90RZo3u4WJoXAL7ns8w@mail.gmail.com> (raw)
In-Reply-To: <BN4PR15MB0547B6736C684F14E4E33BDF858B0@BN4PR15MB0547.namprd15.prod.outlook.com>
References: <BN4PR15MB0547B6736C684F14E4E33BDF858B0@BN4PR15MB0547.namprd15.prod.outlook.com>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

On Tue, Aug 8, 2017 at 8:20 PM, [email protected] <
[email protected]> wrote:

> Hello,
>
>
> We have a fairly large static dataset that we load into Postgres. We made
> the tables UNLOGGED and saw a pretty significant performance improvement
> for the loading. This was all fantastic until the server crashed and we
> were surprised to see during a follow up demo that the data had
> disappeared... Of course, it's all our fault for not understanding the
> implications of UNLOGGED proprely.
>
>
> However, our scenario is truly a set of tables with 100's of millions of
> rows that are effectively WORMs: we write them once only, and then only
> read from them afterwards. As such, they could not be possibly corrupted
> post-load (i think) during a server crash (short of physical disk
> defects...).
>

Yes, this is a feature many people have wanted.   You'd have to somehow
mark the unlogged table as immutable and then do a checkpoint, after which
it would no longer need to be truncated after a crash.  Alternatively, it
could be done automatically where the system would somehow know which
unlogged tables were possibly touched since the last successful checkpoint,
and truncate only those one.  But, no one has implemented such a thing.

>
> I'd like to have the performance improvement during a initial batch
> insert, and then make sure the table remains after "unclean" shutdowns,
> which, as you might have it, includes a regular Windows server shut down
> during patching for example.
>

Why doesn't the Windows scheduled shutdown signal postgres to shutdown
cleanly and wait for it to do so?  That is what is supposed to happen.


> So unlogged tables in practice are pretty flimsy. I tried to ALTER ... SET
> LOGGED, but that takes a VERY long time and pretty much negates the initial
> performance boost of loading into an unlogged table.
>

Are you using streaming or wal logging?

Cheers,

Jeff

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Unlogged tables
  In-Reply-To: <CAMkU=1yPxorV7Soe9nFJc1-1yRZuMq-90RZo3u4WJoXAL7ns8w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox