Postgres Unlogged Tables
crunchydata.com
crunchydata.com
This omits a crucial fact: your data is not gone until Postgresql has gone through its startup recovery phase. If you really need to recover unlogged table data (to which every proper database administrator will rightly say "then WhyTH did you make it unlogged"), you should capture the table files before starting the database server again. And then pay through the nose for a data recovery specialist.
However, a crash will truncate it.
So this isn't exactly true. A crash recovery will truncate it.
Any further reading you can suggest?
from https://www.postgresql.org/docs/15/sql-createtable.html#SQL-...:
> Data written to unlogged tables is not written to the write-ahead log (see Chapter 30), which makes them considerably faster than ordinary tables
and from https://www.postgresql.org/docs/15/glossary.html#GLOSSARY-UN...:
> The property of certain relations that the changes to them are not reflected in the WAL. This disables replication and crash recovery for these relations.
Which both say nothing about the normal data files underlying unlogged tables, so none of what I wrote can be found in the official docs (or maybe it can, just not by me ;)
However, there is also this page from the postgres developer wiki: https://wiki.postgresql.org/wiki/Future_of_storage which does say that pure in-memory tables are not supported by Postgres:
> it would be nice to have in-memory tables if they would perform faster. Somebody could object that PostgreSQL is on-disk database which shouldn't utilize in-memory storage. But users would be interested in such storage engine if it would give serious performance advantages.
Damn you autocorrect.
1. The Buffer Pool (memory for pages in the database)
2. The Log Manager
3. The Transaction Manager
4. The Storage Manager (read/write to disk)
5. Accessor Methods (interpret page bytes as e.g. Heap or BTree)
This is abstract, not particular to Postgres, which doesn't have exact such names for all above things.
Normally, when the Transaction Manager creates transactions that modify records/tuples (using the Accessor Methods) these actions need to be persisted via the Log Manager to the WAL.
The pages of memory backing these records come from the Buffer Pool, and the Buffer Pool must also log certain actions.
Before the Buffer Pool can flush any modified page to disk, the changes up to that page must have been persisted by the WAL via the Storage Manager as well.
When you create unlogged tables, none of this happens, and when you modify records there's no trail.
There's an attempt at tl;dr'ing WAL
I am not an expert (Anarazel is)
The documentation on feature says it is only truncated on crash.
Where did you read it is not saved on disk?
> A crash recovery will truncate it.
That's so nitpicky it has lesser nitpicks living upon it.
Good news, they're not criticizing the word "safe", they're criticizing the word "gone".
> That's so nitpicky it has lesser nitpicks living upon it.
It says "100% gone" when you pull the plug.
Pointing out that is merely queued for deletion is a big deal, not a nitpick.
ok, so before deletion occurs, 1) how are you going to get it back and 2) how do you know how much you have/haven't lost (that transactional integrity I mentioned)?
How much? Probably most of it. The data is not safe. But it's also not gone.
Edit: put another way, you: "Yep, I think we can get something back". Me: "aaaaaaaaaaaargh! mah payroll!"
In this scenario you already screwed up badly but you can salvage a lot. Very different from there being no hope.
I find it hard to get good performance from large queries with CTEs. Often, it's much easier (and uglier) to just split them to multiple steps, create intermediate tables for each step and add necessary indexes.
Temporary tables are then of course even better, since usually you don't want to have these left around. But temporary tables are also unlogged.
Up until Postgres 12, all CTE were materialised before their results were used, effectively making every CTE a temporary table. In Postgres 12 onwards, for side-effect free CTE that are only referenced once, Postgres will take constrains from the parent query, and push them into the CTE, reducing the amount of data they need process, and allowing better use of indexes.
OOI have you tried converting your CTEs into sub-queries as an experiment to see if they’re faster? Even in Postgres 12 onwards, sub-queries and CTEs are treated the same by the query planner, and you can get some surprising differences in performance.
At a certain level of query complexity, no query planner is able to accurately predict the characteristics of sub-queries or CTE, resulting in query plans that are ill-suited to the problem. More often than not, materializing CTEs inside a large query into 2-3 temporary tables results in order-magnitude performance as the database now knows exactly how many rows its dealing with, stats on # of nulls, etc.
My broader point is that CTEs in Postgres are more nuanced than they appear at first blush. They’re frequently presented as simply a method of writing cleaner, more readable SQL. The fact that CTEs receive special treatment by the planner is often missed, along with the fact that two functionally identical queries, one using sub-queries, the other CTEs, can have wildly different performance characteristics.
A second use is importing httpd combined logs and doing some analysis when finding out which chains of http calls caused some kinds of behaviour. When done, the tables get deleted. It allows easy ad hock queries, correlating with monitoring tables, indexing... I used to write some python scripts, but pg did better than expected, and this way of working stuck somehow.
In my understanding, as compared to a multi-row insert, a COPY statement is (1) a lot more efficient to parse/process, (2) uses a special bulk write strategy that avoids churn and large amount of dirty pages in shared buffers.
As compared to multiple single row inserts another benefit is that you're writing a single transaction ID, instead of a lot of individual transaction IDs (causing less freezing work having to be done by autovacuum, etc.)
The initial load might be faster, but you’ll end up repaying that time saving when you turn on logging back on, and Postgres is then forced to read the contents of the table and push it into the WAL anyway. Thereby throwing away any saving you originally made by avoiding the WAL with an unlogged table.
If you’re willing to throw away that functionality, then maybe you could avoid writing the rows to the WAL completely (but it seems Postgres doesn’t support that). But personally I would always be a little suspect of data stored in Postgres that never transited the WAL. It’s a core part of how Postgres provides it durability guarantees, and lots of tools and functionality assume that everything worth protecting gets written to it at least once.
It’s a few years ago so details are blurry. We got daily snapshots and on ingest we created an unlogged temporary table then when complete used INSERT FROM to the destination table if I remember right.
As long as the application handles failure on bulk ingest and you copy the data to a logged table or enable logging on the table once ingested it worked really well.