From: | "ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)com> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Unlogged tables |
Date: | 2017-08-09 03:20:19 |
Message-ID: | BN4PR15MB0547B6736C684F14E4E33BDF858B0@BN4PR15MB0547.namprd15.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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...).
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. 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.
Is there a way to get my cake and eat it too?
Thank you,
Laurent Hasson
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2017-08-09 10:39:12 | Re: Unlogged tables |
Previous Message | Bhaskar Annamalai | 2017-08-04 06:09:33 | Slow queries after db upgrade to 9.6 |