Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dfTf1-00008S-PX for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 16:16:43 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dfTf1-0005rj-AO for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 16:16:43 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfTdF-0002hg-Of for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 16:14:54 +0000 Received: from mail-qk0-x22b.google.com ([2607:f8b0:400d:c09::22b]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dfTdC-0000M7-He for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 16:14:52 +0000 Received: by mail-qk0-x22b.google.com with SMTP id z18so38547486qka.4 for ; Wed, 09 Aug 2017 09:14:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=nQeKs3K0f/GaFDnUhTtDKwdTqsiS5Pa+nbVyYw1bysw=; b=AqCz1b0Kgu3UQoI8G4mMLAeAdUQE2sBaEhtdbHEtrajKXzgGdocAFvQo0qJqLzqXZ4 xC2puuBIFsG0VEjkwlXphWui+S6K+M63A1PO4vW+6VjM4FnHX1jt36RsCVNpaMqonKfR PILki129lTuINVwVZRCOIeMRVofdTOQjejxUHDxSREZVWSSSnuQIbdwzuf/l4Gvq3v3K YsMDgZLtbeGQVZxdHAkQXSMSorRCB74wACMjUSklDLHhldla/yHpCMLrueMhpLwtWe8U 9owxWD5bZ0RYj6DXH3B6Vvn2xAQanGxNZxnkHiY4xBtUUKVMdgdsBojua5MWSMyxqxWX pThA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=nQeKs3K0f/GaFDnUhTtDKwdTqsiS5Pa+nbVyYw1bysw=; b=RTi97iBReVGwj6p2FsB+NFya57P6FRab0pA2Q9Cl5xE6kdgl6JmPFWbSG0RLAV4XjK zOe+2ij1fAz+g1SW1tRdpNe2d0DsVec6Zc3bH/tGf1sGC8DNg/pS879Uvygr72OYJjF9 dl1/BuOQUeWZR0PKpDcfRYzAhk6++ojBgiTc8utiSvkbdlQagQ9vjcnoz1kM9MnKoluB VBWiEyWIFe25LMr/NfmMfqrV5ACN4nHHI4xyqts2M7wp7HDAn5MLr7lvDmiWvV+GiIxd U9/p/HCv09+yIsme9yu6fTRipDswFLB+lecbsyyIF1ZFLPfxj7ITUerSLiuwtKipWgRX FKRQ== X-Gm-Message-State: AHYfb5h4r0Ou6WcaCKTncyjDbTcWUg54p+u17pRelFq8NsfK9yuGUP+k 4D6JfrLwcX2wNg7yAEbYpBpybvsIUSvk X-Received: by 10.55.8.140 with SMTP id 134mr10357510qki.198.1502295289474; Wed, 09 Aug 2017 09:14:49 -0700 (PDT) MIME-Version: 1.0 Received: by 10.140.102.41 with HTTP; Wed, 9 Aug 2017 09:14:48 -0700 (PDT) In-Reply-To: References: From: Jeff Janes Date: Wed, 9 Aug 2017 09:14:48 -0700 Message-ID: Subject: Re: Unlogged tables To: "ldh@laurent-hasson.com" Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a114b006413b2a9055654612c" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a114b006413b2a9055654612c Content-Type: text/plain; charset="UTF-8" On Tue, Aug 8, 2017 at 8:20 PM, ldh@laurent-hasson.com < ldh@laurent-hasson.com> 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 --001a114b006413b2a9055654612c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On T= ue, Aug 8, 2017 at 8:20 PM, ldh@l= aurent-hasson.com <ldh@laurent-hasson.com> wrote:

Hello,


We have a fairly large static dataset that we load into Postgres. We mad= e the tables UNLOGGED and saw a pretty significant performance improvement = for the loading. This was all fantastic until the server crashed and we wer= e 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 million= s of rows that are effectively WORMs: we write them=C2=A0once only, and the= n only read from them afterwards. As such, they could not be possibly corru= pted post-load (i think) during a server crash (short of physical disk defects...).


Yes, this is a feature many people have wanted. =C2=A0= 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.=C2=A0 Alternatively, it could be done automatically where the system= would somehow know which unlogged tables were possibly touched since the l= ast successful checkpoint, and truncate only those one.=C2=A0 But, no one h= as 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" shut= downs, 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 s= hutdown cleanly and wait for it to do so?=C2=A0 That is what is supposed to= happen.
=C2=A0

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 performanc= e boost of loading into an unlogged table.


Are you using streaming or wal logging?

Cheers,

Jeff
--001a114b006413b2a9055654612c--