Is a Postgresql UNLOGGED table completely lost on process crash?
1
vote
1
answer
853
views
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
Asked by mzpq
(165 rep)
Apr 5, 2023, 11:52 AM
Last activity: Apr 6, 2023, 07:01 AM
Last activity: Apr 6, 2023, 07:01 AM