UNLOGGED TABLE's in postgresql.

[skatkov]

It makes a lot of sense to turn cache table into UNLOGGED TABLE for postgresql. Or provide an option to do so.

UNLOGGED TABLES turn off WAL completely. Writting into table gets a significant boost and data sizes will be smaller and will require less vacuum.

But data is easily lost, can't be synched with replicas or will not be backed. All of these drawbacks don't seem like a big problem if youre dealing with cache.

https://www.crunchydata.com/blog/postgresl-unlogged-tables

binlog in mysql functions the same, but it is not a table-specific setting.

Should we implement support for UNLOGGED TABLES? It's easy to turn on or off, at least in PG.

[fabriziomello]

Unlogged tables will make you writes faster but it don't means less vacuum, what happens is the vacuum impact is not too big because the changes are not written to the WAL.

There are other tricks you can do in Postgres to reduce the WAL impact like turning off the synchronous_commit, increasing the commit_delay to a value greater than zero... and on COW filesystems you can turn of the full_page_writes.