From aa@devshock.com Fri Nov 11 20:14:46 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 604672049F7 for ; Fri, 11 Nov 2011 16:14:46 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 61414-06 for ; Fri, 11 Nov 2011 20:14:40 +0000 (UTC) X-Greylist: delayed 00:05:50.610122 by SQLgrey-1.8.0-rc2 Received: from mail-gx0-f174.google.com (mail-gx0-f174.google.com [209.85.161.174]) by mail.postgresql.org (Postfix) with ESMTP id E92F52049F6 for ; Fri, 11 Nov 2011 16:14:39 -0400 (AST) Received: by ggnb2 with SMTP id b2so4548841ggn.19 for ; Fri, 11 Nov 2011 12:14:39 -0800 (PST) Received: by 10.101.57.14 with SMTP id j14mr5965805ank.112.1321042128418; Fri, 11 Nov 2011 12:08:48 -0800 (PST) Received: from AnibalVAIO ([200.85.32.68]) by mx.google.com with ESMTPS id i31sm14566654anm.19.2011.11.11.12.08.45 (version=TLSv1/SSLv3 cipher=OTHER); Fri, 11 Nov 2011 12:08:47 -0800 (PST) From: "Anibal David Acosta" To: Subject: unlogged tables Date: Fri, 11 Nov 2011 17:08:44 -0300 Message-ID: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0056_01CCA094.93FB84C0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AcygrLZ+T6hIE67kQG6bpqeeD6QnmQ== Content-Language: es-py X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201111/145 X-Sequence-Number: 45656 This is a multipart message in MIME format. ------=_NextPart_000_0056_01CCA094.93FB84C0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, just for clarification. Unlogged tables are not memory tables don't? If we stop postgres server (normal stop) and start again, all information in unlogged tables still remain? So, can I expect a data loss just in case of crash, power failure or SO crash don't? In case of crash, is possible that data corruption happened in a unlogged tables? For performance purpose can I use async commit and unlogged tables? Thanks! ------=_NextPart_000_0056_01CCA094.93FB84C0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello, = just for clarification.

 

Unlogged = tables are not memory tables don’t?

 

If we stop = postgres server (normal stop) and start again, all information in = unlogged tables still remain?

 

So, can I = expect a data loss just in case of crash, power failure or SO crash = don’t?

 

In case of crash, is possible that data corruption = happened in a unlogged tables?

 

For = performance purpose can I use async commit and unlogged = tables?

 

 

Thanks!

 

 

 

 

------=_NextPart_000_0056_01CCA094.93FB84C0-- From sfrost@snowman.net Fri Nov 11 20:18:28 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C461B1435024 for ; Fri, 11 Nov 2011 16:18:28 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 63167-07 for ; Fri, 11 Nov 2011 20:18:22 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from tamriel.snowman.net (tamriel.snowman.net [72.66.115.51]) by mail.postgresql.org (Postfix) with ESMTP id C35331435023 for ; Fri, 11 Nov 2011 16:18:22 -0400 (AST) Received: by tamriel.snowman.net (Postfix, from userid 1000) id 50F42229ED; Fri, 11 Nov 2011 15:18:22 -0500 (EST) Date: Fri, 11 Nov 2011 15:18:22 -0500 From: Stephen Frost To: Anibal David Acosta Cc: pgsql-performance@postgresql.org Subject: Re: unlogged tables Message-ID: <20111111201822.GN24234@tamriel.snowman.net> References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="VQl1xLnQPIyet9qq" Content-Disposition: inline In-Reply-To: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/2.6.32-5-amd64 (x86_64) X-Uptime: 15:17:07 up 282 days, 4:47, 24 users, load average: 0.00, 0.02, 0.00 User-Agent: Mutt/1.5.20 (2009-06-14) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-3.101 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-1.201 X-Spam-Level: X-Archive-Number: 201111/146 X-Sequence-Number: 45657 --VQl1xLnQPIyet9qq Content-Type: text/plain; charset=us-ascii Content-Disposition: inline * Anibal David Acosta (aa@devshock.com) wrote: > Unlogged tables are not memory tables don't? Unlogged tables are not memory tables. > If we stop postgres server (normal stop) and start again, all information in > unlogged tables still remain? Yes. > So, can I expect a data loss just in case of crash, power failure or SO > crash don't? Yes. > In case of crash, is possible that data corruption happened in a unlogged > tables? In a crash, unlogged tables are automatically truncated. > For performance purpose can I use async commit and unlogged tables? I'm not aware of any issues (beyond those already documented for async commit..) with having async commit and unlogged tables. THanks, Stephen --VQl1xLnQPIyet9qq Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) iEYEARECAAYFAk69gw4ACgkQrzgMPqB3kiiZeQCfbGjZ2HqS0975jQT1WmZuhBhm s6MAn0iykDJM3qcGGs1AU1OCCTzsSdgt =I8wV -----END PGP SIGNATURE----- --VQl1xLnQPIyet9qq-- From gray.ru@gmail.com Mon Nov 14 08:11:15 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id CD623ED8E0A for ; Mon, 14 Nov 2011 04:11:15 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 69196-07 for ; Mon, 14 Nov 2011 08:11:09 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-gy0-f174.google.com (mail-gy0-f174.google.com [209.85.160.174]) by mail.postgresql.org (Postfix) with ESMTP id 6D36BED8E09 for ; Mon, 14 Nov 2011 04:11:09 -0400 (AST) Received: by gyc15 with SMTP id 15so4628205gyc.19 for ; Mon, 14 Nov 2011 00:11:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=tiP2AQLWpCT/u9y5QWTu7vhh2l5orBdzJYNa0pPqfeU=; b=Rk3xScLK5hB6oZsBWAMlEEdFQuuUmbiBOmnK4cTKPhdoaqLqasxCvCNc0kk8rTEbI/ IyX1v+XpLWryfMrSSEdfqedTjYLzu3QzP23hKMcJ5rmlVvGfqKbE6ZlgJxXL509AhVHy JgE8Uaf74zCTQBfQRu7kTt+IMt5okhboCxxbA= Received: by 10.182.12.69 with SMTP id w5mr4756978obb.31.1321258269253; Mon, 14 Nov 2011 00:11:09 -0800 (PST) MIME-Version: 1.0 Received: by 10.182.5.230 with HTTP; Mon, 14 Nov 2011 00:10:47 -0800 (PST) In-Reply-To: <20111111201822.GN24234@tamriel.snowman.net> References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <20111111201822.GN24234@tamriel.snowman.net> From: Sergey Konoplev Date: Mon, 14 Nov 2011 12:10:47 +0400 Message-ID: Subject: Re: unlogged tables To: Stephen Frost Cc: Anibal David Acosta , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201111/156 X-Sequence-Number: 45667 Hi, On 12 November 2011 00:18, Stephen Frost wrote: > In a crash, unlogged tables are automatically truncated. BTW I wonder what for they are truncated in a crash case? -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com LinkedIn: http://ru.linkedin.com/in/grayhemp JID/GTalk: gray.ru@gmail.com Skype: gray-hemp From dev@archonet.com Mon Nov 14 08:58:23 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id A4EA3ED8E0A for ; Mon, 14 Nov 2011 04:58:23 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 90709-02 for ; Mon, 14 Nov 2011 08:58:17 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by mail.postgresql.org (Postfix) with ESMTP id 4A7F4ED8E09 for ; Mon, 14 Nov 2011 04:58:16 -0400 (AST) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by mail.archonet.com (Postfix) with ESMTP id 32EBA7C029; Mon, 14 Nov 2011 08:58:15 +0000 (GMT) Message-ID: <4EC0D827.8090601@archonet.com> Date: Mon, 14 Nov 2011 08:58:15 +0000 From: Richard Huxton User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.23) Gecko/20110921 Lightning/1.0b2 Thunderbird/3.1.15 MIME-Version: 1.0 To: Sergey Konoplev CC: Stephen Frost , Anibal David Acosta , pgsql-performance@postgresql.org Subject: Re: unlogged tables References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <20111111201822.GN24234@tamriel.snowman.net> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.918 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RDNS_DYNAMIC=0.982 X-Spam-Level: X-Archive-Number: 201111/157 X-Sequence-Number: 45668 On 14/11/11 08:10, Sergey Konoplev wrote: > Hi, > > On 12 November 2011 00:18, Stephen Frost wrote: >> In a crash, unlogged tables are automatically truncated. > > BTW I wonder what for they are truncated in a crash case? Because they bypass the transaction-log (WAL), hence unlogged. There's no way to know whether there were partial updates applied when the system restarts. -- Richard Huxton Archonet Ltd From gray.ru@gmail.com Mon Nov 14 10:09:00 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 1DE1C720082 for ; Mon, 14 Nov 2011 06:09:00 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 31105-02-6 for ; Mon, 14 Nov 2011 10:08:53 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com [209.85.213.174]) by mail.postgresql.org (Postfix) with ESMTP id 495AA720081 for ; Mon, 14 Nov 2011 06:08:46 -0400 (AST) Received: by yenr9 with SMTP id r9so4968309yen.19 for ; Mon, 14 Nov 2011 02:08:46 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; bh=V4KkGxGFbGeRKnIFSPiUJTJhBF8HlF2i4uRx6ipVnXk=; b=bk5xX3pHK8YeeH1XlcfeFnnJBuu2AzUf6AKZaZc50iqnQN/TsHdzfHCSXyb7RaNFgV pKJ7oJfpmekzHG9e6JHiDEKKHMF7D0EiXPahuzb+/FiHJMt9lXEe9b/hWtCqZ9/SwYWZ 0qUrJoCbPgY/t6pA1cqrzyW9Q42OqTO6TUkHM= Received: by 10.182.12.69 with SMTP id w5mr4832123obb.31.1321265326210; Mon, 14 Nov 2011 02:08:46 -0800 (PST) MIME-Version: 1.0 Received: by 10.182.5.230 with HTTP; Mon, 14 Nov 2011 02:08:25 -0800 (PST) In-Reply-To: <4EC0D827.8090601@archonet.com> References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <20111111201822.GN24234@tamriel.snowman.net> <4EC0D827.8090601@archonet.com> From: Sergey Konoplev Date: Mon, 14 Nov 2011 14:08:25 +0400 Message-ID: Subject: Re: unlogged tables To: Richard Huxton Cc: Stephen Frost , Anibal David Acosta , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201111/158 X-Sequence-Number: 45669 On 14 November 2011 12:58, Richard Huxton wrote: > Because they bypass the transaction-log (WAL), hence unlogged. > There's no way to know whether there were partial updates applied when th= e > system restarts. I probably did not understand the "truncate" meaning correct. It truncates all the records of the table or several recent records only? > > -- > =A0Richard Huxton > =A0Archonet Ltd > --=20 Sergey Konoplev Blog: http://gray-hemp.blogspot.com LinkedIn: http://ru.linkedin.com/in/grayhemp JID/GTalk: gray.ru@gmail.com Skype: gray-hemp From dev@archonet.com Mon Nov 14 10:18:04 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C9878720082 for ; Mon, 14 Nov 2011 06:18:04 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 35483-01 for ; Mon, 14 Nov 2011 10:17:59 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by mail.postgresql.org (Postfix) with ESMTP id E8628720081 for ; Mon, 14 Nov 2011 06:17:58 -0400 (AST) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by mail.archonet.com (Postfix) with ESMTP id 948697C029; Mon, 14 Nov 2011 10:17:58 +0000 (GMT) Message-ID: <4EC0EAD6.9010302@archonet.com> Date: Mon, 14 Nov 2011 10:17:58 +0000 From: Richard Huxton User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.23) Gecko/20110921 Lightning/1.0b2 Thunderbird/3.1.15 MIME-Version: 1.0 To: Sergey Konoplev CC: Stephen Frost , Anibal David Acosta , pgsql-performance@postgresql.org Subject: Re: unlogged tables References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <20111111201822.GN24234@tamriel.snowman.net> <4EC0D827.8090601@archonet.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.918 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RDNS_DYNAMIC=0.982 X-Spam-Level: X-Archive-Number: 201111/159 X-Sequence-Number: 45670 On 14/11/11 10:08, Sergey Konoplev wrote: > On 14 November 2011 12:58, Richard Huxton wrote: >> Because they bypass the transaction-log (WAL), hence unlogged. >> There's no way to know whether there were partial updates applied when the >> system restarts. > > I probably did not understand the "truncate" meaning correct. It > truncates all the records of the table or several recent records only? All. Let's say you were doing something like "UPDATE unlogged_table SET x=1 WHERE y=2". If a crash occurs during this command, there's no guarantee that the affected disk pages were all updated. Worse, a single page might be partially updated or even have rubbish in it (depending on the nature of the crash). Without the WAL there's no way to check whether the table is good or not, or even to know what the last updates were. So - the only safe thing to do is truncate the unlogged tables. In the event of a normal shutdown, we can flush all the writes to disk so we know all the data has been written, so there is no need to truncate. -- Richard Huxton Archonet Ltd From gray.ru@gmail.com Mon Nov 14 10:39:30 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 7FB73720082 for ; Mon, 14 Nov 2011 06:39:30 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 44688-01 for ; Mon, 14 Nov 2011 10:39:24 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-gx0-f174.google.com (mail-gx0-f174.google.com [209.85.161.174]) by mail.postgresql.org (Postfix) with ESMTP id 673C7720081 for ; Mon, 14 Nov 2011 06:39:24 -0400 (AST) Received: by ggnb2 with SMTP id b2so6021162ggn.19 for ; Mon, 14 Nov 2011 02:39:24 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; bh=PoXn9ih+uuCMsEmFZPbNdxXtNq2+zz3xpG8rTNmYObY=; b=o8XaqQW3oMWOEtqXD462O4/t85g0qbn2cBvRVvOfvJMtjOybEOTZMhsRihgOK68E53 8tR4qMO2oRszYedz5V/tg859wEbpUs89BieZisY4/RoUVcaEQCQirI5GnPe/CJ7ULraC ulL2lIKfIf5t/kHFjGk3ASUphr8a77voKuC8o= Received: by 10.182.2.136 with SMTP id 8mr843774obu.71.1321267164155; Mon, 14 Nov 2011 02:39:24 -0800 (PST) MIME-Version: 1.0 Received: by 10.182.5.230 with HTTP; Mon, 14 Nov 2011 02:39:03 -0800 (PST) In-Reply-To: <4EC0EAD6.9010302@archonet.com> References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <20111111201822.GN24234@tamriel.snowman.net> <4EC0D827.8090601@archonet.com> <4EC0EAD6.9010302@archonet.com> From: Sergey Konoplev Date: Mon, 14 Nov 2011 14:39:03 +0400 Message-ID: Subject: Re: unlogged tables To: Richard Huxton Cc: Stephen Frost , Anibal David Acosta , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201111/160 X-Sequence-Number: 45671 On 14 November 2011 14:17, Richard Huxton wrote: > On 14/11/11 10:08, Sergey Konoplev wrote: >> >> On 14 November 2011 12:58, Richard Huxton =A0wrote: > Let's say you were doing something like "UPDATE unlogged_table SET x=3D1 = WHERE > y=3D2". If a crash occurs during this command, there's no guarantee that = the > affected disk pages were all updated. Worse, a single page might be > partially updated or even have rubbish in it (depending on the nature of = the > crash). > > Without the WAL there's no way to check whether the table is good or not,= or > even to know what the last updates were. So - the only safe thing to do i= s > truncate the unlogged tables. > > In the event of a normal shutdown, we can flush all the writes to disk so= we > know all the data has been written, so there is no need to truncate. Thank you for the explanation. Now I understand it. > > -- > =A0Richard Huxton > =A0Archonet Ltd > --=20 Sergey Konoplev Blog: http://gray-hemp.blogspot.com LinkedIn: http://ru.linkedin.com/in/grayhemp JID/GTalk: gray.ru@gmail.com Skype: gray-hemp From aa@devshock.com Mon Nov 14 17:11:23 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 987801093AC0 for ; Mon, 14 Nov 2011 13:11:23 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 58771-04 for ; Mon, 14 Nov 2011 17:11:17 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-yw0-f46.google.com (mail-yw0-f46.google.com [209.85.213.46]) by mail.postgresql.org (Postfix) with ESMTP id 4575A1093ABF for ; Mon, 14 Nov 2011 13:11:17 -0400 (AST) Received: by ywt32 with SMTP id 32so2399157ywt.19 for ; Mon, 14 Nov 2011 09:11:16 -0800 (PST) Received: by 10.146.190.21 with SMTP id n21mr3622566yaf.39.1321290357502; Mon, 14 Nov 2011 09:05:57 -0800 (PST) Received: from AnibalVAIO ([200.85.32.68]) by mx.google.com with ESMTPS id 8sm62844024anv.16.2011.11.14.09.05.53 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 14 Nov 2011 09:05:55 -0800 (PST) From: "Anibal David Acosta" To: "'Sergey Konoplev'" , "'Richard Huxton'" Cc: "'Stephen Frost'" , References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <20111111201822.GN24234@tamriel.snowman.net> <4EC0D827.8090601@archonet.com> <4EC0EAD6.9010302@archonet.com> In-Reply-To: Subject: Re: unlogged tables Date: Mon, 14 Nov 2011 14:05:51 -0300 Message-ID: <00ea01cca2ef$abcf3a30$036dae90$@devshock.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQG0h8c2HxwgVHVwA7Oudni7xLo/1gKNzE9QAZGzEw4Bc7WZpAHcxNALAamzBiMCHqGblZWDDJWA Content-Language: es-py X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201111/161 X-Sequence-Number: 45672 Maybe an option like "Recover from file " will be useful So, for example, daily some process do a COPY of entire table to a file=20 In case of crash postgres recover content from the file. :) -----Mensaje original----- De: Sergey Konoplev [mailto:gray.ru@gmail.com]=20 Enviado el: lunes, 14 de noviembre de 2011 07:39 a.m. Para: Richard Huxton CC: Stephen Frost; Anibal David Acosta; pgsql-performance@postgresql.org Asunto: Re: [PERFORM] unlogged tables On 14 November 2011 14:17, Richard Huxton wrote: > On 14/11/11 10:08, Sergey Konoplev wrote: >> >> On 14 November 2011 12:58, Richard Huxton =A0wrote: > Let's say you were doing something like "UPDATE unlogged_table SET = x=3D1=20 > WHERE y=3D2". If a crash occurs during this command, there's no=20 > guarantee that the affected disk pages were all updated. Worse, a=20 > single page might be partially updated or even have rubbish in it=20 > (depending on the nature of the crash). > > Without the WAL there's no way to check whether the table is good or=20 > not, or even to know what the last updates were. So - the only safe=20 > thing to do is truncate the unlogged tables. > > In the event of a normal shutdown, we can flush all the writes to disk = > so we know all the data has been written, so there is no need to = truncate. Thank you for the explanation. Now I understand it. > > -- > =A0Richard Huxton > =A0Archonet Ltd > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com LinkedIn: http://ru.linkedin.com/in/grayhemp JID/GTalk: gray.ru@gmail.com Skype: gray-hemp From Kevin.Grittner@wicourts.gov Mon Nov 14 17:27:01 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id D04381093ABF for ; Mon, 14 Nov 2011 13:27:01 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 65173-07 for ; Mon, 14 Nov 2011 17:26:52 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 5553A1093ABE for ; Mon, 14 Nov 2011 13:26:52 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Mon, 14 Nov 2011 11:26:40 -0600 Message-Id: <4EC0FAE80200002500042E6C@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Mon, 14 Nov 2011 11:26:32 -0600 From: "Kevin Grittner" To: "'Richard Huxton'" , "Anibal David Acosta" , "'Sergey Konoplev'" Cc: , "'Stephen Frost'" Subject: Re: unlogged tables References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <20111111201822.GN24234@tamriel.snowman.net> <4EC0D827.8090601@archonet.com> <4EC0EAD6.9010302@archonet.com> <00ea01cca2ef$abcf3a30$036dae90$@devshock.com> In-Reply-To: <00ea01cca2ef$abcf3a30$036dae90$@devshock.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-3.101 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-1.201 X-Spam-Level: X-Archive-Number: 201111/162 X-Sequence-Number: 45673 "Anibal David Acosta" wrote: > Maybe an option like "Recover from file " will be useful > So, for example, daily some process do a COPY of entire table to a > file > > In case of crash postgres recover content from the file. If you need to recover file contents on a crash, then an unlogged table is probably not the right choice. There is always asynchronous commit. -Kevin From aa@devshock.com Mon Nov 14 17:38:44 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 3C2CA1093ABF for ; Mon, 14 Nov 2011 13:38:44 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 70610-06 for ; Mon, 14 Nov 2011 17:38:38 +0000 (UTC) X-Greylist: delayed 00:32:39.946176 by SQLgrey-1.8.0-rc2 Received: from mail-gy0-f174.google.com (mail-gy0-f174.google.com [209.85.160.174]) by mail.postgresql.org (Postfix) with ESMTP id EF2E61093ABE for ; Mon, 14 Nov 2011 13:38:37 -0400 (AST) Received: by gyc15 with SMTP id 15so5121620gyc.19 for ; Mon, 14 Nov 2011 09:38:37 -0800 (PST) Received: by 10.146.200.27 with SMTP id x27mr1523071yaf.31.1321292317379; Mon, 14 Nov 2011 09:38:37 -0800 (PST) Received: from AnibalVAIO ([200.85.32.68]) by mx.google.com with ESMTPS id i31sm41093062anm.19.2011.11.14.09.38.34 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 14 Nov 2011 09:38:36 -0800 (PST) From: "Anibal David Acosta" To: "'Kevin Grittner'" , "'Richard Huxton'" , "'Sergey Konoplev'" Cc: , "'Stephen Frost'" References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <20111111201822.GN24234@tamriel.snowman.net> <4EC0D827.8090601@archonet.com> <4EC0EAD6.9010302@archonet.com> <00ea01cca2ef$abcf3a30$036dae90$@devshock.com> <4EC0FAE80200002500042E6C@gw.wicourts.gov> In-Reply-To: <4EC0FAE80200002500042E6C@gw.wicourts.gov> Subject: Re: unlogged tables Date: Mon, 14 Nov 2011 14:38:31 -0300 Message-ID: <00ff01cca2f4$3cb86860$b6293920$@devshock.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQG0h8c2HxwgVHVwA7Oudni7xLo/1gKNzE9QAZGzEw4Bc7WZpAHcxNALAamzBiMCHqGblQJY0lSEApP7xZ+VW61cgA== Content-Language: es-py X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.585 tagged_above=-10 required=5 tests=BAYES_00=-1.9, SARE_MILLIONSOF=0.315 X-Spam-Level: X-Archive-Number: 201111/163 X-Sequence-Number: 45674 I am doing asynchronous commit but sometimes I think that there are so many "things" in an insert/update transaction, for a table that has not too much important information. My table is a statistics counters table, so I can live with a partial data loss, but not with a full data loss because many counters are weekly and monthly. Unlogged table can increase speed, this table has about 1.6 millions of update per hour, but unlogged with a chance of loss all information on a crash are not a good idea for this. Anyway, thanks Kevin! -----Mensaje original----- De: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Enviado el: lunes, 14 de noviembre de 2011 02:27 p.m. Para: 'Richard Huxton'; Anibal David Acosta; 'Sergey Konoplev' CC: pgsql-performance@postgresql.org; 'Stephen Frost' Asunto: Re: [PERFORM] unlogged tables "Anibal David Acosta" wrote: > Maybe an option like "Recover from file " will be useful So, for > example, daily some process do a COPY of entire table to a file > > In case of crash postgres recover content from the file. If you need to recover file contents on a crash, then an unlogged table is probably not the right choice. There is always asynchronous commit. -Kevin From Kevin.Grittner@wicourts.gov Mon Nov 14 17:50:41 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 0A5D11EAEB54 for ; Mon, 14 Nov 2011 13:50:41 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 77259-03 for ; Mon, 14 Nov 2011 17:50:35 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id A40EE1EAEB52 for ; Mon, 14 Nov 2011 13:50:34 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Mon, 14 Nov 2011 11:50:33 -0600 Message-Id: <4EC100810200002500042E7C@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Mon, 14 Nov 2011 11:50:25 -0600 From: "Kevin Grittner" To: "'Richard Huxton'" , "Anibal David Acosta" , "'Sergey Konoplev'" Cc: , "'Stephen Frost'" Subject: Re: unlogged tables References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <20111111201822.GN24234@tamriel.snowman.net> <4EC0D827.8090601@archonet.com> <4EC0EAD6.9010302@archonet.com> <00ea01cca2ef$abcf3a30$036dae90$@devshock.com> <4EC0FAE80200002500042E6C@gw.wicourts.gov> <00ff01cca2f4$3cb86860$b6293920$@devshock.com> In-Reply-To: <00ff01cca2f4$3cb86860$b6293920$@devshock.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.786 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-1.201, SARE_MILLIONSOF=0.315 X-Spam-Level: X-Archive-Number: 201111/164 X-Sequence-Number: 45675 "Anibal David Acosta" wrote: > I am doing asynchronous commit but sometimes I think that there > are so many "things" in an insert/update transaction, for a table > that has not too much important information. > > My table is a statistics counters table, so I can live with a > partial data loss, but not with a full data loss because many > counters are weekly and monthly. > > Unlogged table can increase speed, this table has about 1.6 > millions of update per hour, but unlogged with a chance of loss > all information on a crash are not a good idea for this. pg_dump -t 'tablename' from a cron job? (Make sure to rotate dump file names, maybe with day of week or some such.) -Kevin From josh@agliodbs.com Tue Nov 15 20:51:29 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id BC9F11B5E5C4 for ; Tue, 15 Nov 2011 16:51:29 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 77117-06 for ; Tue, 15 Nov 2011 20:51:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 986B91B5E5B5 for ; Tue, 15 Nov 2011 16:51:21 -0400 (AST) Received: from smtp.01.com ([216.115.88.169]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RQPyM-0000iA-VE for pgsql-performance@postgresql.org; Tue, 15 Nov 2011 20:51:21 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id 5622C9D00B6 for ; Tue, 15 Nov 2011 14:51:02 -0600 (CST) X-Virus-Scanned: amavisd-new at smtp-out-1.01.com Received: from smtp.01.com ([127.0.0.1]) by localhost (smtp-out-1.01.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Azlojks4yvRM for ; Tue, 15 Nov 2011 14:51:02 -0600 (CST) Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id 2714E9D00B3 for ; Tue, 15 Nov 2011 14:51:02 -0600 (CST) Received: from Sidney-Stratton.local (70-36-143-92.dsl.dynamic.sonic.net [70.36.143.92]) by smtp-out-1.01.com (Postfix) with ESMTPSA id E82AB9D00B6 for ; Tue, 15 Nov 2011 14:51:01 -0600 (CST) Message-ID: <4EC2D0B5.6060901@agliodbs.com> Date: Tue, 15 Nov 2011 12:51:01 -0800 From: Josh Berkus Organization: PostgreSQL Experts Inc. User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.2.24) Gecko/20111103 Lightning/1.0b2 Thunderbird/3.1.16 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: unlogged tables References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <20111111201822.GN24234@tamriel.snowman.net> <4EC0D827.8090601@archonet.com> <4EC0EAD6.9010302@archonet.com> <00ea01cca2ef$abcf3a30$036dae90$@devshock.com> <4EC0FAE80200002500042E6C@gw.wicourts.gov> <00ff01cca2f4$3cb86860$b6293920$@devshock.com> <4EC100810200002500042E7C@gw.wicourts.gov> In-Reply-To: <4EC100810200002500042E7C@gw.wicourts.gov> X-Enigmail-Version: 1.1.1 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.585 tagged_above=-10 required=5 tests=BAYES_00=-1.9, SARE_MILLIONSOF=0.315 X-Spam-Level: X-Archive-Number: 201111/183 X-Sequence-Number: 45694 >> Unlogged table can increase speed, this table has about 1.6 >> millions of update per hour, but unlogged with a chance of loss >> all information on a crash are not a good idea for this. > > pg_dump -t 'tablename' from a cron job? (Make sure to rotate dump > file names, maybe with day of week or some such.) Or just "CREATE TABLE AS" copy the table every hour to a second, backup table. Then it would be much easier to script automated restore of the data. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com From lists@peufeu.com Sun Dec 4 12:28:22 2011 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id C031B3E3C9A for ; Sun, 4 Dec 2011 08:28:22 -0400 (AST) Received: from 7.mo4.mail-out.ovh.net ([178.33.253.54] helo=mo4.mail-out.ovh.net) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RXBB7-0007Nt-FZ for pgsql-performance@postgresql.org; Sun, 04 Dec 2011 12:28:22 +0000 Received: from mail177.ha.ovh.net (b6.ovh.net [213.186.33.56]) by mo4.mail-out.ovh.net (Postfix) with SMTP id DD9F3106465F for ; Sun, 4 Dec 2011 13:28:11 +0100 (CET) Received: from b0.ovh.net (HELO queueout) (213.186.33.50) by b0.ovh.net with SMTP; 4 Dec 2011 14:28:07 +0200 Received: from civ69-1-88-168-96-119.fbx.proxad.net (HELO apollo13) (lists%peufeu.com@88.168.96.119) by ns0.ovh.net with SMTP; 4 Dec 2011 14:28:05 +0200 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes To: "'Kevin Grittner'" , "'Richard Huxton'" , "'Sergey Konoplev'" , "Anibal David Acosta" Cc: pgsql-performance@postgresql.org, "'Stephen Frost'" X-Ovh-Mailout: 178.32.228.4 (mo4.mail-out.ovh.net) Subject: Re: unlogged tables References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <20111111201822.GN24234@tamriel.snowman.net> <4EC0D827.8090601@archonet.com> <4EC0EAD6.9010302@archonet.com> <00ea01cca2ef$abcf3a30$036dae90$@devshock.com> <4EC0FAE80200002500042E6C@gw.wicourts.gov> <00ff01cca2f4$3cb86860$b6293920$@devshock.com> Date: Sun, 04 Dec 2011 13:28:03 +0100 MIME-Version: 1.0 From: "Pierre C" Message-ID: In-Reply-To: <00ff01cca2f4$3cb86860$b6293920$@devshock.com> User-Agent: Opera Mail/11.52 (Linux) X-Ovh-Tracer-Id: 7445576084665403061 X-Ovh-Remote: 88.168.96.119 (civ69-1-88-168-96-119.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-OVH-SPAMSTATE: OK X-OVH-SPAMSCORE: -100 X-OVH-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrfeefjedruddvucetggdotefuucfrrhhofhhilhgvmecuqfggjfenuceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmnecujfgurheptgfvufhfffgggffhkfgjfgesthekredttderjeenucfhrhhomhepfdfrihgvrhhrvgcuvedfuceolhhishhtshesphgvuhhfvghurdgtohhmqeenucffohhmrghinhepne X-Spam-Check: DONE|U 0.5/N X-VR-SPAMSTATE: OK X-VR-SPAMSCORE: -100 X-VR-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrfeefjedruddvucetggdotefuucfrrhhofhhilhgvmecuqfggjfenuceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmnecujfgurheptgfvufhfffgggffhkfgjfgesthekredttderjeenucfhrhhomhepfdfrihgvrhhrvgcuvedfuceolhhishhtshesphgvuhhfvghurdgtohhmqeenucffohhmrghinhepne Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201112/25 X-Sequence-Number: 45825 > My table is a statistics counters table, so I can live with a partial =20 > data > loss, but not with a full data loss because many counters are weekly an= d > monthly. > > Unlogged table can increase speed, this table has about 1.6 millions of > update per hour, but unlogged with a chance of loss all information on = a > crash are not a good idea for this. You could use an unlogged table for hourly updates, and periodically, =20 accumulate those counters to a (logged) daily/weekly table... The hourly table could be rebuilt by examining only 1 hour's worth of =20 data, so it isn't too much of a problem if it's lost. The other tables =20 would get much less updates. From gabriel.dodan@gmail.com Mon Apr 13 19:31:52 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhk5I-0005w4-F6 for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 19:31:52 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yhk5H-0008OW-Tl for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 19:31:51 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yhk5G-0008ON-Mk for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 19:31:50 +0000 Received: from mwork.nabble.com ([162.253.133.43]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhk59-0006dE-6v for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 19:31:48 +0000 Received: from msam.nabble.com (unknown [162.253.133.85]) by mwork.nabble.com (Postfix) with ESMTP id 8A8441ABCA12 for ; Mon, 13 Apr 2015 12:32:13 -0700 (PDT) Date: Mon, 13 Apr 2015 12:31:42 -0700 (MST) From: dgabriel To: pgsql-performance@postgresql.org Message-ID: <1428953502160-5845576.post@n5.nabble.com> In-Reply-To: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> Subject: Re: unlogged tables MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -0.3 (/) 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 "In the event of a normal shutdown, we can flush all the writes to disk so we know all the data has been written, so there is no need to truncate." Isn't possible to periodically flush data to disk and in case of crush postgres to load only the data that existed at last flush? The periodic flush could be configurable, for example every 30 minutes or after x rows updated/inserted. -- View this message in context: http://postgresql.nabble.com/unlogged-tables-tp4985453p5845576.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From matioli.matheus@gmail.com Mon Apr 13 19:36:40 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhk9v-00065h-VF for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 19:36:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yhk9u-00027g-EA for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 19:36:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yhk9t-00027Y-HC for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 19:36:37 +0000 Received: from mail-wg0-x22c.google.com ([2a00:1450:400c:c00::22c]) by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Yhk9o-0004Nj-Re for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 19:36:36 +0000 Received: by wgso17 with SMTP id o17so92863478wgs.1 for ; Mon, 13 Apr 2015 12:36:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=B+8KKwDH4Mn4PjA04qopN7T8gmPnMmoHKFzQsc8R05Y=; b=VrB4q7vo59k+DYWIv0mmIg5w298HrHPuu3yhYOh2gXeB9Ne54DstQlBcA/JaJhsccr IITvdQ62zSx/A4SGTQsrA3ihEHSAR+JjiOPmxej1lxR8XXpMRWylz71QE1KAgt6jAbje pfMY5X9mapuagFjuf0yOgRLAfdvdwridBkeYfD1qlAhO6C8/Mhg4Rj3I4EESxnE7uVjA A3xPtREPXjWuf17AM5f3krQ3jouVsIAi60l5dOKoRpko+6kiy1lOCiEiVTEQrfK/6iB4 bqlpNDjJ/sHKegpOiBLXvM6gmINKprijqhDrAcZmoH2RV3gm+evrsQ+3VFFR0JMr/e8R nl1w== X-Received: by 10.180.20.144 with SMTP id n16mr24113654wie.44.1428953790788; Mon, 13 Apr 2015 12:36:30 -0700 (PDT) MIME-Version: 1.0 Received: by 10.194.78.44 with HTTP; Mon, 13 Apr 2015 12:36:10 -0700 (PDT) In-Reply-To: <1428953502160-5845576.post@n5.nabble.com> References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> From: Matheus de Oliveira Date: Mon, 13 Apr 2015 16:36:10 -0300 Message-ID: Subject: Re: unlogged tables To: dgabriel Cc: pgsql-performance Content-Type: multipart/alternative; boundary=bcaec53f2e2f19b9a40513a03b12 X-Pg-Spam-Score: -2.7 (--) 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 --bcaec53f2e2f19b9a40513a03b12 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Mon, Apr 13, 2015 at 4:31 PM, dgabriel wrote: > "In the event of a normal shutdown, we can flush all the writes to disk > so we know all the data has been written, so there is no need to truncate= ." > > Isn't possible to periodically flush data to disk and in case of crush > postgres to load only the data that existed at last flush? The periodic > flush could be configurable, for example every 30 minutes or after x rows > updated/inserted. > There is no such facility implemented for UNLOGGED TABLEs. That could be a feature request though. Best regards, --=20 Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br n=C3=ADvel F! www.dextra.com.br/postgres --bcaec53f2e2f19b9a40513a03b12 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
--
Matheus de Oliveira
Analista de B= anco de Dados
Dextra Sistemas - MPS.Br n=C3=ADvel F!
www.dextra.com.br/postgres=

--bcaec53f2e2f19b9a40513a03b12-- From gabriel.dodan@gmail.com Mon Apr 13 20:16:50 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhkmo-0007Om-8o for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 20:16:50 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yhkmn-0007wu-FQ for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 20:16:49 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yhkmm-0007wm-IQ for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 20:16:48 +0000 Received: from mwork.nabble.com ([162.253.133.43]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhkmj-0007Sk-9m for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 20:16:46 +0000 Received: from msam.nabble.com (unknown [162.253.133.85]) by mwork.nabble.com (Postfix) with ESMTP id 9C77B1ABDAB0 for ; Mon, 13 Apr 2015 13:16:45 -0700 (PDT) Date: Mon, 13 Apr 2015 13:16:14 -0700 (MST) From: dgabriel To: pgsql-performance@postgresql.org Message-ID: <1428956174244-5845580.post@n5.nabble.com> In-Reply-To: References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> Subject: Re: unlogged tables MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -0.3 (/) 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 That will be a very useful feature. I don' t care if i loss 1-2 hours of data. I know we could have some cron jobs to dump the table periodically but the table could be big, so this operation could be expensive. Also i have to detect when postgres crush, i have no idea how i can detect if postgres crushed. Then i have somehow to attache a script at postgres start, to restore the dumps...the dump solution is very complicate and unreliable. A periodic flush feature will be amazing! How is the procedure for feature request on postgres, github? Thanks! -- View this message in context: http://postgresql.nabble.com/unlogged-tables-tp4985453p5845580.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From yves@zioup.com Mon Apr 13 20:30:57 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhl0T-0007uc-M3 for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 20:30:57 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yhl0T-0001uS-2H for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 20:30:57 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yhl0S-0001uK-62 for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 20:30:56 +0000 Received: from smtp-out-03.shaw.ca ([64.59.136.139]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhl0O-0007jf-4V for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 20:30:53 +0000 X-Cloudmark-SP-Filtered: true X-Cloudmark-SP-Result: v=1.1 cv=tLeJwtg1FCvAouMblIYY1Z5/U6XdMrtw4y2B9g+QINc= c=1 sm=1 a=motdEeDeaRIA:10 a=BLceEmwcHowA:10 a=IkcTkHD0fZMA:10 a=1AzCEyHvRsrOnBOCSdCQag==:17 a=N8iSzGwtAAAA:8 a=Gb-LMZaLwfUWzAt1fecA:9 a=QEXdDO2ut3YA:10 a=HpAAvcLHHh0Zw7uRqdWCyQ==:117 Received: from unknown (HELO home.zioup.com) ([70.72.160.156]) by smtp-out-03.shaw.ca with ESMTP; 13 Apr 2015 14:30:50 -0600 Received: from [192.168.2.80] (epicurus.zioup.net [192.168.2.80]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by home.zioup.com (Postfix) with ESMTPSA id E7CE5A03B0 for ; Mon, 13 Apr 2015 14:30:49 -0600 (MDT) Message-ID: <552C2779.8050004@zioup.com> Date: Mon, 13 Apr 2015 14:30:49 -0600 From: Yves Dorfsman User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.5.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: unlogged tables References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> <1428956174244-5845580.post@n5.nabble.com> In-Reply-To: <1428956174244-5845580.post@n5.nabble.com> Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.6 (--) 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 On 2015-04-13 14:16, dgabriel wrote: > That will be a very useful feature. I agree, unlogged tables would be a lot more useful if they didn't "disappear" on re-start. > could be expensive. Also i have to detect when postgres crush, i have no > idea how i can detect if postgres crushed. Then i have somehow to attache a > script at postgres start, to restore the dumps...the dump solution is very > complicate and unreliable. A periodic flush feature will be amazing! In my experience postgres is very aggressive in getting rid of unlogged tables, it does get rid of them from shutdowns that seem perfectly fine (no crash). A lot of people get surprised by this. -- http://yves.zioup.com gpg: 4096R/32B0F416 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From matioli.matheus@gmail.com Mon Apr 13 20:41:01 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YhlAD-0008EZ-PY for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 20:41:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YhlAC-00044K-OL for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 20:41:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YhlAB-00044B-Bw for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 20:40:59 +0000 Received: from mail-wg0-x229.google.com ([2a00:1450:400c:c00::229]) by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YhlA7-0005Yy-3t for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 20:40:58 +0000 Received: by wgsk9 with SMTP id k9so94246058wgs.3 for ; Mon, 13 Apr 2015 13:40:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=BvIwZpbDD7wzg/Z5p7ZPElCNsWGLnzU1UgvcQZEKUAw=; b=X/rMqxiQf5iaUdA62uizdeDHt/zDOJFUBHfXdmql7h68UhIZnn/k6Wv7jo41UaSdVg UOJ6M8K9yPAlWeGt8nimmmE3zkjxQvQvfzKKWtf83oDdYJ3GudwVqwisT9LH0lLPtR1D YEpdsVIJrYRbg6Nmir+lXEkkjv4ve/nsUO13LORst18mBqTHiK7rxkv1SrndwYQivPtT TbkjCCr1x4RsIUg6LXHko6rpF6ew5mpM9/sQIpYdAb9DPbb9JqIE+qKApmTRQ93GbLfK xA4dSWBh11k9WD2xUdw5S2ci5rxbZTWR3q7LypnRvjsTfwzEnYyeBQXEhsLRjv2T4oSb kKxQ== X-Received: by 10.181.25.225 with SMTP id it1mr24985066wid.8.1428957653772; Mon, 13 Apr 2015 13:40:53 -0700 (PDT) MIME-Version: 1.0 Received: by 10.194.78.44 with HTTP; Mon, 13 Apr 2015 13:40:33 -0700 (PDT) In-Reply-To: <552C2779.8050004@zioup.com> References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> <1428956174244-5845580.post@n5.nabble.com> <552C2779.8050004@zioup.com> From: Matheus de Oliveira Date: Mon, 13 Apr 2015 17:40:33 -0300 Message-ID: Subject: Re: unlogged tables To: Yves Dorfsman Cc: pgsql-performance Content-Type: multipart/alternative; boundary=001a11360a2c5a30570513a12197 X-Pg-Spam-Score: -2.7 (--) 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 --001a11360a2c5a30570513a12197 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Mon, Apr 13, 2015 at 5:30 PM, Yves Dorfsman wrote: > > In my experience postgres is very aggressive in getting rid of unlogged > tables, it does get rid of them from shutdowns that seem perfectly fine (= no > crash). A lot of people get surprised by this. Shutdowns in "fast" or "smart" modes does not get rid of unlogged tables. But if you do "immediate", then it does, and I don't see why people get surprised by it, as you probably shouldn't be using "immediate" mode in normal circumstances. Best regards, --=20 Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br n=C3=ADvel F! www.dextra.com.br/postgres --001a11360a2c5a30570513a12197 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

= On Mon, Apr 13, 2015 at 5:30 PM, Yves Dorfsman <yves@zioup.com>= wrote:

In my experience postgres is very aggressive in getting rid of unlog= ged
tables, it does get rid of them from shutdowns that seem perfectly fine (no=
crash). A lot of people get surprised by this.

=
Shutdowns in "fast" or "smart&quo= t; modes does not get rid of unlogged tables. But if you do "immediate= ", then it does, and I don't see why people get surprised by it, a= s you probably shouldn't be using "immediate" mode in normal = circumstances.

=
Best regards,
--
Matheus de Oliveira
Analista de = Banco de Dados
Dextra Sistemas - MPS.Br n=C3=ADvel F!
www.dextra.com.br/postgre= s

--001a11360a2c5a30570513a12197-- From david.g.johnston@gmail.com Mon Apr 13 20:49:24 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YhlIK-0008Ru-Cr for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 20:49:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YhlIJ-0006Fu-Mv for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 20:49:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YhlII-0006Fj-GG for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 20:49:22 +0000 Received: from mail-ig0-x22e.google.com ([2607:f8b0:4001:c05::22e]) by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YhlIF-0005gh-5h for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 20:49:21 +0000 Received: by igblo3 with SMTP id lo3so54898767igb.0 for ; Mon, 13 Apr 2015 13:49:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=wfr+BYcW7/lSTSDjqeozf2oJ6/G/BU701lN+ZxOUJbA=; b=w3lbd5AyxV5IDJAvz7q8u8Fl0wDJ5/S4rENZd8XMtCG3t3fVA15PkuIcWDL9ON7O7s YVKKdEFuzAAf5uw38QBjDbBgAb76DRvDRWCgswkCklWxS8PnnAvSugtUuAAcs+lPs12T 3fLBW/vVDSeRr8KI5fAx31GMFXPkW6CszWfaej4Lg7CXncXPo1fdveJDxIjF10qDBrdx aUo6sY59DcAEeCqV5RTAaqGEikGWGsbjT6aRnMJYht9GWiVJdavMAdXcf2xKZ7ImoOdH 8KB+vlI1VuPRu83s1p3c+wYB6yF4OTOzkRlfzTtHKocsmWEW1ayxjzmUUHjOemjRg/vI LtQQ== MIME-Version: 1.0 X-Received: by 10.107.170.135 with SMTP id g7mr24410363ioj.2.1428958156253; Mon, 13 Apr 2015 13:49:16 -0700 (PDT) Received: by 10.36.64.15 with HTTP; Mon, 13 Apr 2015 13:49:16 -0700 (PDT) In-Reply-To: References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> Date: Mon, 13 Apr 2015 13:49:16 -0700 Message-ID: Subject: Re: unlogged tables From: "David G. Johnston" To: Matheus de Oliveira Cc: dgabriel , pgsql-performance Content-Type: multipart/alternative; boundary=001a114270644d73d90513a13f0b X-Pg-Spam-Score: -1.7 (-) 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 --001a114270644d73d90513a13f0b Content-Type: text/plain; charset=UTF-8 On Monday, April 13, 2015, Matheus de Oliveira wrote: > > On Mon, Apr 13, 2015 at 4:31 PM, dgabriel > wrote: > >> "In the event of a normal shutdown, we can flush all the writes to disk >> so we know all the data has been written, so there is no need to >> truncate." >> >> Isn't possible to periodically flush data to disk and in case of crush >> postgres to load only the data that existed at last flush? The periodic >> flush could be configurable, for example every 30 minutes or after x rows >> updated/inserted. >> > > There is no such facility implemented for UNLOGGED TABLEs. That could be a > feature request though. > > Well, that is half right anyway. UNLOGGED tables obey checkpoints just like any other table. The missing feature is an option to leaved restored the last checkpoint. Instead, not knowing whether there were changes since the last checkpoint, the system truncated the relation. What use case is there for a behavior that the last checkpoint data is left on the relation upon restarting - not knowing whether it was possible the other data could have been written subsequent? David J. --001a114270644d73d90513a13f0b Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Monday, April 13, 2015, Matheus de Oliveira <matioli.matheus@gmail.com> wrote:

On Mon, Apr 13, 2015 at 4:31 PM, dgabriel <gabriel.dodan@gmail.com>= wrote:
&q= uot;In the event of a normal shutdown, we can flush all the writes to disk<= br> so we know all the data has been written, so there is no need to truncate.&= quot;

Isn't possible to periodically flush data to disk and in case of crush<= br> postgres to load only the data that existed at last flush? The periodic
flush could be configurable, for example every 30 minutes or after x rows updated/inserted.

There is no such facility implemented for UNLOGGED TABLEs. That could b= e a feature request though.


=
Well, that is half right anyway.=C2=A0 UNLOGGED tables obey checkpoint= s just like any other table.=C2=A0 The missing feature is an option to leav= ed=C2=A0restored the last checkpoint.=C2=A0 Instead, not knowing whether th= ere were changes since the last checkpoint, the system truncated the relati= on.

What use case is there for a behavior that the= last checkpoint data is left on the relation upon restarting - not knowing= whether it was possible the other data could have been written subsequent?=

David J.
--001a114270644d73d90513a13f0b-- From Jim.Nasby@BlueTreble.com Mon Apr 13 20:59:07 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YhlRj-0000K5-Ns for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 20:59:07 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YhlRi-0008Pz-Up for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 20:59:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YhlRh-0008Pp-NN for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 20:59:05 +0000 Received: from mail-by2on0614.outbound.protection.outlook.com ([2a01:111:f400:fc0c::614] helo=na01-by2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YhlRX-0005rx-Pm for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 20:59:04 +0000 Authentication-Results: postgresql.org; dkim=none (message not signed) header.d=none; Received: from decina.local (70.113.16.71) by BLUPR11MB0676.namprd11.prod.outlook.com (25.163.216.146) with Microsoft SMTP Server (TLS) id 15.1.136.25; Mon, 13 Apr 2015 20:58:47 +0000 Message-ID: <552C2E03.5060800@BlueTreble.com> Date: Mon, 13 Apr 2015 15:58:43 -0500 From: Jim Nasby User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:31.0) Gecko/20100101 Thunderbird/31.4.0 MIME-Version: 1.0 To: "David G. Johnston" , Matheus de Oliveira CC: dgabriel , pgsql-performance Subject: Re: unlogged tables References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> In-Reply-To: Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [70.113.16.71] X-ClientProxiedBy: BLUPR05CA0043.namprd05.prod.outlook.com (10.141.20.13) To BLUPR11MB0676.namprd11.prod.outlook.com (25.163.216.146) X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BLUPR11MB0676; X-Forefront-Antispam-Report: BMV:1; SFV:NSPM; SFS:(10009020)(6009001)(24454002)(51704005)(479174004)(377454003)(80316001)(59896002)(65806001)(65816999)(15975445007)(54356999)(2950100001)(64126003)(47776003)(62966003)(77156002)(4001350100001)(50466002)(86362001)(66066001)(122386002)(76176999)(50986999)(65956001)(42186005)(36756003)(87976001)(93886004)(23676002)(33656002)(92566002)(19580395003)(40100003)(19580405001)(83506001)(46102003)(4001410100001)(85282002)(9078065003)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:BLUPR11MB0676; H:decina.local; FPR:; SPF:None; MLV:sfv; LANG:en; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(5002010)(5005006); SRVR:BLUPR11MB0676; BCL:0; PCL:0; RULEID:; SRVR:BLUPR11MB0676; X-Forefront-PRVS: 0545EFAC9A X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 13 Apr 2015 20:58:47.4357 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BLUPR11MB0676 X-Pg-Spam-Score: -1.9 (-) 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 On 4/13/15 3:49 PM, David G. Johnston wrote: > On Monday, April 13, 2015, Matheus de Oliveira > > wrote: > On Mon, Apr 13, 2015 at 4:31 PM, dgabriel > wrote: > > "In the event of a normal shutdown, we can flush all the writes > to disk > so we know all the data has been written, so there is no need to > truncate." > > Isn't possible to periodically flush data to disk and in case of > crush > postgres to load only the data that existed at last flush? The > periodic > flush could be configurable, for example every 30 minutes or > after x rows > updated/inserted. > > There is no such facility implemented for UNLOGGED TABLEs. That > could be a feature request though. > > Well, that is half right anyway. UNLOGGED tables obey checkpoints just > like any other table. The missing feature is an option to > leaved restored the last checkpoint. Instead, not knowing whether there > were changes since the last checkpoint, the system truncated the relation. > > What use case is there for a behavior that the last checkpoint data is > left on the relation upon restarting - not knowing whether it was > possible the other data could have been written subsequent? Yeah, this is not something that would be very easy to accomplish, because a buffer can get evicted and written to disk at any point. It wouldn't be too hard to read every unlogged table during recovery and see if there are any pages that were written after the last checkpoint, but that obviously won't be very fast. Actually, I suppose we could dedicate a fork for unlogged tables and use that to record the newest LSN of any page that's been written out. But if you have much of any write activity on the table that's probably going to be completely useless. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From alvherre@2ndquadrant.com Mon Apr 13 21:12:46 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhlew-0000on-EJ for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 21:12:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yhlev-00030n-5u for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 21:12:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yhlet-00030f-Vl for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 21:12:44 +0000 Received: from smtprelay0113.b.hostedemail.com ([64.98.42.113] helo=smtprelay.b.hostedemail.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhlep-00069y-Fj for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 21:12:42 +0000 Received: from filter.hostedemail.com (10.5.19.248.rfc1918.com [10.5.19.248]) by smtprelay01.b.hostedemail.com (Postfix) with ESMTP id 56A462D2A21; Mon, 13 Apr 2015 21:12:38 +0000 (UTC) X-Session-Marker: 616C76686572726540616C76682E6E6F2D69702E6F7267 X-Spam-Summary: 50, 0, 0, , d41d8cd98f00b204, alvherre@alvh.no-ip.org, :::::::::, RULES_HIT:41:355:379:599:800:967:973:988:989:1260:1263:1277:1311:1312:1313:1314:1345:1359:1437:1515:1516:1518:1519:1534:1541:1593:1594:1595:1596:1711:1730:1747:1777:1792:2198:2199:2393:2525:2561:2564:2682:2685:2828:2859:2933:2937:2939:2942:2945:2947:2951:2954:3022:3138:3139:3140:3141:3142:3352:3657:3834:3865:3866:3867:3868:3870:3871:3872:3873:3874:3934:3936:3938:3941:3944:3947:3950:3953:3956:3959:4250:4321:4605:4659:5007:6248:6261:7903:8599:8985:9025:9121:9388:10004:10400:10450:10455:10848:11232:11256:11257:11658:11854:11914:12043:12517:12519:12555:12682:12698:12737:12933:12937:13069:13095:13311:13357:13894:13895:19904:19999:21080, 0, RBL:none, CacheIP:none, Bayesian:0.5, 0.5, 0.5, Netcheck:none, DomainCache:0, MSF:not bulk, SPF:fn, MSBL:0, DNSBL:none, Custom_rules:0:0:0 X-HE-Tag: drain56_5122337cd3a18 X-Filterd-Recvd-Size: 2303 Received: from alvin.alvh.no-ip.org (unknown [186.37.127.61]) (Authenticated sender: alvherre@alvh.no-ip.org) by omf03.b.hostedemail.com (Postfix) with ESMTPA; Mon, 13 Apr 2015 21:12:37 +0000 (UTC) Received: by alvin.alvh.no-ip.org (Postfix, from userid 1000) id A0872F1A; Mon, 13 Apr 2015 18:13:12 -0300 (CLST) Date: Mon, 13 Apr 2015 18:13:12 -0300 From: Alvaro Herrera To: Jim Nasby Cc: "David G. Johnston" , Matheus de Oliveira , dgabriel , pgsql-performance Subject: Re: unlogged tables Message-ID: <20150413211312.GS4369@alvh.no-ip.org> References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> <552C2E03.5060800@BlueTreble.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <552C2E03.5060800@BlueTreble.com> User-Agent: Mutt/1.5.23 (2014-03-12) X-Pg-Spam-Score: -1.9 (-) 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 Jim Nasby wrote: > Yeah, this is not something that would be very easy to accomplish, because a > buffer can get evicted and written to disk at any point. It wouldn't be too > hard to read every unlogged table during recovery and see if there are any > pages that were written after the last checkpoint, but that obviously won't > be very fast. If you consider only tables, then yeah perhaps this is easy to accomplish (not really convinced myself). But if you consider indexes, things are not so easy anymore. In the thread from 2011 (which this started as a reply to) the OP was doing frequent UPDATEs to keep track of counts of something. I think that would be better served by using INSERTs of deltas and periodic accumulation of grouped values, as suggested in http://www.postgresql.org/message-id/20150305211601.GW3291@alvh.no-ip.org This has actually been suggested many times over the years. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From Jim.Nasby@BlueTreble.com Mon Apr 13 22:38:46 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhn0A-0003bH-N5 for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 22:38:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yhn09-0002eX-Mg for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 22:38:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yhn08-0002eI-FH for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 22:38:44 +0000 Received: from mail-bl2on0661.outbound.protection.outlook.com ([2a01:111:f400:fc09::661] helo=na01-bl2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhn02-0007iS-Ka for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 22:38:43 +0000 Authentication-Results: postgresql.org; dkim=none (message not signed) header.d=none; Received: from decina.local (70.113.16.71) by CY1PR11MB0684.namprd11.prod.outlook.com (25.163.236.22) with Microsoft SMTP Server (TLS) id 15.1.136.25; Mon, 13 Apr 2015 22:38:30 +0000 Message-ID: <552C4561.4060009@BlueTreble.com> Date: Mon, 13 Apr 2015 17:38:25 -0500 From: Jim Nasby User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:31.0) Gecko/20100101 Thunderbird/31.4.0 MIME-Version: 1.0 To: Alvaro Herrera CC: "David G. Johnston" , Matheus de Oliveira , dgabriel , pgsql-performance Subject: Re: unlogged tables References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> <552C2E03.5060800@BlueTreble.com> <20150413211312.GS4369@alvh.no-ip.org> In-Reply-To: <20150413211312.GS4369@alvh.no-ip.org> Content-Type: text/plain; charset="windows-1252"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [70.113.16.71] X-ClientProxiedBy: SN1PR07CA0013.namprd07.prod.outlook.com (25.162.170.151) To CY1PR11MB0684.namprd11.prod.outlook.com (25.163.236.22) X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:CY1PR11MB0684; X-Microsoft-Antispam-PRVS: X-Forefront-Antispam-Report: BMV:1; SFV:NSPM; SFS:(10009020)(6009001)(479174004)(24454002)(377454003)(51704005)(83506001)(77156002)(4001350100001)(86362001)(62966003)(33656002)(42186005)(15975445007)(19580405001)(122386002)(40100003)(87976001)(80316001)(19580395003)(46102003)(47776003)(93886004)(76176999)(66066001)(50986999)(59896002)(65816999)(54356999)(64126003)(23746002)(65956001)(2950100001)(110136001)(50466002)(65806001)(92566002)(36756003)(85282002)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:CY1PR11MB0684; H:decina.local; FPR:; SPF:None; MLV:sfv; LANG:en; X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(5005006)(5002010); SRVR:CY1PR11MB0684; BCL:0; PCL:0; RULEID:; SRVR:CY1PR11MB0684; X-Forefront-PRVS: 0545EFAC9A X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 13 Apr 2015 22:38:30.1975 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY1PR11MB0684 X-Pg-Spam-Score: -1.9 (-) 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 On 4/13/15 4:13 PM, Alvaro Herrera wrote: > Jim Nasby wrote: > >> Yeah, this is not something that would be very easy to accomplish, because a >> buffer can get evicted and written to disk at any point. It wouldn't be too >> hard to read every unlogged table during recovery and see if there are any >> pages that were written after the last checkpoint, but that obviously won't >> be very fast. > > If you consider only tables, then yeah perhaps this is easy to > accomplish (not really convinced myself). But if you consider indexes, > things are not so easy anymore. Are indexes not guaranteed to have LSNs? I thought they basically followed the same write rules as heap pages in regard to WAL first. Though, if you have an index that doesn't support logging (like hash) you're still hosed... > In the thread from 2011 (which this started as a reply to) the OP was I don't keep PGSQL emails from that far back... ;) > doing frequent UPDATEs to keep track of counts of something. I think > that would be better served by using INSERTs of deltas and periodic > accumulation of grouped values, as suggested in > http://www.postgresql.org/message-id/20150305211601.GW3291@alvh.no-ip.org > This has actually been suggested many times over the years. What I was suggesting certainly wouldn't help you if you were getting any serious amount of changes to the count. I am wondering though what the bottleneck in HEAD is with doing an UPDATE instead of an INSERT, at least where unlogged would help significantly. I didn't think we logged all that much more for an UPDATE. Heck, with HOT you might even be able to log less. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From jeff.janes@gmail.com Mon Apr 13 23:49:17 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yho6O-0005mw-TX for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 23:49:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yho6N-0003Od-Vo for pgsql-performance@arkaria.postgresql.org; Mon, 13 Apr 2015 23:49:16 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yho6M-0003OV-QJ for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 23:49:15 +0000 Received: from mail-lb0-x234.google.com ([2a00:1450:4010:c04::234]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Yho6J-0002ul-BK for pgsql-performance@postgresql.org; Mon, 13 Apr 2015 23:49:12 +0000 Received: by lbbqq2 with SMTP id qq2so71731089lbb.3 for ; Mon, 13 Apr 2015 16:49:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=GSWzUEqZW/rF46o0T6ASG0z80VmWZ7qlDMjLa1/WB+s=; b=taSjcCdX1LB3YR0cSIge2zRn2rZpErps7QZmGk8YJTpqfRKnKziS/CZ83OOb3TDCBD hGof7CREzBaPw/8eVGOATbQjLQ7rBKLVJwsi/M6mdVfxNcUjoPgrmCqYIJ08ccO+K4yZ ucXEeB2j0Uy1dievBjmu7g86LCjVVCnO36sOmXXTu00ZZcxrtrnCaIJF9JXBQaPCJiqx s1woIs2ohjyM8z+56eljC1skQP3zCHTPv9cKJFFt2fYErvNvfMw5GaAVNfXkgdaO7VAj j+lVDEzp+Y5zzkXNtDuYK5GbiZ7jzah5qzL3JJxHFJzPCNvKoKWjLAb+EnAiwkPJh4k5 l3WQ== MIME-Version: 1.0 X-Received: by 10.152.6.197 with SMTP id d5mr15528789laa.121.1428968948932; Mon, 13 Apr 2015 16:49:08 -0700 (PDT) Received: by 10.25.37.132 with HTTP; Mon, 13 Apr 2015 16:49:08 -0700 (PDT) In-Reply-To: References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> Date: Mon, 13 Apr 2015 16:49:08 -0700 Message-ID: Subject: Re: unlogged tables From: Jeff Janes To: "David G. Johnston" Cc: Matheus de Oliveira , dgabriel , pgsql-performance Content-Type: multipart/alternative; boundary=089e0149431898a50a0513a3c25e X-Pg-Spam-Score: -2.7 (--) 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 --089e0149431898a50a0513a3c25e Content-Type: text/plain; charset=UTF-8 On Mon, Apr 13, 2015 at 1:49 PM, David G. Johnston < david.g.johnston@gmail.com> wrote: > On Monday, April 13, 2015, Matheus de Oliveira > wrote: > >> >> On Mon, Apr 13, 2015 at 4:31 PM, dgabriel >> wrote: >> >>> "In the event of a normal shutdown, we can flush all the writes to disk >>> so we know all the data has been written, so there is no need to >>> truncate." >>> >>> Isn't possible to periodically flush data to disk and in case of crush >>> postgres to load only the data that existed at last flush? The periodic >>> flush could be configurable, for example every 30 minutes or after x rows >>> updated/inserted. >>> >> >> There is no such facility implemented for UNLOGGED TABLEs. That could be >> a feature request though. >> > One way would be to lock dirty buffers from unlogged relations into shared_buffers (which hardly seems like a good thing) until the start of a "super-checkpoint" and then write them all out as fast as possible (which kind of defeats checkpoint_completion_target). And then if the crash happened during a super-checkpoint, the data would still be inconsistent and need to be truncated. > >> > Well, that is half right anyway. UNLOGGED tables obey checkpoints just > like any other table. > Do they? I thought they only obeyed shutdown checkpoints, not online checkpoints. I do remember some changes around this area, but none that completely reverted that logic. > The missing feature is an option to leaved restored the last checkpoint. > Instead, not knowing whether there were changes since the last checkpoint, > the system truncated the relation. > > What use case is there for a behavior that the last checkpoint data is > left on the relation upon restarting - not knowing whether it was possible > the other data could have been written subsequent? > I would like a way to have unlogged tables be available on a replica provided that no changes were made to them between the pg_basebackup and the recovery point. My use case is that I mark certain read-only-after-bulk-loading tables as unlogged solely to avoid blowing out the log archive during the loading phase and refresh phase. This is stuff like vendor catalogs, NCBI datasets, ChEMBL datasets, etc, which can simply be re-derived from the reference. It would be nice if these were still available (without having to repeat the ETL) after crashes provided they were not written to since a checkpoint, and available on cloned test servers without having to repeat the ETL on those as well. As for "maybe its corrupt, maybe it isn't, but lets keep them anyway", yeah, I have little use for that. Cheers, Jeff --089e0149431898a50a0513a3c25e Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On M= on, Apr 13, 2015 at 1:49 PM, David G. Johnston <david.g.johnston@= gmail.com> wrote:
On Mo= nday, April 13, 2015, Matheus de Oliveira <matioli.matheus@gmail.com> wrote:<= br>

On Mon, Apr 13, 2015 at 4:31 PM, dgabriel <gabriel.dodan@gmail.com> wrote:
"In the event of a normal shutdown, = we can flush all the writes to disk
so we know all the data has been written, so there is no need to truncate.&= quot;

Isn't possible to periodically flush data to disk and in case of crush<= br> postgres to load only the data that existed at last flush? The periodic
flush could be configurable, for example every 30 minutes or after x rows updated/inserted.

There is no such facility implemented for UNLOGGED TABLEs. That could b= e a feature request though.

One way would be to lock dirty buffers from unlogged r= elations into shared_buffers (which hardly seems like a good thing) until t= he start of a "super-checkpoint" and then write them all out as f= ast as possible (which kind of defeats checkpoint_completion_target).=C2=A0= And then if the crash happened during a super-checkpoint, the data would s= till be inconsistent and need to be truncated.
=C2=A0


Well,= that is half right anyway.=C2=A0 UNLOGGED tables obey checkpoints just lik= e any other table.=C2=A0

Do they?=C2= =A0 I thought they only obeyed shutdown checkpoints, not online checkpoints= .=C2=A0 I do remember some changes around this area, but none that complete= ly reverted that logic.

=C2=A0
The missing feature is an option to leaved=C2=A0restored the last = checkpoint.=C2=A0 Instead, not knowing whether there were changes since the= last checkpoint, the system truncated the relation.

What use case is there for a behavior that the last checkpoint data is l= eft on the relation upon restarting - not knowing whether it was possible t= he other data could have been written subsequent?
I would like a way to have unlogged tables be available on a r= eplica provided that no changes were made to them between the pg_basebackup= and the recovery point.

My use case is that I mar= k certain read-only-after-bulk-loading tables as unlogged solely to avoid b= lowing out the log archive during the loading phase and refresh phase.=C2= =A0 This is stuff like vendor catalogs, NCBI datasets, ChEMBL datasets, etc= , which can simply be re-derived from the reference.=C2=A0 It would be nice= if these were still available (without having to repeat the ETL) after cra= shes provided they were not written to since a checkpoint, and available on= cloned test servers without having to repeat the ETL on those as well.
=C2=A0
As for "maybe its corrupt, maybe it isn't,= but lets keep them anyway", yeah, I have little use for that.

Cheers,

Jeff
--089e0149431898a50a0513a3c25e-- From david.g.johnston@gmail.com Tue Apr 14 00:32:29 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YhomC-0007J8-MX for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 00:32:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YhomA-0000ia-H0 for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 00:32:26 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yhom9-0000iT-Bx for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 00:32:25 +0000 Received: from mail-ig0-x22b.google.com ([2607:f8b0:4001:c05::22b]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Yhom5-0003jT-PF for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 00:32:23 +0000 Received: by iget9 with SMTP id t9so66088553ige.1 for ; Mon, 13 Apr 2015 17:32:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=PbgLBcqHeY947FVI0ypOZr+zE0vEA6/U8a34UqWjE5M=; b=NHKp+lm0aSV0jG61CjrVz1d46Zw50vlNq4eEj7dLd4CTGbO0nl2ebQtvnNKSmYYTnF +aD1iL8wt2Ixv6mtTMhcopV8V/0yILIuA2TvfyFYogfJ1LO5Io2ZP2KYHbrkAKLvQz5C THY7KTm3r0IwIB4j5aku1LrjQNlj6ElQd88RYjD8urmjh9MXLYaH3/d8caRXfINVt47w ALSKDH52lYDoWV4nDpyG25QLgksa8w/0cF+6byBTCCwmhTL7XX3ucWNO+362jFcN8asl w8AjDuUNTAmXueyFGDq772M2wf+MWNXVEMwqX48bVn3nXZUE0HsClfvV+UWM0xFdGu6v YUBQ== MIME-Version: 1.0 X-Received: by 10.50.41.8 with SMTP id b8mr20239764igl.38.1428971540701; Mon, 13 Apr 2015 17:32:20 -0700 (PDT) Received: by 10.36.64.15 with HTTP; Mon, 13 Apr 2015 17:32:20 -0700 (PDT) In-Reply-To: References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> Date: Mon, 13 Apr 2015 17:32:20 -0700 Message-ID: Subject: Re: unlogged tables From: "David G. Johnston" To: Jeff Janes Cc: Matheus de Oliveira , dgabriel , pgsql-performance Content-Type: multipart/alternative; boundary=089e0116197a13fd8a0513a45db2 X-Pg-Spam-Score: -2.7 (--) 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 --089e0116197a13fd8a0513a45db2 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Mon, Apr 13, 2015 at 4:49 PM, Jeff Janes wrote: > On Mon, Apr 13, 2015 at 1:49 PM, David G. Johnston < > david.g.johnston@gmail.com> wrote: > >> On Monday, April 13, 2015, Matheus de Oliveira >> wrote: >> >>> >>> On Mon, Apr 13, 2015 at 4:31 PM, dgabriel >>> wrote: >>> >>>> "In the event of a normal shutdown, we can flush all the writes to dis= k >>>> so we know all the data has been written, so there is no need to >>>> truncate." >>>> >>>> Isn't possible to periodically flush data to disk and in case of crush >>>> postgres to load only the data that existed at last flush? The periodi= c >>>> flush could be configurable, for example every 30 minutes or after x >>>> rows >>>> updated/inserted. >>>> >>> >>> There is no such facility implemented for UNLOGGED TABLEs. That could b= e >>> a feature request though. >>> >> > One way would be to lock dirty buffers from unlogged relations into > shared_buffers (which hardly seems like a good thing) until the start of = a > "super-checkpoint" and then write them all out as fast as possible (which > kind of defeats checkpoint_completion_target). And then if the crash > happened during a super-checkpoint, the data would still be inconsistent > and need to be truncated. > > >> >>> >> Well, that is half right anyway. UNLOGGED tables obey checkpoints just >> like any other table. >> > > Do they? I thought they only obeyed shutdown checkpoints, not online > checkpoints. I do remember some changes around this area, but none that > completely reverted that logic. > > =E2=80=8BI vaguely recall that conversation now...I'm not positive on the e= xact mechanics here and, as it pertains to the OP, the difference you describe is immaterial since in either case the status quo mandates an "all or nothing" approach to an unlogged table's contents.=E2=80=8B > > >> The missing feature is an option to leaved restored the last checkpoint. >> Instead, not knowing whether there were changes since the last checkpoin= t, >> the system truncated the relation. >> >> What use case is there for a behavior that the last checkpoint data is >> left on the relation upon restarting - not knowing whether it was possib= le >> the other data could have been written subsequent? >> > > I would like a way to have unlogged tables be available on a replica > provided that no changes were made to them between the pg_basebackup and > the recovery point. > > My use case is that I mark certain read-only-after-bulk-loading tables as > unlogged solely to avoid blowing out the log archive during the loading > phase and refresh phase. This is stuff like vendor catalogs, NCBI > datasets, ChEMBL datasets, etc, which can simply be re-derived from the > reference. It would be nice if these were still available (without havin= g > to repeat the ETL) after crashes provided they were not written to since = a > checkpoint, and available on cloned test servers without having to repeat > the ETL on those as well. > > =E2=80=8BMy gut reaction is that those should be in their own clusters and = accessed via postgres_fdw... That particular use-case would probably best be served with a separate replication channel which pushes data files from the primary to the slaves and allows for the slave to basically "rewrite" its existing table by pointing to the newly supplied version. Some kind of "CREATE STATIC TABLE" and "PUSH STATIC TABLE TO {all | replica name}" command combo...though ideally with less manual intervention... David J.=E2=80=8B --089e0116197a13fd8a0513a45db2 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On Mon, Ap= r 13, 2015 at 4:49 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Mon, Apr= 13, 2015 at 1:49 PM, David G. Johnston <david.g.johnston@gmail.c= om> wrote:
On Monday, April 13, 201= 5, Matheus de Oliveira <matioli.matheus@gmail.com> wrote:

On Mon, Apr 13, 2015 at 4:31 PM, dgabriel <gabriel= .dodan@gmail.com> wrote:
"In the event of a normal shutdown, we can flush all th= e writes to disk
so we know all the data has been written, so there is no need to truncate.&= quot;

Isn't possible to periodically flush data to disk and in case of crush<= br> postgres to load only the data that existed at last flush? The periodic
flush could be configurable, for example every 30 minutes or after x rows updated/inserted.

There is no such facility implemented for UNLOGGED TABLEs. That could b= e a feature request though.

One way would be to lock dirty buffers from unl= ogged relations into shared_buffers (which hardly seems like a good thing) = until the start of a "super-checkpoint" and then write them all o= ut as fast as possible (which kind of defeats checkpoint_completion_target)= .=C2=A0 And then if the crash happened during a super-checkpoint, the data = would still be inconsistent and need to be truncated.
=C2=A0
=


Well, that is half right anyway.=C2=A0 UNLOGGED tables obey checkpo= ints just like any other table.=C2=A0

Do they?=C2=A0 I thought they only obeyed shutdown checkpoints, not= online checkpoints.=C2=A0 I do remember some changes around this area, but= none that completely reverted that logic.

<= /div>

=E2=80= =8BI vaguely recall that conversation now...I'm not positive on the exa= ct mechanics here and, as it pertains to the OP, the difference you describ= e is immaterial since in either case the status quo mandates an "all o= r nothing" approach to an unlogged table's contents.=E2=80=8B
=C2=A0
=C2=A0
The missing feature is an o= ption to leaved=C2=A0restored the last checkpoint.=C2=A0 Instead, not knowi= ng whether there were changes since the last checkpoint, the system truncat= ed the relation.

What use case is there for a beha= vior that the last checkpoint data is left on the relation upon restarting = - not knowing whether it was possible the other data could have been writte= n subsequent?

I would like a w= ay to have unlogged tables be available on a replica provided that no chang= es were made to them between the pg_basebackup and the recovery point.

<= div>My use case is that I mark certain read-only-after-bulk-loading tables = as unlogged solely to avoid blowing out the log archive during the loading = phase and refresh phase.=C2=A0 This is stuff like vendor catalogs, NCBI dat= asets, ChEMBL datasets, etc, which can simply be re-derived from the refere= nce.=C2=A0 It would be nice if these were still available (without having t= o repeat the ETL) after crashes provided they were not written to since a c= heckpoint, and available on cloned test servers without having to repeat th= e ETL on those as well.
=C2=A0

=E2=80=8BMy gut reaction is that th= ose should be in their own clusters and accessed via postgres_fdw...
<= /div>

That par= ticular use-case would probably best be served with a separate replication = channel which pushes data files from the primary to the slaves and allows f= or the slave to basically "rewrite" its existing table by pointin= g to the newly supplied version.=C2=A0 Some kind of "CREATE STATIC TAB= LE" and "PUSH STATIC TABLE TO {all | replica name}" command = combo...though ideally with less manual intervention...

David J.=E2=80=8B=C2=A0

--089e0116197a13fd8a0513a45db2-- From Jim.Nasby@BlueTreble.com Tue Apr 14 02:45:37 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhqr3-0003UA-GF for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 02:45:37 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yhqr2-0003li-VZ for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 02:45:37 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yhqr1-0003lX-SK for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 02:45:36 +0000 Received: from mail-bl2on0603.outbound.protection.outlook.com ([2a01:111:f400:fc09::603] helo=na01-bl2-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yhqqx-0006FM-VK for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 02:45:33 +0000 Received: from CY1PR11MB0682.namprd11.prod.outlook.com (25.163.236.20) by CY1PR11MB0427.namprd11.prod.outlook.com (25.163.90.26) with Microsoft SMTP Server (TLS) id 15.1.136.25; Tue, 14 Apr 2015 02:45:26 +0000 Authentication-Results: postgresql.org; dkim=none (message not signed) header.d=none; Received: from decina.local (70.113.16.71) by CY1PR11MB0682.namprd11.prod.outlook.com (25.163.236.20) with Microsoft SMTP Server (TLS) id 15.1.136.25; Tue, 14 Apr 2015 02:45:24 +0000 Message-ID: <552C7F41.6070101@BlueTreble.com> Date: Mon, 13 Apr 2015 21:45:21 -0500 From: Jim Nasby User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:31.0) Gecko/20100101 Thunderbird/31.4.0 MIME-Version: 1.0 To: "David G. Johnston" , Jeff Janes CC: Matheus de Oliveira , dgabriel , pgsql-performance Subject: Re: unlogged tables References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> In-Reply-To: Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 8bit X-Originating-IP: [70.113.16.71] X-ClientProxiedBy: BLUPR05CA0044.namprd05.prod.outlook.com (10.141.20.14) To CY1PR11MB0682.namprd11.prod.outlook.com (25.163.236.20) X-Microsoft-Antispam: UriScan:; BCL:0; PCL:0; RULEID:; SRVR:CY1PR11MB0682; UriScan:; BCL:0; PCL:0; RULEID:; SRVR:CY1PR11MB0427; X-Microsoft-Antispam-PRVS: X-Forefront-Antispam-Report: BMV:1; SFV:NSPM; SFS:(10009020)(6009001)(51704005)(24454002)(377454003)(479174004)(122386002)(87976001)(46102003)(50466002)(93886004)(33656002)(62966003)(36756003)(66066001)(65956001)(92566002)(65806001)(59896002)(15975445007)(23676002)(83506001)(86362001)(2950100001)(65816999)(77156002)(76176999)(50986999)(19580395003)(87266999)(54356999)(47776003)(42186005)(4001350100001)(85282002)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:CY1PR11MB0682; H:decina.local; FPR:; SPF:None; MLV:sfv; LANG:en; X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(5005006)(5002010); SRVR:CY1PR11MB0682; BCL:0; PCL:0; RULEID:; SRVR:CY1PR11MB0682; X-Forefront-PRVS: 054642504A X-MS-Exchange-CrossTenant-OriginalArrivalTime: 14 Apr 2015 02:45:24.1648 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY1PR11MB0682 X-OriginatorOrg: bluetreble.com X-Pg-Spam-Score: -1.9 (-) 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 On 4/13/15 7:32 PM, David G. Johnston wrote: > The missing feature is an option to leaved restored the last > checkpoint. Instead, not knowing whether there were changes > since the last checkpoint, the system truncated the relation. > > What use case is there for a behavior that the last checkpoint > data is left on the relation upon restarting - not knowing > whether it was possible the other data could have been written > subsequent? > > > I would like a way to have unlogged tables be available on a replica > provided that no changes were made to them between the pg_basebackup > and the recovery point. > > > My use case is that I mark certain read-only-after-bulk-loading > tables as unlogged solely to avoid blowing out the log archive > during the loading phase and refresh phase. This is stuff like > vendor catalogs, NCBI datasets, ChEMBL datasets, etc, which can > simply be re-derived from the reference. It would be nice if these > were still available (without having to repeat the ETL) after > crashes provided they were not written to since a checkpoint, and > available on cloned test servers without having to repeat the ETL on > those as well. > > > ​My gut reaction is that those should be in their own clusters and > accessed via postgres_fdw... Likely to produce really crappy plans if the tables are of any real size... > That particular use-case would probably best be served with a separate > replication channel which pushes data files from the primary to the > slaves and allows for the slave to basically "rewrite" its existing > table by pointing to the newly supplied version. Some kind of "CREATE > STATIC TABLE" and "PUSH STATIC TABLE TO {all | replica name}" command > combo...though ideally with less manual intervention... You still have the same problem of knowing if someone has scribbled on the data since the last checkpoint. There's been recent discussion of adding support for read-only tables. If we had those, we might be able to support something like... INSERT INTO unlogged; ALTER TABLE unlogged SET READ ONLY; CHECKPOINT; /* take backup */ This should be safe as long as we WAL log changes to read-only status (which presumably we would). How much work that would entail though, I don't know. Ultimately you still have to get the data over to the other machine anyway. ISTM it'd be a LOT more useful to look at ways to make the WAL logging of bulk inserts (and especially COPY into a known empty table) a lot more efficient. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From david.g.johnston@gmail.com Tue Apr 14 03:28:39 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YhrWg-00055f-TD for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 03:28:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YhrWg-00035f-3o for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 03:28:38 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YhrWd-00035Q-Iu for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 03:28:35 +0000 Received: from mail-ig0-x22e.google.com ([2607:f8b0:4001:c05::22e]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YhrWa-0006zz-8O for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 03:28:33 +0000 Received: by igbqf9 with SMTP id qf9so5169369igb.1 for ; Mon, 13 Apr 2015 20:28:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=Z4U9Sv+QvZrRR/4W/IYZAjgw58uvR4EjJ5e3oue2sZM=; b=pV0WmdRmwnrok+QBPBqXSCmWBEVuUoy+yG6fUOtydI/8ypY9MxyV0RYq1gIiQ8nWiE l7Tz0x3vZLJDr3Zdyjt0RChbJN6x4TepOZbeh75y1qw+gj6sXgOpMwLeDZRU6gJtipHf SlCX0vGFJi/LUY0eM+HA/griqgj/Kqh0Hgd6RPEJJpQcKtCbH+0rNlgo1x/x7R0TRcBE xp6iiTp3SBjTUN4FZKmJV5qRhnOKeH8QjKWnumZqez6eSYTAXTlfNsXO6+u0IYXeETQW WUJT+9DBZ0166QMqT9TF0bb913pzqdhIPsg2lHOJgMr/JIyuk66+yvgUkmsl+gYed4Ec pNMg== MIME-Version: 1.0 X-Received: by 10.107.3.199 with SMTP id e68mr22066455ioi.92.1428982111491; Mon, 13 Apr 2015 20:28:31 -0700 (PDT) Received: by 10.36.64.15 with HTTP; Mon, 13 Apr 2015 20:28:31 -0700 (PDT) In-Reply-To: <552C7F41.6070101@BlueTreble.com> References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> <552C7F41.6070101@BlueTreble.com> Date: Mon, 13 Apr 2015 20:28:31 -0700 Message-ID: Subject: Re: unlogged tables From: "David G. Johnston" To: Jim Nasby Cc: Jeff Janes , Matheus de Oliveira , dgabriel , pgsql-performance Content-Type: multipart/alternative; boundary=001a113ed422255ec00513a6d32c X-Pg-Spam-Score: -2.7 (--) 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 --001a113ed422255ec00513a6d32c Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Mon, Apr 13, 2015 at 7:45 PM, Jim Nasby wrote= : > On 4/13/15 7:32 PM, David G. Johnston wrote:=E2=80=8B > > That particular use-case would probably best be served with a separate >> replication channel which pushes data files from the primary to the >> slaves and allows for the slave to basically "rewrite" its existing >> table by pointing to the newly supplied version. Some kind of "CREATE >> STATIC TABLE" and "PUSH STATIC TABLE TO {all | replica name}" command >> combo...though ideally with less manual intervention... >> > > You still have the same problem of knowing if someone has scribbled on th= e > data since the last checkpoint. > =E2=80=8BThat seems like an automation concern though...the more limited id= ea was to simply have a means for a table to exist on the master and allow the user to cause an exact copy of that table to appear on a replica via direct data transfer (i.e., without need to create a backup/dump). If the table already exists on the replica the existing version remains as-is until the new table is fully push and then a filenode pointer update happens. If changes are made to the master the two tables will remain diverged until a new push occurs.=E2=80=8B I imaging this same idea could be handled external to the database though I'm don't know enough to comment on the specific technical merits of each.= =E2=80=8B > There's been recent discussion of adding support for read-only tables. If > we had those, we might be able to support something like... > > INSERT INTO unlogged; > ALTER TABLE unlogged SET READ ONLY; > CHECKPOINT; > /* take backup */ > > This should be safe as long as we WAL log changes to read-only status > (which presumably we would). > > How much work that would entail though, I don't know. > > Ultimately you still have to get the data over to the other machine > anyway. ISTM it'd be a LOT more useful to look at ways to make the WAL > logging of bulk inserts (and especially COPY into a known empty table) a > lot more efficient. > > =E2=80=8BJeff Janes makes a comment about wanting "...to avoid blowing out = the log archive..."; which I also don't quite follow... WAL does seem to be designed to solve a different problem that what is described here - lots of small changes versus few large changes. Improving WAL to move the size at which small becomes large is a win but another channel designed for few large changes may be less complex to implement. The current work in logical replication likely has merit here as well but my familiarity with that technology is fairly limited. David J. --001a113ed422255ec00513a6d32c Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On Mon, Ap= r 13, 2015 at 7:45 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
On 4/13/15 7:32 = PM, David G. Johnston wrote:=E2=80=8B
That particular use-case would probably best be served with a separate
replication channel which pushes data files from the primary to the
slaves and allows for the slave to basically "rewrite" its existi= ng
table by pointing to the newly supplied version.=C2=A0 Some kind of "C= REATE
STATIC TABLE" and "PUSH STATIC TABLE TO {all | replica name}"= ; command
combo...though ideally with less manual intervention...

 You still have the same problem of knowing if someone has scribbled on the = data since the last checkpoint.

=E2= =80=8BThat seems like an automation concern though...the more limited idea = was to simply have a means for a table to exist on the master and allow the= user to cause an exact copy of that table to appear on a replica via direc= t data transfer (i.e., without need to create a backup/dump).=C2=A0 If the = table already exists on the replica the existing version remains as-is unti= l the new table is fully push and then a filenode pointer update happens.= =C2=A0 If changes are made to the master the two tables will remain diverge= d until a new push occurs.=E2=80=8B

I ima= ging this same idea could be handled external to the database though I'= m don't know enough to comment on the specific technical merits of each= .=E2=80=8B


There's been recent discussion of adding support for read-only tables. = If we had those, we might be able to support something like...

INSERT INTO unlogged;
ALTER TABLE unlogged SET READ ONLY;
CHECKPOINT;
/* take backup */

This should be safe as long as we WAL log changes to read-only status (whic= h presumably we would).

How much work that would entail though, I don't know.

Ultimately you still have to get the data over to the other machine anyway.= ISTM it'd be a LOT more useful to look at ways to make the WAL logging= of bulk inserts (and especially COPY into a known empty table) a lot more = efficient.


=E2=80=8BJeff Janes makes a comment about wanting "...= t= o avoid blowing out the log archive..."; which I also don't quite = follow...

WAL does seem to be designed to = solve a different problem that what is described here - lots of small chang= es versus few large changes.=C2=A0 Improving WAL to move the size at which = small becomes large is a win but another channel designed for few large cha= nges may be less complex to implement.=C2=A0 The current work in logical re= plication likely has merit here as well but my familiarity with that techno= logy is fairly limited.

David J.
--001a113ed422255ec00513a6d32c-- From yves@zioup.com Tue Apr 14 15:41:49 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yi2yD-0004pT-Gg for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 15:41:49 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yi2yC-0001tk-VK for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 15:41:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yi2yB-0001qh-60 for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 15:41:47 +0000 Received: from smtp-out-01.shaw.ca ([64.59.136.137]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yi2y6-0001ez-6Q for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 15:41:45 +0000 X-Cloudmark-SP-Filtered: true X-Cloudmark-SP-Result: v=1.1 cv=BkBxTXsSVzyY7C2joSQK+JNK4mJr1It1Skr7Xe8+Jh8= c=1 sm=1 a=motdEeDeaRIA:10 a=BLceEmwcHowA:10 a=IkcTkHD0fZMA:10 a=1AzCEyHvRsrOnBOCSdCQag==:17 a=N8iSzGwtAAAA:8 a=Xzj5fuOlYcKBNXdjLMEA:9 a=QEXdDO2ut3YA:10 a=HpAAvcLHHh0Zw7uRqdWCyQ==:117 Received: from unknown (HELO home.zioup.com) ([70.72.160.156]) by smtp-out-01.shaw.ca with ESMTP; 14 Apr 2015 09:41:38 -0600 Received: from [192.168.2.80] (epicurus.zioup.net [192.168.2.80]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by home.zioup.com (Postfix) with ESMTPSA id A248BA035F for ; Tue, 14 Apr 2015 09:41:38 -0600 (MDT) Message-ID: <552D3532.5020807@zioup.com> Date: Tue, 14 Apr 2015 09:41:38 -0600 From: Yves Dorfsman User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.5.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: unlogged tables References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> In-Reply-To: Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.6 (--) 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 On 2015-04-13 17:49, Jeff Janes wrote: > > One way would be to lock dirty buffers from unlogged relations into > shared_buffers (which hardly seems like a good thing) until the start of a > "super-checkpoint" and then write them all out as fast as possible (which kind > of defeats checkpoint_completion_target). And then if the crash happened > during a super-checkpoint, the data would still be inconsistent and need to be > truncated. > What do you call a "super-checkpoint"? -- http://yves.zioup.com gpg: 4096R/32B0F416 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From gabriel.dodan@gmail.com Tue Apr 14 15:57:24 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yi3DI-0006xP-Kp for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 15:57:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yi3DH-0004ji-Gz for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 15:57:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yi3DG-0004jb-Ey for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 15:57:22 +0000 Received: from mwork.nabble.com ([162.253.133.43]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yi3D8-0001y2-4F for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 15:57:21 +0000 Received: from msam.nabble.com (unknown [162.253.133.85]) by mwork.nabble.com (Postfix) with ESMTP id CAE4E1AD5351 for ; Tue, 14 Apr 2015 08:56:42 -0700 (PDT) Date: Tue, 14 Apr 2015 08:56:10 -0700 (MST) From: dgabriel To: pgsql-performance@postgresql.org Message-ID: <1429026970751-5845650.post@n5.nabble.com> In-Reply-To: References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> Subject: Re: unlogged tables MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -0.3 (/) 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 David G Johnston wrote > Well, that is half right anyway. UNLOGGED tables obey checkpoints just > like any other table. The missing feature is an option to leaved restored > the last checkpoint. Instead, not knowing whether there were changes > since the last checkpoint, the system truncated the relation. > > What use case is there for a behavior that the last checkpoint data is > left on the relation upon restarting - not knowing whether it was possible > the other data could have been written subsequent? If is possible to restore the table at last checkpoint state that will be more than enough. I don't care about the changes since last checkpoint, I am willing to lose those changes. There are use cases where is acceptable to lose some data, for example in a cache system, it is not a big issue if we lose some cached data. -- View this message in context: http://postgresql.nabble.com/unlogged-tables-tp4985453p5845650.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From jeff.janes@gmail.com Tue Apr 14 16:58:54 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yi4Ao-0006T2-CX for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 16:58:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yi4An-0005bT-LN for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 16:58:53 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yi4Am-0005bN-NO for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 16:58:52 +0000 Received: from mail-la0-x234.google.com ([2a00:1450:4010:c03::234]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Yi4Aj-00058h-MP for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 16:58:50 +0000 Received: by labbd9 with SMTP id bd9so13148138lab.2 for ; Tue, 14 Apr 2015 09:58:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=UGxNm7k8YVrX1eqfeA+TFCzSqxpzPv61lHpBYGWsYbk=; b=y6u46ioxf8NP+lb7iLYzIeCa6g3o0ujDuHHPPErjoUHrM9W/9G8ScQeuexn7yUKHsm D6TfM30SkyE6NqTgewc+XGMO3l5YbNvqxoUAjLmIjSLCE57WVoZB1oQMFBq8+uOggXst z/YnALSdd+N5jF/tbF8o2UFycW35QVCPyVx8kOVVS5v7LxRukADNqfpzVYsK5pGuTx4h kFnTcGX7Y1ponhll0IdY4sgCl9YuxuLXb6lZzPUvrquTed6uF9v+WDnMo5T5Za2TtSoV ICoLF+l3J7yYQNcpon1rLccneujK5rj95cN5HOT3puN0DKCejj1S19L+YAupTRkYRxSo oPOw== MIME-Version: 1.0 X-Received: by 10.152.26.130 with SMTP id l2mr19957210lag.20.1429030726920; Tue, 14 Apr 2015 09:58:46 -0700 (PDT) Received: by 10.25.37.132 with HTTP; Tue, 14 Apr 2015 09:58:46 -0700 (PDT) In-Reply-To: <552D3532.5020807@zioup.com> References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> <552D3532.5020807@zioup.com> Date: Tue, 14 Apr 2015 09:58:46 -0700 Message-ID: Subject: Re: unlogged tables From: Jeff Janes To: Yves Dorfsman Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=089e0158c16ed9f0030513b22467 X-Pg-Spam-Score: -2.7 (--) 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 --089e0158c16ed9f0030513b22467 Content-Type: text/plain; charset=UTF-8 On Tue, Apr 14, 2015 at 8:41 AM, Yves Dorfsman wrote: > On 2015-04-13 17:49, Jeff Janes wrote: > > > > One way would be to lock dirty buffers from unlogged relations into > > shared_buffers (which hardly seems like a good thing) until the start of > a > > "super-checkpoint" and then write them all out as fast as possible > (which kind > > of defeats checkpoint_completion_target). And then if the crash happened > > during a super-checkpoint, the data would still be inconsistent and need > to be > > truncated. > > > > What do you call a "super-checkpoint"? > A hypothetical checkpoint which includes writing and flushing pages of unlogged tables. Presumably you wouldn't want every checkpoint to do this, because if done the way I described the super-checkpoint is a vulnerable period. Crashes that happen during it would result in truncation of the unlogged relation. Since that is the very thing we want to avoid, you would want to make these vulnerable periods rare. Cheers, Jeff --089e0158c16ed9f0030513b22467 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On T= ue, Apr 14, 2015 at 8:41 AM, Yves Dorfsman <yves@zioup.com> wro= te:
On 2015-04-13 17:49,= Jeff Janes wrote:
>
> One way would be to lock dirty buffers from unlogged relations into > shared_buffers (which hardly seems like a good thing) until the start = of a
> "super-checkpoint" and then write them all out as fast as po= ssible (which kind
> of defeats checkpoint_completion_target).=C2=A0 And then if the crash = happened
> during a super-checkpoint, the data would still be inconsistent and ne= ed to be
> truncated.
>

What do you call a "super-checkpoint"?

A hypothetical checkpoint which includes writing and flush= ing pages of unlogged tables.

Presumably you would= n't want every checkpoint to do this, because if done the way I describ= ed the super-checkpoint is a vulnerable period.=C2=A0 Crashes that happen d= uring it would result in truncation of the unlogged relation.=C2=A0 Since t= hat is the very thing we want to avoid, you would want to make these vulner= able periods rare.

Cheers,

Jeff
--089e0158c16ed9f0030513b22467-- From jeff.janes@gmail.com Tue Apr 14 17:37:27 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yi4m6-0003ZQ-TB for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 17:37:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yi4m6-0007WV-8J for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 17:37:26 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yi4m5-0007WI-04 for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 17:37:25 +0000 Received: from mail-la0-x22d.google.com ([2a00:1450:4010:c03::22d]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Yi4m1-0005s2-IH for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 17:37:23 +0000 Received: by labbd9 with SMTP id bd9so13988881lab.2 for ; Tue, 14 Apr 2015 10:37:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=OLzSFjFTjQs1XjSD1h6qAPcoPeuLs1PR0PXdp7hb14c=; b=UsqeEkJfzzIy+uso2I81Krp+nQJRrOuYeacBA645t871Yr9HTzxRLeBJe7IjangdaS sDKsgVHloQoX7YkH1r+ZWJgFU/bbeGgD8q4W5DBBaHvbNYw5+8Dsh+iiE/miWOoOifVU evcql0CvcvXscX1Gshsm7AYB/5jpGacKvXVuhmFFSLfGQnWO8s33eOVeFkPEd0sqGk4G I24ema3FAa5v+EBp/K+iK4nsoHe0YvMyzSxulbjCXaA64J2eEMemnDsLwRI53eV7AP7X 8j9jRvYL06FyvxqUTqVIppveTIV+FTSMWvIjsWaLzZL02pyAnzbwMsSBKEJ6CUtykBqm obTA== MIME-Version: 1.0 X-Received: by 10.112.143.137 with SMTP id se9mr12632483lbb.60.1429033039422; Tue, 14 Apr 2015 10:37:19 -0700 (PDT) Received: by 10.25.37.132 with HTTP; Tue, 14 Apr 2015 10:37:19 -0700 (PDT) In-Reply-To: References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> <552C7F41.6070101@BlueTreble.com> Date: Tue, 14 Apr 2015 10:37:19 -0700 Message-ID: Subject: Re: unlogged tables From: Jeff Janes To: "David G. Johnston" Cc: Jim Nasby , Matheus de Oliveira , dgabriel , pgsql-performance Content-Type: multipart/alternative; boundary=089e01183662afeac30513b2aefb X-Pg-Spam-Score: -2.7 (--) 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 --089e01183662afeac30513b2aefb Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Mon, Apr 13, 2015 at 8:28 PM, David G. Johnston < david.g.johnston@gmail.com> wrote: > On Mon, Apr 13, 2015 at 7:45 PM, Jim Nasby > wrote: > >> >> >> There's been recent discussion of adding support for read-only tables. I= f >> we had those, we might be able to support something like... >> >> INSERT INTO unlogged; >> ALTER TABLE unlogged SET READ ONLY; >> CHECKPOINT; >> /* take backup */ >> >> This should be safe as long as we WAL log changes to read-only status >> (which presumably we would). >> >> How much work that would entail though, I don't know. >> > Right. I've been keeping an eye on that discussion with the same intention. The big question is how, during recovery, does it know what state the table was in without being able to read from the system catalogs? Perhaps it would be the checkpointer's duty at the end of the checkpoint to remove the init fork for unlogged relations which were turned to read only before that checkpoint started. > >> Ultimately you still have to get the data over to the other machine >> anyway. ISTM it'd be a LOT more useful to look at ways to make the WAL >> logging of bulk inserts (and especially COPY into a known empty table) a >> lot more efficient. >> >> > =E2=80=8BJeff Janes makes a comment about wanting "...to avoid blowing ou= t the > log archive..."; which I also don't quite follow... > I think the WAL logging of bulk COPY is pretty space-efficient already, provided it is not indexed at the time of the COPY. But no amount of efficiency improvement is going to make them small enough for me want to keep the WAL logs around beyond the next base backup. What I would really want is a way to make two separate WAL streams; changes to this set of tables goes to the "keep forever, for PITR" stream, and changes to this other set of tables go to the "keep until pg_basebackup is next run" stream. Of course you couldn't have fk constraints between the two different sets of tables. Having to get the data over to the other machine doesn't bother me, it is just a question of how to do it without permanently intermingling it with WAL logs which I want to keep forever. The FDW would be a good option, except the overhead (both execution overhead and the overhead of poor plans) seems to be too large. I haven't explored it as much as I would like. Cheers, Jeff --089e01183662afeac30513b2aefb Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On M= on, Apr 13, 2015 at 8:28 PM, David G. Johnston <david.g.johnston@= gmail.com> wrote:
On Mon, Apr 13, 2015 at 7:45 PM, Jim= Nasby <= Jim.Nasby@blu= etreble.com> wro= te:


There's been recent discussion of adding support for read-only tables. = If we had those, we might be able to support something like...

INSERT INTO unlogged;
ALTER TABLE unlogged SET READ ONLY;
CHECKPOINT;
/* take backup */

This should be safe as long as we WAL log changes to read-only status (whic= h presumably we would).

How much work that would entail though, I don't know.
<= /span>

Right.=C2=A0 I'= ;ve been keeping an eye on that discussion with the same intention.=C2=A0 T= he big question is how, during recovery, does it know what state the table = was in without being able to read from the system catalogs?=C2=A0 Perhaps i= t would be the checkpointer's duty at the end of the checkpoint to remo= ve the init fork for unlogged relations which were turned to read only befo= re that checkpoint started.
=C2=A0

Ultimately you still have to get the data over to the other machine anyway.= ISTM it'd be a LOT more useful to look at ways to make the WAL logging= of bulk inserts (and especially COPY into a known empty table) a lot more = efficient.


=E2=80=8BJeff Janes makes= a comment about wanting "...to avoid blowing out the log archive...&qu= ot;; which I also don't quite follow...
<= /blockquote>

I think the WAL logging of bulk COPY is pre= tty space-efficient already, provided it is not indexed at the time of the = COPY.=C2=A0 But no amount of efficiency improvement is going to make them s= mall enough for me want to keep the WAL logs around beyond the next base ba= ckup.

What I would really want is a way to make tw= o separate WAL streams; changes to this set of tables goes to the "kee= p forever, for PITR" stream, and changes to this other set of tables g= o to the "keep until pg_basebackup is next run" stream.=C2=A0 Of = course you couldn't have fk constraints between the two different sets = of tables.

Having to get the data over to the othe= r machine doesn't bother me, it is just a question of how to do it with= out permanently intermingling it with WAL logs which I want to keep forever= .

The FDW would be a good option, except the overh= ead (both execution overhead and the overhead of poor plans) seems to be to= o large.=C2=A0 I haven't explored it as much as I would like.

Cheers,

Jeff
--089e01183662afeac30513b2aefb-- From Jim.Nasby@BlueTreble.com Tue Apr 14 20:34:48 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yi7Xk-0000BU-9a for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 20:34:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yi7Xj-000459-D0 for pgsql-performance@arkaria.postgresql.org; Tue, 14 Apr 2015 20:34:47 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yi7Xi-000452-6e for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 20:34:46 +0000 Received: from mail-bn1on0661.outbound.protection.outlook.com ([2a01:111:f400:fc10::661] helo=na01-bn1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yi7Xd-0000jW-SV for pgsql-performance@postgresql.org; Tue, 14 Apr 2015 20:34:44 +0000 Authentication-Results: postgresql.org; dkim=none (message not signed) header.d=none; Received: from [192.168.2.12] (108.193.68.187) by BLUPR11MB0676.namprd11.prod.outlook.com (25.163.216.146) with Microsoft SMTP Server (TLS) id 15.1.136.25; Tue, 14 Apr 2015 20:34:35 +0000 Message-ID: <552D79D3.4040801@BlueTreble.com> Date: Tue, 14 Apr 2015 15:34:27 -0500 From: Jim Nasby User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:31.0) Gecko/20100101 Thunderbird/31.4.0 MIME-Version: 1.0 To: dgabriel , Subject: Re: unlogged tables References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com> <1428953502160-5845576.post@n5.nabble.com> <1429026970751-5845650.post@n5.nabble.com> In-Reply-To: <1429026970751-5845650.post@n5.nabble.com> Content-Type: text/plain; charset="windows-1252"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [108.193.68.187] X-ClientProxiedBy: BN3PR0301CA0021.namprd03.prod.outlook.com (25.160.180.159) To BLUPR11MB0676.namprd11.prod.outlook.com (25.163.216.146) X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BLUPR11MB0676; X-Forefront-Antispam-Report: BMV:1; SFV:NSPM; SFS:(10009020)(6049001)(6009001)(24454002)(51704005)(377454003)(479174004)(107886001)(92566002)(23746002)(19580395003)(93886004)(33656002)(4001410100001)(46102003)(83506001)(54356999)(87266999)(65816999)(15975445007)(50986999)(42186005)(76176999)(65956001)(122386002)(87976001)(77096005)(86362001)(36756003)(2950100001)(117156001)(62966003)(47776003)(4001350100001)(77156002)(50466002)(64126003)(66066001)(85282002)(336755003)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:BLUPR11MB0676; H:[192.168.2.12]; FPR:; SPF:None; MLV:sfv; LANG:en; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(5002010)(5005006); SRVR:BLUPR11MB0676; BCL:0; PCL:0; RULEID:; SRVR:BLUPR11MB0676; X-Forefront-PRVS: 054642504A X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 14 Apr 2015 20:34:35.3527 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BLUPR11MB0676 X-Pg-Spam-Score: -1.9 (-) 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 On 4/14/15 10:56 AM, dgabriel wrote: > David G Johnston wrote >> Well, that is half right anyway. UNLOGGED tables obey checkpoints just >> like any other table. The missing feature is an option to leaved restored >> the last checkpoint. Instead, not knowing whether there were changes >> since the last checkpoint, the system truncated the relation. >> >> What use case is there for a behavior that the last checkpoint data is >> left on the relation upon restarting - not knowing whether it was possible >> the other data could have been written subsequent? > > If is possible to restore the table at last checkpoint state that will be > more than enough. I don't care about the changes since last checkpoint, I am > willing to lose those changes. There are use cases where is acceptable to > lose some data, for example in a cache system, it is not a big issue if we > lose some cached data. It is not. Unless you ensure that data is written to WAL (on disk) BEFORE it is written to the data pages, you will probably have corruption after a crash, and have no way to prevent or possibly even detect the corruption. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From ldh@laurent-hasson.com Wed Aug 9 03:20:19 2017 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dfHXp-0003XJ-Rk for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 03:20:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dfHXp-0007gr-EU for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 03:20:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfHXo-0007gc-PP for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 03:20:29 +0000 Received: from mail-sn1nam01on0063.outbound.protection.outlook.com ([104.47.32.63] helo=NAM01-SN1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfHXj-0003lb-Om for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 03:20:28 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=NETORG2528281.onmicrosoft.com; s=selector1-laurenthasson-com01i; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=ylbJsEUW+FSr0FDS/U8Ajm+CLxVzVuTmJk8fo/Farbk=; b=L/X42c9ZpclDMeIr2cpJ9m71Nle4i+VxW35n0sEZlUkb9D9QuDqjV+xb5zdqCtOcdt3vTVvg5N5Z+jwbweiirj4jBiAEzuFV16YxxhLChBCvLeYRq0N834NWPpMnNkL6ZIW7+yXzrYft79J1si6UAwgn5BEHNJTOoEBmJqAdgUg= Received: from BN4PR15MB0547.namprd15.prod.outlook.com (10.164.61.157) by BN4PR15MB0545.namprd15.prod.outlook.com (10.164.61.155) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P256) id 15.1.1320.16; Wed, 9 Aug 2017 03:20:20 +0000 Received: from BN4PR15MB0547.namprd15.prod.outlook.com ([10.164.61.157]) by BN4PR15MB0547.namprd15.prod.outlook.com ([10.164.61.157]) with mapi id 15.01.1320.018; Wed, 9 Aug 2017 03:20:19 +0000 From: "ldh@laurent-hasson.com" To: "pgsql-performance@postgresql.org" Subject: Unlogged tables Thread-Topic: Unlogged tables Thread-Index: AQHTELxZLq7x/1InREuLnDINR7olQg== Date: Wed, 9 Aug 2017 03:20:19 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: spf=none (sender IP is ) smtp.mailfrom=ldh@laurent-hasson.com; x-originating-ip: [12.204.85.2] x-ms-publictraffictype: Email x-microsoft-exchange-diagnostics: 1;BN4PR15MB0545;6:ELRWXB11H2u3TQgY5noZzGB3G3gqdTJg3Qw/QjolwHj92U4roUjC+KJUH07USS1USU8W70y51j11vPu4Z31F3N7HeDafRTFXTLKQCjMD+Yu8Epnbhz4kg0Qa5XR6g4BkacI3xVsmO/Je+sYzvjjF3MNJaa9CzVZ6TwbyaSWNANcwVlkRcfxeNBYmp2VVEflpjaHneiCDORmRbECBWtTJjDyY+VIsGgqD8AGFPPw+NrNsOp7kQ3OeK/YrehsyZZ0QWNG4OdbuzHj71YqzyYpedUZrauB90jU+Z8My8Czy8OAtUlOKMpvQ8ws4QGKnVHRt8wGAyzxKaLil6dk2ykOpfw==;5:mIbsRnTGM3LasBrlEmI6NQpR2BGt8RP4mjvY+k+6mIBFl8VH2RrSjpjDqL7DA9UfbqErUh3aPkqr8SxHwPQ3d4HFK/J00vlaj/dmhFbXnbWcHDI7x4OTY/mpCo8F3CkoVilIn9xu0ioDT0i1duMxrw==;24:8aFAoOo5MiNO9FVfTiB7MolLz1ShsvWfZp9runqZ88QjvlJTlaEF0729eVnljUqikPeMkYizdFTLF+Ik5nHG/JsMcH9IBCpZlNavarMGEG4=;7:V8LJEtHmwba/OMWVqzedTsOlsaufhNI1SsH101UzIfXr9bMxAzJ9QbTIXiy1QkwhWrR0Y/GKoFraWb29Xb1eQTlDrfeEr1mXNgZC1F14b6zown6HWjAS2TAETLm8riuyWDU8GMklj5dOIKzbhiv9EIsq3Q02VRu9u07v6dwzgBefgx1vi4Gl4HWgkfRE+oKhVBkPMOg4Lmksba1YD6KXxYBsN9edcUAZOlCHz84WO5s= x-ms-exchange-antispam-srfa-diagnostics: SSOS; x-ms-office365-filtering-correlation-id: c41b5007-a1d9-47b1-e8c5-08d4ded59070 x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:(300000500095)(300135000095)(300000501095)(300135300095)(300000502095)(300135100095)(22001)(2017030254152)(2017082002075)(300000503095)(300135400095)(2017052603031)(201703131423075)(201702281549075)(300000504095)(300135200095)(300000505095)(300135600095)(300000506095)(300135500095);SRVR:BN4PR15MB0545; x-ms-traffictypediagnostic: BN4PR15MB0545: x-exchange-antispam-report-test: UriScan:(158342451672863); x-microsoft-antispam-prvs: x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(100000700101)(100105000095)(100000701101)(100105300095)(100000702101)(100105100095)(6040450)(601004)(2401047)(8121501046)(5005006)(3002001)(10201501046)(93006095)(93001095)(100000703101)(100105400095)(6041248)(20161123560025)(201703131423075)(201703061421075)(20161123564025)(2016111802025)(20161123558100)(20161123555025)(20161123562025)(6043046)(6072148)(201708071742011)(100000704101)(100105200095)(100000705101)(100105500095);SRVR:BN4PR15MB0545;BCL:0;PCL:0;RULEID:(100000800101)(100110000095)(100000801101)(100110300095)(100000802101)(100110100095)(100000803101)(100110400095)(100000804101)(100110200095)(100000805101)(100110500095);SRVR:BN4PR15MB0545; x-forefront-prvs: 0394259C80 x-forefront-antispam-report: SFV:NSPM;SFS:(10009020)(6009001)(39400400002)(39450400003)(199003)(189002)(86582002)(221733001)(33656002)(508600001)(101416001)(54356999)(50986999)(102836003)(3846002)(6116002)(2900100001)(97736004)(14454004)(25786009)(38730400002)(66066001)(189998001)(2906002)(55016002)(6436002)(6506006)(7736002)(74316002)(99286003)(8676002)(81166006)(2501003)(86362001)(7696004)(3480700004)(8936002)(6606003)(5640700003)(6916009)(81156014)(110136004)(3280700002)(7116003)(3660700001)(68736007)(106356001)(77096006)(9686003)(2351001)(19627405001)(105586002)(5660300001)(54896002)(53936002);DIR:OUT;SFP:1101;SCL:1;SRVR:BN4PR15MB0545;H:BN4PR15MB0547.namprd15.prod.outlook.com;FPR:;SPF:None;PTR:InfoNoRecords;A:1;MX:1;LANG:en; received-spf: None (protection.outlook.com: laurent-hasson.com does not designate permitted sender hosts) spamdiagnosticoutput: 1:99 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_BN4PR15MB0547B6736C684F14E4E33BDF858B0BN4PR15MB0547namp_" MIME-Version: 1.0 X-OriginatorOrg: laurent-hasson.com X-MS-Exchange-CrossTenant-originalarrivaltime: 09 Aug 2017 03:20:19.5520 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 7f9c0a0d-79fd-4329-b129-754c4501a177 X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN4PR15MB0545 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 --_000_BN4PR15MB0547B6736C684F14E4E33BDF858B0BN4PR15MB0547namp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, We have a fairly large static dataset that we load into Postgres. We made t= he tables UNLOGGED and saw a pretty significant performance improvement for= the loading. This was all fantastic until the server crashed and we were s= urprised 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 UNLOG= GED proprely. However, our scenario is truly a set of tables with 100's of millions of ro= ws 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-lo= ad (i think) during a server crash (short of physical disk defects...). 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 patch= ing for example. So unlogged tables in practice are pretty flimsy. I tried = to ALTER ... SET LOGGED, but that takes a VERY long time and pretty much ne= gates the initial performance boost of loading into an unlogged table. Is there a way to get my cake and eat it too? Thank you, Laurent Hasson --_000_BN4PR15MB0547B6736C684F14E4E33BDF858B0BN4PR15MB0547namp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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 millions of= rows that are effectively WORMs: we write them once only, and then on= ly 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...).


I'd like to have the performance improvement during a initial batch inse= rt, and then make sure the table remains after "unclean" shutdown= s, which, as you might have it, includes a regular Windows server shut down= during patching for example. 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.


Is there a way to get my cake and eat it too?


Thank you,

Laurent Hasson




--_000_BN4PR15MB0547B6736C684F14E4E33BDF858B0BN4PR15MB0547namp_-- From michael.paquier@gmail.com Wed Aug 9 10:39:12 2017 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dfOOU-0003ee-9W for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 10:39:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dfOOT-00050G-PV for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 10:39:17 +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 1dfOOS-0004zL-KO for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 10:39:16 +0000 Received: from mail-yw0-x22a.google.com ([2607:f8b0:4002:c05::22a]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dfOOP-0001GC-Qy for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 10:39:15 +0000 Received: by mail-yw0-x22a.google.com with SMTP id p68so37399831ywg.0 for ; Wed, 09 Aug 2017 03:39:13 -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=SlYKRLLBFhlyZcGeoosyxCylroNt8rJhvEs+BdCLHgM=; b=Zyz/06TESWeOIY8ee9/K5bLr7cw7jm+ZJqIgbZ5827DBWtdaiBqrj9qL92RzUF2jot 5ZVOE0k7/0cRujLVHnIZuDylUmZDFXuQORK1mI69QKXlU4nTv7U25NnsN8405iHICSHF K4um045YcxN9m+BcijOHi962zIE9xHIr9Z8RZbRXCdU14aQC3XLBZGCv5/5TxF78Ws0G XSYc1YE0FcE4XZ7rKEyaNg8xJOdTN+lpGyM34bQIFyQETX7LTBLg773EDlFV9HHukfGF r46F0nPgIvBoTP1mhgifPP0loX6aqnolCfmBbx5PT83HkpHXBHtPqBok5rZlZxbtxiyv tbOA== 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=SlYKRLLBFhlyZcGeoosyxCylroNt8rJhvEs+BdCLHgM=; b=M13yZf+tRFRUqjIVOoVzHjhCXruImRPLlRSwrbbRUI8q1oN3+HEHRf05I7++Tb9dkc SW5nqYb1YICPEAYpXswz3B5GE1D9bDYK1Ua2oQsElm0bhxef3W6LmnK4Re8AcYGXIh6m BvDi1aYODOeXHwewRqp3HLA7l6hgiIeiEwvy7qdOc0N6wl8DYYcBcMg4WmRFL6VtM24b dEqnvUu6J5l7bEJhybV1AF6df2T6Iyo1Ah/kHF6rU7TDyApavqFZVt+BiK5x84Fga0iR t/MqZ0e/TSQYRSMwQnXwSybOpwgVzTcO45poPDxnnTRV753puKPhjRBNtMIyY3CfZj2Z v+KQ== X-Gm-Message-State: AHYfb5iVYuMNb+2lCc/d5QCKrEj/oQxKMDR0O5cSfrk7Bk+0EWa0RdNp 53J+dTu7gS/HGDb2RGd1Mewkzn6sGQ== X-Received: by 10.129.177.195 with SMTP id p186mr5927472ywh.45.1502275152767; Wed, 09 Aug 2017 03:39:12 -0700 (PDT) MIME-Version: 1.0 Received: by 10.37.105.193 with HTTP; Wed, 9 Aug 2017 03:39:12 -0700 (PDT) In-Reply-To: References: From: Michael Paquier Date: Wed, 9 Aug 2017 12:39:12 +0200 Message-ID: Subject: Re: Unlogged tables To: "ldh@laurent-hasson.com" Cc: "pgsql-performance@postgresql.org" Content-Type: text/plain; charset="UTF-8" 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 On Wed, Aug 9, 2017 at 5:20 AM, ldh@laurent-hasson.com wrote: > 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. This is documented. > 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...). > > 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. So unlogged tables in practice are pretty flimsy. All the data that you want to keep needs to be durable anyway, so you will need to WAL-log it, and full page writes of those relation pages will need to be created at least once. After you get past the checkpoint the data will still be around. If you want to improve the performance once, there are a couple of tricks, like switching wal_level to minimal, preferring COPY over multi-value INSERT, batch a lot of them in the same transaction. Of course you can as well increase wal_max_size to trigger less checkpoints, or use synchronous_commit = off to reduce fsync costs. > 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. This triggers a table rewrite and makes sure that all the data gets WAL-logged. The cost to pay for durability. > Is there a way to get my cake and eat it too? Not completely. Making data durable will have a cost at the end, but you can leverage it. -- Michael -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From david.g.johnston@gmail.com Wed Aug 9 14:37:29 2017 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dfS8r-00021w-FP for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 14:39:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dfS8q-0006vt-Qd for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 14:39:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfS75-0003qS-KZ for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 14:37:35 +0000 Received: from mail-qt0-x22b.google.com ([2607:f8b0:400d:c0d::22b]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dfS72-0001mP-2Q for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 14:37:35 +0000 Received: by mail-qt0-x22b.google.com with SMTP id t37so37328507qtg.5 for ; Wed, 09 Aug 2017 07:37:31 -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=CRVhw4KXa2L5ct8ToWiVrn9VwCerCrICUu/uvFgagck=; b=WV00N4/YvPM90ED5ZCagEFTkNhhBYysXc8VwElLCI4bn6fvSU+j+9++nhmlaiaBdrV U8NUNyyNV9fCFb5EzSsUdULI1BTrggJuh4gfjs4aVkUoQVPX41Ol+9f4YFW1EChLvKxO IfzJVrQtZzniC7V3x6w5l7mf1wc4s6Vxie7u9BjyMxNNG9TN544sQf6sVPLha9saJlG1 wEJWPhfoVbnBOHv9U0mwMWm57w+ODsCiHbdi5x9QC4tTUiMDWd2iSWBzrnkMDefvTUlp DeFptKbuLOr1xEDeZq7NuycLfhTF8+5ZGClvfx5+xSHHLOqzEiFXFPr1HQAjLXDxRWUw hieg== 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=CRVhw4KXa2L5ct8ToWiVrn9VwCerCrICUu/uvFgagck=; b=MtE7AeSjmqIXWNMnVQoIoV/6W9WRDyjWAafLppi5+7Qcp1gYfm7HDXHXtl8auUYzNp a+tQtCF68KFsGV0JmK47nPWsO8CIBLnMgkNUzRLHoKeHOFfADYS1yjHMmAQKNKtROES+ mlocuYFhoknRe6kWSf0gFs0Ag51MXO05JOEnV7k6uIpQX8O8vAJdSs1KSDH2mqGVcguV 6geG03UQjnAPmIykEnj2mCloM/FivKFJqgakJW6t+HZvmDIE1zgZymawp5TZSpi/CX78 Nmf0Lm/guUvloRN7e2glsqOG0nOYQhdPTUHu1hItHj21QTCHrx/aZSY6aMvmk9wrGoMz EU2g== X-Gm-Message-State: AHYfb5gRmQ1Uxn1H5A6TQz/YpUbp2dVmHQXG6wV6lEvfyFcoAyPiLHW3 VpyolxO2LcI6mCk9c7vxlXVVTHXTS0V0 X-Received: by 10.200.24.105 with SMTP id n38mr2551161qtk.291.1502289450723; Wed, 09 Aug 2017 07:37:30 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.153.23 with HTTP; Wed, 9 Aug 2017 07:37:29 -0700 (PDT) In-Reply-To: References: From: "David G. Johnston" Date: Wed, 9 Aug 2017 07:37:29 -0700 Message-ID: Subject: Re: Unlogged tables To: Michael Paquier Cc: "ldh@laurent-hasson.com" , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a114ff3fe0f6db10556530590" 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 --001a114ff3fe0f6db10556530590 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Aug 9, 2017 at 3:39 AM, Michael Paquier wrote: > This triggers a table rewrite and makes sure that all the data gets > WAL-logged. The cost to pay for durability. > > > Is there a way to get my cake and eat it too? > > Not completely. Making data durable will have a cost at the end, but > you can leverage it. > > =E2=80=8BAren't you over-playing the role of the WAL in providing durabilit= y. An unlogged table remains intact after a clean shutdown and so is "durable" if one considers the primary "permanence" aspect of the word. The trade-off the OP wishes for is "lose crash-safety to gain write-once (to the data files) performance". Seeming having this on a per-table basis would be part of the desirability. It sounds like OP would be willing to place the table into "read only" mode in order to ensure this - which is something that is not presently possible. I could envision that putting an unlogged table into read-only mode would cause the system to ensure that the data files are fully populated and then set a flag in the catalog that informs the crash recovery process to go ahead and omit truncating that particular unlogged table since the data files are known to be accurate. David J. --001a114ff3fe0f6db10556530590 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Au= g 9, 2017 at 3:39 AM, Michael Paquier <michael.paquier@gmail.com> wrote:
This trigger= s a table rewrite and makes sure that all the data gets
WAL-logged. The cost to pay for durability.

> Is there a way to get my cake and eat it too?

Not completely. Making data durable will have a cost at the end, but=
you can leverage it.

=

=E2=80=8BAren't you over-p= laying the role of the WAL in providing durability.=C2=A0 An unlogged table= remains intact after a clean shutdown and so is "durable" if one= considers the primary "permanence" aspect of the word.
<= br>
The trade-off the OP wishes for is "lose crash-safety to g= ain write-once (to the data files) performance".=C2=A0 Seeming having = this on a per-table basis would be part of the desirability.=C2=A0 It sound= s like OP would be willing to place the table into "read only" mo= de in order to ensure this - which is something that is not presently possi= ble.=C2=A0 I could envision that putting an unlogged table into read-only m= ode would cause the system to ensure that the data files are fully populate= d and then set a flag in the catalog that informs the crash recovery proces= s to go ahead and omit truncating that particular unlogged table since the = data files are known to be accurate.

David J.
--001a114ff3fe0f6db10556530590-- From sfrost@snowman.net Wed Aug 9 15:12:26 2017 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dfSf7-0004RI-NV for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 15:12:45 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dfSf6-0001a4-Kb for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 15:12:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfSf6-0001Zs-5m for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 15:12:44 +0000 Received: from static-96-255-250-162.washdc.fios.verizon.net ([96.255.250.162] helo=tamriel.snowman.net) by magus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dfSep-0002bc-Ax for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 15:12:43 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 507665F795; Wed, 9 Aug 2017 11:12:26 -0400 (EDT) Date: Wed, 9 Aug 2017 11:12:26 -0400 From: Stephen Frost To: "David G. Johnston" Cc: Michael Paquier , "ldh@laurent-hasson.com" , "pgsql-performance@postgresql.org" Subject: Re: Unlogged tables Message-ID: <20170809151226.GK4628@tamriel.snowman.net> References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="00zH/wdDQmWzr4bS" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.21 (2010-09-15) 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 --00zH/wdDQmWzr4bS Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable David, all, * David G. Johnston (david.g.johnston@gmail.com) wrote: > On Wed, Aug 9, 2017 at 3:39 AM, Michael Paquier > wrote: >=20 > > This triggers a table rewrite and makes sure that all the data gets > > WAL-logged. The cost to pay for durability. That's not entirely accurate- there are certain cases where we don't have to WAL-log the data, in fact we've got a specific optimization to avoid WAL logging when it isn't necessary (see src/backend/commands/copy.c:2392 or so), and the data will still be durable once the transaction commits. There are limitations there though, of course, but it sounds like those are ones the OP may be happy to live with in this case. > > > Is there a way to get my cake and eat it too? > > > > Not completely. Making data durable will have a cost at the end, but > > you can leverage it. > > Aren't you over-playing the role of the WAL in providing durability. An > unlogged table remains intact after a clean shutdown and so is "durable" = if > one considers the primary "permanence" aspect of the word. In database terms, however, durable is intended to be in the face of a crash and not just a clean shutdown, otherwise we wouldn't need to bother with this whole WAL thing at all. > The trade-off the OP wishes for is "lose crash-safety to gain write-once > (to the data files) performance". Seeming having this on a per-table bas= is > would be part of the desirability. It sounds like OP would be willing to > place the table into "read only" mode in order to ensure this - which is > something that is not presently possible. I could envision that putting = an > unlogged table into read-only mode would cause the system to ensure that > the data files are fully populated and then set a flag in the catalog that > informs the crash recovery process to go ahead and omit truncating that > particular unlogged table since the data files are known to be accurate. This does sound like a pretty interesting idea, though not really necessary unless OP has a mix of data that needs to be WAL-log'd and data that doesn't. What I believe OP is really looking for here, specifically, is using wal_level =3D minimal while creating the table (or truncating it) within the same transaction as the data load is done. That will avoid having the table's contents written into the WAL, and PG will treat it as a regular table post-commit, meaning that it won't be truncated on a database crash. Thanks! Stephen --00zH/wdDQmWzr4bS Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJZiyZaAAoJEO1sijiDR2RV600P/1wXbeHIThyJ2RuzGwARCqX0 ADJYTDYrLE2zULWdNvuLLLWt4Wlv6qsYGQEyNy774kDg1bCdUlkDWkF65Pz/Rm2l Nw380GI87el4eZtiem36vhLbAVi6DnbrIpjxxwyOXe+A4TRWWV9YdXAuWmwTcdTr Z44bkEHBeJtz80vBcH3ezH3CmRjXViS+inh9w2jWjWX5/ERaSuO13vydQ9RZN3ps SJk8Dfd3+kdz4dydSsWqAXp1sLpBXO+LClYQiPbCo6/XxDFoOA6Bbdt2vUhJ/MYf aaPibAXU7JoPAD6+TYuFed7BjmIPjfPoQTAnRyP5LOAqVJHiITW6bMz/EIb03HGZ TvlSIy50JQxbMC0O+qIrlef7Pxb3ExTtPjtuJi9c3JJQVtd6YUttK1yjf9KDqvk0 rA0XGZGZvR5XsFvKOTWMHnnXBZPKw7LD3ZCOUqBF537en6C5IDittPH8oaL1lKtP /rDqAnOnm61axG4Xktp0Ms0MGpfVBiBdwqHR6lukcB/YYwY7S+stQ9xOfGy7Xb6k GZjryPnjxsaI2wFoNVODqT6qoyCyR0IMMnjWqDElAI7Mt1z91UCUnnHZEqH7BK7C En3smpY7ZxgJoRga7NUdmYZAMw0wjcRJgIkr5wMFK6EcclW36t4EdTrmRu2Q4HmF qQY/be88/aJb6p82v13r =WGs5 -----END PGP SIGNATURE----- --00zH/wdDQmWzr4bS-- From jeff.janes@gmail.com Wed Aug 9 16:14:48 2017 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-- From gneuner2@comcast.net Wed Aug 9 18:15:37 2017 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dfVWF-0006as-0Y for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 18:15:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dfVWE-0007HM-Eh for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 18:15:46 +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 1dfVWD-0007H0-Jt for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 18:15:45 +0000 Received: from resqmta-ch2-03v.sys.comcast.net ([2001:558:fe21:29:69:252:207:35]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfVWA-0003Km-Bu for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 18:15:44 +0000 Received: from resomta-ch2-16v.sys.comcast.net ([69.252.207.112]) by resqmta-ch2-03v.sys.comcast.net with ESMTP id fVV0dHHdadtjefVW8d13Md; Wed, 09 Aug 2017 18:15:40 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=q20161114; t=1502302540; bh=AP320I6OI26khmHcefek7FJxIjC20XoQnhLWnTF8k9Q=; h=Received:Received:To:From:Subject:Message-ID:Date:MIME-Version: Content-Type; b=LFAnetWWERRzGFsMdYBLN4vRk9xEHH2Q36Ae+3KI7D46t52BhDnnQP/HUYEVuMjgf lHR6CFgkHwj2eij8xMIBJFcS/RcxKLEuLKGT4+kdHYqXNj1expDfNxcAZGhzz1oJOA Gu/YJQ3zgFmMgfc4p3qDdrWrC+L031NqMpZWQNrgG0QrzLWrCB04tFfxjr+8F52u/r lO9tX0bl6tNc4rqVKmYvm5OEWTYX3o/dM1Q2puJwB0yeMSguDHNLxj6UxqCs4wVVpr nh7lc/XCPRUH7uUuq9VxmfdebtWm/7BtYcx4GM0eOYm2uGAAxZY39dJMdQrjK0Mb/g 35VHquauiJKig== Received: from [192.168.0.67] ([73.68.146.26]) by resomta-ch2-16v.sys.comcast.net with SMTP id fVW7dKXR3eaimfVW7djaAW; Wed, 09 Aug 2017 18:15:40 +0000 To: pgsql-performance@postgresql.org From: George Neuner Subject: Re: Unlogged tables Message-ID: <314ce53a-ee57-0be3-7815-f0e99fc0a6e4@comcast.net> Date: Wed, 9 Aug 2017 14:15:37 -0400 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.2.1 MIME-Version: 1.0 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit Content-Language: en-US X-CMAE-Envelope: MS4wfLjMqGmVqdXDbNhccl3LsbxBwcQG8+GZR7uX+zVuJwJcKOX40GVkGpAajnCMxFgPZkuv3F7djVfRK/bFWkHcgDaGx3panXEKeLbRpJ4ffX8dpwg4lndq rKZyoBR2TgoKFB/aGZvMMz/ZXIrQi6+CyZbdFxpyrlc7h3xxxsxLHgrR+w5TuKRBG80ADbZoGJSwHw== 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 On Wed, 9 Aug 2017 09:14:48 -0700, Jeff Janes wrote: >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. Windows *does* signal shutdown (and sleep and hibernate and wakeup). pg_ctl can catch these signals only when running as a service ... it will not catch any system signals when run as an application. George -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From ldh@laurent-hasson.com Wed Aug 9 18:30:10 2017 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dfVmB-0007Ok-QS for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 18:32:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dfVmB-0007ZD-D0 for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 18:32:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfVkN-0002Se-OR for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 18:30:23 +0000 Received: from mail-by2nam03on0050.outbound.protection.outlook.com ([104.47.42.50] helo=NAM03-BY2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfVkE-0007Fd-TB for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 18:30:23 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=NETORG2528281.onmicrosoft.com; s=selector1-laurenthasson-com01i; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=fOL8HKQcavYgQEZPQK+5+AUW4nsH3TrXzxRZwQeeUqc=; b=oarbT/4vDqMGfiPSESa/BdXPAM/jjtGbzIkvvK510aEIK4JdYba35dst1DGVCiDUG5nAyrVoPBIOrgKlPC9XV44MMnhP42XqcyMQj5crlKer6z/Cxu3Hk+C3FpstzSuUo779bkV8WVqJ4/k1m4tRe0tH3E5bCrxR6fmUb7iIBIM= Received: from BN4PR15MB0547.namprd15.prod.outlook.com (10.164.61.157) by BN4PR15MB0548.namprd15.prod.outlook.com (10.164.61.158) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P256) id 15.1.1320.16; Wed, 9 Aug 2017 18:30:10 +0000 Received: from BN4PR15MB0547.namprd15.prod.outlook.com ([10.164.61.157]) by BN4PR15MB0547.namprd15.prod.outlook.com ([10.164.61.157]) with mapi id 15.01.1320.018; Wed, 9 Aug 2017 18:30:10 +0000 From: "ldh@laurent-hasson.com" To: George Neuner , postgres performance list Subject: Re: Unlogged tables Thread-Topic: [PERFORM] Unlogged tables Thread-Index: AQHTETt/Lq7x/1InREuLnDINR7olQqJ8WJmS Date: Wed, 9 Aug 2017 18:30:10 +0000 Message-ID: References: <314ce53a-ee57-0be3-7815-f0e99fc0a6e4@comcast.net> In-Reply-To: <314ce53a-ee57-0be3-7815-f0e99fc0a6e4@comcast.net> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: spf=none (sender IP is ) smtp.mailfrom=ldh@laurent-hasson.com; x-originating-ip: [107.77.234.34] x-ms-publictraffictype: Email x-microsoft-exchange-diagnostics: 1;BN4PR15MB0548;6:BipjzAAFv+mqrOZP7XT+avgiuRpOOiaHq77e/oqLtQkKIJcHwqQsPmaJz3Rw2+MN+EUe1py7PaRLoVe2lxzHTCqsiu68lHVzwxE7IA+WDgZW/D2TEWEi8YIZUGP/NJMlklAL4q4nHAyfMMXy/ZcM9bNpTdM4mo0J3gpXy4laaT1F/NU2N3wp0UN3Ri7Hox8IKBbe1Iyf+AtiwD1Uf0BjrW+lJSNjqqiDWb+KfLW2vAN5H60BKk4sbzl59r5852XPEsXax88kMSeTL1G1HLhVu6SDME6b3rPLUoxpNyUHD19pUSgYm0XbaLcYE7hLQ2EcKRjPCMhzcL3D4/yCarHo2w==;5:vOD7Tal9NGOYdSkVSUxXQcwDr/ruMgzeytEBKJYSsbh7Ha+ud+DMOPjT8OA9fg2asPw493VjBhcWzJMptRCJMidaeaDcM0mQBx0zxjLO6wo3EZ6JuW57kYVd5zbWyGs5SAxpbLAaF6ltY8CwexJfIg==;24:k/RPmnnMvpVnSvMGqBrEhCCVAKS1b01ihh93v5lYZWw2aIf3nytrE5cIKxKdlzWRZyjadWz9kP4mfJdRerFESTGKV2kp32Ujcr/172rGpHw=;7:ib5spXQ8LZDaTrc9yATlyIu2cm2NxxCf34U1e5qrWCu35KUhOPlCLZQULH5Vt0TCRI2N3XFVtYkaDGx6eiYZGgEPg8qbrnJGTwsxmIua0lZz+DBHV62BW1iNLJd1eIONGbF26ll0iaHP1WCSwqMeypJ7ePLQ/pvlKpYVD481vQe3LAxlt1GBVQ9G2QYnO5T3o2Qy+p7YcdSXyDl8FYSlWPdczHj50//eiopjU1m3+PY= x-ms-exchange-antispam-srfa-diagnostics: SSOS; x-ms-office365-filtering-correlation-id: b20e0f1d-126f-4d33-066d-08d4df54ab45 x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:(300000500095)(300135000095)(300000501095)(300135300095)(22001)(300000502095)(300135100095)(2017030254152)(2017082002075)(300000503095)(300135400095)(2017052603031)(201703131423075)(201702281549075)(300000504095)(300135200095)(300000505095)(300135600095)(300000506095)(300135500095);SRVR:BN4PR15MB0548; x-ms-traffictypediagnostic: BN4PR15MB0548: x-exchange-antispam-report-test: UriScan:(68173958961439)(101568727142576); x-microsoft-antispam-prvs: x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(100000700101)(100105000095)(100000701101)(100105300095)(100000702101)(100105100095)(6040450)(601004)(2401047)(5005006)(8121501046)(93006095)(93001095)(100000703101)(100105400095)(10201501046)(3002001)(6041248)(20161123560025)(20161123564025)(201703131423075)(201703061421075)(2016111802025)(20161123555025)(20161123558100)(20161123562025)(6072148)(6043046)(201708071742011)(100000704101)(100105200095)(100000705101)(100105500095);SRVR:BN4PR15MB0548;BCL:0;PCL:0;RULEID:(100000800101)(100110000095)(100000801101)(100110300095)(100000802101)(100110100095)(100000803101)(100110400095)(100000804101)(100110200095)(100000805101)(100110500095);SRVR:BN4PR15MB0548; x-forefront-prvs: 0394259C80 x-forefront-antispam-report: SFV:NSPM;SFS:(10009020)(39400400002)(39450400003)(199003)(189002)(24454002)(50986999)(54356999)(8676002)(105586002)(6306002)(8666007)(102836003)(6116002)(54896002)(6436002)(606006)(236005)(3846002)(53546010)(9686003)(106356001)(55016002)(6246003)(5660300001)(66066001)(508600001)(38730400002)(53936002)(99286003)(33656002)(2900100001)(101416001)(77096006)(81156014)(6506006)(68736007)(7736002)(8936002)(3660700001)(2950100002)(76176999)(229853002)(2906002)(97736004)(81166006)(14454004)(86362001)(25786009)(74316002)(966005)(86582002)(7696004)(189998001)(3280700002)(19623405001);DIR:OUT;SFP:1101;SCL:1;SRVR:BN4PR15MB0548;H:BN4PR15MB0547.namprd15.prod.outlook.com;FPR:;SPF:None;PTR:InfoNoRecords;A:1;MX:1;LANG:en; received-spf: None (protection.outlook.com: laurent-hasson.com does not designate permitted sender hosts) spamdiagnosticoutput: 1:99 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_BN4PR15MB0547F5864AD9691E31458FE1858B0BN4PR15MB0547namp_" MIME-Version: 1.0 X-OriginatorOrg: laurent-hasson.com X-MS-Exchange-CrossTenant-originalarrivaltime: 09 Aug 2017 18:30:10.5225 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 7f9c0a0d-79fd-4329-b129-754c4501a177 X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN4PR15MB0548 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 --_000_BN4PR15MB0547F5864AD9691E31458FE1858B0BN4PR15MB0547namp_ Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 T2ssIEkgYW0gbm90IHN1cmUuIEkgcnVuIFBvc3RncmVzIGFzIGEgc2VydmljZSwgYW5kIHdoZW4g bXkgV2luZG93cyByZWJvb3RlZCBhZnRlciBhIHBhdGNoLCBVTkxPR0dFRCB0YWJsZXMgd2VyZSBj bGVhbmVkLi4uIG1heWJlIHRoZSBwYXRjaCBwcm9jZXNzIGluIFdpbmRvd3MgbWVzc2VkIHNvbWV0 aGluZyB1cCwgSSBkb24ndCBrbm93Lg0KDQpGcm9tOiBnbmV1bmVyMkBjb21jYXN0Lm5ldA0KU2Vu dDogQXVndXN0IDksIDIwMTcgMTM6MTcNClRvOiBwZ3NxbC1wZXJmb3JtYW5jZUBwb3N0Z3Jlc3Fs Lm9yZw0KU3ViamVjdDogUmU6IFtQRVJGT1JNXSBVbmxvZ2dlZCB0YWJsZXMNCg0KDQpPbiBXZWQs IDkgQXVnIDIwMTcgMDk6MTQ6NDggLTA3MDAsIEplZmYgSmFuZXMgPGplZmYuamFuZXNAZ21haWwu Y29tPiB3cm90ZToNCg0KID5XaHkgZG9lc24ndCB0aGUgV2luZG93cyBzY2hlZHVsZWQgc2h1dGRv d24gc2lnbmFsIHBvc3RncmVzIHRvIHNodXRkb3duDQogPmNsZWFubHkgYW5kIHdhaXQgZm9yIGl0 IHRvIGRvIHNvPyAgVGhhdCBpcyB3aGF0IGlzIHN1cHBvc2VkIHRvIGhhcHBlbi4NCg0KV2luZG93 cyAqZG9lcyogc2lnbmFsIHNodXRkb3duIChhbmQgc2xlZXAgYW5kIGhpYmVybmF0ZSBhbmQgd2Fr ZXVwKS4NCnBnX2N0bCBjYW4gY2F0Y2ggdGhlc2Ugc2lnbmFscyBvbmx5IHdoZW4gcnVubmluZyBh cyBhIHNlcnZpY2UgLi4uIGl0DQp3aWxsIG5vdCBjYXRjaCBhbnkgc3lzdGVtIHNpZ25hbHMgd2hl biBydW4gYXMgYW4gYXBwbGljYXRpb24uDQoNCkdlb3JnZQ0KDQoNCi0tDQpTZW50IHZpYSBwZ3Nx bC1wZXJmb3JtYW5jZSBtYWlsaW5nIGxpc3QgKHBnc3FsLXBlcmZvcm1hbmNlQHBvc3RncmVzcWwu b3JnKQ0KVG8gbWFrZSBjaGFuZ2VzIHRvIHlvdXIgc3Vic2NyaXB0aW9uOg0KaHR0cDovL3d3dy5w b3N0Z3Jlc3FsLm9yZy9tYWlscHJlZi9wZ3NxbC1wZXJmb3JtYW5jZQ0K --_000_BN4PR15MB0547F5864AD9691E31458FE1858B0BN4PR15MB0547namp_ Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: base64 PGh0bWw+DQo8aGVhZD4NCjxtZXRhIGh0dHAtZXF1aXY9IkNvbnRlbnQtVHlwZSIgY29udGVudD0i dGV4dC9odG1sOyBjaGFyc2V0PXV0Zi04Ij4NCjxtZXRhIGh0dHAtZXF1aXY9IkNvbnRlbnQtU2Vj dXJpdHktUG9saWN5IiBjb250ZW50PSJzY3JpcHQtc3JjICdzZWxmJzsgaW1nLXNyYyAqIGNpZDog ZGF0YTo7Ij4NCjwvaGVhZD4NCjxib2R5IHN0eWxlPSJiYWNrZ3JvdW5kLWNvbG9yOiByZ2IoMjU1 LCAyNTUsIDI1NSk7IGJhY2tncm91bmQtaW1hZ2U6IGluaXRpYWw7IGxpbmUtaGVpZ2h0OiBpbml0 aWFsOyI+DQo8ZGl2IGlkPSJyZXNwb25zZV9jb250YWluZXJfQkJQUElEIiBzdHlsZT0ib3V0bGlu ZTpub25lO2ZvbnQtc2l6ZTppbml0aWFsO2ZvbnQtZmFtaWx5OiZxdW90O0NhbGlicmkmcXVvdDss JnF1b3Q7U2xhdGUgUHJvJnF1b3Q7LHNhbnMtc2VyaWYsJnF1b3Q7c2Fucy1zZXJpZiZxdW90OyIg ZGlyPSJhdXRvIiBjb250ZW50ZWRpdGFibGU9ImZhbHNlIj4NCjxkaXYgbmFtZT0iQkIxMCIgZGly PSJhdXRvIiBzdHlsZT0id2lkdGg6IDEwMCU7IHBhZGRpbmc6IGluaXRpYWw7IGZvbnQtc2l6ZTog aW5pdGlhbDsgdGV4dC1hbGlnbjogaW5pdGlhbDsgYmFja2dyb3VuZC1jb2xvcjogcmdiKDI1NSwg MjU1LCAyNTUpOyI+DQo8ZGl2IG5hbWU9IkJCMTAiIGlkPSJCQjEwX3Jlc3BvbnNlX2Rpdl9CQlBQ SUQiIGRpcj0iYXV0byIgc3R5bGU9ImZvbnQtZmFtaWx5OiBDYWxpYnJpLCAmcXVvdDtTbGF0ZSBQ cm8mcXVvdDssIHNhbnMtc2VyaWYsIHNhbnMtc2VyaWY7IGZvbnQtc2l6ZTogaW5pdGlhbDsgdGV4 dC1hbGlnbjogaW5pdGlhbDsgd2lkdGg6IDM4MHB4OyBwYWRkaW5nOiBpbml0aWFsOyI+DQpPaywg SSBhbSBub3Qgc3VyZS4gSSBydW4gUG9zdGdyZXMgYXMgYSBzZXJ2aWNlLCBhbmQgd2hlbiBteSBX aW5kb3dzIHJlYm9vdGVkIGFmdGVyIGEgcGF0Y2gsIFVOTE9HR0VEIHRhYmxlcyB3ZXJlIGNsZWFu ZWQuLi4gbWF5YmUgdGhlIHBhdGNoIHByb2Nlc3MgaW4gV2luZG93cyBtZXNzZWQgc29tZXRoaW5n IHVwLCBJIGRvbid0IGtub3cuPC9kaXY+DQo8ZGl2Pjxicj4NCjwvZGl2Pg0KPC9kaXY+DQo8L2Rp dj4NCjxkaXYgaWQ9Il9vcmlnaW5hbF9tc2dfaGVhZGVyX0JCUFBJRCIgZGlyPSJhdXRvIj4NCjx0 YWJsZSB3aWR0aD0iMTAwJSIgc3R5bGU9ImJhY2tncm91bmQtY29sb3I6IHdoaXRlOyBib3JkZXIt c3BhY2luZzogMHB4OyBkaXNwbGF5OiB0YWJsZTsgb3V0bGluZTogbm9uZTsiIGNvbnRlbnRlZGl0 YWJsZT0iZmFsc2UiPg0KPHRib2R5Pg0KPHRyPg0KPHRkIGNvbHNwYW49IjIiIHN0eWxlPSJwYWRk aW5nOiBpbml0aWFsOyBmb250LXNpemU6IGluaXRpYWw7IHRleHQtYWxpZ246IGluaXRpYWw7IGJh Y2tncm91bmQtY29sb3I6IHJnYigyNTUsIDI1NSwgMjU1KTsiPg0KPGRpdiBkYXRhLWhhcy1pbmxp bmUtZWRpdHM9IiIgc3R5bGU9ImJvcmRlci1yaWdodDogbm9uZTsgYm9yZGVyLWJvdHRvbTogbm9u ZTsgYm9yZGVyLWxlZnQ6IG5vbmU7IGJvcmRlci1pbWFnZTogaW5pdGlhbDsgYm9yZGVyLXRvcDog MXB0IHNvbGlkIHJnYigxODEsIDE5NiwgMjIzKTsgcGFkZGluZzogM3B0IDBpbiAwaW47IGZvbnQt ZmFtaWx5OiBUYWhvbWEsICZxdW90O0JCIEFscGhhIFNhbnMmcXVvdDssICZxdW90O1NsYXRlIFBy byZxdW90OzsgZm9udC1zaXplOiAxMHB0OyI+DQo8ZGl2IGlkPSJmcm9tIj48Yj5Gcm9tOjwvYj4g Z25ldW5lcjJAY29tY2FzdC5uZXQ8L2Rpdj4NCjxkaXYgaWQ9InNlbnQiPjxiPlNlbnQ6PC9iPiBB dWd1c3QgOSwgMjAxNyAxMzoxNzwvZGl2Pg0KPGRpdiBpZD0idG8iPjxiPlRvOjwvYj4gcGdzcWwt cGVyZm9ybWFuY2VAcG9zdGdyZXNxbC5vcmc8L2Rpdj4NCjxkaXYgaWQ9InN1YmplY3QiPjxiPlN1 YmplY3Q6PC9iPiBSZTogW1BFUkZPUk1dIFVubG9nZ2VkIHRhYmxlczwvZGl2Pg0KPC9kaXY+DQo8 L3RkPg0KPC90cj4NCjwvdGJvZHk+DQo8L3RhYmxlPg0KPGRpdiBzdHlsZT0iYm9yZGVyLXJpZ2h0 OiBub25lOyBib3JkZXItYm90dG9tOiBub25lOyBib3JkZXItbGVmdDogbm9uZTsgYm9yZGVyLWlt YWdlOiBpbml0aWFsOyBib3JkZXItdG9wOiAxcHQgc29saWQgcmdiKDE4NiwgMTg4LCAyMDkpOyBk aXNwbGF5OiBibG9jazsgcGFkZGluZzogaW5pdGlhbDsgZm9udC1zaXplOiBpbml0aWFsOyB0ZXh0 LWFsaWduOiBpbml0aWFsOyBiYWNrZ3JvdW5kLWNvbG9yOiByZ2IoMjU1LCAyNTUsIDI1NSk7Ij4N CjwvZGl2Pg0KPGJyPg0KPC9kaXY+DQo8IS0tc3RhcnQgb2YgX29yaWdpbmFsQ29udGVudCAtLT4N CjxkaXYgbmFtZT0iQkIxMCIgZGlyPSJhdXRvIiBzdHlsZT0iYmFja2dyb3VuZC1pbWFnZTogaW5p dGlhbDsgbGluZS1oZWlnaHQ6IGluaXRpYWw7IG91dGxpbmU6IG5vbmU7IiBjb250ZW50ZWRpdGFi bGU9ImZhbHNlIj4NCjxkaXYgaWQ9InNzYy0zMDQ4MTE2MjU0MDQ5MzYxMjA2Ij48c3R5bGU+I3Nz Yy0zMDQ4MTE2MjU0MDQ5MzYxMjA2IC5FbWFpbFF1b3Rle21hcmdpbi1sZWZ0OiAxcHQ7cGFkZGlu Zy1sZWZ0OiA0cHQ7Ym9yZGVyLWxlZnQ6IHJnYigxMjgsIDAsIDApIDJweCBzb2xpZDt9PC9zdHls ZT4NCjxkaXY+PGZvbnQgc2l6ZT0iMiI+PHNwYW4gc3R5bGU9ImZvbnQtc2l6ZToxMHB0Ij4NCjxk aXYgY2xhc3M9IlBsYWluVGV4dCI+T24gV2VkLCA5IEF1ZyAyMDE3IDA5OjE0OjQ4IC0wNzAwLCBK ZWZmIEphbmVzICZsdDtqZWZmLmphbmVzQGdtYWlsLmNvbSZndDsgd3JvdGU6PGJyPg0KPGJyPg0K Jm5ic3A7Jmd0O1doeSBkb2Vzbid0IHRoZSBXaW5kb3dzIHNjaGVkdWxlZCBzaHV0ZG93biBzaWdu YWwgcG9zdGdyZXMgdG8gc2h1dGRvd248YnI+DQombmJzcDsmZ3Q7Y2xlYW5seSBhbmQgd2FpdCBm b3IgaXQgdG8gZG8gc28/Jm5ic3A7IFRoYXQgaXMgd2hhdCBpcyBzdXBwb3NlZCB0byBoYXBwZW4u PGJyPg0KPGJyPg0KV2luZG93cyAqZG9lcyogc2lnbmFsIHNodXRkb3duIChhbmQgc2xlZXAgYW5k IGhpYmVybmF0ZSBhbmQgd2FrZXVwKS4mbmJzcDsgPGJyPg0KcGdfY3RsIGNhbiBjYXRjaCB0aGVz ZSBzaWduYWxzIG9ubHkgd2hlbiBydW5uaW5nIGFzIGEgc2VydmljZSAuLi4gaXQgPGJyPg0Kd2ls bCBub3QgY2F0Y2ggYW55IHN5c3RlbSBzaWduYWxzIHdoZW4gcnVuIGFzIGFuIGFwcGxpY2F0aW9u Ljxicj4NCjxicj4NCkdlb3JnZTxicj4NCjxicj4NCjxicj4NCi0tIDxicj4NClNlbnQgdmlhIHBn c3FsLXBlcmZvcm1hbmNlIG1haWxpbmcgbGlzdCAocGdzcWwtcGVyZm9ybWFuY2VAcG9zdGdyZXNx bC5vcmcpPGJyPg0KVG8gbWFrZSBjaGFuZ2VzIHRvIHlvdXIgc3Vic2NyaXB0aW9uOjxicj4NCjxh IGhyZWY9Imh0dHA6Ly93d3cucG9zdGdyZXNxbC5vcmcvbWFpbHByZWYvcGdzcWwtcGVyZm9ybWFu Y2UiPmh0dHA6Ly93d3cucG9zdGdyZXNxbC5vcmcvbWFpbHByZWYvcGdzcWwtcGVyZm9ybWFuY2U8 L2E+PGJyPg0KPC9kaXY+DQo8L3NwYW4+PC9mb250PjwvZGl2Pg0KPC9kaXY+DQo8IS0tZW5kIG9m IF9vcmlnaW5hbENvbnRlbnQgLS0+PC9kaXY+DQo8L2JvZHk+DQo8L2h0bWw+DQo= --_000_BN4PR15MB0547F5864AD9691E31458FE1858B0BN4PR15MB0547namp_-- From gneuner2@comcast.net Wed Aug 9 19:52:26 2017 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dfX3h-0003mM-Cu for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 19:54:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dfX3f-0007o5-Oi for pgsql-performance@arkaria.postgresql.org; Wed, 09 Aug 2017 19:54:23 +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 1dfX1u-0004el-9V for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 19:52:34 +0000 Received: from resqmta-ch2-02v.sys.comcast.net ([2001:558:fe21:29:69:252:207:34]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfX1r-0005cK-13 for pgsql-performance@postgresql.org; Wed, 09 Aug 2017 19:52:32 +0000 Received: from resomta-ch2-09v.sys.comcast.net ([69.252.207.105]) by resqmta-ch2-02v.sys.comcast.net with ESMTP id fX1ddqdqOjjy1fX1pdXmDT; Wed, 09 Aug 2017 19:52:29 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=q20161114; t=1502308349; bh=Fqus5pnXT6urCe8qEyh4Hkqil23hOBBZ7kp693ZkXlE=; h=Received:Received:Subject:To:From:Message-ID:Date:MIME-Version: Content-Type; b=j0REOqcSo7Lvn4AyrQHsDZgO4QYOI6wps8zBoRoZHaqEl/YaqdECNGtZH9sk306bD om5lvyymj1txkm/yBPEvEBzExjz35LtGRFyAzLP0Gqg+EYdPrQDjPk0Fs6rjek/uFM hPLWYviob9rRTiW/bYjVAk+uSZ6kx2A8b4tJ/VnoV+11NejvjlmoshiXyssqOFkkYp kpUoqOEj3zxTCZCdVZjh3keiEoXnFuddXv/fMoCsPYhwtPelHNvEV4kuHqJE6hQv47 Q0eoEKF6MegfitW6Gy/LVh6lpyCkmzMZSTU4QhuqnWs9l7CBSZm/CEMP1Le5QL+CUB Fjsm3wfB5KPpw== Received: from [192.168.0.67] ([73.68.146.26]) by resomta-ch2-09v.sys.comcast.net with SMTP id fX1ody4Bs5oEJfX1ododAz; Wed, 09 Aug 2017 19:52:29 +0000 Subject: Re: Unlogged tables To: "ldh@laurent-hasson.com" References: <314ce53a-ee57-0be3-7815-f0e99fc0a6e4@comcast.net> From: George Neuner Cc: pgsql-performance@postgresql.org Message-ID: <6634901f-8d4e-023a-283f-5230168e85ff@comcast.net> Date: Wed, 9 Aug 2017 15:52:26 -0400 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.2.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------BAC58A93B588383D6539DC2D" Content-Language: en-US X-CMAE-Envelope: MS4wfDy5KnV8tmhzDD+jWJjMt7lILqLSOcnPWgUWfrpclv3VZPUEhI12NkhIRSrpLchosGsrOkmR7J6m+SHTJERQp5ri6qJESJScNqmiRO/YZHljjLukqTTT d7VuMqmuoU5LNCF136f0tOO64fy+Dc7SbiJrs+juMKbyase5y2aw9ZaXGjpQX3WtTL1+stQKeuerWY1VRF+fc576Wmoa1pMEqS5z1KvUXh7RKU8T0PDybbKG 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 This is a multi-part message in MIME format. --------------BAC58A93B588383D6539DC2D Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit Please don't top post. On 8/9/2017 2:30 PM, ldh@laurent-hasson.com wrote: > > On 8/9/2017 2:17 PM, gneuner2@comcast.net wrote: > > >> On Wed, 9 Aug 2017 09:14:48 -0700, Jeff Janes wrote: > > >> 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. > > > Windows *does* signal shutdown (and sleep and hibernate and wakeup). > > pg_ctl can catch these signals only when running as a service ... it > > will not catch any system signals when run as an application. > > Ok, I am not sure. I run Postgres as a service, and when my Windows > rebooted after a patch, UNLOGGED tables were cleaned... maybe the > patch process in Windows messed something up, I don't know. Hmm. Do you have checkpoint intervals set very long? Or do you have the Windows shutdown delay(s) set short? Data in unlogged tables persists only AFTER a checkpoint ... if the tables had been written to and were "dirty", and the system went down before the shutdown checkpoint (or before the shutdown checkpoint completed), then the tables would be truncated at the next startup. Service control in Windows is very different from Unix/Linux, and Windows is not completely POSIX compatible. I develop software for Windows and Linux, but I only use Postgresql. Postgresql was written originally for Unix and it is possible that the Windows version is not doing something quite right. I took a quick glance at the source for pg_ctl: SERVICE_CONTROL_SHUTDOWN and SERVICE_CONTROL_STOP both just set an shared event to notify the writer processes to terminate. Offhand I don't see where pg_ctl - running as a service - is waiting for the writer processes to actually terminate ( it does wait if run from the command line ). It's possible that your system shut down too quickly and the WAL writer was killed instead of terminating cleanly. Just FYI, re: Postgresql as a user application. Windows doesn't send *signals* (ala Unix) at all ... it is message based. The control messages are different for applications and services - e.g., WM_SHUTDOWN is sent to applications, SERVICE_CONTROL_SHUTDOWN is sent to services. In order for an application to catch a message, it must create a window. pg_ctl is a command line program which does not create any windows (in any mode). It was designed to enable it to run as a service, but when run as a user application it will can't receive any system messages. The user *must* manually stop a running database cluster before shutting down or sleeping. George --------------BAC58A93B588383D6539DC2D Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit Please don't top post.

On 8/9/2017 2:30 PM, ldh@laurent-hasson.com wrote:
> On 8/9/2017 2:17 PM, gneuner2@comcast.net wrote:

>> On Wed, 9 Aug 2017 09:14:48 -0700, Jeff Janes <jeff.janes@gmail.com> wrote:

>> 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.

> Windows *does* signal shutdown (and sleep and hibernate and wakeup). 
> pg_ctl can catch these signals only when running as a service ... it
> will not catch any system signals when run as an application.

Ok, I am not sure. I run Postgres as a service, and when my Windows rebooted after a patch, UNLOGGED tables were cleaned... maybe the patch process in Windows messed something up, I don't know.

Hmm.  Do you have checkpoint intervals set very long?  Or do you have the Windows shutdown delay(s) set short?

Data in unlogged tables persists only AFTER a checkpoint ... if the tables had been written to and were "dirty", and the system went down before the shutdown checkpoint (or before the shutdown checkpoint completed), then the tables would be truncated at the next startup. 


Service control in Windows is very different from Unix/Linux, and Windows is not completely POSIX compatible.  I develop software for Windows and Linux, but I only use Postgresql.  Postgresql was written originally for Unix and it is possible that the Windows version is not doing something quite right.

I took a quick glance at the source for pg_ctl:  SERVICE_CONTROL_SHUTDOWN and SERVICE_CONTROL_STOP both just set an shared event to notify the writer processes to terminate.  Offhand I don't see where pg_ctl - running as a service - is waiting for the writer processes to actually terminate ( it does wait if run from the command line ).   It's possible that your system shut down too quickly and the WAL writer was killed instead of terminating cleanly.


Just FYI, re: Postgresql as a user application. 

Windows doesn't send *signals* (ala Unix) at all ... it is message based.  The control messages are different for applications and services - e.g., WM_SHUTDOWN is sent to applications, SERVICE_CONTROL_SHUTDOWN is sent to services.  In order for an application to catch a message, it must create a window. 

pg_ctl is a command line program which does not create any windows (in any mode).  It was designed to enable it to run as a service, but when run as a user application it will can't receive any system messages.  The user *must* manually stop a running database cluster before shutting down or sleeping.

George

--------------BAC58A93B588383D6539DC2D-- From ldh@laurent-hasson.com Thu Aug 10 05:29:35 2017 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dfg4I-0004gl-EI for pgsql-performance@arkaria.postgresql.org; Thu, 10 Aug 2017 05:31:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dfg4H-00007d-Nv for pgsql-performance@arkaria.postgresql.org; Thu, 10 Aug 2017 05:31:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfg2T-0005Mt-GL for pgsql-performance@postgresql.org; Thu, 10 Aug 2017 05:29:45 +0000 Received: from mail-cys01nam02on0065.outbound.protection.outlook.com ([104.47.37.65] helo=NAM02-CY1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfg2O-0005eQ-RY for pgsql-performance@postgresql.org; Thu, 10 Aug 2017 05:29:44 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=NETORG2528281.onmicrosoft.com; s=selector1-laurenthasson-com01i; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=OziGQiDDNfG+avlEna3chNpXYiETmQ4C4Ada5DFiUks=; b=m5Lz7LKduUhBBonvPPG3diSrfQ0zfS7LEx3h0MK6zkLA7goi3TI2IMcZLhkvYc6nWbT3MZ76WQdnOl0hkhNbrpKJQL8ZyMemmIQqqS358SGa9mhfVKbMD4P4s49mqZ5XC5NiaMjrxvkEVlv/tYf6ruoDPafsK3IVXkGMXxVVrd0= Received: from BN4PR15MB0547.namprd15.prod.outlook.com (10.164.61.157) by BN4PR15MB0545.namprd15.prod.outlook.com (10.164.61.155) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P256) id 15.1.1320.16; Thu, 10 Aug 2017 05:29:35 +0000 Received: from BN4PR15MB0547.namprd15.prod.outlook.com ([10.164.61.157]) by BN4PR15MB0547.namprd15.prod.outlook.com ([10.164.61.157]) with mapi id 15.01.1320.018; Thu, 10 Aug 2017 05:29:35 +0000 From: "ldh@laurent-hasson.com" To: George Neuner CC: postgres performance list Subject: Re: Unlogged tables Thread-Topic: [PERFORM] Unlogged tables Thread-Index: AQHTETt/Lq7x/1InREuLnDINR7olQqJ8WJmSgAAW/ACAAKFBvg== Date: Thu, 10 Aug 2017 05:29:35 +0000 Message-ID: References: <314ce53a-ee57-0be3-7815-f0e99fc0a6e4@comcast.net> ,<6634901f-8d4e-023a-283f-5230168e85ff@comcast.net> In-Reply-To: <6634901f-8d4e-023a-283f-5230168e85ff@comcast.net> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: spf=none (sender IP is ) smtp.mailfrom=ldh@laurent-hasson.com; x-originating-ip: [12.204.85.2] x-ms-publictraffictype: Email x-microsoft-exchange-diagnostics: 1;BN4PR15MB0545;6:MXHk25GANp189cINOuj7CBTZhM9vMCreMdT/5LAaKkiWpLRbahrKXSy7UePxhVWkpY3JG6MUI67PgOYaJ3CNUJSyeXGsTFKG29jbwma521bqhz2ENkJCfstAXCbaL4pbLjp+zn8M5YnXfhjEd7Y+W9aA5Te4OocBILp64CGxO99nBarWMZ91OJm9Q/VdVDDepHBebKKpMcw/Tf7A34/IiJrWnMPNpx4SNCSndrTK49XvMdojEo3Y93sopt7EXm7BnV6MnSz+uDO9ohWU1BA45oCIq3Miq6I4UQ2gb3/IS/9K8NceeioaiJBzSG34PemetVpFmbRdcDWcYEXluNFHuw==;5:IYWpYp7jeNUofuvsSSuxuVg12Efd5rvFQfO+7pe5LGQqaznv2HMWpbEmR1VWNWR/qdT/N86RIkeqkZtK/+SsHDJWPtW24Rsto3uefAU+W3nEoF4+rDfJWF87Ksdv19gvDVFywqGN75cwC+gQbq9ZAw==;24:6qqMfbmWzAF7ZUDrf9o6jttAEnwGe2fJU3WEoaP9nvC7eOFq2xZ3yZHnPpvmyCQuFzX1SP0uJ07P1odiY2adEnRWINFfOc3ZwSG1B8zW5Hw=;7:7MAIKvc1Em1mx1ajETvKCuvpqBjomNY7cD9fvg4OHsmRu/NlWYjlgWMHP0O5oTllFE42G46aXQ8ZXsZmg5j0K1QrnIRiwMrYvTFI3O9O/I3EliCSnlB1iuK5HCF00jYryLfkxNDAtw1bt7Jur72sMM3ERb0ZSQWk3VNEQPAqWKK6zW9uAjcHPxostGkT6U+kYXw7bjEvZCy2sJ0bYziHgbBdFDPUoP/ov2vj1eNxt8s= x-ms-exchange-antispam-srfa-diagnostics: SSOS; x-ms-office365-filtering-correlation-id: 10aa3f7b-6e2c-4160-255f-08d4dfb0c971 x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:(300000500095)(300135000095)(300000501095)(300135300095)(300000502095)(300135100095)(22001)(2017030254152)(2017082002075)(300000503095)(300135400095)(2017052603031)(201703131423075)(201702281549075)(300000504095)(300135200095)(300000505095)(300135600095)(300000506095)(300135500095);SRVR:BN4PR15MB0545; x-ms-traffictypediagnostic: BN4PR15MB0545: x-exchange-antispam-report-test: UriScan:(68173958961439)(278428928389397); x-microsoft-antispam-prvs: x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(100000700101)(100105000095)(100000701101)(100105300095)(100000702101)(100105100095)(6040450)(601004)(2401047)(5005006)(8121501046)(3002001)(93006095)(93001095)(10201501046)(100000703101)(100105400095)(6041248)(20161123564025)(20161123562025)(2016111802025)(20161123558100)(20161123560025)(201703131423075)(201703061421075)(20161123555025)(6043046)(6072148)(201708071742011)(100000704101)(100105200095)(100000705101)(100105500095);SRVR:BN4PR15MB0545;BCL:0;PCL:0;RULEID:(100000800101)(100110000095)(100000801101)(100110300095)(100000802101)(100110100095)(100000803101)(100110400095)(100000804101)(100110200095)(100000805101)(100110500095);SRVR:BN4PR15MB0545; x-forefront-prvs: 03950F25EC x-forefront-antispam-report: SFV:NSPM;SFS:(10009020)(39450400003)(39400400002)(189002)(24454002)(377454003)(69234005)(199003)(6436002)(81166006)(8676002)(86362001)(74316002)(6916009)(8936002)(8666007)(2950100002)(7736002)(55016002)(6506006)(2906002)(99286003)(7696004)(53936002)(9686003)(54896002)(106356001)(236005)(105586002)(77096006)(229853002)(3660700001)(5660300001)(6246003)(68736007)(4326008)(3280700002)(110136004)(38730400002)(86582002)(54356999)(102836003)(6116002)(3846002)(76176999)(50986999)(101416001)(508600001)(33656002)(53546010)(66066001)(97736004)(14454004)(2900100001)(25786009)(189998001)(81156014)(19623405001);DIR:OUT;SFP:1101;SCL:1;SRVR:BN4PR15MB0545;H:BN4PR15MB0547.namprd15.prod.outlook.com;FPR:;SPF:None;PTR:InfoNoRecords;MX:1;A:1;LANG:en; received-spf: None (protection.outlook.com: laurent-hasson.com does not designate permitted sender hosts) spamdiagnosticoutput: 1:99 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_BN4PR15MB05477F51C9D6D8B45178F64585880BN4PR15MB0547namp_" MIME-Version: 1.0 X-OriginatorOrg: laurent-hasson.com X-MS-Exchange-CrossTenant-originalarrivaltime: 10 Aug 2017 05:29:35.0299 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 7f9c0a0d-79fd-4329-b129-754c4501a177 X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN4PR15MB0545 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 --_000_BN4PR15MB05477F51C9D6D8B45178F64585880BN4PR15MB0547namp_ Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 DQoNClNlbnQgZnJvbSBteSBCbGFja0JlcnJ5IC0gdGhlIG1vc3Qgc2VjdXJlIG1vYmlsZSBkZXZp Y2UNCkZyb206IGduZXVuZXIyQGNvbWNhc3QubmV0DQpTZW50OiBBdWd1c3QgOSwgMjAxNyAxNDo1 Mg0KVG86IGxkaEBsYXVyZW50LWhhc3Nvbi5jb20NCkNjOiBwZ3NxbC1wZXJmb3JtYW5jZUBwb3N0 Z3Jlc3FsLm9yZw0KU3ViamVjdDogUmU6IFtQRVJGT1JNXSBVbmxvZ2dlZCB0YWJsZXMNCg0KDQpQ bGVhc2UgZG9uJ3QgdG9wIHBvc3QuDQoNCk9uIDgvOS8yMDE3IDI6MzAgUE0sIGxkaEBsYXVyZW50 LWhhc3Nvbi5jb208bWFpbHRvOmxkaEBsYXVyZW50LWhhc3Nvbi5jb20+IHdyb3RlOg0KPiBPbiA4 LzkvMjAxNyAyOjE3IFBNLCBnbmV1bmVyMkBjb21jYXN0Lm5ldDxtYWlsdG86Z25ldW5lcjJAY29t Y2FzdC5uZXQ+IHdyb3RlOg0KDQo+PiBPbiBXZWQsIDkgQXVnIDIwMTcgMDk6MTQ6NDggLTA3MDAs IEplZmYgSmFuZXMgPGplZmYuamFuZXNAZ21haWwuY29tPjxtYWlsdG86amVmZi5qYW5lc0BnbWFp bC5jb20+IHdyb3RlOg0KDQo+PiBXaHkgZG9lc24ndCB0aGUgV2luZG93cyBzY2hlZHVsZWQgc2h1 dGRvd24gc2lnbmFsIHBvc3RncmVzIHRvIHNodXRkb3duDQo+PiBjbGVhbmx5IGFuZCB3YWl0IGZv ciBpdCB0byBkbyBzbz8gIFRoYXQgaXMgd2hhdCBpcyBzdXBwb3NlZCB0byBoYXBwZW4uDQoNCj4g V2luZG93cyAqZG9lcyogc2lnbmFsIHNodXRkb3duIChhbmQgc2xlZXAgYW5kIGhpYmVybmF0ZSBh bmQgd2FrZXVwKS4NCj4gcGdfY3RsIGNhbiBjYXRjaCB0aGVzZSBzaWduYWxzIG9ubHkgd2hlbiBy dW5uaW5nIGFzIGEgc2VydmljZSAuLi4gaXQNCj4gd2lsbCBub3QgY2F0Y2ggYW55IHN5c3RlbSBz aWduYWxzIHdoZW4gcnVuIGFzIGFuIGFwcGxpY2F0aW9uLg0KDQpPaywgSSBhbSBub3Qgc3VyZS4g SSBydW4gUG9zdGdyZXMgYXMgYSBzZXJ2aWNlLCBhbmQgd2hlbiBteSBXaW5kb3dzIHJlYm9vdGVk IGFmdGVyIGEgcGF0Y2gsIFVOTE9HR0VEIHRhYmxlcyB3ZXJlIGNsZWFuZWQuLi4gbWF5YmUgdGhl IHBhdGNoIHByb2Nlc3MgaW4gV2luZG93cyBtZXNzZWQgc29tZXRoaW5nIHVwLCBJIGRvbid0IGtu b3cuDQoNCkhtbS4gIERvIHlvdSBoYXZlIGNoZWNrcG9pbnQgaW50ZXJ2YWxzIHNldCB2ZXJ5IGxv bmc/ICBPciBkbyB5b3UgaGF2ZSB0aGUgV2luZG93cyBzaHV0ZG93biBkZWxheShzKSBzZXQgc2hv cnQ/DQoNCkRhdGEgaW4gdW5sb2dnZWQgdGFibGVzIHBlcnNpc3RzIG9ubHkgQUZURVIgYSBjaGVj a3BvaW50IC4uLiBpZiB0aGUgdGFibGVzIGhhZCBiZWVuIHdyaXR0ZW4gdG8gYW5kIHdlcmUgImRp cnR5IiwgYW5kIHRoZSBzeXN0ZW0gd2VudCBkb3duIGJlZm9yZSB0aGUgc2h1dGRvd24gY2hlY2tw b2ludCAob3IgYmVmb3JlIHRoZSBzaHV0ZG93biBjaGVja3BvaW50IGNvbXBsZXRlZCksIHRoZW4g dGhlIHRhYmxlcyB3b3VsZCBiZSB0cnVuY2F0ZWQgYXQgdGhlIG5leHQgc3RhcnR1cC4NCg0KDQpT ZXJ2aWNlIGNvbnRyb2wgaW4gV2luZG93cyBpcyB2ZXJ5IGRpZmZlcmVudCBmcm9tIFVuaXgvTGlu dXgsIGFuZCBXaW5kb3dzIGlzIG5vdCBjb21wbGV0ZWx5IFBPU0lYIGNvbXBhdGlibGUuICBJIGRl dmVsb3Agc29mdHdhcmUgZm9yIFdpbmRvd3MgYW5kIExpbnV4LCBidXQgSSBvbmx5IHVzZSBQb3N0 Z3Jlc3FsLiAgUG9zdGdyZXNxbCB3YXMgd3JpdHRlbiBvcmlnaW5hbGx5IGZvciBVbml4IGFuZCBp dCBpcyBwb3NzaWJsZSB0aGF0IHRoZSBXaW5kb3dzIHZlcnNpb24gaXMgbm90IGRvaW5nIHNvbWV0 aGluZyBxdWl0ZSByaWdodC4NCg0KSSB0b29rIGEgcXVpY2sgZ2xhbmNlIGF0IHRoZSBzb3VyY2Ug Zm9yIHBnX2N0bDogIFNFUlZJQ0VfQ09OVFJPTF9TSFVURE9XTiBhbmQgU0VSVklDRV9DT05UUk9M X1NUT1AgYm90aCBqdXN0IHNldCBhbiBzaGFyZWQgZXZlbnQgdG8gbm90aWZ5IHRoZSB3cml0ZXIg cHJvY2Vzc2VzIHRvIHRlcm1pbmF0ZS4gIE9mZmhhbmQgSSBkb24ndCBzZWUgd2hlcmUgcGdfY3Rs IC0gcnVubmluZyBhcyBhIHNlcnZpY2UgLSBpcyB3YWl0aW5nIGZvciB0aGUgd3JpdGVyIHByb2Nl c3NlcyB0byBhY3R1YWxseSB0ZXJtaW5hdGUgKCBpdCBkb2VzIHdhaXQgaWYgcnVuIGZyb20gdGhl IGNvbW1hbmQgbGluZSApLiAgIEl0J3MgcG9zc2libGUgdGhhdCB5b3VyIHN5c3RlbSBzaHV0IGRv d24gdG9vIHF1aWNrbHkgYW5kIHRoZSBXQUwgd3JpdGVyIHdhcyBraWxsZWQgaW5zdGVhZCBvZiB0 ZXJtaW5hdGluZyBjbGVhbmx5Lg0KDQoNCkp1c3QgRllJLCByZTogUG9zdGdyZXNxbCBhcyBhIHVz ZXIgYXBwbGljYXRpb24uDQoNCldpbmRvd3MgZG9lc24ndCBzZW5kICpzaWduYWxzKiAoYWxhIFVu aXgpIGF0IGFsbCAuLi4gaXQgaXMgbWVzc2FnZSBiYXNlZC4gIFRoZSBjb250cm9sIG1lc3NhZ2Vz IGFyZSBkaWZmZXJlbnQgZm9yIGFwcGxpY2F0aW9ucyBhbmQgc2VydmljZXMgLSBlLmcuLCBXTV9T SFVURE9XTiBpcyBzZW50IHRvIGFwcGxpY2F0aW9ucywgU0VSVklDRV9DT05UUk9MX1NIVVRET1dO IGlzIHNlbnQgdG8gc2VydmljZXMuICBJbiBvcmRlciBmb3IgYW4gYXBwbGljYXRpb24gdG8gY2F0 Y2ggYSBtZXNzYWdlLCBpdCBtdXN0IGNyZWF0ZSBhIHdpbmRvdy4NCg0KcGdfY3RsIGlzIGEgY29t bWFuZCBsaW5lIHByb2dyYW0gd2hpY2ggZG9lcyBub3QgY3JlYXRlIGFueSB3aW5kb3dzIChpbiBh bnkgbW9kZSkuICBJdCB3YXMgZGVzaWduZWQgdG8gZW5hYmxlIGl0IHRvIHJ1biBhcyBhIHNlcnZp Y2UsIGJ1dCB3aGVuIHJ1biBhcyBhIHVzZXIgYXBwbGljYXRpb24gaXQgd2lsbCBjYW4ndCByZWNl aXZlIGFueSBzeXN0ZW0gbWVzc2FnZXMuICBUaGUgdXNlciAqbXVzdCogbWFudWFsbHkgc3RvcCBh IHJ1bm5pbmcgZGF0YWJhc2UgY2x1c3RlciBiZWZvcmUgc2h1dHRpbmcgZG93biBvciBzbGVlcGlu Zy4NCg0KR2VvcmdlDQoNCg0KSGVsbG8gR2VvcmdlLi4uIEkga25vdyBhYm91dCBub3QgZG9pbmcg dG9wIHBvc3RpbmcgYnV0IHdhcyBlbWFpbGluZyBmcm9tIG15IHBob25lLCBhbmQganVzdCByZWNl bnRseSBtb3ZlZCB0byBBbmRyb2lkLiBJIHRoaW5rIEkgYW0gc3RpbGwgbm90IGNvbmZpZ3VyZWQg cmlnaHQuDQoNClNvbWV3aGF0IG9ydGhvZ29uYWwsIGJ1dCBhbnkgcGFydGljdWxhciByZWFzb24g d2h5IHRvcCBwb3N0cyA9PSBiYWQsIG9yIGp1c3QgY29udmVudGlvbj8NCg0KSSB3aWxsIHRyeSBh IGZldyBzY2VuYXJpb3MgYW5kIHJlcG9ydCBiYWNrLiBJIGRvIG5vdCBiZWxpZXZlIEkgaGF2ZSBs b25nIGNwIGludGVydmFscyBhbmQgSSBkbyBub3QgYmVsaWV2ZSB0aGUgd2luZG93cyBtYWNoaW5l IHNodXRzIGRvd24gZmFzdGVyIHRoYW4gJ25vcm1hbCcNCg0KRmluYWxseSwgbXkgdHJ1ZSBxdWVz dGlvbiB3YXMgd2hldGhlciBQb3N0Z3JlcyB3b3VsZCBzdXBwb3J0IHNvbWV0aGluZyBsaWtlIHdv cm0gd2l0aCB0aGUgcGVyZm9ybWFuY2UgYmVuZWZpdHMgb2YgVU5MT0dHRUQsIGJ1dCBub3QgdGhl IGluY29udmVuaWVuY2VzIG9mIGF1dG8gdHJ1bmNhdGVzLg0KDQpUaGFua3MuDQo= --_000_BN4PR15MB05477F51C9D6D8B45178F64585880BN4PR15MB0547namp_ Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: base64 PGh0bWw+DQo8aGVhZD4NCjxtZXRhIGh0dHAtZXF1aXY9IkNvbnRlbnQtVHlwZSIgY29udGVudD0i dGV4dC9odG1sOyBjaGFyc2V0PXV0Zi04Ij4NCjxtZXRhIGh0dHAtZXF1aXY9IkNvbnRlbnQtU2Vj dXJpdHktUG9saWN5IiBjb250ZW50PSJzY3JpcHQtc3JjICdzZWxmJzsgaW1nLXNyYyAqIGNpZDog ZGF0YTo7Ij4NCjwvaGVhZD4NCjxib2R5IHN0eWxlPSJiYWNrZ3JvdW5kLWNvbG9yOiByZ2IoMjU1 LCAyNTUsIDI1NSk7IGJhY2tncm91bmQtaW1hZ2U6IGluaXRpYWw7IGxpbmUtaGVpZ2h0OiBpbml0 aWFsOyI+DQo8ZGl2IGlkPSJyZXNwb25zZV9jb250YWluZXJfQkJQUElEIiBzdHlsZT0ib3V0bGlu ZTpub25lO2ZvbnQtc2l6ZTppbml0aWFsO2ZvbnQtZmFtaWx5OiZxdW90O0NhbGlicmkmcXVvdDss JnF1b3Q7U2xhdGUgUHJvJnF1b3Q7LHNhbnMtc2VyaWYsJnF1b3Q7c2Fucy1zZXJpZiZxdW90OyIg ZGlyPSJhdXRvIiBjb250ZW50ZWRpdGFibGU9ImZhbHNlIj4NCjxkaXYgbmFtZT0iQkIxMCIgZGly PSJhdXRvIiBzdHlsZT0id2lkdGg6IDEwMCU7IHBhZGRpbmc6IGluaXRpYWw7IGZvbnQtc2l6ZTog aW5pdGlhbDsgdGV4dC1hbGlnbjogaW5pdGlhbDsgYmFja2dyb3VuZC1jb2xvcjogcmdiKDI1NSwg MjU1LCAyNTUpOyI+DQo8YnIgc3R5bGU9ImRpc3BsYXk6aW5pdGlhbCI+DQo8L2Rpdj4NCjxkaXYg bmFtZT0iQkIxMCIgZGlyPSJhdXRvIiBzdHlsZT0id2lkdGg6IDEwMCU7IHBhZGRpbmc6IGluaXRp YWw7IGZvbnQtc2l6ZTogaW5pdGlhbDsgdGV4dC1hbGlnbjogaW5pdGlhbDsgYmFja2dyb3VuZC1j b2xvcjogcmdiKDI1NSwgMjU1LCAyNTUpOyI+DQo8YnIgc3R5bGU9ImRpc3BsYXk6aW5pdGlhbCI+ DQo8L2Rpdj4NCjxkaXYgaWQ9ImJsYWNrYmVycnlfc2lnbmF0dXJlX0JCUFBJRCIgbmFtZT0iQkIx MCIgZGlyPSJhdXRvIj4NCjxkaXYgbmFtZT0iQkIxMCIgZGlyPSJhdXRvIiBzdHlsZT0icGFkZGlu ZzogaW5pdGlhbDsgZm9udC1zaXplOiBpbml0aWFsOyB0ZXh0LWFsaWduOiBpbml0aWFsOyBiYWNr Z3JvdW5kLWNvbG9yOiByZ2IoMjU1LCAyNTUsIDI1NSk7Ij4NClNlbnQgZnJvbSBteSBCbGFja0Jl cnJ5IC0gdGhlIG1vc3Qgc2VjdXJlIG1vYmlsZSBkZXZpY2U8L2Rpdj4NCjwvZGl2Pg0KPC9kaXY+ DQo8ZGl2IGlkPSJfb3JpZ2luYWxfbXNnX2hlYWRlcl9CQlBQSUQiIGRpcj0iYXV0byI+DQo8dGFi bGUgd2lkdGg9IjEwMCUiIHN0eWxlPSJiYWNrZ3JvdW5kLWNvbG9yOiB3aGl0ZTsgYm9yZGVyLXNw YWNpbmc6IDBweDsgZGlzcGxheTogdGFibGU7IG91dGxpbmU6IG5vbmU7IiBjb250ZW50ZWRpdGFi bGU9ImZhbHNlIj4NCjx0Ym9keT4NCjx0cj4NCjx0ZCBjb2xzcGFuPSIyIiBzdHlsZT0icGFkZGlu ZzogaW5pdGlhbDsgZm9udC1zaXplOiBpbml0aWFsOyB0ZXh0LWFsaWduOiBpbml0aWFsOyBiYWNr Z3JvdW5kLWNvbG9yOiByZ2IoMjU1LCAyNTUsIDI1NSk7Ij4NCjxkaXYgZGF0YS1oYXMtaW5saW5l LWVkaXRzPSIiIHN0eWxlPSJib3JkZXItcmlnaHQ6IG5vbmU7IGJvcmRlci1ib3R0b206IG5vbmU7 IGJvcmRlci1sZWZ0OiBub25lOyBib3JkZXItaW1hZ2U6IGluaXRpYWw7IGJvcmRlci10b3A6IDFw dCBzb2xpZCByZ2IoMTgxLCAxOTYsIDIyMyk7IHBhZGRpbmc6IDNwdCAwaW4gMGluOyBmb250LWZh bWlseTogVGFob21hLCAmcXVvdDtCQiBBbHBoYSBTYW5zJnF1b3Q7LCAmcXVvdDtTbGF0ZSBQcm8m cXVvdDs7IGZvbnQtc2l6ZTogMTBwdDsiPg0KPGRpdiBpZD0iZnJvbSI+PGI+RnJvbTo8L2I+IGdu ZXVuZXIyQGNvbWNhc3QubmV0PC9kaXY+DQo8ZGl2IGlkPSJzZW50Ij48Yj5TZW50OjwvYj4gQXVn dXN0IDksIDIwMTcgMTQ6NTI8L2Rpdj4NCjxkaXYgaWQ9InRvIj48Yj5Ubzo8L2I+IGxkaEBsYXVy ZW50LWhhc3Nvbi5jb208L2Rpdj4NCjxkaXYgaWQ9ImNjIj48Yj5DYzo8L2I+IHBnc3FsLXBlcmZv cm1hbmNlQHBvc3RncmVzcWwub3JnPC9kaXY+DQo8ZGl2IGlkPSJzdWJqZWN0Ij48Yj5TdWJqZWN0 OjwvYj4gUmU6IFtQRVJGT1JNXSBVbmxvZ2dlZCB0YWJsZXM8L2Rpdj4NCjwvZGl2Pg0KPC90ZD4N CjwvdHI+DQo8L3Rib2R5Pg0KPC90YWJsZT4NCjxkaXYgc3R5bGU9ImJvcmRlci1yaWdodDogbm9u ZTsgYm9yZGVyLWJvdHRvbTogbm9uZTsgYm9yZGVyLWxlZnQ6IG5vbmU7IGJvcmRlci1pbWFnZTog aW5pdGlhbDsgYm9yZGVyLXRvcDogMXB0IHNvbGlkIHJnYigxODYsIDE4OCwgMjA5KTsgZGlzcGxh eTogYmxvY2s7IHBhZGRpbmc6IGluaXRpYWw7IGZvbnQtc2l6ZTogaW5pdGlhbDsgdGV4dC1hbGln bjogaW5pdGlhbDsgYmFja2dyb3VuZC1jb2xvcjogcmdiKDI1NSwgMjU1LCAyNTUpOyI+DQo8L2Rp dj4NCjxicj4NCjwvZGl2Pg0KPCEtLXN0YXJ0IG9mIF9vcmlnaW5hbENvbnRlbnQgLS0+DQo8ZGl2 IG5hbWU9IkJCMTAiIGRpcj0iYXV0byIgc3R5bGU9ImJhY2tncm91bmQtaW1hZ2U6IGluaXRpYWw7 IGxpbmUtaGVpZ2h0OiBpbml0aWFsOyBvdXRsaW5lOiBub25lOyIgY29udGVudGVkaXRhYmxlPSJm YWxzZSI+DQo8ZGl2IGlkPSJzc2MyMTI1MzA4OTc4ODUxOTc5NjcwIj48c3R5bGU+I3NzYzIxMjUz MDg5Nzg4NTE5Nzk2NzAgI3NzYy0zMDQ4MTE2MjU0MDQ5MzYxMjA2IC5FbWFpbFF1b3Rle21hcmdp bi1sZWZ0OiAxcHQ7cGFkZGluZy1sZWZ0OiA0cHQ7Ym9yZGVyLWxlZnQ6IHJnYigxMjgsIDAsIDAp IDJweCBzb2xpZDt9PC9zdHlsZT4NCjxkaXY+PC9kaXY+DQpQbGVhc2UgZG9uJ3QgdG9wIHBvc3Qu PGJyPg0KPGJyPg0KPGRpdiBjbGFzcz0ibW96LWNpdGUtcHJlZml4Ij5PbiA4LzkvMjAxNyAyOjMw IFBNLCA8YSBjbGFzcz0ibW96LXR4dC1saW5rLWFiYnJldmlhdGVkIiBocmVmPSJtYWlsdG86bGRo QGxhdXJlbnQtaGFzc29uLmNvbSI+DQpsZGhAbGF1cmVudC1oYXNzb24uY29tPC9hPiB3cm90ZTo8 YnI+DQo8L2Rpdj4NCjxibG9ja3F1b3RlIHR5cGU9ImNpdGUiPg0KPGRpdiBpZD0icmVzcG9uc2Vf Y29udGFpbmVyX0JCUFBJRCIgZGlyPSJhdXRvIj4NCjxkaXYgZGlyPSJhdXRvIiBzdHlsZT0id2lk dGg6MTAwJTtiYWNrZ3JvdW5kLWNvbG9yOnJnYiggMjU1ICwgMjU1ICwgMjU1ICkiPiZndDsgT24g OC85LzIwMTcgMjoxNyBQTSwNCjxhIGNsYXNzPSJtb3otdHh0LWxpbmstYWJicmV2aWF0ZWQiIGhy ZWY9Im1haWx0bzpnbmV1bmVyMkBjb21jYXN0Lm5ldCI+Z25ldW5lcjJAY29tY2FzdC5uZXQ8L2E+ IHdyb3RlOjwvZGl2Pg0KPC9kaXY+DQo8ZGl2IGlkPSJfb3JpZ2luYWxfbXNnX2hlYWRlcl9CQlBQ SUQiIGRpcj0iYXV0byI+PGJyPg0KPC9kaXY+DQo8ZGl2IGRpcj0iYXV0byIgc3R5bGU9Im91dGxp bmU6bm9uZSI+DQo8ZGl2IGlkPSJzc2MtMzA0ODExNjI1NDA0OTM2MTIwNiI+DQo8ZGl2Pjxmb250 IHNpemU9IjIiPjxzcGFuIHN0eWxlPSJmb250LXNpemU6MTBwdCI+DQo8ZGl2IGNsYXNzPSJQbGFp blRleHQiPiZndDsmZ3Q7IE9uIFdlZCwgOSBBdWcgMjAxNyAwOToxNDo0OCAtMDcwMCwgSmVmZiBK YW5lcyA8YSBjbGFzcz0ibW96LXR4dC1saW5rLXJmYzIzOTZFIiBocmVmPSJtYWlsdG86amVmZi5q YW5lc0BnbWFpbC5jb20iPg0KJmx0O2plZmYuamFuZXNAZ21haWwuY29tJmd0OzwvYT4gd3JvdGU6 PGJyPg0KPGJyPg0KJmd0OyZndDsgV2h5IGRvZXNuJ3QgdGhlIFdpbmRvd3Mgc2NoZWR1bGVkIHNo dXRkb3duIHNpZ25hbCBwb3N0Z3JlcyB0byBzaHV0ZG93bjxicj4NCiZndDsmZ3Q7IGNsZWFubHkg YW5kIHdhaXQgZm9yIGl0IHRvIGRvIHNvPyZuYnNwOyBUaGF0IGlzIHdoYXQgaXMgc3VwcG9zZWQg dG8gaGFwcGVuLjxicj4NCjxicj4NCiZndDsgV2luZG93cyAqZG9lcyogc2lnbmFsIHNodXRkb3du IChhbmQgc2xlZXAgYW5kIGhpYmVybmF0ZSBhbmQgd2FrZXVwKS4mbmJzcDsgPGJyPg0KJmd0OyBw Z19jdGwgY2FuIGNhdGNoIHRoZXNlIHNpZ25hbHMgb25seSB3aGVuIHJ1bm5pbmcgYXMgYSBzZXJ2 aWNlIC4uLiBpdCA8YnI+DQomZ3Q7IHdpbGwgbm90IGNhdGNoIGFueSBzeXN0ZW0gc2lnbmFscyB3 aGVuIHJ1biBhcyBhbiBhcHBsaWNhdGlvbi48YnI+DQo8YnI+DQo8L2Rpdj4NCjwvc3Bhbj48L2Zv bnQ+PC9kaXY+DQo8L2Rpdj4NCjwvZGl2Pg0KPC9ibG9ja3F1b3RlPg0KPGJsb2NrcXVvdGUgdHlw ZT0iY2l0ZSI+DQo8ZGl2IGlkPSJyZXNwb25zZV9jb250YWluZXJfQkJQUElEIiBkaXI9ImF1dG8i Pg0KPGRpdiBkaXI9ImF1dG8iIHN0eWxlPSJ3aWR0aDoxMDAlO2JhY2tncm91bmQtY29sb3I6cmdi KCAyNTUgLCAyNTUgLCAyNTUgKSI+DQo8ZGl2IGlkPSJCQjEwX3Jlc3BvbnNlX2Rpdl9CQlBQSUQi IGRpcj0iYXV0byIgc3R5bGU9ImZvbnQtZmFtaWx5OidjYWxpYnJpJyAsICdzbGF0ZSBwcm8nICwg c2Fucy1zZXJpZiAsIHNhbnMtc2VyaWY7d2lkdGg6MzgwcHgiPg0KT2ssIEkgYW0gbm90IHN1cmUu IEkgcnVuIFBvc3RncmVzIGFzIGEgc2VydmljZSwgYW5kIHdoZW4gbXkgV2luZG93cyByZWJvb3Rl ZCBhZnRlciBhIHBhdGNoLCBVTkxPR0dFRCB0YWJsZXMgd2VyZSBjbGVhbmVkLi4uIG1heWJlIHRo ZSBwYXRjaCBwcm9jZXNzIGluIFdpbmRvd3MgbWVzc2VkIHNvbWV0aGluZyB1cCwgSSBkb24ndCBr bm93LjwvZGl2Pg0KPC9kaXY+DQo8L2Rpdj4NCjwvYmxvY2txdW90ZT4NCjxicj4NCkhtbS4mbmJz cDsgRG8geW91IGhhdmUgY2hlY2twb2ludCBpbnRlcnZhbHMgc2V0IHZlcnkgbG9uZz8mbmJzcDsg T3IgZG8geW91IGhhdmUgdGhlIFdpbmRvd3Mgc2h1dGRvd24gZGVsYXkocykgc2V0IHNob3J0Pzxi cj4NCjxicj4NCkRhdGEgaW4gdW5sb2dnZWQgdGFibGVzIHBlcnNpc3RzIG9ubHkgQUZURVIgYSBj aGVja3BvaW50IC4uLiBpZiB0aGUgdGFibGVzIGhhZCBiZWVuIHdyaXR0ZW4gdG8gYW5kIHdlcmUg JnF1b3Q7ZGlydHkmcXVvdDssIGFuZCB0aGUgc3lzdGVtIHdlbnQgZG93biBiZWZvcmUgdGhlIHNo dXRkb3duIGNoZWNrcG9pbnQgKG9yIGJlZm9yZSB0aGUgc2h1dGRvd24gY2hlY2twb2ludCBjb21w bGV0ZWQpLCB0aGVuIHRoZSB0YWJsZXMgd291bGQgYmUgdHJ1bmNhdGVkIGF0IHRoZQ0KIG5leHQg c3RhcnR1cC4mbmJzcDsgPGJyPg0KPGJyPg0KPGJyPg0KU2VydmljZSBjb250cm9sIGluIFdpbmRv d3MgaXMgdmVyeSBkaWZmZXJlbnQgZnJvbSBVbml4L0xpbnV4LCBhbmQgV2luZG93cyBpcyBub3Qg Y29tcGxldGVseSBQT1NJWCBjb21wYXRpYmxlLiZuYnNwOyBJIGRldmVsb3Agc29mdHdhcmUgZm9y IFdpbmRvd3MgYW5kIExpbnV4LCBidXQgSSBvbmx5IHVzZSBQb3N0Z3Jlc3FsLiZuYnNwOyBQb3N0 Z3Jlc3FsIHdhcyB3cml0dGVuIG9yaWdpbmFsbHkgZm9yIFVuaXggYW5kIGl0IGlzIHBvc3NpYmxl IHRoYXQgdGhlIFdpbmRvd3MNCiB2ZXJzaW9uIGlzIG5vdCBkb2luZyBzb21ldGhpbmcgcXVpdGUg cmlnaHQuPGJyPg0KPGJyPg0KSSB0b29rIGEgcXVpY2sgZ2xhbmNlIGF0IHRoZSBzb3VyY2UgZm9y IHBnX2N0bDombmJzcDsgU0VSVklDRV9DT05UUk9MX1NIVVRET1dOIGFuZCBTRVJWSUNFX0NPTlRS T0xfU1RPUCBib3RoIGp1c3Qgc2V0IGFuIHNoYXJlZCBldmVudCB0byBub3RpZnkgdGhlIHdyaXRl ciBwcm9jZXNzZXMgdG8gdGVybWluYXRlLiZuYnNwOyBPZmZoYW5kIEkgZG9uJ3Qgc2VlIHdoZXJl IHBnX2N0bCAtIHJ1bm5pbmcgYXMgYSBzZXJ2aWNlIC0gaXMgd2FpdGluZyBmb3IgdGhlIHdyaXRl cg0KIHByb2Nlc3NlcyB0byBhY3R1YWxseSB0ZXJtaW5hdGUgKCBpdCBkb2VzIHdhaXQgaWYgcnVu IGZyb20gdGhlIGNvbW1hbmQgbGluZSApLiZuYnNwOyZuYnNwOyBJdCdzIHBvc3NpYmxlIHRoYXQg eW91ciBzeXN0ZW0gc2h1dCBkb3duIHRvbyBxdWlja2x5IGFuZCB0aGUgV0FMIHdyaXRlciB3YXMg a2lsbGVkIGluc3RlYWQgb2YgdGVybWluYXRpbmcgY2xlYW5seS48YnI+DQo8YnI+DQo8YnI+DQpK dXN0IEZZSSwgcmU6IFBvc3RncmVzcWwgYXMgYSB1c2VyIGFwcGxpY2F0aW9uLiZuYnNwOyA8YnI+ DQo8YnI+DQpXaW5kb3dzIGRvZXNuJ3Qgc2VuZCAqc2lnbmFscyogKGFsYSBVbml4KSBhdCBhbGwg Li4uIGl0IGlzIG1lc3NhZ2UgYmFzZWQuJm5ic3A7IFRoZSBjb250cm9sIG1lc3NhZ2VzIGFyZSBk aWZmZXJlbnQgZm9yIGFwcGxpY2F0aW9ucyBhbmQgc2VydmljZXMgLSBlLmcuLCBXTV9TSFVURE9X TiBpcyBzZW50IHRvIGFwcGxpY2F0aW9ucywgU0VSVklDRV9DT05UUk9MX1NIVVRET1dOIGlzIHNl bnQgdG8gc2VydmljZXMuJm5ic3A7IEluIG9yZGVyIGZvciBhbiBhcHBsaWNhdGlvbg0KIHRvIGNh dGNoIGEgbWVzc2FnZSwgaXQgbXVzdCBjcmVhdGUgYSB3aW5kb3cuJm5ic3A7IDxicj4NCjxicj4N CnBnX2N0bCBpcyBhIGNvbW1hbmQgbGluZSBwcm9ncmFtIHdoaWNoIGRvZXMgbm90IGNyZWF0ZSBh bnkgd2luZG93cyAoaW4gYW55IG1vZGUpLiZuYnNwOyBJdCB3YXMgZGVzaWduZWQgdG8gZW5hYmxl IGl0IHRvIHJ1biBhcyBhIHNlcnZpY2UsIGJ1dCB3aGVuIHJ1biBhcyBhIHVzZXIgYXBwbGljYXRp b24gaXQgd2lsbCBjYW4ndCByZWNlaXZlIGFueSBzeXN0ZW0gbWVzc2FnZXMuJm5ic3A7IFRoZSB1 c2VyICptdXN0KiBtYW51YWxseSBzdG9wIGEgcnVubmluZyBkYXRhYmFzZQ0KIGNsdXN0ZXIgYmVm b3JlIHNodXR0aW5nIGRvd24gb3Igc2xlZXBpbmcuPGJyPg0KPGJyPg0KR2VvcmdlPGJyPg0KPGJy Pg0KPC9kaXY+DQo8ZGl2IGlkPSJzc2MyMTI1MzA4OTc4ODUxOTc5NjcwIj48YnI+DQo8L2Rpdj4N CjxkaXYgaWQ9InNzYzIxMjUzMDg5Nzg4NTE5Nzk2NzAiPkhlbGxvIEdlb3JnZS4uLiBJIGtub3cg YWJvdXQgbm90IGRvaW5nIHRvcCBwb3N0aW5nIGJ1dCB3YXMgZW1haWxpbmcgZnJvbSBteSBwaG9u ZSwgYW5kIGp1c3QgcmVjZW50bHkgbW92ZWQgdG8gQW5kcm9pZC4gSSB0aGluayBJIGFtIHN0aWxs IG5vdCBjb25maWd1cmVkIHJpZ2h0LjwvZGl2Pg0KPGRpdiBpZD0ic3NjMjEyNTMwODk3ODg1MTk3 OTY3MCI+PGJyPg0KPC9kaXY+DQo8ZGl2IGlkPSJzc2MyMTI1MzA4OTc4ODUxOTc5NjcwIj5Tb21l d2hhdCBvcnRob2dvbmFsLCBidXQgYW55IHBhcnRpY3VsYXIgcmVhc29uIHdoeSB0b3AgcG9zdHMg PT0gYmFkLCBvciBqdXN0IGNvbnZlbnRpb24/Jm5ic3A7PC9kaXY+DQo8ZGl2IGlkPSJzc2MyMTI1 MzA4OTc4ODUxOTc5NjcwIj48YnI+DQo8L2Rpdj4NCjxkaXYgaWQ9InNzYzIxMjUzMDg5Nzg4NTE5 Nzk2NzAiPkkgd2lsbCB0cnkgYSBmZXcgc2NlbmFyaW9zIGFuZCByZXBvcnQgYmFjay4gSSBkbyBu b3QgYmVsaWV2ZSBJIGhhdmUgbG9uZyBjcCBpbnRlcnZhbHMgYW5kIEkgZG8gbm90IGJlbGlldmUg dGhlIHdpbmRvd3MgbWFjaGluZSBzaHV0cyBkb3duIGZhc3RlciB0aGFuICdub3JtYWwnPC9kaXY+ DQo8ZGl2IGlkPSJzc2MyMTI1MzA4OTc4ODUxOTc5NjcwIj48YnI+DQo8L2Rpdj4NCjxkaXYgaWQ9 InNzYzIxMjUzMDg5Nzg4NTE5Nzk2NzAiPkZpbmFsbHksIG15IHRydWUgcXVlc3Rpb24gd2FzIHdo ZXRoZXIgUG9zdGdyZXMgd291bGQgc3VwcG9ydCBzb21ldGhpbmcgbGlrZSB3b3JtIHdpdGggdGhl IHBlcmZvcm1hbmNlIGJlbmVmaXRzIG9mIFVOTE9HR0VELCBidXQgbm90IHRoZSBpbmNvbnZlbmll bmNlcyBvZiBhdXRvIHRydW5jYXRlcy48L2Rpdj4NCjxkaXYgaWQ9InNzYzIxMjUzMDg5Nzg4NTE5 Nzk2NzAiPjxicj4NCjwvZGl2Pg0KPGRpdiBpZD0ic3NjMjEyNTMwODk3ODg1MTk3OTY3MCI+VGhh bmtzLjwvZGl2Pg0KPCEtLWVuZCBvZiBfb3JpZ2luYWxDb250ZW50IC0tPjwvZGl2Pg0KPC9ib2R5 Pg0KPC9odG1sPg0K --_000_BN4PR15MB05477F51C9D6D8B45178F64585880BN4PR15MB0547namp_-- From gneuner2@comcast.net Thu Aug 10 22:28:37 2017 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dfvyS-0004k3-6Q for pgsql-performance@arkaria.postgresql.org; Thu, 10 Aug 2017 22:30:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dfvyR-0002My-7p for pgsql-performance@arkaria.postgresql.org; Thu, 10 Aug 2017 22:30:39 +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 1dfvwe-0007dT-24 for pgsql-performance@postgresql.org; Thu, 10 Aug 2017 22:28:48 +0000 Received: from resqmta-ch2-06v.sys.comcast.net ([2001:558:fe21:29:69:252:207:38]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfvwZ-0007fC-TO for pgsql-performance@postgresql.org; Thu, 10 Aug 2017 22:28:46 +0000 Received: from resomta-ch2-03v.sys.comcast.net ([69.252.207.99]) by resqmta-ch2-06v.sys.comcast.net with ESMTP id fvwMdYH5ocp5xfvwXdaK2a; Thu, 10 Aug 2017 22:28:41 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=q20161114; t=1502404121; bh=Yqpx/fxxi3USi0wXkbFb4AAIf2zJVTHo6riKF5CLnug=; h=Received:Received:Subject:To:From:Message-ID:Date:MIME-Version: Content-Type; b=i5Rumpo+5x+hl3a1YX2/H32JyOKWEgP2DCnkjUfPHALhSEmE2skyERqa8YZ5kSYQ4 Occ4KDuuTmPz/Gfy4aSZI4t0bfmNYiBID90IymDzwzK6aWvMHo+on52NLLy7fsU1uq 7hwiDK9gtiwyGrRpKtiRApomg7ArsnKHBHBM+Xas9YzdFSG5C0vk1zaUffruVneX6R RIU1bCGoHQNtbhtA8qix05DBFkOHAk5+VnmBV9D2vK295veVJRmNsVWU0OWqUXcmrZ p1kcSbC/Nrk5KRWeOBoHsPXv4ad2IPu1cVKoATCKE+MNXQexugJfFv/wxoo82sqg/n Wt0qvo7A2Jw8g== Received: from [192.168.0.67] ([73.68.146.26]) by resomta-ch2-03v.sys.comcast.net with SMTP id fvwWdSgQh1HNEfvwWdQMWg; Thu, 10 Aug 2017 22:28:41 +0000 Subject: Re: Unlogged tables To: "ldh@laurent-hasson.com" References: <314ce53a-ee57-0be3-7815-f0e99fc0a6e4@comcast.net> <6634901f-8d4e-023a-283f-5230168e85ff@comcast.net> From: George Neuner Cc: pgsql-performance@postgresql.org Message-ID: Date: Thu, 10 Aug 2017 18:28:37 -0400 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.2.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A129A810D5D617F62AACC4AA" Content-Language: en-US X-CMAE-Envelope: MS4wfF66bfRv4ey+IT6O9Tt8eDv2wH13u9/8I+Yjvi89MrJRnIwPunRaJHQ3eAQYftT1mVJMYv+felSou/cGfBy2zyIQKQ7MNZhMhNoPH9iBqS/jB2JGAUiS +nra84orBWZWTcj3UvNFHD3EW/1zV5n/+W6KCQcwsKRbBCw4X2VKkzNewy7msq8PffBooa9dBtX19DF8NwvnJuEM5CFVER8AZv7SNIl9s2AF7Fde3WUocRvf 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 This is a multi-part message in MIME format. --------------A129A810D5D617F62AACC4AA Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit On 8/10/2017 1:29 AM, ldh@laurent-hasson.com wrote: > Hello George... I know about not doing top posting but was emailing > from my phone, and just recently moved to Android. I think I am still > not configured right. > > Somewhat orthogonal, but any particular reason why top posts == bad, > or just convention? The standard joke reply is: Because it messes up the order in which people normally read. > Why is top-posting such a bad thing? >> Top-posting. >>> What is the most annoying thing in e-mail? It is just convention, but with a good reason: most posts in groups are part of a discussion, and it's hard to follow a discussion when replies are far from the comment or question that provoked them. The convention for discussion is "interleaved" style. The email top posting convention serves a different purpose: to preserve a record of the communication. Polite people often use a mix of styles: copying the [latest portion of the ] quoted message to the top and replying to it inline (as with a discussion). see https://en.wikipedia.org/wiki/Posting_style Then too, there is the issue of editing. With an email, typically only a handful of people will receive it. With a public group or mailing list, all of the participants - perhaps thousands - will receive the post. When lots of people in a popular discussion quote the entire message, it quickly grows to an unwieldy size and eventually will be rejected by the servers. The polite thing when replying is to edit the original message to include just information relevant to your reply, and then reply inline. Leave archiving of the discussion to the servers. > I will try a few scenarios and report back. I do not believe I have > long cp intervals and I do not believe the windows machine shuts down > faster than 'normal' Your problem still may be related to the shutdown delay. The way it works is: Windows sends a shutdown message to the service, and the service replies with an estimate of how long it will take to stop. Until the service terminates, Windows waits and periodically polls the service asking for its progress. Windows continues to wait until the service process either terminates, or until the system configured "drop-dead" timeout occurs, at which time Windows forcibly kills the service and continues with the shutdown. The problem is that Postgresql is not a single process: pg_ctl spawns a bunch of children. Looking further at the source, I believe pg_ctl is waiting for the children to terminate before stopping itself - but it is NOT responding to Windows progress messages, so Windows has no idea whether it is making headway or needs more time to complete. Windows has no idea that those other processes are connected to the Postgresql service, so if it times out and kills pg_ctl, it assumes it is done with Postgresql. The other processes then may be killed whether or not they are finished. > Finally, my true question was whether Postgres would support something > like worm with the performance benefits of UNLOGGED, but not the > inconveniences of auto truncates. I saw some of the other responses re: that issue. As I mentioned previously, an unlogged table will be truncated on startup if it is dirty - i.e. there were any updates that haven't survived at least one checkpoint. The only thing you could try to do is force a checkpoint immediately following an unlogged table write. But that is expensive performance wise and is not encouraged. https://www.postgresql.org/docs/current/static/sql-checkpoint.html George --------------A129A810D5D617F62AACC4AA Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit

On 8/10/2017 1:29 AM, ldh@laurent-hasson.com wrote:
Hello George... I know about not doing top posting but was emailing from my phone, and just recently moved to Android. I think I am still not configured right.

Somewhat orthogonal, but any particular reason why top posts == bad, or just convention?

The standard joke reply is:

   Because it messes up the order in which people normally read.
   > Why is top-posting such a bad thing? 
   >> Top-posting.
   >>> What is the most annoying thing in e-mail?

<grin>

It is just convention, but with a good reason:  most posts in groups are part of a discussion, and it's hard to follow a discussion when replies are far from the comment or question that provoked them.   The convention for discussion is "interleaved" style.

The email top posting convention serves a different purpose: to preserve a record of the communication.  Polite people often use a mix of styles:  copying the [latest portion of the ] quoted message to the top and replying to it inline (as with a discussion).

see https://en.wikipedia.org/wiki/Posting_style


Then too, there is the issue of editing.  With an email, typically only a handful of people will receive it.  With a public group or mailing list, all of the participants  - perhaps thousands - will receive the post.  When lots of people in a popular discussion quote the entire message, it quickly grows to an unwieldy size and eventually will be rejected by the servers.

The polite thing when replying is to edit the original message to include just information relevant to your reply, and then reply inline.  Leave archiving of the discussion to the servers.


I will try a few scenarios and report back. I do not believe I have long cp intervals and I do not believe the windows machine shuts down faster than 'normal'

Your problem still may be related to the shutdown delay. 

The way it works is: Windows sends a shutdown message to the service, and the service replies with an estimate of how long it will take to stop.  Until the service terminates, Windows waits and periodically polls the service asking for its progress.  Windows continues to wait until the service process either terminates, or until the system configured "drop-dead" timeout occurs, at which time Windows forcibly kills the service and continues with the shutdown.

The problem is that Postgresql is not a single process: pg_ctl spawns a bunch of children.  Looking further at the source, I believe pg_ctl is waiting for the children to terminate before stopping itself - but it is NOT responding to Windows progress messages, so Windows has no idea whether it is making headway or needs more time to complete. 

Windows has no idea that those other processes are connected to the Postgresql service, so if it times out and kills pg_ctl, it assumes it is done with Postgresql.  The other processes then may be killed whether or not they are finished.


Finally, my true question was whether Postgres would support something like worm with the performance benefits of UNLOGGED, but not the inconveniences of auto truncates.

I saw some of the other responses re: that issue. 

As I mentioned previously, an unlogged table will be truncated on startup if it is dirty - i.e. there were any updates that haven't survived at least one checkpoint.  The only thing you could try to do is force a checkpoint immediately following an unlogged table write.  But that is expensive performance wise and is not encouraged.

https://www.postgresql.org/docs/current/static/sql-checkpoint.html

George

--------------A129A810D5D617F62AACC4AA-- From gneuner2@comcast.net Thu Aug 10 22:52:46 2017 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dfwK9-0006SG-Rb for pgsql-performance@arkaria.postgresql.org; Thu, 10 Aug 2017 22:53:05 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dfwK9-0006cd-D5 for pgsql-performance@arkaria.postgresql.org; Thu, 10 Aug 2017 22:53:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfwK7-0006cE-Nc for pgsql-performance@postgresql.org; Thu, 10 Aug 2017 22:53:03 +0000 Received: from resqmta-po-12v.sys.comcast.net ([2001:558:fe16:19:96:114:154:171]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dfwK0-0003Zg-Ph for pgsql-performance@postgresql.org; Thu, 10 Aug 2017 22:53:02 +0000 Received: from resomta-po-05v.sys.comcast.net ([96.114.154.229]) by resqmta-po-12v.sys.comcast.net with ESMTP id fwJbdxxTDIhT4fwJtdBI8f; Thu, 10 Aug 2017 22:52:49 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=q20161114; t=1502405569; bh=fXzXM4itfZavXy2VcGkep3MgtaoVqCMG0OjY4RGsj64=; h=Received:Received:Subject:To:From:Message-ID:Date:MIME-Version: Content-Type; b=JdvRjkBqt5HDWw+yDVrPpudVSYWwS86cKvlSK6swOl5FQD0RopVZnBDeZtRSig3nD /z8BS4VUIP9ggDGeDYORTApWJ/EdSmWPr2JYckaNzYbHvmZwycnhKY6StKprJdp/nF SoH4/ypBJKM8/xAnhXSYJ5e3MDgGsZ8KVvy/EsGhrB8cvU465y7oN5Z5eXiBq6juVK UKR/GEXHe4wuWR7JP21WJqpazTGnFWVNWyi9W0aJ30IpWsjAud0kuDmYQoePpDEvty cr/KwEk7ntkkDoP+3l/HgG6bcbyy2i9/jwKCCPc5fCU+La1jfMBj6d5UnEevsVw1rf kSgyA8ipbSgAw== Received: from [192.168.0.67] ([73.68.146.26]) by resomta-po-05v.sys.comcast.net with SMTP id fwJsdCGhfi3ocfwJtdX5wl; Thu, 10 Aug 2017 22:52:49 +0000 Subject: Re: Unlogged tables To: "ldh@laurent-hasson.com" References: <314ce53a-ee57-0be3-7815-f0e99fc0a6e4@comcast.net> <6634901f-8d4e-023a-283f-5230168e85ff@comcast.net> Cc: pgsql-performance@postgresql.org From: George Neuner Message-ID: <803241c0-fb96-2f41-4648-d75aefab2091@comcast.net> Date: Thu, 10 Aug 2017 18:52:46 -0400 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.2.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------C7D0637A1A8C6EB966148700" Content-Language: en-US X-CMAE-Envelope: MS4wfEb8To5B9lELNy6qmzNraSgw2xfxNBM9v/Xcpfz8GzoP6D0AUIVaBk8umEWgiCSe78/PwVYiKVzDF2IRvKvTHic+HSuUpAihesOVgwflMbyU3NPH1w2A 6hsx6EX+Gam9f6lgYfoTE75yEAIM1nD/9FfiL2QPorSeqV0KUWeQQvU0Tnh/hJgRa79kTf8mdRXG2OoSuXUmZFzyWCKZbNE/+vBBKlzSX5aZ46rx7PgOUvGO 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 This is a multi-part message in MIME format. --------------C7D0637A1A8C6EB966148700 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit On 8/10/2017 1:29 AM, ldh@laurent-hasson.com wrote: > > Finally, my true question was whether Postgres would support something > like worm with the performance benefits of UNLOGGED, but not the > inconveniences of auto truncates. > If you can live with the limitations, one other thing you might try is storing WORM data in the filesystem and accessing it via file_fdw. https://www.postgresql.org/docs/current/static/file-fdw.html There are a lot of downsides to this: file_fdw tables are read-only, so you have to update the external file through some other means. Also, I've never used file_fdw, so I'm not sure whether you can create indexes on the tables - and even if you can, you would need to manually recreate the indexes periodically because Postgresql won't see your updates. George --------------C7D0637A1A8C6EB966148700 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit

On 8/10/2017 1:29 AM, ldh@laurent-hasson.com wrote:

Finally, my true question was whether Postgres would support something like worm with the performance benefits of UNLOGGED, but not the inconveniences of auto truncates.


If you can live with the limitations, one other thing you might try is storing WORM data in the filesystem and accessing it via file_fdw.
https://www.postgresql.org/docs/current/static/file-fdw.html

There are a lot of downsides to this:  file_fdw tables are read-only, so you have to update the external file through some other means.  Also, I've never used file_fdw, so I'm not sure whether you can create indexes on the tables - and even if you can, you would need to manually recreate the indexes periodically because Postgresql won't see your updates.

George

--------------C7D0637A1A8C6EB966148700--