Subject: Re: Unlogged tables
To: "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>
References: <314ce53a-ee57-0be3-7815-f0e99fc0a6e4@comcast.net>
 <BN4PR15MB0547F5864AD9691E31458FE1858B0@BN4PR15MB0547.namprd15.prod.outlook.com>
 <6634901f-8d4e-023a-283f-5230168e85ff@comcast.net>
 <BN4PR15MB05477F51C9D6D8B45178F64585880@BN4PR15MB0547.namprd15.prod.outlook.com>
Cc: pgsql-performance@postgresql.org
From: George Neuner <gneuner2@comcast.net>
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: 
 <BN4PR15MB05477F51C9D6D8B45178F64585880@BN4PR15MB0547.namprd15.prod.outlook.com>
Content-Type: multipart/alternative;
 boundary="------------C7D0637A1A8C6EB966148700"
Content-Language: en-US
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

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html;
      charset=windows-1252">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <br>
    <br>
    <div class="moz-cite-prefix">On 8/10/2017 1:29 AM,
      <a class="moz-txt-link-abbreviated" href="mailto:ldh@laurent-hasson.com">ldh@laurent-hasson.com</a> wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:BN4PR15MB05477F51C9D6D8B45178F64585880@BN4PR15MB0547.namprd15.prod.outlook.com">
      <meta http-equiv="Content-Type" content="text/html;
        charset=windows-1252">
      <meta http-equiv="Content-Security-Policy" content="script-src
        'self'; img-src * cid: data:;">
      <div name="BB10" dir="auto" style="background-image: initial;
        line-height: initial; outline: none;" contenteditable="false"><br>
        <div id="ssc2125308978851979670">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.</div>
        <br>
      </div>
    </blockquote>
    <br>
    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.<br>
    <a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/current/static/file-fdw.html">https://www.postgresql.org/docs/current/static/file-fdw.html</a><br>
    <br>
    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.<br>
    <br>
    George<br>
    <br>
  </body>
</html>

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