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