[raffraffraff]

Isn't this extremely dangerous? Disk write caches aren't used most of the time, except on battery backed HBAs. And databases are typically configured to use O_DIRECT for a reason: COMMITs are supposed to be durable. We had this fight at a previous company when an engineer based database server hardware recommendation on a dangerously misconfigured database server, and did not consider the effect of caches. As soon as a safe configuration was used in production, performance dropped off a cliff, particularly on random IO. So the question we had to ask was: do you want to trade durability for performance? Or do you now have to carve up your databases into shards that fit the IO performance characteristics of the badly chosen servers you purchased, and waste rack space and CPU power?

[barrkel]

Parent is talking about temporary tables. Those are normally only live for the duration of a transaction (well, session, but in practice if you're using temporary tables across multiple transactions you have a logical application-level transaction which needs to be able to handle failure part-way through). After your transaction the writes to non-temporary tables should be persistent.

Postgres temp tables on ramdisk are a problem for a different reason, the WAL, as pointed to by a sibling comment.

[natmaka]

> Postgres temp tables on ramdisk are a problem for a different reason, the WAL, as pointed to by a sibling comment

TEMPORARY tables are UNLOGGED, and therefore they aren't WALed

[raffraffraff]

Gotcha, somehow missed that. Yeah, tmp tables on disks are painful and I've made the same optimization on MySQL whenever it wasn't possible to eliminate the need to tmp tables by refactoring SQL.