On Wed, Jan 31, 2018 at 10:52:38AM -0600, Alexander Stoddard wrote: > If a table is set to unlogged is it inherently non-durable? That, is any > crash or unsafe shutdown _must_ result in truncation upon recovery? Yes, they are designed like that. Upon recovery all unlogged tables are re-initialized. What happens internally is that their init fork files gets copied to become the one in use. > I can imagine a table that is bulk loaded in a warehousing scenario and > then sitting statically could be safe, but maybe the question becomes how > could the system know it is unchanged if it isn't logged... > > Would a correct solution be to bulk load into an unlogged table and then > (fairly cheaply?) set the table back to being logged? Note that switching a table from unlogged to logged using ALTER TABLE generates a large amount of WAL, as you need to make this table's data consistent it is necessary to copy a full image of it. > If I my mental model is correct the table would be liable to being lost to > a crash during loading but once set back to logged it is durable. Is that > correct? Yes, ALTER TABLE makes that possible but be careful about the WAL generated because of the table rewrite. > Is it the case that setting a large table to logged status after loading it > unlogged is a reasonably cheap operation? If you load a logged table the cost would be the same as switching it from unlogged to logged. So what matters is how much operation is generated between the time you created the table, like random writes on it. For an initial load on a very large table, you could reduce wal_level temporarily to minimal, and make the WAL generated less painful. For a one-time load this can matter. -- Michael
Attachment:
signature.asc
Description: PGP signature
