From: "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>
To: "pgsql-performance@postgresql.org" <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: 
 <BN4PR15MB0547B6736C684F14E4E33BDF858B0@BN4PR15MB0547.namprd15.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
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-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: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<style type=3D"text/css" style=3D"display:none;"><!-- P {margin-top:0;margi=
n-bottom:0;} --></style>
</head>
<body dir=3D"ltr">
<div id=3D"divtagdefaultwrapper" style=3D"font-size:12pt;color:#000000;font=
-family:Calibri,Helvetica,sans-serif;" dir=3D"ltr">
<p>Hello,</p>
<p><br>
</p>
<p>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.</p>
<p><br>
</p>
<p>However, our scenario is truly a set of tables with 100's of millions of=
 rows that are effectively WORMs: we write them&nbsp;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...).<br>
</p>
<p><br>
</p>
<p>I'd like to have the performance improvement during a initial batch inse=
rt, and then make sure the table remains after &quot;unclean&quot; 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.</p>
<p><br>
</p>
<p><span>Is there a way to get my cake and eat it too?</span></p>
<p><span><br>
</span></p>
<p><span>Thank you,</span></p>
<p><span>Laurent Hasson</span><br>
</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
</div>
</body>
</html>

--_000_BN4PR15MB0547B6736C684F14E4E33BDF858B0BN4PR15MB0547namp_--