1

I use UNLOGGED tables for a few very large tables in a data warehouse style application.

Until recently, I understood UNLOGGED to mean “won’t write to the WAL” - which in turn means that recent changes maybe lost on a process crash / unclean termination, and that there will be no replication

Maybe I’m misunderstanding the language in the documentation (or maybe I’m not) but when I read the documentation recently, I understood it to mean the entire table will be truncated on an unclean exit. Is that right?

The question is, on an unclean exit will the entire table will be TRUNCATEd (per the meaning of TRUNCATE in PostgreSQL) or does it mean truncated as in the everyday use- the end will abruptly terminate, only the most recent rows lost, due to there being no WAL

Surely it myst be the latter? Being completely TRUNCATEd makes no sense to me; if I have an UNLOGGED table with a year’s worth of data, and today the postgres process goes down, why would it destroy the entire contents of the table?

Or am I misunderstanding and it’s only recent changes (that would normally be restorable via WAL or similarly, by a spare) that would be lost?

The blurb from the official PostgreSQL documentation is as follows, the bit about truncation us what I’m referring to:

If specified, the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log (see Chapter 30), which makes them considerably faster than ordinary tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown.

I’m not sure how I never noticed this language but it has me somewhat alarmed. Losing the most recent few days worth of data isn’t a problem for my use, but to have to restore the entire table (~60GB worth of data per-table) from the backups I keep (non-WAL backups, obviously) due to a simple process crash is alarming

0

1 Answer 1

1

Yes, the table will be empty after crash recovery. There is no other way to ensure consistency.

One thing you can do is to regularly backup the unlogged table with pg_dump.

1
  • Yikes, thank you for confirming
    – adam
    Apr 8, 2023 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.