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.

[Dandush03]

@dhh , @djmb or @rosa thoughts here?

[djmb]

@Dandush03 - is these theoretical observations or are they based on something you've seen in production?

In general I would be happier recommending @fabriziomello's suggestions than using UNLOGGED tables as that seems safer, though only where you have a dedicated Solid Cache database.

Solid Cache is designed to allow you to have very large and durable caches, some applications needs that is not something we know for sure, but I don't think UNLOGGED tables would be a good default as it means losing the cache if the database does not shut down gracefully.

You suggest that Solid Cache will be update heavy. This is very application dependent, some will be update heavy, but I'd expect (maybe I'm wrong) that most are insert heavy with a much smaller number of updates.

Regarding HOT updates, the byte_size value is what prevent these and that is required for the cache expiry algorithm - have you seen that this is an actual problem though?

[Dandush03]

@djmb 👍 agreed..... I’m not suggesting UNLOGGED as a default. Logged tables are the right baseline for Solid Cache. My intent was only an opt-in knob for specific deployments.

What I wanted to highlight is the workload shape Rails caching often creates.

Rails encourages Rails.cache.fetch(key, expires_in: …):

Rails.cache.fetch(key, expires_in: 15.minutes) { recompute_value }

While fetch doesn’t rewrite on every read, hot keys are periodically recomputed and rewritten on expiry. That yields:

• stable lookup key
• mutable payload
• repeated rewrites of the same logical key

So even with a stable index (key_hash), you still get UPDATE-like churn on a concentrated keyset.

The MVCC effects here (dead tuples, vacuum dependence) aren’t theoretical..... they’re simply how PostgreSQL handles any UPDATE-heavy pattern: each rewrite creates a new tuple version and leaves the old one dead until vacuum.

Given that some cache deployments explicitly accept loss on crash and don’t replicate cache tables, an opt-in PG-only UNLOGGED mode could reduce WAL/vacuum pressure in those cases; but again, fully agree it shouldn’t be the default.