Has anyone tried using Postgres as a LRU cache for data in cloud blob
storage? I have a lot of data in Cassandra and want to move that to
Backblaze (like S3 but cheaper and less available). Cassandra is working
well but I have to add new nodes more frequently that I would like. The
older data is rarely needed but newer and recently accessed records need to
be local. Everything is on leased servers at Hetzner.
I was thinking that I could use a table partitioned by created date (one
partition per day) and drop the oldest partition(s) when it starts getting
full. This wouldn’t be LRU but would probably work for my application.
create table activity_stream (activity_id text primary key,created
timestamp,last_read timestamp,data bytea -- 1k to approx 200k,
mostly on the lower side)-- Partition would use created to split by day
I could update last_read whenever a record is read. If I use a fill factor
of less than 100 and only update last_read would I avoid dead tuple
problems with HOT tuple optimisation?Then before dropping the oldest
partition I can update created = last_read on recently read records to move
them to other partitions making it LRU. This will be a small percentage of
the data.
Any suggestions / other approaches? Thanks.
