I wouldn't expect this to be a likely approach used by Lemmy anytime soon. Applications that run at massive scale (like Facebook and reddit) use a wide variety of storage engines, whereas Lemmy uses Postgres for almost everything. When running at massive scale, read-only storage engines are cheaper and easier to deal with than read/write storage engines... so it makes sense for reddit to to move old-data (which naturally changes infrequently) to read-only storage (making any writes at all impossible) to save a little money on storage even if the machinery for doing that makes reddit more complicated to run for the engineers that work there.

For Lemmy, it's probably more important to optimize for being easy to run (so more admins run Lemmy instances), even if it makes the storage a little more expensive. Postgres tables can definitely scale to hundreds of millions of rows, and there's little overhead in allowing the old ones to be written to as long as they're in Postgres.

Now, someday... it's possible that the number of posts in a single larger Lemmy instance become untenable for even a single very large PG instance to run, and I'm that far future maybe it makes sense to work on tiered storage for Lemmy. But there are other strategies for scaling PG to big datasets that are at least as likely in that scenario. So I'm not predicting Lemmy will employ cold storage anytime soon.