Best practice for "recycling" rows on PostgreSQL (UPDATE vs INSERT + DELETE) I searched responses on &quot;UPDATE vs INSERT + DELETE&quot; in general terms and the consensus is UPDATE is faster, as expected.
My question is centered on a somewhat niche use case. Let's say you have a table of fairly high-frequency ephemeral data. For example, modelling notifications on a social network, those are created very often and you always see the latest N.
Would it make sense to keep the latest N (say 20) and when one comes in, the Nth row is UPDATED into the new one (ID included). Rather than inserting a new one and then trimming old rows (maybe in bulk on an interval)?
Any thoughts?
Postgres is MVCC. Every write produces a new row-version irrespective of whether the operation is insert or update. So I would not expect the proposed ring buffer style to give performance benefits.
With single-value systems, and fixed length rows I would expect faster performance. I'd like to see some thorough testing before I believed it was worthwhile, though.