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.

[Dandush03]

Fun fact about PostgreSQL tuples: PostgreSQL never updates a row in place — every UPDATE actually creates a new tuple and marks the previous one as dead, so even when Solid Cache updates the same key over and over again it is constantly inserting new tuple versions and relying on VACUUM to clean up later. That makes Solid Cache a very UPDATE-heavy, high-churn workload that behaves much closer to an ephemeral store than a normal OLTP table, yet today it is treated like a regular durable table. If the schema and update path were explicitly HOT-friendly (only the lookup columns indexed and the frequently updated columns left unindexed), PostgreSQL could use HOT updates and avoid touching indexes on every write, which would significantly reduce index bloat and write amplification. On top of that, for a cache store durability across crashes is usually not required, so making the Solid Cache tables UNLOGGED (or at least allowing this as a configuration option) would avoid generating WAL for cache writes and would drastically reduce IO and replication pressure. In practice, because Solid Cache updates the same keys very frequently, dead tuples and autovacuum pressure grow fast and WAL volume becomes unnecessarily high for data that can always be rebuilt, so optimizing for HOT updates and allowing UNLOGGED tables would make Solid Cache much more aligned with how PostgreSQL actually behaves under heavy cache-like workloads.