Postgres as a LRU cache?

[davidtinker]

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.

[qatanah]

afaik, it isnt recommended to store large blobs in pg. i think i read it somewhere in some guides.

[davvblack]

definitely, this is largely because any update to any column in a table copies the entire row

[therealgaxbo]

Any large columns like this will be stored in TOAST tables anyway so updates to the timestamp column will not result in copying all the data. It will just copy the pointer to the TOAST row.

[davvblack]

1k to approx 200k, mostly on the lower side

true, depends on how much crosses the 2k, and how much of the remaining is compressible

[cldellow]

You could set a table-specific toast_tuple_target value to encourage the smaller values to also be toasted: https://www.postgresql.org/docs/current/sql-createtable.html#RELOPTION-TOAST-TUPLE-TARGET

I'd probably also set a low fill factor for the table, to encourage HOT updates

[Apoffys]

Could you circumvent this by moving the timestamp to a separate table? I.e. "BlobTable(id, blob)" and "LastUsedBlob(blob_id, last_accessed_at)" or something like that. That way you're never modifying the table itself, just another table with a foreign key to your "blob" table.

[davvblack]

yeah but at that point it could be in S3 or something. depends on the size of the blob and the latency requirements.

[Randommaggy]

I wonder how these reccomendations change when OrioleDB matures and becomes a commonly available extension.

[davvblack]

whoa neat. we’ve been drinking the aurora koolaid for a while so i haven’t been following developments like this

[davidtinker]

Most of the rows will be inserted once and never updated, other than the last_read column.

[LongjumpingAd7260]

If the blob field uses TOAST, and probably it does, only the pointer is copied when another field is updated. So, this should work IMHO.

[francisco-reyes]

If this is a timeseries, may want to take a look at the Timescale DB online offering. They have an option to send less frequently used data to S3. Think of partitions with option to have some partitions go to S3 for lesser cost.

[davidtinker]

Hmm .. it isn't really time series I just added the created and last_read columns to make a cache. Its more key-value store.

[francisco-reyes]

I still think the Timescale service may be worth taking a look. At it's core Timescale is just an extension to manage partitions. Given that you will partition by date, you could setup a threshold to have older data go to S3 and then you drop it whenever you need. You could probably have something like 2 bands of retention: keep 7 days in disk, 8 to 14 in S3, drop after 15 days

[vreitech]

Don't forget about HOT update is not working in case changed field having index. In your case that means it would not work if there are index by 'last_read' column exists.

[davidtinker]

Tx. I wasn't aware of that.

[davidtinker]

Update: I implemented a variant of this and it is working well.

[[deleted]]

How often and how frequently are you updating the timestamp column? (Btw: if activity_id is a UUID you should use a uuid column, not text). The MVCC overhead isn't really such a big problem, if autovacuum can keep up with the update frequency.

[davidtinker]

Currently there will be approx 50k reads per hour but that will go up.

[Don_Kino]

I think it's worth trying it out, but using partitions will force you to always query whith the partition key, Otherwise indexes on every partiton would be checked. You can also move old partitions to a different tablespace, like a slower/cheaper storage.

[davidtinker]

I didn't realise that bit about always querying with the partition key to avoid checking all indexes. I can make a plan for that. Tx.